[Home] [Help]
PACKAGE BODY: APPS.CSI_FA_INSTANCE_GRP
Source
1 PACKAGE BODY csi_fa_instance_grp AS
2 /* $Header: csigfaib.pls 120.15 2006/09/19 20:13:18 brmanesh noship $ */
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 BEGIN
421
422 x_return_status := fnd_api.g_ret_sts_success;
423
424 savepoint fa_grp_create_instance;
425
426 -- validate mandatory fields
427
428 -- inventory item id
429 csi_item_instance_vld_pvt.check_reqd_param_num(
430 p_number => p_instance_rec.inventory_item_id,
431 p_param_name => 'p_instance_rec.inventory_item_id',
432 p_api_name => 'csi_fa_instance_grp.create_item_instance');
433
434 -- validation organization id
435 csi_item_instance_vld_pvt.check_reqd_param_num(
436 p_number => p_instance_rec.vld_organization_id,
437 p_param_name => 'p_instance_rec.vld_organization_id',
438 p_api_name => 'csi_fa_instance_grp.create_item_instance');
439
440 -- location type code
441 csi_item_instance_vld_pvt.check_reqd_param_char(
442 p_variable => p_instance_rec.location_type_code,
443 p_param_name => 'p_instance_re.location_type_code',
444 p_api_name => 'csi_fa_instance_grp.create_item_instance');
445
446 -- location id
447 csi_item_instance_vld_pvt.check_reqd_param_num(
448 p_number => p_instance_rec.location_id,
449 p_param_name => 'p_instance_rec.location_id',
450 p_api_name => 'csi_fa_instance_grp.create_item_instance');
451
452 -- source_transaction_date in csi_transactions
453 csi_item_instance_vld_pvt.check_reqd_param_date(
454 p_date => px_csi_txn_rec.source_transaction_date,
455 p_param_name => 'px_csi_txn_rec.source_transaction_date',
456 p_api_name => 'csi_fa_instance_grp.create_item_instance');
457
458 -- transaction_type_id in csi_transactions
459 csi_item_instance_vld_pvt.check_reqd_param_num(
460 p_number => px_csi_txn_rec.transaction_type_id,
461 p_param_name => 'px_csi_txn_rec.transaction_type_id',
462 p_api_name => 'csi_fa_instance_grp.create_item_instance');
463
464 -- derive eam_item_type
465 IF nvl(p_instance_rec.inventory_item_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
466 AND
467 nvl(p_instance_rec.vld_organization_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
468 THEN
469
470 SELECT eam_item_type,
471 serial_number_control_code
472 INTO l_eam_item_type,
473 l_serial_control_code
474 FROM mtl_system_items
475 WHERE inventory_item_id = p_instance_rec.inventory_item_id
476 AND organization_id = p_instance_rec.vld_organization_id;
477
478 IF l_eam_item_type in (1, 3) AND l_serial_control_code <> 1 THEN
479 l_eam_item := TRUE;
480 END IF;
481
482 END IF;
483
484 IF nvl(p_fixed_asset_rec.asset_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
485
486 IF nvl(p_fixed_asset_rec.asset_location_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
487 OR
488 nvl(p_fixed_asset_rec.asset_quantity, fnd_api.g_miss_num) = fnd_api.g_miss_num
489 OR
490 nvl(p_fixed_asset_rec.book_type_code, fnd_api.g_miss_char) = fnd_api.g_miss_char
491 THEN
492
493 derive_fa_missing_values(
494 p_instance_rec => p_instance_rec,
495 p_fixed_asset_rec => p_fixed_asset_rec,
496 x_fa_location_id => l_fa_location_id,
497 x_fa_quantity => l_fa_quantity,
498 x_fa_book_type_code => l_fa_book_type_code,
499 x_return_status => l_return_status);
500
501 IF l_return_status <> fnd_api.g_ret_sts_success THEN
502 RAISE fnd_api.g_exc_error;
503 END IF;
504 END IF;
505 END IF;
506
507 csi_transactions_pvt.create_transaction(
508 p_api_version => 1.0,
509 p_commit => fnd_api.g_false,
510 p_init_msg_list => fnd_api.g_true,
511 p_validation_level => fnd_api.g_valid_level_full,
512 p_success_if_exists_flag => 'Y',
513 p_transaction_rec => px_csi_txn_rec,
514 x_return_status => l_return_status,
515 x_msg_count => l_msg_count,
516 x_msg_data => l_msg_data);
517
518 IF l_return_status <> fnd_api.g_ret_sts_success THEN
519 RAISE fnd_api.g_exc_error;
520 END IF;
521
522 IF p_instance_serial_tbl.COUNT > 0 THEN
523 FOR srl_ind IN p_instance_serial_tbl.FIRST .. p_instance_serial_tbl.LAST
524 LOOP
525
526 g_inst_ind := g_inst_ind + 1;
527
528 l_csi_txn_tbl(g_inst_ind) := px_csi_txn_rec;
529
530 l_instance_tbl(g_inst_ind) := p_instance_rec;
531
532 l_instance_tbl(g_inst_ind).quantity := 1;
533 --l_instance_tbl(g_inst_ind).mfg_serial_number_flag := 'Y';
534
535
536 -- override with serial attributes
537 l_instance_tbl(g_inst_ind).instance_number :=
538 nvl(p_instance_serial_tbl(srl_ind).instance_number, l_miss_char);
539 l_instance_tbl(g_inst_ind).serial_number :=
540 nvl(p_instance_serial_tbl(srl_ind).serial_number, l_miss_char);
541 l_instance_tbl(g_inst_ind).lot_number :=
542 nvl(p_instance_serial_tbl(srl_ind).lot_number, p_instance_rec.lot_number);
543 l_instance_tbl(g_inst_ind).external_reference :=
544 nvl(p_instance_serial_tbl(srl_ind).external_reference, p_instance_rec.external_reference);
545 l_instance_tbl(g_inst_ind).instance_usage_code :=
546 nvl(p_instance_serial_tbl(srl_ind).instance_usage_code, p_instance_rec.instance_usage_code);
547 l_instance_tbl(g_inst_ind).operational_status_code :=
548 nvl(p_instance_serial_tbl(srl_ind).operational_status_code,
549 p_instance_rec.operational_status_code);
550 l_instance_tbl(g_inst_ind).instance_description :=
551 nvl(p_instance_serial_tbl(srl_ind).instance_description, p_instance_rec.instance_description);
552
553 -- override with eam attributes
554 l_instance_tbl(g_inst_ind).asset_criticality_code :=
555 nvl(p_eam_rec.asset_criticality_code, l_miss_char);
556 l_instance_tbl(g_inst_ind).category_id :=
557 nvl(p_eam_rec.category_id, l_miss_num);
558
559
560 IF p_party_tbl.COUNT > 0 THEN
561 FOR pty_ind IN p_party_tbl.FIRST .. p_party_tbl.LAST
562 LOOP
563
564 g_pty_ind := g_pty_ind + 1;
565 l_party_tbl(g_pty_ind) := p_party_tbl(pty_ind);
566 l_party_tbl(g_pty_ind).parent_tbl_index := g_inst_ind;
567
568
569 IF p_party_account_tbl.COUNT > 0 THEN
570 FOR pa_ind IN p_party_account_tbl.FIRST .. p_party_account_tbl.LAST
571 LOOP
572 g_pa_ind := g_pa_ind + 1;
573 l_account_tbl(g_pa_ind) := p_party_account_tbl(pa_ind);
574 l_account_tbl(g_pa_ind).parent_tbl_index := g_pty_ind;
575 END LOOP;
576 END IF;
577
578 END LOOP;
579 END IF;
580
581 IF nvl(p_fixed_asset_rec.asset_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
582 g_ia_ind := g_ia_ind + 1;
583 l_instance_asset_tbl(g_ia_ind).parent_tbl_index := g_inst_ind;
584 l_instance_asset_tbl(g_ia_ind).fa_asset_id := p_fixed_asset_rec.asset_id;
585 l_instance_asset_tbl(g_ia_ind).fa_book_type_code := l_fa_book_type_code;
586 l_instance_asset_tbl(g_ia_ind).fa_location_id := l_fa_location_id;
587 l_instance_asset_tbl(g_ia_ind).asset_quantity := 1; -- for serialized
588 l_instance_asset_tbl(g_ia_ind).update_status := 'IN_SERVICE';
589 l_instance_asset_tbl(g_ia_ind).fa_sync_flag := p_fixed_asset_rec.fa_sync_flag;
590 l_instance_asset_tbl(g_ia_ind).fa_sync_validation_reqd :=
591 nvl(p_fixed_asset_rec.fa_sync_validation_reqd, fnd_api.g_false);
592
593 validate_inst_asset(
594 px_inst_asset_rec => l_instance_asset_tbl(g_ia_ind),
595 x_return_status => l_return_status);
596
597 IF l_return_status <> fnd_api.g_ret_sts_success THEN
598 RAISE fnd_api.g_exc_error;
599 END IF;
600
601 END IF;
602
603 END LOOP;
604 ELSE
605
606 g_inst_ind := g_inst_ind + 1;
607
608 l_csi_txn_tbl(g_inst_ind) := px_csi_txn_rec;
609
610 l_instance_tbl(g_inst_ind) := p_instance_rec;
611
612 -- override with eam attributes
613 l_instance_tbl(g_inst_ind).asset_criticality_code :=
614 nvl(p_eam_rec.asset_criticality_code, l_miss_char);
615 l_instance_tbl(g_inst_ind).category_id :=
616 nvl(p_eam_rec.category_id, l_miss_num);
617
618 IF p_party_tbl.COUNT > 0 THEN
619 FOR pty_ind IN p_party_tbl.FIRST .. p_party_tbl.LAST
620 LOOP
621
622 g_pty_ind := g_pty_ind + 1;
623 l_party_tbl(g_pty_ind) := p_party_tbl(pty_ind);
624 l_party_tbl(g_pty_ind).parent_tbl_index := g_inst_ind;
625
626 IF p_party_account_tbl.COUNT > 0 THEN
627 FOR pa_ind IN p_party_account_tbl.FIRST .. p_party_account_tbl.LAST
628 LOOP
629 g_pa_ind := g_pa_ind + 1;
630 l_account_tbl(g_pa_ind) := p_party_account_tbl(pa_ind);
631 l_account_tbl(g_pa_ind).parent_tbl_index := g_pty_ind;
632 END LOOP;
633 END IF;
634
635 END LOOP;
636 END IF;
637
638 IF nvl(p_fixed_asset_rec.asset_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
639 g_ia_ind := g_ia_ind + 1;
640 l_instance_asset_tbl(g_ia_ind).parent_tbl_index := g_inst_ind;
641 l_instance_asset_tbl(g_ia_ind).fa_asset_id := p_fixed_asset_rec.asset_id;
642 l_instance_asset_tbl(g_ia_ind).fa_book_type_code := l_fa_book_type_code;
643 l_instance_asset_tbl(g_ia_ind).fa_location_id := l_fa_location_id;
644 l_instance_asset_tbl(g_ia_ind).asset_quantity := p_instance_rec.quantity;
645 l_instance_asset_tbl(g_ia_ind).update_status := 'IN_SERVICE';
646 l_instance_asset_tbl(g_ia_ind).fa_sync_flag := p_fixed_asset_rec.fa_sync_flag;
647 l_instance_asset_tbl(g_ia_ind).fa_sync_validation_reqd :=
648 nvl(p_fixed_asset_rec.fa_sync_validation_reqd, fnd_api.g_false);
649
650 validate_inst_asset(
651 px_inst_asset_rec => l_instance_asset_tbl(g_ia_ind),
652 x_return_status => l_return_status);
653
654 IF l_return_status <> fnd_api.g_ret_sts_success THEN
655 RAISE fnd_api.g_exc_error;
656 END IF;
657
658 END IF;
659
660 END IF;
661
662 debug('instance_tbl.count : '||l_instance_tbl.count);
663 debug('instance_asset_tbl.count : '||l_instance_asset_tbl.count);
664
665 csi_item_instance_grp.create_item_instance (
666 p_api_version => 1.0,
667 p_commit => fnd_api.g_false,
668 p_init_msg_list => fnd_api.g_true,
669 p_validation_level => fnd_api.g_valid_level_full,
670 p_instance_tbl => l_instance_tbl,
671 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
672 p_party_tbl => l_party_tbl,
673 p_account_tbl => l_account_tbl,
674 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
675 p_org_assignments_tbl => l_org_assignments_tbl,
676 p_asset_assignment_tbl => l_instance_asset_tbl,
677 p_txn_tbl => l_csi_txn_tbl,
678 p_call_from_bom_expl => 'N',
679 p_grp_error_tbl => l_grp_error_tbl,
680 x_return_status => l_return_status,
681 x_msg_count => l_msg_count,
682 x_msg_data => l_msg_data);
683
684 IF l_return_status <> fnd_api.g_ret_sts_success THEN
685 RAISE fnd_api.g_exc_error;
686 END IF;
687
688 IF l_grp_error_tbl.COUNT > 0 THEN
689 -- errors should be passes out as error.
690 FOR err_ind IN l_grp_error_tbl.FIRST ..l_grp_error_tbl.LAST
691 LOOP
692 IF l_grp_error_tbl(err_ind).process_status = 'E' THEN
693 l_error_message := l_grp_error_tbl(err_ind).error_message;
694 RAISE fnd_api.g_exc_error;
695 END IF;
696 END LOOP;
697
698 END IF;
699
700 IF l_instance_tbl.COUNT > 0 THEN
701 FOR inst_ind IN l_instance_tbl.FIRST .. l_instance_tbl.LAST
702 LOOP
703
704 IF l_eam_item THEN
705 -- to be uncommented later
706 eam_maint_attributes_pub.create_maint_attributes(
707 p_api_version => 1.0,
708 p_init_msg_list => fnd_api.g_true,
709 p_commit => fnd_api.g_false,
710 p_validation_level => fnd_api.g_valid_level_full,
711 p_instance_id => l_instance_tbl(inst_ind).instance_id,
712 p_owning_department_id => p_eam_rec.owning_department_id,
713 p_accounting_class_code => p_eam_rec.wip_accounting_class_code,
714 p_area_id => p_eam_rec.area_id,
715 p_parent_instance_id => p_eam_rec.parent_instance_id,
716 x_return_status => l_return_status,
717 x_msg_count => l_msg_count,
718 x_msg_data => l_msg_data);
719
720 IF l_return_status <> fnd_api.g_ret_sts_success THEN
721 RAISE fnd_api.g_exc_error;
722 END IF;
723 END IF;
724
725 END LOOP;
726 END IF;
727
728 x_instance_tbl := l_instance_tbl;
729 x_instance_asset_tbl := l_instance_asset_tbl;
730
731
732 IF l_instance_asset_tbl.COUNT > 0 THEN
733 FOR l_ind IN l_instance_asset_tbl.FIRST .. l_instance_asset_tbl.LAST
734 LOOP
735 IF l_instance_asset_tbl(l_ind).fa_sync_flag = 'N' THEN
736 l_warning_flag := 'Y';
737 END IF;
738 END LOOP;
739 END IF;
740
741 IF l_warning_flag = 'Y' THEN
742 fnd_message.set_name('CSI', 'CSI_INST_ASSET_SYNC_WARNING');
743 fnd_msg_pub.add;
744 x_return_status := 'W';
745 x_error_message := dump_error_stack;
746 END IF;
747
748 EXCEPTION
749 WHEN fnd_api.g_exc_error THEN
750 rollback to fa_grp_create_instance;
751 x_return_status := fnd_api.g_ret_sts_error;
752 x_error_message := dump_error_stack;
753 WHEN others THEN
754 rollback to fa_grp_create_instance;
755 x_return_status := fnd_api.g_ret_sts_unexp_error;
756 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
757 fnd_message.set_token('API_NAME','csi_fa_instance_grp.create_item_instance');
758 fnd_message.set_token('SQL_ERROR',SQLERRM);
759 fnd_msg_pub.add;
760 x_error_message := dump_error_stack;
761 END create_item_instance;
762
763 PROCEDURE copy_item_instance(
764 p_fixed_asset_rec IN fixed_asset_rec,
765 p_instance_rec IN csi_datastructures_pub.instance_rec,
766 p_instance_serial_tbl IN instance_serial_tbl,
767 p_eam_rec IN eam_rec,
768 p_copy_parties IN varchar2,
769 p_copy_accounts IN varchar2,
770 p_copy_contacts IN varchar2,
771 p_copy_org_assignments IN varchar2,
772 p_copy_asset_assignments IN varchar2,
773 p_copy_pricing_attribs IN varchar2,
774 p_copy_ext_attribs IN varchar2,
775 p_copy_inst_children IN varchar2,
776 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
777 x_instance_tbl OUT nocopy csi_datastructures_pub.instance_tbl,
778 x_instance_asset_tbl OUT nocopy csi_datastructures_pub.instance_asset_tbl,
779 x_return_status OUT nocopy varchar2,
780 x_error_message OUT nocopy varchar2)
781 IS
782
783 TYPE copy_flags_rec IS RECORD(
784 copy_parties varchar2(1),
785 copy_accounts varchar2(1),
786 copy_contacts varchar2(1),
787 copy_org_assignments varchar2(1),
788 copy_asset_assignments varchar2(1),
789 copy_pricing_attribs varchar2(1),
790 copy_ext_attribs varchar2(1),
791 copy_inst_children varchar2(1));
792
793 l_fa_flow varchar2(1);
794 l_copy_flags_rec copy_flags_rec;
795 l_instance_rec csi_datastructures_pub.instance_rec;
796
797 l_miss_num number := fnd_api.g_miss_num;
798 l_miss_char varchar2(200) := fnd_api.g_miss_char;
799
800 l_instance_tbl csi_datastructures_pub.instance_tbl;
801
802 o_ind binary_integer := 0;
803 o_instance_tbl csi_datastructures_pub.instance_tbl;
804
805 ia_ind binary_integer := 0;
806 l_instance_asset_tbl csi_datastructures_pub.instance_asset_tbl;
807
808 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
809 l_msg_count number;
810 l_msg_data varchar2(4000);
811 l_error_message varchar2(2000);
812
813 l_serial_control_code number;
814 l_eam_item_type number;
815 l_eam_item boolean := FALSE;
816 l_owning_department_id number;
817 l_wip_accounting_class_code varchar2(200);
818 l_parent_instance_id number;
819 l_area_id number;
820
821 PROCEDURE do_copy(
822 p_fa_flow IN varchar2,
823 p_instance_rec IN csi_datastructures_pub.instance_rec,
824 p_copy_flags_rec IN copy_flags_rec,
825 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
826 x_instance_tbl OUT nocopy csi_datastructures_pub.instance_tbl,
827 x_return_status OUT nocopy varchar2)
828 IS
829 l_instance_tbl csi_datastructures_pub.instance_tbl;
830 l_internal_party_id number;
831
832 l_instance_rec csi_datastructures_pub.instance_rec;
833 l_party_tbl csi_datastructures_pub.party_tbl;
834 l_party_acct_tbl csi_datastructures_pub.party_account_tbl;
835 l_inst_asset_tbl csi_datastructures_pub.instance_asset_tbl;
836 l_eav_tbl csi_datastructures_pub.extend_attrib_values_tbl;
837 l_pricing_attribs_tbl csi_datastructures_pub.pricing_attribs_tbl;
838 l_org_units_tbl csi_datastructures_pub.organization_units_tbl;
839 l_inst_id_lst csi_datastructures_pub.id_tbl;
840
841 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
842 l_msg_count number;
843 l_msg_data varchar2(4000);
844 BEGIN
845
846 x_return_status := fnd_api.g_ret_sts_success;
847
848 csi_item_instance_pvt.copy_item_instance(
849 p_api_version => 1.0,
850 p_commit => fnd_api.g_false,
851 p_init_msg_list => fnd_api.g_true,
852 p_validation_level => fnd_api.g_valid_level_full,
853 p_source_instance_rec => p_instance_rec,
854 p_copy_ext_attribs => p_copy_flags_rec.copy_ext_attribs,
855 p_copy_org_assignments => p_copy_flags_rec.copy_org_assignments,
856 p_copy_parties => p_copy_flags_rec.copy_parties,
857 p_copy_contacts => p_copy_flags_rec.copy_contacts,
858 p_copy_accounts => p_copy_flags_rec.copy_accounts,
859 p_copy_asset_assignments => p_copy_flags_rec.copy_asset_assignments,
860 p_copy_pricing_attribs => p_copy_flags_rec.copy_pricing_attribs,
861 p_copy_inst_children => p_copy_flags_rec.copy_inst_children,
862 p_call_from_split => fnd_api.g_false,
863 p_txn_rec => px_csi_txn_rec,
864 x_new_instance_tbl => l_instance_tbl,
865 x_return_status => l_return_status,
866 x_msg_count => l_msg_count,
867 x_msg_data => l_msg_data);
868
869 IF l_return_status <> fnd_api.g_ret_sts_success THEN
870 RAISE fnd_api.g_exc_error;
871 END IF;
872
873 IF nvl(p_fa_flow, 'N') = 'Y' THEN
874
875 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
876 csi_gen_utility_pvt.populate_install_param_rec;
877 END IF;
878
879 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
880
881 l_party_tbl.delete;
882
883 SELECT instance_party_id,
884 object_version_number,
885 party_id
886 INTO l_party_tbl(1).instance_party_id,
887 l_party_tbl(1).object_version_number,
888 l_party_tbl(1).party_id
889 FROM csi_i_parties
890 WHERE instance_id = l_instance_tbl(1).instance_id -- for copy children case need to change
891 AND relationship_type_code = 'OWNER';
892
893 IF l_party_tbl(1).party_id <> l_internal_party_id THEN
894
895 l_party_tbl(1).instance_id := l_instance_tbl(1).instance_id;
896 l_party_tbl(1).party_source_table := 'HZ_PARTIES';
897 l_party_tbl(1).party_id := l_internal_party_id;
898 l_party_tbl(1).relationship_type_code := 'OWNER';
899 l_party_tbl(1).contact_flag := 'N';
900
901 -- change the owner to internal
902 csi_item_instance_pub.update_item_instance(
903 p_api_version => 1.0,
904 p_commit => fnd_api.g_false,
905 p_init_msg_list => fnd_api.g_true,
906 p_validation_level => fnd_api.g_valid_level_full,
907 p_instance_rec => l_instance_rec,
908 p_ext_attrib_values_tbl => l_eav_tbl,
909 p_party_tbl => l_party_tbl,
910 p_account_tbl => l_party_acct_tbl,
911 p_pricing_attrib_tbl => l_pricing_attribs_tbl,
912 p_org_assignments_tbl => l_org_units_tbl,
913 p_txn_rec => px_csi_txn_rec,
914 p_asset_assignment_tbl => l_inst_asset_tbl,
915 x_instance_id_lst => l_inst_id_lst,
916 x_return_status => l_return_status,
917 x_msg_count => l_msg_count,
918 x_msg_data => l_msg_data);
919
920 IF l_return_status <> fnd_api.g_ret_sts_success THEN
921 RAISE fnd_api.g_exc_error;
922 END IF;
923
924 END IF;
925
926 END IF;
927
928 x_instance_tbl := l_instance_tbl;
929
930 EXCEPTION
931 WHEN fnd_api.g_exc_error THEN
932 x_return_status := fnd_api.g_ret_sts_error;
933 END do_copy;
934
935 BEGIN
936
937 x_return_status := fnd_api.g_ret_sts_success;
938
939 savepoint fa_grp_copy_instance;
940
941 csi_item_instance_vld_pvt.check_reqd_param_num (
942 p_number => p_instance_rec.instance_id,
943 p_param_name => 'p_instance_rec.instance_id',
944 p_api_name => 'csi_fa_instance_rec.copy_item_instance');
945
946 csi_transactions_pvt.create_transaction(
947 p_api_version => 1.0,
948 p_commit => fnd_api.g_false,
949 p_init_msg_list => fnd_api.g_true,
950 p_validation_level => fnd_api.g_valid_level_full,
951 p_success_if_exists_flag => 'Y',
952 p_transaction_rec => px_csi_txn_rec,
953 x_return_status => l_return_status,
954 x_msg_count => l_msg_count,
955 x_msg_data => l_msg_data);
956
957 IF l_return_status <> fnd_api.g_ret_sts_success THEN
958 RAISE fnd_api.g_exc_error;
959 END IF;
960
961 IF nvl(p_fixed_asset_rec.asset_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
962 l_fa_flow := 'Y';
963 l_copy_flags_rec.copy_parties := fnd_api.g_false;
964 l_copy_flags_rec.copy_accounts := fnd_api.g_false;
965 l_copy_flags_rec.copy_contacts := fnd_api.g_false;
966 ELSE
967 l_fa_flow := 'N';
968 l_copy_flags_rec.copy_parties := p_copy_parties;
969 l_copy_flags_rec.copy_accounts := p_copy_accounts;
970 l_copy_flags_rec.copy_contacts := p_copy_contacts;
971 END IF;
972
973 l_copy_flags_rec.copy_org_assignments := p_copy_org_assignments;
974 l_copy_flags_rec.copy_pricing_attribs := p_copy_pricing_attribs;
975 l_copy_flags_rec.copy_ext_attribs := p_copy_ext_attribs;
976 l_copy_flags_rec.copy_asset_assignments := p_copy_asset_assignments;
977 l_copy_flags_rec.copy_inst_children := p_copy_inst_children;
978
979 -- derive eam_item_type
980 IF nvl(p_instance_rec.inventory_item_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
981 AND
982 nvl(p_instance_rec.vld_organization_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
983 THEN
984
985 SELECT eam_item_type,
986 serial_number_control_code
987 INTO l_eam_item_type,
988 l_serial_control_code
989 FROM mtl_system_items
990 WHERE inventory_item_id = p_instance_rec.inventory_item_id
991 AND organization_id = p_instance_rec.vld_organization_id;
992
993 IF l_eam_item_type in (1, 3) AND l_serial_control_code <> 1 THEN
994 l_eam_item := TRUE;
995 END IF;
996
997 END IF;
998
999 IF p_instance_serial_tbl.COUNT > 0 THEN
1000
1001 FOR srl_ind IN p_instance_serial_tbl.FIRST .. p_instance_serial_tbl.LAST
1002 LOOP
1003
1004 l_instance_rec := p_instance_rec;
1005
1006 -- override with serial attributes
1007 l_instance_rec.instance_number :=
1008 nvl(p_instance_serial_tbl(srl_ind).instance_number, l_miss_char);
1009 l_instance_rec.serial_number :=
1010 nvl(p_instance_serial_tbl(srl_ind).serial_number, l_miss_char);
1011 l_instance_rec.lot_number :=
1012 nvl(p_instance_serial_tbl(srl_ind).lot_number, l_miss_char);
1013 l_instance_rec.external_reference :=
1014 nvl(p_instance_serial_tbl(srl_ind).external_reference, p_instance_rec.external_reference);
1015 l_instance_rec.instance_usage_code :=
1016 nvl(p_instance_serial_tbl(srl_ind).instance_usage_code, p_instance_rec.instance_usage_code);
1017 l_instance_rec.operational_status_code :=
1018 nvl(p_instance_serial_tbl(srl_ind).operational_status_code,
1019 p_instance_rec.operational_status_code);
1020 l_instance_rec.instance_description :=
1021 nvl(p_instance_serial_tbl(srl_ind).instance_description, p_instance_rec.instance_description);
1022 l_instance_rec.quantity := 1;
1023
1024 -- override with eam attributes
1025 l_instance_rec.asset_criticality_code :=
1026 nvl(p_eam_rec.asset_criticality_code, l_miss_char);
1027 l_instance_rec.category_id :=
1028 nvl(p_eam_rec.category_id, l_miss_num);
1029
1030 do_copy(
1031 p_fa_flow => l_fa_flow,
1032 p_instance_rec => l_instance_rec,
1033 p_copy_flags_rec => l_copy_flags_rec,
1034 px_csi_txn_rec => px_csi_txn_rec,
1035 x_instance_tbl => l_instance_tbl,
1036 x_return_status => l_return_status);
1037
1038 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1039 l_error_message := dump_error_stack;
1040 RAISE fnd_api.g_exc_error;
1041 END IF;
1042
1043 IF l_instance_tbl.COUNT > 0 THEN
1044 FOR inst_ind IN l_instance_tbl.FIRST .. l_instance_tbl.LAST
1045 LOOP
1046 o_ind := o_ind + 1;
1047 o_instance_tbl(o_ind) := l_instance_tbl(inst_ind);
1048 END LOOP;
1049 END IF;
1050
1051 END LOOP;
1052
1053 ELSE
1054 l_instance_rec := p_instance_rec;
1055 -- override with eam attributes
1056 l_instance_rec.asset_criticality_code :=
1057 nvl(p_eam_rec.asset_criticality_code, l_miss_char);
1058 l_instance_rec.category_id :=
1059 nvl(p_eam_rec.category_id, l_miss_num);
1060
1061 do_copy(
1062 p_fa_flow => l_fa_flow,
1063 p_instance_rec => l_instance_rec,
1064 p_copy_flags_rec => l_copy_flags_rec,
1065 px_csi_txn_rec => px_csi_txn_rec,
1066 x_instance_tbl => l_instance_tbl,
1067 x_return_status => l_return_status);
1068
1069 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1070 l_error_message := dump_error_stack;
1071 RAISE fnd_api.g_exc_error;
1072 END IF;
1073
1074 IF l_instance_tbl.COUNT > 0 THEN
1075 FOR inst_ind IN l_instance_tbl.FIRST .. l_instance_tbl.LAST
1076 LOOP
1077 o_ind := o_ind + 1;
1078 o_instance_tbl(o_ind) := l_instance_tbl(inst_ind);
1079 END LOOP;
1080 END IF;
1081
1082 END IF;
1083
1084 IF o_instance_tbl.COUNT > 0 THEN
1085 FOR inst_ind IN o_instance_tbl.FIRST .. o_instance_tbl.LAST
1086 LOOP
1087
1088 IF nvl(p_fixed_asset_rec.asset_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1089 ia_ind := ia_ind + 1;
1090 l_instance_asset_tbl(ia_ind).instance_id := o_instance_tbl(inst_ind).instance_id;
1091 l_instance_asset_tbl(ia_ind).fa_asset_id := p_fixed_asset_rec.asset_id;
1092 l_instance_asset_tbl(ia_ind).fa_book_type_code := p_fixed_asset_rec.book_type_code;
1093 l_instance_asset_tbl(ia_ind).fa_location_id := p_fixed_asset_rec.asset_location_id;
1094 l_instance_asset_tbl(ia_ind).asset_quantity := o_instance_tbl(inst_ind).quantity;
1095 l_instance_asset_tbl(ia_ind).update_status := 'IN_SERVICE';
1096 l_instance_asset_tbl(ia_ind).fa_sync_flag := p_fixed_asset_rec.fa_sync_flag;
1097 l_instance_asset_tbl(ia_ind).fa_sync_validation_reqd :=
1098 p_fixed_asset_rec.fa_sync_validation_reqd;
1099 END IF;
1100
1101 IF l_eam_item THEN
1102
1103 /*Need to flip the EAM attributes.*/
1104 IF p_eam_rec.owning_department_id = FND_API.G_MISS_NUM THEN
1105 l_owning_department_id := NULL;
1106 ELSIF p_eam_rec.owning_department_id IS NULL THEN
1107 l_owning_department_id := FND_API.G_MISS_NUM;
1108 END IF;
1109
1110 IF p_eam_rec.wip_accounting_class_code = FND_API.G_MISS_CHAR THEN
1111 l_wip_accounting_class_code := NULL;
1112 ELSIF p_eam_rec.owning_department_id IS NULL THEN
1113 l_wip_accounting_class_code := FND_API.G_MISS_CHAR;
1114 END IF;
1115
1116 IF p_eam_rec.area_id = FND_API.G_MISS_NUM THEN
1117 l_area_id := NULL;
1118 ELSIF p_eam_rec.owning_department_id IS NULL THEN
1119 l_area_id := FND_API.G_MISS_NUM;
1120 END IF;
1121
1122 IF p_eam_rec.parent_instance_id = FND_API.G_MISS_NUM THEN
1123 l_parent_instance_id := NULL;
1124 ELSIF p_eam_rec.owning_department_id IS NULL THEN
1125 l_parent_instance_id := FND_API.G_MISS_NUM;
1126 END IF;
1127
1128 eam_maint_attributes_pub.create_maint_attributes(
1129 p_api_version => 1.0,
1130 p_init_msg_list => fnd_api.g_true,
1131 p_commit => fnd_api.g_false,
1132 p_validation_level => fnd_api.g_valid_level_full,
1133 p_instance_id => l_instance_tbl(inst_ind).instance_id,
1134 p_owning_department_id => l_owning_department_id,
1135 p_accounting_class_code => l_wip_accounting_class_code,
1136 p_area_id => l_area_id,
1137 p_parent_instance_id => l_parent_instance_id,
1138 x_return_status => l_return_status,
1139 x_msg_count => l_msg_count,
1140 x_msg_data => l_msg_data);
1141
1142 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1143 RAISE fnd_api.g_exc_error;
1144 END IF;
1145 END IF;
1146
1147 END LOOP;
1148 END IF;
1149
1150 IF l_instance_asset_tbl.COUNT > 0 THEN
1151
1152 create_instance_assets(
1153 px_instance_asset_tbl => l_instance_asset_tbl,
1154 px_csi_txn_rec => px_csi_txn_rec,
1155 x_return_status => l_return_status,
1156 x_error_message => l_error_message);
1157
1158 IF l_return_status IN (fnd_api.g_ret_sts_success, 'W') THEN
1159 x_return_status := l_return_status;
1160 x_error_message := l_error_message;
1161 ELSE
1162 RAISE fnd_api.g_exc_error;
1163 END IF;
1164
1165 END IF;
1166
1167 x_instance_tbl := o_instance_tbl;
1168 x_instance_asset_tbl := l_instance_asset_tbl;
1169
1170 EXCEPTION
1171 WHEN fnd_api.g_exc_error THEN
1172 rollback to fa_grp_copy_instance;
1173 x_return_status := fnd_api.g_ret_sts_error;
1174 x_error_message := dump_error_stack;
1175 WHEN others THEN
1176 rollback to fa_grp_copy_instance;
1177 x_return_status := fnd_api.g_ret_sts_unexp_error;
1178 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
1179 fnd_message.set_token('API_NAME','csi_fa_instance_grp.copy_item_instance');
1180 fnd_message.set_token('SQL_ERROR',SQLERRM);
1181 fnd_msg_pub.add;
1182 x_error_message := dump_error_stack;
1183 END copy_item_instance;
1184
1185 --
1186 PROCEDURE associate_item_instance(
1187 p_fixed_asset_rec IN fixed_asset_rec,
1188 p_instance_tbl IN csi_datastructures_pub.instance_tbl,
1189 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
1190 x_instance_asset_tbl OUT nocopy csi_datastructures_pub.instance_asset_tbl,
1191 x_return_status OUT nocopy varchar2,
1192 x_error_message OUT nocopy varchar2)
1193 IS
1194
1195 l_fa_location_id number;
1196 l_fa_quantity number;
1197 l_fa_book_type_code varchar2(30);
1198
1199 l_instance_asset_tbl csi_datastructures_pub.instance_asset_tbl;
1200 l_lookup_tbl csi_asset_pvt.lookup_tbl;
1201 l_asset_count_rec csi_asset_pvt.asset_count_rec;
1202 l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
1203 l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl;
1204
1205 g_ia_ind binary_integer := 0;
1206
1207 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1208 l_msg_count number;
1209 l_msg_data varchar2(2000);
1210
1211 l_error_message varchar2(2000);
1212
1213 BEGIN
1214 x_return_status := fnd_api.g_ret_sts_success;
1215
1216 debug('associate_item_isntance');
1217
1218 IF p_instance_tbl.COUNT > 0 THEN
1219
1220 FOR inst_ind IN p_instance_tbl.FIRST .. p_instance_tbl.LAST
1221 LOOP
1222
1223 g_ia_ind := g_ia_ind + 1;
1224 l_instance_asset_tbl(g_ia_ind).instance_id := p_instance_tbl(inst_ind).instance_id;
1225 l_instance_asset_tbl(g_ia_ind).fa_asset_id := p_fixed_asset_rec.asset_id;
1226 l_instance_asset_tbl(g_ia_ind).fa_book_type_code := p_fixed_asset_rec.book_type_code;
1227 l_instance_asset_tbl(g_ia_ind).fa_location_id := p_fixed_asset_rec.asset_location_id;
1228 l_instance_asset_tbl(g_ia_ind).asset_quantity := p_instance_tbl(inst_ind).quantity;
1229 l_instance_asset_tbl(g_ia_ind).update_status := 'IN_SERVICE';
1230
1231 IF nvl(p_fixed_asset_rec.asset_location_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
1232 OR
1233 nvl(p_fixed_asset_rec.asset_quantity, fnd_api.g_miss_num) = fnd_api.g_miss_num
1234 OR
1235 nvl(p_fixed_asset_rec.book_type_code, fnd_api.g_miss_char) = fnd_api.g_miss_char
1236 THEN
1237
1238 derive_fa_missing_values(
1239 p_instance_rec => p_instance_tbl(inst_ind),
1240 p_fixed_asset_rec => p_fixed_asset_rec,
1241 x_fa_location_id => l_fa_location_id,
1242 x_fa_quantity => l_fa_quantity,
1243 x_fa_book_type_code => l_fa_book_type_code,
1244 x_return_status => l_return_status);
1245
1246 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1247 RAISE fnd_api.g_exc_error;
1248 END IF;
1249
1250 debug('location_id :'||l_fa_location_id);
1251
1252 IF nvl(p_fixed_asset_rec.asset_location_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1253 l_instance_asset_tbl(g_ia_ind).fa_location_id := l_fa_location_id;
1254 END IF;
1255
1256 IF nvl(p_fixed_asset_rec.asset_quantity, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1257 l_instance_asset_tbl(g_ia_ind).asset_quantity := l_fa_quantity;
1258 END IF;
1259
1260 l_instance_asset_tbl(g_ia_ind).fa_book_type_code := l_fa_book_type_code;
1261 l_instance_asset_tbl(g_ia_ind).fa_sync_flag := p_fixed_asset_rec.fa_sync_flag;
1262 l_instance_asset_tbl(g_ia_ind).fa_sync_validation_reqd :=
1263 nvl(p_fixed_asset_rec.fa_sync_validation_reqd, fnd_api.g_false);
1264
1265 END IF;
1266
1267 END LOOP;
1268 END IF;
1269
1270 IF l_instance_asset_tbl.COUNT > 0 THEN
1271
1272 csi_transactions_pvt.create_transaction(
1273 p_api_version => 1.0,
1274 p_commit => fnd_api.g_false,
1275 p_init_msg_list => fnd_api.g_true,
1276 p_validation_level => fnd_api.g_valid_level_full,
1277 p_success_if_exists_flag => 'Y',
1278 p_transaction_rec => px_csi_txn_rec,
1279 x_return_status => l_return_status,
1280 x_msg_count => l_msg_count,
1281 x_msg_data => l_msg_data);
1282
1283 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1284 RAISE fnd_api.g_exc_error;
1285 END IF;
1286
1287 create_instance_assets(
1288 px_instance_asset_tbl => l_instance_asset_tbl,
1289 px_csi_txn_rec => px_csi_txn_rec,
1290 x_return_status => l_return_status,
1291 x_error_message => l_error_message);
1292
1293 IF l_return_status IN (fnd_api.g_ret_sts_success, 'W') THEN
1294 x_return_status := l_return_status;
1295 x_error_message := l_error_message;
1296 ELSE
1297 RAISE fnd_api.g_exc_error;
1298 END IF;
1299
1300 END IF;
1301
1302 EXCEPTION
1303 WHEN fnd_api.g_exc_error THEN
1304 x_return_status := fnd_api.g_ret_sts_error;
1305 x_error_message := dump_error_stack;
1306 WHEN others THEN
1307 x_return_status := fnd_api.g_ret_sts_unexp_error;
1308 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
1309 fnd_message.set_token('API_NAME','csi_fa_instance_grp.associate_item_instance');
1310 fnd_message.set_token('SQL_ERROR',SQLERRM);
1311 fnd_msg_pub.add;
1312 x_error_message := dump_error_stack;
1313 END associate_item_instance;
1314
1315 PROCEDURE update_asset_association(
1316 p_instance_asset_tbl IN csi_datastructures_pub.instance_asset_tbl,
1317 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
1318 x_return_status OUT nocopy varchar2,
1319 x_error_message OUT nocopy varchar2)
1320 IS
1321
1322 l_instance_asset_rec csi_datastructures_pub.instance_asset_rec;
1323 l_lookup_tbl csi_asset_pvt.lookup_tbl;
1324 l_asset_count_rec csi_asset_pvt.asset_count_rec;
1325 l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
1326 l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl;
1327 l_warning_flag varchar2(1) := 'N';
1328
1329 l_return_status varchar2(2000) := fnd_api.g_ret_sts_success;
1330 l_msg_count number;
1331 l_msg_data varchar2(2000);
1332
1333 BEGIN
1334
1335 x_return_status := fnd_api.g_ret_sts_success;
1336 IF p_instance_asset_tbl.count > 0 THEN
1337
1338 csi_transactions_pvt.create_transaction(
1339 p_api_version => 1.0,
1340 p_commit => fnd_api.g_false,
1341 p_init_msg_list => fnd_api.g_true,
1342 p_validation_level => fnd_api.g_valid_level_full,
1343 p_success_if_exists_flag => 'Y',
1344 p_transaction_rec => px_csi_txn_rec,
1345 x_return_status => l_return_status,
1346 x_msg_count => l_msg_count,
1347 x_msg_data => l_msg_data);
1348
1349 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1350 RAISE fnd_api.g_exc_error;
1351 END IF;
1352
1353 FOR l_ind IN p_instance_asset_tbl.FIRST .. p_instance_asset_tbl.LAST
1354 LOOP
1355
1356 l_instance_asset_rec := p_instance_asset_tbl(l_ind);
1357
1358 csi_asset_pvt.update_instance_asset (
1359 p_api_version => 1.0,
1360 p_commit => fnd_api.g_false,
1361 p_init_msg_list => fnd_api.g_true,
1362 p_validation_level => fnd_api.g_valid_level_full,
1363 p_instance_asset_rec => l_instance_asset_rec,
1364 p_txn_rec => px_csi_txn_rec,
1365 x_return_status => l_return_status,
1366 x_msg_count => l_msg_count,
1367 x_msg_data => l_msg_data,
1368 p_lookup_tbl => l_lookup_tbl,
1369 p_asset_count_rec => l_asset_count_rec,
1370 p_asset_id_tbl => l_asset_id_tbl,
1371 p_asset_loc_tbl => l_asset_loc_tbl);
1372
1373 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1374 RAISE fnd_api.g_exc_error;
1375 END IF;
1376
1377 IF l_instance_asset_rec.fa_sync_flag = 'N' THEN
1378 l_warning_flag := 'Y';
1379 END IF;
1380
1381 END LOOP;
1382
1383 END IF;
1384
1385 IF l_warning_flag = 'Y' THEN
1386 fnd_message.set_name('CSI', 'CSI_INST_ASSET_SYNC_WARNING');
1387 fnd_msg_pub.add;
1388 x_return_status := 'W';
1389 x_error_message := dump_error_stack;
1390 END IF;
1391
1392 EXCEPTION
1393 WHEN fnd_api.g_exc_error THEN
1394 x_return_status := fnd_api.g_ret_sts_error;
1395 x_error_message := dump_error_stack;
1396 WHEN others THEN
1397 x_return_status := fnd_api.g_ret_sts_unexp_error;
1398 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
1399 fnd_message.set_token('API_NAME','csi_fa_instance_grp.update_asset_association');
1400 fnd_message.set_token('SQL_ERROR',SQLERRM);
1401 fnd_msg_pub.add;
1402 x_error_message := dump_error_stack;
1403 END update_asset_association;
1404
1405 PROCEDURE create_instance_assets(
1406 px_instance_asset_tbl IN OUT nocopy csi_datastructures_pub.instance_asset_tbl,
1407 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
1408 x_return_status OUT nocopy varchar2,
1409 x_error_message OUT nocopy varchar2)
1410 IS
1411
1412 l_fixed_asset_rec fixed_asset_rec;
1413 l_instance_rec csi_datastructures_pub.instance_rec;
1414 l_lookup_tbl csi_asset_pvt.lookup_tbl;
1415 l_asset_count_rec csi_asset_pvt.asset_count_rec;
1416 l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
1417 l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl;
1418
1419 l_asset_quantity number;
1420 l_asset_location_id number;
1421
1422 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1423 l_msg_count number;
1424 l_msg_data varchar2(2000);
1425
1426 l_warning_flag varchar2(1) := 'N';
1427
1428
1429 BEGIN
1430
1431 x_return_status := fnd_api.g_ret_sts_success;
1432
1433 debug('create_instance_assets');
1434
1435 savepoint create_instance_assets;
1436
1437 IF px_instance_asset_tbl.COUNT > 0 THEN
1438
1439 FOR ia_ind IN px_instance_asset_tbl.FIRST .. px_instance_asset_tbl.LAST
1440 LOOP
1441
1442 IF nvl(px_instance_asset_tbl(ia_ind).fa_location_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
1443 OR
1444 nvl(px_instance_asset_tbl(ia_ind).asset_quantity, fnd_api.g_miss_num) = fnd_api.g_miss_num
1445 THEN
1446
1447 l_instance_rec.instance_id := px_instance_asset_tbl(ia_ind).instance_id;
1448 l_fixed_asset_rec.asset_id := px_instance_asset_tbl(ia_ind).fa_asset_id;
1449 l_fixed_asset_rec.asset_location_id := px_instance_asset_tbl(ia_ind).fa_location_id;
1450 l_fixed_asset_rec.asset_quantity := px_instance_asset_tbl(ia_ind).asset_quantity;
1451
1452 derive_fa_missing_values(
1453 p_instance_rec => l_instance_rec,
1454 p_fixed_asset_rec => l_fixed_asset_rec,
1455 x_fa_location_id => l_asset_location_id,
1456 x_fa_quantity => l_asset_quantity,
1457 x_fa_book_type_code => px_instance_asset_tbl(ia_ind).fa_book_type_code,
1458 x_return_status => l_return_status);
1459
1460 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1461 RAISE fnd_api.g_exc_error;
1462 END IF;
1463
1464 IF nvl(px_instance_asset_tbl(ia_ind).fa_location_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
1465 THEN
1466 px_instance_asset_tbl(ia_ind).fa_location_id := l_asset_location_id;
1467 END IF;
1468
1469 IF nvl(px_instance_asset_tbl(ia_ind).asset_quantity, fnd_api.g_miss_num) = fnd_api.g_miss_num
1470 THEN
1471 px_instance_asset_tbl(ia_ind).asset_quantity := l_asset_quantity;
1472 END IF;
1473
1474 END IF;
1475
1476 validate_inst_asset(
1477 px_inst_asset_rec => px_instance_asset_tbl(ia_ind),
1478 x_return_status => l_return_status);
1479
1480 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1481 RAISE fnd_api.g_exc_error;
1482 END IF;
1483
1484 IF nvl(px_instance_asset_tbl(ia_ind).instance_asset_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1485
1486 csi_asset_pvt.create_instance_asset(
1487 p_api_version => 1.0,
1488 p_commit => fnd_api.g_false,
1489 p_init_msg_list => fnd_api.g_true,
1490 p_validation_level => fnd_api.g_valid_level_full,
1491 p_instance_asset_rec => px_instance_asset_tbl(ia_ind),
1492 p_txn_rec => px_csi_txn_rec,
1493 p_lookup_tbl => l_lookup_tbl,
1494 p_asset_count_rec => l_asset_count_rec,
1495 p_asset_id_tbl => l_asset_id_tbl,
1496 p_asset_loc_tbl => l_asset_loc_tbl,
1497 x_return_status => l_return_status,
1498 x_msg_count => l_msg_count,
1499 x_msg_data => l_msg_data);
1500
1501 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1502 RAISE fnd_api.g_exc_error;
1503 END IF;
1504
1505 ELSE
1506 null;
1507 END IF;
1508
1509 IF px_instance_asset_tbl(ia_ind).fa_sync_flag = 'N' THEN
1510 l_warning_flag := 'Y';
1511 END IF;
1512
1513 END LOOP;
1514
1515 END IF;
1516
1517 IF l_warning_flag = 'Y' THEN
1518 fnd_message.set_name('CSI', 'CSI_INST_ASSET_SYNC_WARNING');
1519 fnd_msg_pub.add;
1520 x_return_status := 'W';
1521 x_error_message := dump_error_stack;
1522 ELSE
1523 x_return_status := fnd_api.g_ret_sts_success;
1524 END IF;
1525
1526 EXCEPTION
1527 WHEN fnd_api.g_exc_error THEN
1528 rollback to create_instance_assets;
1529 x_return_status := fnd_api.g_ret_sts_error;
1530 x_error_message := dump_error_stack;
1531 WHEN others THEN
1532 rollback to create_instance_assets;
1533 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1534 x_error_message := substr(sqlerrm, 1, 2000);
1535 END create_instance_assets;
1536
1537 END csi_fa_instance_grp;