[Home] [Help]
PACKAGE BODY: APPS.CSE_ASSET_CREATION_PKG
Source
1 PACKAGE BODY cse_asset_creation_pkg AS
2 /* $Header: CSEIFACB.pls 120.30.12010000.2 2008/10/17 19:20:21 fli ship $ */
3
4 l_debug varchar2(1) := NVL(fnd_profile.value('cse_debug_option'),'N');
5
6 PROCEDURE debug(
7 p_message IN varchar2)
8 IS
9 BEGIN
10 IF l_debug = 'Y' THEN
11 cse_debug_pub.add(p_message);
12 IF nvl(fnd_global.conc_request_id, -1) <> -1 THEN
13 fnd_file.put_line(fnd_file.log, p_message);
14 END IF;
15 END IF;
16 EXCEPTION
17 WHEN others THEN
18 null;
19 END debug;
20
21 FUNCTION fill(
22 p_column in varchar2,
23 p_width in number,
24 p_side in varchar2 default 'R')
25 RETURN varchar2 IS
26 l_column varchar2(2000);
27 BEGIN
28 l_column := nvl(p_column, ' ');
29 IF p_side = 'L' THEN
30 return(lpad(l_column, p_width, ' '));
31 ELSIF p_side = 'R' THEN
32 return(rpad(l_column, p_width, ' '));
33 END IF;
34 END fill;
35
36 PROCEDURE out(
37 p_message in varchar2)
38 IS
39 BEGIN
40 IF nvl(fnd_global.conc_request_id, -1) <> -1 THEN
41 fnd_file.put_line(fnd_file.output, p_message);
42 END IF;
43 EXCEPTION
44 WHEN others THEN
45 null;
46 END out;
47
48 PROCEDURE asset_creation_report(
49 p_txn_status_tbl IN txn_status_tbl)
50 IS
51
52 l_total_count number;
53 l_error_count number;
54 l_success_count number;
55
56 FUNCTION valid_txn_count(
57 p_ts_tbl IN txn_status_tbl) RETURN NUMBER
58 IS
59 l_count number := 0;
60 BEGIN
61 FOR l_ind IN p_ts_tbl.FIRST .. p_ts_tbl.LAST
62 LOOP
63 IF p_ts_tbl(l_ind).valid_txn_flag = 'Y' THEN
64 l_count := l_count + 1;
65 END IF;
66 END LOOP;
67 RETURN l_count;
68 END valid_txn_count;
69
70 FUNCTION error_txn_count(
71 p_ts_tbl IN txn_status_tbl) RETURN NUMBER
72 IS
73 l_count number := 0;
74 BEGIN
75 FOR l_ind IN p_ts_tbl.FIRST .. p_ts_tbl.LAST
76 LOOP
77 IF p_ts_tbl(l_ind).valid_txn_flag = 'Y' AND p_ts_tbl(l_ind).processed_flag = 'E' THEN
78 l_count := l_count + 1;
79 END IF;
80 END LOOP;
81 RETURN l_count;
82 END error_txn_count;
83
84 BEGIN
85 IF p_txn_status_tbl.COUNT > 0 THEN
86
87 l_total_count := valid_txn_count(p_txn_status_tbl);
88 l_error_count := error_txn_count(p_txn_status_tbl);
89
90 l_success_count := l_total_count - l_error_count;
91 out(' ');
92 out('Transactions :-' );
93 out(' Total : '||l_total_count);
94 out(' Processed : '||l_success_count);
95 out(' Failed : '||l_error_count);
96
97 IF l_error_count > 0 THEN
98 out(' ');
99 out(fill('csi_transaction_id', 20)||
100 fill('error_text', 80));
101 out(fill('------------------', 20)||
102 fill('----------', 80));
103 FOR l_ind IN p_txn_status_tbl.FIRST .. p_txn_status_tbl.LAST
104 LOOP
105 IF p_txn_status_tbl(l_ind).processed_flag = 'E' THEN
106 out(fill(p_txn_status_tbl(l_ind).csi_txn_id, 20)||
107 fill(p_txn_status_tbl(l_ind).error_message, 80));
108 END IF;
109 END LOOP;
110 END IF;
111
112 END IF;
113 END asset_creation_report;
114
115 PROCEDURE dump_inst_tbl(
116 p_inst_tbl IN instance_tbl)
117 IS
118 BEGIN
119 IF p_inst_tbl.count > 0 THEN
120 FOR l_ind IN p_inst_tbl.first .. p_inst_tbl.last
121 LOOP
122
123 debug('instance info :- record # '||l_ind);
124
125 debug(' instance_id : '||p_inst_tbl(l_ind).instance_id);
126 debug(' subinventory_code : '||p_inst_tbl(l_ind).subinventory_code);
127 debug(' primary_uom_code : '||p_inst_tbl(l_ind).primary_uom_code);
128 debug(' serial_number : '||p_inst_tbl(l_ind).serial_number);
129 debug(' lot_number : '||p_inst_tbl(l_ind).lot_number);
130 debug(' pa_project_id : '||p_inst_tbl(l_ind).pa_project_id);
131 debug(' pa_project_task_id : '||p_inst_tbl(l_ind).pa_project_task_id);
132 debug(' rcv_txn_id : '||p_inst_tbl(l_ind).rcv_txn_id);
133 debug(' po_distribution_id : '||p_inst_tbl(l_ind).po_distribution_id);
134 debug(' location_type_code : '||p_inst_tbl(l_ind).location_type_code);
135 debug(' location_id : '||p_inst_tbl(l_ind).location_id);
136 debug(' mtl_dist_acct_id : '||p_inst_tbl(l_ind).mtl_dist_acct_id);
137 debug(' redeploy_flag : '||p_inst_tbl(l_ind).redeploy_flag);
138 debug(' asset_description : '||p_inst_tbl(l_ind).asset_description);
139 debug(' asset_units : '||p_inst_tbl(l_ind).quantity);
140 debug(' asset_unit_cost : '||p_inst_tbl(l_ind).asset_unit_cost);
141 debug(' asset_cost : '||p_inst_tbl(l_ind).asset_cost);
142 debug(' asset_category_id : '||p_inst_tbl(l_ind).asset_category_id);
143 debug(' book_type_code : '||p_inst_tbl(l_ind).book_type_code);
144 debug(' date_placed_in_service : '||p_inst_tbl(l_ind).date_placed_in_service);
145 debug(' asset_location_id : '||p_inst_tbl(l_ind).asset_location_id);
146 debug(' asset_key_ccid : '||p_inst_tbl(l_ind).asset_key_ccid);
147 debug(' deprn_expense_ccid : '||p_inst_tbl(l_ind).deprn_expense_ccid);
148 debug(' payables_ccid : '||p_inst_tbl(l_ind).payables_ccid);
149 debug(' tag_number : '||p_inst_tbl(l_ind).tag_number);
150 debug(' model_number : '||p_inst_tbl(l_ind).tag_number);
151 debug(' manufacturer_name : '||p_inst_tbl(l_ind).manufacturer_name);
152 debug(' employee_id : '||p_inst_tbl(l_ind).employee_id);
153 debug(' search_method : '||p_inst_tbl(l_ind).search_method);
154
155 END LOOP;
156 END IF;
157 END dump_inst_tbl;
158
159 PROCEDURE log_error(
160 p_instance_rec IN instance_rec,
161 p_error_message IN varchar2)
162 IS
163 l_error_rec csi_datastructures_pub.transaction_error_rec;
164 l_error_id number;
165 l_source_type varchar2(20);
166 l_error_message varchar2(2000);
167
168 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
169 l_msg_count number;
170 l_msg_data varchar2(2000);
171
172 BEGIN
173
174 l_error_message := rtrim(p_error_message);
175
176 IF l_error_message IS NULL THEN
177 l_error_message := cse_util_pkg.dump_error_stack;
178 IF l_error_message IS NULL THEN
179 l_error_message := substr(sqlerrm, 1, 240);
180 END IF;
181 END IF;
182
183 -- not making it as 'E' because the it clashes with the CSI Error Logic
184 l_error_rec.processed_flag := 'A';
185 l_error_rec.source_type := 'CSEFATIE';
186 l_error_rec.source_id := p_instance_rec.csi_txn_id;
187 l_error_rec.transaction_id := p_instance_rec.csi_txn_id;
188 l_error_rec.transaction_type_id := 123;
189 l_error_rec.error_text := l_error_message;
190 l_error_rec.inventory_item_id := p_instance_rec.inventory_item_id;
191 l_error_rec.serial_number := p_instance_rec.serial_number;
192 l_error_rec.lot_number := p_instance_rec.lot_number;
193 l_error_rec.inv_material_transaction_id := p_instance_rec.mtl_txn_id;
194 l_error_rec.transaction_error_date := sysdate;
195 l_error_rec.instance_id := p_instance_rec.instance_id;
196
197 BEGIN
198
199 SELECT transaction_error_id
200 INTO l_error_id
201 FROM csi_txn_errors
202 WHERE source_type = 'CSEFATIE'
203 AND source_id = l_error_rec.source_id
204 AND rownum < 2;
205
206 UPDATE csi_txn_errors
207 SET error_text = l_error_rec.error_text,
208 last_updated_by = fnd_global.user_id,
209 last_update_login = fnd_global.login_id,
210 last_update_date = sysdate
211 WHERE transaction_error_id = l_error_id;
212
213 debug(' error updated. transaction_error_id : '||l_error_id);
214
215 EXCEPTION
216 WHEN no_data_found THEN
217
218 csi_transactions_pvt.create_txn_error (
219 p_api_version => 1.0,
220 p_init_msg_list => fnd_api.g_true,
221 p_commit => fnd_api.g_false,
222 p_validation_level => fnd_api.g_valid_level_full,
223 p_txn_error_rec => l_error_rec,
224 x_transaction_error_id => l_error_id,
225 x_return_status => l_return_status,
226 x_msg_count => l_msg_count,
227 x_msg_data => l_msg_data);
228
229 IF l_return_status <> fnd_api.g_ret_sts_success THEN
230 RAISE fnd_api.g_exc_error;
231 END IF;
232
233 debug(' new error logged. transaction_error_id : '||l_error_id);
234 END;
235
236 EXCEPTION
237 WHEN fnd_api.g_exc_error THEN
238 NULL;
239 -- i mean if you can't log the error then what else will you do.
240 -- just leave the transaction as pending so that atleast the next run
241 -- will pick it yp
242 END log_error;
243
244 PROCEDURE complete_csi_txn(
245 p_csi_txn_id IN number,
246 x_return_status OUT nocopy varchar2,
247 x_error_message OUT nocopy varchar2)
248 IS
249 l_txn_rec csi_datastructures_pub.transaction_rec;
250 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
251 l_msg_count number;
252 l_msg_data varchar2(2000);
253 BEGIN
254
255 x_return_status := fnd_api.g_ret_sts_success;
256
257 l_txn_rec.transaction_id := p_csi_txn_id;
258 l_txn_rec.source_group_ref_id := fnd_global.conc_request_id;
259 l_txn_rec.transaction_status_code := cse_datastructures_pub.g_complete ;
260
261 SELECT object_version_number
262 INTO l_txn_rec.object_version_number
263 FROM csi_transactions
264 WHERE transaction_id = l_txn_rec.transaction_id;
265
266 csi_transactions_pvt.update_transactions(
267 p_api_version => 1.0,
268 p_init_msg_list => fnd_api.g_true,
269 p_commit => fnd_api.g_false,
270 p_validation_level => fnd_api.g_valid_level_full,
271 p_transaction_rec => l_txn_rec,
272 x_return_status => l_return_status,
273 x_msg_count => l_msg_count,
274 x_msg_data => l_msg_data);
275
276 IF l_return_status <> fnd_api.g_ret_sts_success THEN
277 RAISE fnd_api.g_exc_error;
278 END IF;
279
280 EXCEPTION
281 WHEN fnd_api.g_exc_error THEN
282 x_return_status := fnd_api.g_ret_sts_error;
283 END complete_csi_txn;
284
285 PROCEDURE get_redeploy_flag(
286 p_instance_id IN number,
287 p_transaction_date IN date,
288 x_redeploy_flag OUT nocopy varchar2)
289 IS
290 l_redeploy_flag varchar2(1) := 'N';
291
292 CURSOR all_txn_cur(
293 p_serial_number in varchar2,
294 p_item_id in number,
295 p_mtl_txn_id in number)
296 IS
297 SELECT mmt.creation_date mtl_creation_date,
298 mmt.transaction_id mtl_txn_id,
299 mmt.transaction_action_id mtl_action_id,
300 mmt.transaction_source_type_id mtl_src_type_id,
301 mmt.ship_to_location_id location_id
302 FROM mtl_unit_transactions mut,
303 mtl_material_transactions mmt
304 WHERE mut.serial_number = p_serial_number
305 AND mut.inventory_item_id = p_item_id
306 AND mmt.transaction_id = mut.transaction_id
307 AND mmt.transaction_id < p_mtl_txn_id
308 UNION
309 SELECT mmt.creation_date mtl_creation_date,
310 mmt.transaction_id mtl_txn_id,
311 mmt.transaction_action_id mtl_action_id,
312 mmt.transaction_source_type_id mtl_src_type_id,
313 mmt.ship_to_location_id location_id
314 FROM mtl_unit_transactions mut,
315 mtl_transaction_lot_numbers mtln,
316 mtl_material_transactions mmt
317 WHERE mut.serial_number = p_serial_number
318 AND mut.inventory_item_id = p_item_id
319 AND mtln.organization_id = mut.organization_id
320 AND mtln.transaction_date = mut.transaction_date
321 AND mtln.serial_transaction_id = mut.transaction_id
322 AND mmt.transaction_id = mtln.transaction_id
323 AND mmt.transaction_id < p_mtl_txn_id
324 ORDER BY 1 desc, 2 desc;
325
326
327 CURSOR deploy_cur IS
328 SELECT 'Y'
329 FROM csi_transactions ct,
330 csi_item_instances_h ciih
331 WHERE ciih.instance_id = p_instance_id
332 AND ct.transaction_id = ciih.transaction_id
333 AND ct.transaction_date < p_transaction_date
334 AND ct.transaction_type_id in (110, 108, 132, 133);
335
336 /* -- redeploy transactions
337 -------------------------------
338 110 - out of service
339 108 - project item in service
340 132 - issue to hz location
341 133 - misc issue to hz location
342 -------------------------------
343 */
344
345 BEGIN
346 FOR deploy_rec IN deploy_cur
347 LOOP
348 l_redeploy_flag := 'Y';
349 exit;
350 END LOOP;
351 x_redeploy_flag := l_redeploy_flag;
352 END get_redeploy_flag;
353
354 FUNCTION transaction_pending(
355 p_csi_txn_id IN number,
356 p_instance_id IN number)
357 RETURN boolean
358 IS
359 CURSOR txn_cur IS
360 SELECT ct.transaction_id
361 FROM csi_transactions ct,
362 csi_item_instances_h ciih
363 WHERE ciih.instance_id = p_instance_id
364 AND ct.transaction_id = ciih.transaction_id
365 AND ct.transaction_id < p_csi_txn_id
366 AND ct.transaction_status_code = 'PENDING';
367 BEGIN
368 FOR txn_rec IN txn_cur
369 LOOP
370 RETURN TRUE;
371 END LOOP;
372 RETURN FALSE;
373 END transaction_pending;
374
375 PROCEDURE get_base_amount (
376 p_po_distribution_id IN number,
377 p_current_cost IN number,
378 p_book_type_code IN varchar2,
379 x_base_amount OUT nocopy number,
380 x_return_status OUT nocopy varchar2,
381 x_error_msg OUT nocopy varchar2)
382 IS
383 CURSOR po_sob_currency_cur IS
384 SELECT poh.rate_type,
385 poh.currency_code,
386 pod.rate,
387 poh.rate_date,
388 sob.currency_code,
389 pod.set_of_books_id
390 FROM po_distributions_all pod,
391 po_headers_all poh,
392 gl_sets_of_books sob
393 WHERE pod.po_distribution_id = p_po_distribution_id
394 AND poh.po_header_id = pod.po_header_id
395 AND sob.set_of_books_id = pod.set_of_books_id ;
396
397 CURSOR base_currency_cur IS
398 SELECT gsob.currency_code
399 FROM gl_sets_of_books gsob,
400 fa_book_controls fbc
401 WHERE fbc.book_type_code = p_book_type_code
402 AND gsob.set_of_books_id = fbc.set_of_books_id ;
403
404 l_rate_type varchar2(30);
405 l_po_currency_code varchar2(15);
406 l_po_to_basecur_rate number;
407 l_fa_currency_code varchar2(15);
408 l_po_sob_currency_code varchar2(15);
409 l_po_sob_id number;
410 l_base_amount number;
411 l_rate_date date;
412
413 PROCEDURE round_currency (
414 p_amount IN number,
415 p_currency_code IN varchar2,
416 x_rounded_amount OUT nocopy number)
417 IS
418 CURSOR round_currency_cur IS
419 SELECT decode(fc.minimum_accountable_unit,
420 NULL, ROUND(p_amount, FC.precision),
421 ROUND(p_amount/FC.minimum_accountable_unit) * FC.minimum_accountable_unit)
422 FROM fnd_currencies fc
423 WHERE fc.currency_code = p_currency_code;
424 BEGIN
425 OPEN round_currency_cur;
426 FETCH round_currency_cur INTO x_rounded_amount ;
427 CLOSE round_currency_cur;
428 END round_currency;
429
430 BEGIN
431 x_return_status := fnd_api.g_ret_sts_success ;
432
433 OPEN base_currency_cur ;
434 FETCH base_currency_cur INTO l_fa_currency_code ;
435 CLOSE base_currency_cur ;
436
437 OPEN po_sob_currency_cur ;
438 FETCH po_sob_currency_cur
439 INTO l_rate_type,
440 l_po_currency_code,
441 l_po_to_basecur_rate,
442 l_rate_date,
443 l_po_sob_currency_code,
444 l_po_sob_id;
445
446 IF (po_sob_currency_cur%NOTFOUND OR l_rate_type is NULL) THEN
447 IF l_fa_currency_code = l_po_sob_currency_code THEN
448 debug('p_current_cost : '||p_current_cost);
449
450 l_base_amount := p_current_cost ;
451 ELSE
452 -- Convert amount from PO Reporting currency to FA Reporting Currency
453 l_base_amount := GL_Currency_API.Convert_Amount(
454 l_po_sob_currency_code,
455 l_fa_currency_code,
456 l_rate_date,
457 l_rate_type,
458 p_current_cost);
459 END IF;
460 END IF;
461
462 IF l_rate_type IS NOT NULL THEN
463 IF l_rate_type <> 'User' THEN
464 l_base_amount := GL_Currency_API.Convert_Amount(
465 l_po_currency_code,
466 l_fa_currency_code,
467 l_rate_date,
468 l_rate_type,
469 p_current_cost);
470 ELSIF l_rate_type = 'User' THEN
471 IF l_fa_currency_code = l_po_sob_currency_code THEN
472 round_currency ( p_current_cost * l_po_to_basecur_rate, l_fa_currency_code, x_base_amount);
473 ELSE
474 round_currency( p_current_cost * l_po_to_basecur_rate, l_po_sob_currency_code, x_base_amount);
475
476 l_base_amount := GL_Currency_API.Convert_Amount(
477 l_po_sob_currency_code,
478 l_fa_currency_code,
479 l_rate_date,
480 l_rate_type,
481 x_base_amount);
482 END IF ; --l_fa_currency_code = l_po_sob_currency_code
483 END IF ; --l_rate_type <> 'USer'
484 END IF ; --l_rate_type is NOT NULL
485
486 CLOSE po_sob_currency_cur ;
487
488 IF l_base_amount IS NULL THEN
489 x_error_msg := 'Unable to derive base amount for PO receit transaction';
490 RAISE fnd_api.g_exc_error;
491 END IF ;
492
493 x_base_amount := l_base_amount;
494
495 EXCEPTION
496 WHEN fnd_api.g_exc_error THEN
497 x_return_status := fnd_api.g_ret_sts_error ;
498 END get_base_amount ;
499
500 PROCEDURE get_fa_location_id(
501 p_location_type_code IN varchar2,
502 p_location_id IN number,
503 x_fa_location_id OUT nocopy number,
504 x_return_status OUT nocopy varchar2)
505 IS
506
507 l_location_table varchar2(30);
508 l_hz_or_hr varchar2(1);
509
510 CURSOR loc_map_cur(p_location_table IN varchar2) IS
511 SELECT fa_location_id
512 FROM csi_a_locations
513 WHERE location_table in ('LOCATION_CODES', p_location_table)
514 AND location_id = p_location_id
515 AND sysdate BETWEEN nvl(active_start_date, sysdate - 1)
516 AND nvl(active_end_date, sysdate + 1);
517 BEGIN
518
519 x_return_status := fnd_api.g_ret_sts_success;
520
521 IF p_location_type_code = 'INVENTORY' THEN
522 l_location_table := 'HR_LOCATIONS';
523 ELSIF p_location_type_code = 'HZ_LOCATIONS' THEN
524 BEGIN
525 SELECT 'Y' INTO l_hz_or_hr
526 FROM hz_locations
527 WHERE location_id = p_location_id;
528 l_location_table := 'HZ_LOCATIONS';
529 EXCEPTION
530 WHEN no_data_found THEN
531 l_location_table := 'HR_LOCATIONS';
532 END;
533 ELSE
534 l_location_table := 'LOCATION_CODES';
535 END IF;
536
537 FOR loc_rec IN loc_map_cur(l_location_table)
538 LOOP
539 x_fa_location_id := loc_rec.fa_location_id;
540 exit;
541 END LOOP;
542
543 IF x_fa_location_id is null then
544 RAISE fnd_api.g_exc_error;
545 END IF;
546 EXCEPTION
547 WHEN fnd_api.g_exc_error THEN
548 x_return_status := fnd_api.g_ret_sts_error;
549 END get_fa_location_id;
550
551 PROCEDURE derive_asset_attribs(
552 px_instance_tbl IN OUT nocopy instance_tbl,
553 x_return_status OUT nocopy varchar2,
554 x_error_message OUT nocopy varchar2)
555 IS
556
557 l_asset_attrib_rec cse_datastructures_pub.asset_attrib_rec;
558
559 l_asset_description varchar2(80);
560 l_inst_tbl instance_tbl;
561 l_asset_cost number;
562 l_asset_unit_cost number;
563 l_base_amount number;
564 l_source_type varchar2(3);
565 l_source_txn_id number;
566 l_asset_category varchar2(240);
567 l_asset_category_id number;
568 l_default_group_asset_id number;
569 l_book_type_code varchar2(30);
570 l_dpi date;
571 l_asset_key_ccid number;
572 l_fa_location_id number;
573 l_deprn_expense_ccid number;
574 l_payables_ccid number;
575 l_tag_number varchar2(15);
576 l_model_number varchar2(40);
577 l_manufacturer_name varchar2(30);
578 l_employee_id number;
579 l_search_method varchar2(10);
580
581 l_hook_used varchar2(1);
582 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
583 l_error_message varchar2(2000);
584
585 BEGIN
586
587 debug('inside derive_asset_attribs');
588
589 x_return_status := fnd_api.g_ret_sts_success;
590
591 l_inst_tbl := px_instance_tbl;
592
593 IF l_inst_tbl.COUNT > 0 THEN
594 FOR l_ind IN l_inst_tbl.FIRST .. l_inst_tbl.LAST
595 LOOP
596
597 IF l_inst_tbl(l_ind).csi_txn_type_id in (105, 112) THEN
598 l_source_type := 'PO';
599 ELSE
600 l_source_type := 'INV';
601 END IF;
602
603 SELECT source_transaction_type
604 INTO l_asset_attrib_rec.source_transaction_type
605 FROM csi_txn_types
606 WHERE transaction_type_id = l_inst_tbl(l_ind).csi_txn_type_id;
607
608 l_asset_attrib_rec.instance_id := l_inst_tbl(l_ind).instance_id;
609 l_asset_attrib_rec.inventory_item_id := l_inst_tbl(l_ind).inventory_item_id;
610 l_asset_attrib_rec.serial_number := l_inst_tbl(l_ind).serial_number;
611 l_asset_attrib_rec.organization_id := l_inst_tbl(l_ind).organization_id;
612 l_asset_attrib_rec.inv_master_organization_id := l_inst_tbl(l_ind).organization_id;
613 l_asset_attrib_rec.subinventory_name := l_inst_tbl(l_ind).subinventory_code;
614 l_asset_attrib_rec.transaction_quantity := l_inst_tbl(l_ind).quantity;
615 l_asset_attrib_rec.transaction_id := l_inst_tbl(l_ind).csi_txn_id;
616 l_asset_attrib_rec.transaction_date := l_inst_tbl(l_ind).csi_txn_date;
617 l_asset_attrib_rec.depreciable_flag := l_inst_tbl(l_ind).depreciable_flag;
618 l_asset_attrib_rec.transaction_type_id := l_inst_tbl(l_ind).csi_txn_type_id;
619 l_asset_attrib_rec.rcv_transaction_id := l_inst_tbl(l_ind).rcv_txn_id;
620 l_asset_attrib_rec.po_distribution_id := l_inst_tbl(l_ind).po_distribution_id;
621 l_asset_attrib_rec.inv_material_transaction_id := l_inst_tbl(l_ind).mtl_txn_id;
622 l_asset_attrib_rec.location_type_code := l_inst_tbl(l_ind).location_type_code;
623 l_asset_attrib_rec.location_id := l_inst_tbl(l_ind).location_id;
624 l_asset_attrib_rec.source_transaction_type := l_inst_tbl(l_ind).source_txn_type;
625
626 IF l_ind = 1 THEN
627
628 -- asset description
629 l_asset_description := cse_asset_util_pkg.asset_description(
630 p_asset_attrib_rec => l_asset_attrib_rec,
631 x_error_msg => l_error_message,
632 x_return_status => l_return_status);
633 IF l_return_status <> fnd_api.g_ret_sts_success THEN
634 RAISE fnd_api.g_exc_error;
635 END IF;
636
637 -- asset unit cost
638 cse_asset_util_pkg.get_unit_cost(
639 p_source_txn_type => l_source_type,
640 p_source_txn_id => l_inst_tbl(l_ind).rcv_txn_id,
641 p_inventory_item_id => l_inst_tbl(l_ind).inventory_item_id,
642 p_organization_id => l_inst_tbl(l_ind).organization_id,
643 x_unit_cost => l_asset_unit_cost,
644 x_error_msg => l_error_message,
645 x_return_status => l_return_status);
646
647 IF l_return_status <> fnd_api.g_ret_sts_success THEN
648 RAISE fnd_api.g_exc_error;
649 END IF;
650
651 debug(' asset_unit_cost : '||l_asset_unit_cost);
652
653 -- asset category
654 l_asset_category_id :=
655 cse_asset_util_pkg.asset_category(
656 p_asset_attrib_rec => l_asset_attrib_rec,
657 x_error_msg => l_error_message,
658 x_return_status => l_return_status);
659
660 IF l_return_status <> fnd_api.g_ret_sts_success THEN
661 RAISE fnd_api.g_exc_error;
662 END IF;
663
664 l_asset_attrib_rec.asset_category_id := l_asset_category_id;
665
666 IF nvl(l_asset_attrib_rec.asset_category_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
667
668 SELECT concatenated_segments
669 INTO l_asset_category
670 FROM fa_categories_b_kfv
671 WHERE category_id = l_asset_category_id;
672
673 -- book type code
674 l_book_type_code :=
675 cse_asset_util_pkg.book_type(
676 p_asset_attrib_rec => l_asset_attrib_rec,
677 x_error_msg => l_error_message,
678 x_return_status => l_return_status);
679
680 IF l_return_status <> fnd_api.g_ret_sts_success THEN
681 RAISE fnd_api.g_exc_error;
682 END IF;
683
684 l_asset_attrib_rec.book_type_code := l_book_type_code;
685
686 -- default asset group id
687 BEGIN
688 SELECT default_group_asset_id
689 INTO l_default_group_asset_id
690 FROM fa_category_books
691 WHERE category_id = l_asset_category_id
692 AND book_type_code = l_book_type_code;
693 EXCEPTION
694 WHEN no_data_found THEN
695 fnd_message.set_name('CSE', 'CSE_ASSET_BOOK_CAT_UNDEFINED');
696 fnd_message.set_token('ASSET_CAT', l_asset_category);
697 fnd_message.set_token('BOOK_TYPE_CODE', l_book_type_code);
698 l_error_message := fnd_message.get;
699 RAISE fnd_api.g_exc_error;
700 END;
701 ELSE
702 fnd_message.set_name('CSE', 'CSE_ASSET_CAT_ERROR');
703 l_error_message := fnd_message.get;
704 RAISE fnd_api.g_exc_error;
705 END IF;
706
707 -- date placed in service
708 l_dpi :=
709 cse_asset_util_pkg.date_place_in_service(
710 p_asset_attrib_rec => l_asset_attrib_rec,
711 x_error_msg => l_error_message,
712 x_return_status => l_return_status);
713
714 IF l_return_status <> fnd_api.g_ret_sts_success THEN
715 RAISE fnd_api.g_exc_error;
716 END IF;
717
718 l_asset_key_ccid :=
719 cse_asset_util_pkg.asset_key(
720 p_asset_attrib_rec => l_asset_attrib_rec,
721 x_error_msg => l_error_message,
722 x_return_status => l_return_status);
723
724 IF l_return_status <> fnd_api.g_ret_sts_success THEN
725 RAISE fnd_api.g_exc_error;
726 END IF;
727
728 -- get fa location
729 get_fa_location_id(
730 p_location_type_code => l_inst_tbl(l_ind).location_type_code,
731 p_location_id => l_inst_tbl(l_ind).location_id,
732 x_fa_location_id => l_fa_location_id,
733 x_return_status => l_return_status);
734
735 IF l_return_status <> fnd_api.g_ret_sts_success THEN
736 debug(' location_type_code : '||l_inst_tbl(l_ind).location_type_code);
737 debug(' location_id : '||l_inst_tbl(l_ind).location_id);
738 fnd_message.set_name('CSE','CSE_FA_CREATION_ATRIB_ERROR');
739 fnd_message.set_token('ASSET_ATTRIBUTE','LOCATION');
740 fnd_message.set_token('CSI_TRANSACTION_ID',l_inst_tbl(l_ind).csi_txn_id);
741 l_error_message := fnd_message.get;
742 RAISE fnd_api.g_exc_error;
743 END IF;
744
745 -- get deprn expense ccid
746 l_deprn_expense_ccid :=
747 cse_asset_util_pkg.deprn_expense_ccid(
748 p_asset_attrib_rec => l_asset_attrib_rec,
749 x_error_msg => l_error_message,
750 x_return_status => l_return_status);
751
752 IF l_return_status <> fnd_api.g_ret_sts_success THEN
753 RAISE fnd_api.g_exc_error;
754 END IF;
755
756 -- payables ccid
757 l_payables_ccid :=
758 cse_asset_util_pkg.payables_ccid(
759 p_asset_attrib_rec => l_asset_attrib_rec,
760 x_error_msg => l_error_message,
761 x_return_status => l_return_status);
762
763 IF l_return_status <> fnd_api.g_ret_sts_success THEN
764 --l_payables_ccid := l_inst_tbl(l_ind).mtl_dist_acct_id;
765 RAISE fnd_api.g_exc_error;
766 END IF;
767
768 -- tag number
769 l_tag_number :=
770 cse_asset_util_pkg.tag_number(
771 p_asset_attrib_rec => l_asset_attrib_rec,
772 x_error_msg => l_error_message,
773 x_return_status => l_return_status);
774
775 IF l_return_status <> fnd_api.g_ret_sts_success THEN
776 RAISE fnd_api.g_exc_error;
777 END IF;
778
779 -- model number
780 l_model_number :=
781 cse_asset_util_pkg.model_number(
782 p_asset_attrib_rec => l_asset_attrib_rec,
783 x_error_msg => l_error_message,
784 x_return_status => l_return_status);
785
786 IF l_return_status <> fnd_api.g_ret_sts_success THEN
787 RAISE fnd_api.g_exc_error;
788 END IF;
789
790 -- manufacturer
791 l_manufacturer_name :=
792 cse_asset_util_pkg.manufacturer(
793 p_asset_attrib_rec => l_asset_attrib_rec,
794 x_error_msg => l_error_message,
795 x_return_status => l_return_status);
796
797 IF l_return_status <> fnd_api.g_ret_sts_success THEN
798 RAISE fnd_api.g_exc_error;
799 END IF;
800
801 -- employee
802 l_employee_id :=
803 cse_asset_util_pkg.employee(
804 p_asset_attrib_rec => l_asset_attrib_rec,
805 x_error_msg => l_error_message,
806 x_return_status => l_return_status);
807
808 IF l_return_status <> fnd_api.g_ret_sts_success THEN
809 RAISE fnd_api.g_exc_error;
810 END IF;
811
812 -- search method
813 l_search_method :=
814 cse_asset_util_pkg.search_method(
815 p_asset_attrib_rec => l_asset_attrib_rec,
816 x_error_msg => l_error_message,
817 x_return_status => l_return_status);
818
819 IF l_return_status <> fnd_api.g_ret_sts_success THEN
820 RAISE fnd_api.g_exc_error;
821 END IF;
822
823 END IF; -- first record only
824
825 l_asset_cost := NVL(l_inst_tbl(l_ind).quantity,0) * NVL(l_asset_unit_cost,0);
826
827 IF l_source_type = 'PO' THEN
828 get_base_amount (
829 p_po_distribution_id => l_inst_tbl(l_ind).po_distribution_id,
830 p_current_cost => l_asset_cost,
831 p_book_type_code => l_book_type_code,
832 x_base_amount => l_base_amount,
833 x_return_status => l_return_status,
834 x_error_msg => l_error_message);
835 IF l_return_status <> fnd_api.g_ret_sts_success THEN
836 RAISE fnd_api.g_exc_error;
837 END IF;
838 l_asset_cost := l_base_amount;
839
840 SELECT poh.po_header_id,
841 poh.segment1,
842 poh.vendor_id
843 INTO l_inst_tbl(l_ind).po_header_id,
844 l_inst_tbl(l_ind).po_number,
845 l_inst_tbl(l_ind).po_vendor_id
846 FROM po_headers_all poh,
847 po_distributions_all pod
848 WHERE pod.po_distribution_id = l_inst_tbl(l_ind).po_distribution_id
849 AND poh.po_header_id = pod.po_header_id;
850
851 END IF;
852
853 l_inst_tbl(l_ind).asset_description := l_asset_description;
854 l_inst_tbl(l_ind).asset_unit_cost := l_asset_unit_cost;
855 l_inst_tbl(l_ind).asset_cost := l_asset_cost;
856 l_inst_tbl(l_ind).asset_category_id := l_asset_category_id;
857 l_inst_tbl(l_ind).group_asset_id := l_default_group_asset_id;
858 l_inst_tbl(l_ind).book_type_code := l_book_type_code;
859 l_inst_tbl(l_ind).date_placed_in_service := l_dpi;
860 l_inst_tbl(l_ind).asset_key_ccid := l_asset_key_ccid;
861 l_inst_tbl(l_ind).asset_location_id := l_fa_location_id;
862 l_inst_tbl(l_ind).deprn_expense_ccid := l_deprn_expense_ccid;
863 l_inst_tbl(l_ind).payables_ccid := l_payables_ccid;
864 l_inst_tbl(l_ind).tag_number := l_tag_number;
865 l_inst_tbl(l_ind).model_number := l_model_number;
866 l_inst_tbl(l_ind).manufacturer_name := l_manufacturer_name;
867 l_inst_tbl(l_ind).employee_id := l_employee_id;
868 l_inst_tbl(l_ind).search_method := l_search_method;
869
870 END LOOP;
871 END IF;
872 px_instance_tbl := l_inst_tbl;
873
874 EXCEPTION
875 WHEN fnd_api.g_exc_error THEN
876 x_return_status := fnd_api.g_ret_sts_error;
877 x_error_message := l_error_message;
878 END derive_asset_attribs;
879
880 PROCEDURE get_fixed_assets(
881 p_fa_query_rec IN fa_query_rec,
882 x_fixed_asset_rec OUT nocopy fixed_asset_rec,
883 x_return_status OUT nocopy varchar2,
884 x_error_message OUT nocopy varchar2)
885 IS
886
887 l_stmt varchar2(2000)
888 := 'SELECT fad.asset_id, fad.asset_number, fad.asset_category_id, fad.asset_key_ccid, '||
889 'fad.tag_number, fad.description, fad.manufacturer_name, fad.serial_number, '||
890 'fad.model_number, fad.current_units, fb.book_type_code, '||
891 'fb.date_placed_in_service, fb.cost, cia.instance_asset_id '||
892 'FROM fa_books fb, fa_additions fad, csi_i_assets cia, csi_item_instances cii '||
893 'WHERE fb.asset_id = fad.asset_id '||
894 'AND fb.date_ineffective is null '||
895 'AND cia.fa_asset_id = fad.asset_id '||
896 'AND cii.instance_id = cia.instance_id ';
897
898 l_and_clause varchar2(540);
899
900 l_cursor_id number;
901 l_rows_returned number;
902 l_ind binary_integer := 0;
903 l_asset_rec fixed_asset_rec;
904
905 BEGIN
906
907 x_return_status := fnd_api.g_ret_sts_success;
908
909 IF p_fa_query_rec.book_type_code is not null THEN
910 l_and_clause := 'AND fb.book_type_code = :book_type_code ';
911 END IF;
912
913 IF p_fa_query_rec.asset_category_id is not null THEN
914 l_and_clause := l_and_clause || 'AND fad.asset_category_id = :asset_category_id ';
915 END IF;
916
917 IF p_fa_query_rec.date_placed_in_service IS not null THEN
918 l_and_clause := l_and_clause || 'AND fb.date_placed_in_service = :dpi ';
919 END IF;
920
921 IF p_fa_query_rec.serial_number IS not null THEN
922 l_and_clause := l_and_clause || 'AND fad.serial_number = :serial_number ';
923 END IF;
924
925 IF p_fa_query_rec.model_number IS not null THEN
926 l_and_clause := l_and_clause || 'AND fad.model_number = :model_number ';
927 END IF;
928
929 IF p_fa_query_rec.tag_nuber IS not null THEN
930 l_and_clause := l_and_clause || 'AND fad.tag_number = :tag_number ';
931 END IF;
932
933 IF p_fa_query_rec.manufacturer_name IS not null THEN
934 l_and_clause := l_and_clause || 'AND fad.manfacturer_name = :manufacturer_name ';
935 END IF;
936
937 IF p_fa_query_rec.asset_key_ccid IS not null THEN
938 l_and_clause := l_and_clause || 'AND fad.asset_key_ccid = :asset_key_ccid ';
939 END IF;
940
941 IF p_fa_query_rec.inventory_item_id IS not null THEN
942 l_and_clause := l_and_clause || 'AND cii.inventory_item_id = :inventory_item_id ';
943 END IF;
944
945 IF p_fa_query_rec.search_method = 'FIFO' THEN
946 l_and_clause := l_and_clause || 'ORDER BY fb.date_placed_in_service, fad.asset_id';
947 ELSIF p_fa_query_rec.search_method = 'LIFO' THEN
948 l_and_clause := l_and_clause || 'ORDER BY fb.date_placed_in_service desc, fad.asset_id desc ';
949 END IF;
950
951 l_stmt := l_stmt||l_and_clause;
952
953 debug('fa query : '||l_stmt);
954
955 -- open cursor and parse
956 l_cursor_id := dbms_sql.open_cursor;
957 dbms_sql.parse(l_cursor_id, l_stmt , dbms_sql.native);
958
959 -- bind variables
960 IF p_fa_query_rec.inventory_item_id is not null THEN
961 dbms_sql.bind_variable(l_cursor_id, ':inventory_item_id', p_fa_query_rec.inventory_item_id);
962 END IF;
963
964 IF p_fa_query_rec.book_type_code is not null THEN
965 dbms_sql.bind_variable(l_cursor_id, ':book_type_code', p_fa_query_rec.book_type_code);
966 END IF;
967
968 IF p_fa_query_rec.asset_category_id is not null THEN
969 dbms_sql.bind_variable(l_cursor_id, ':asset_category_id', p_fa_query_rec.asset_category_id);
970 END IF;
971
972 IF p_fa_query_rec.date_placed_in_service IS not null THEN
973 dbms_sql.bind_variable(l_cursor_id, ':dpi', p_fa_query_rec.date_placed_in_service);
974 END IF;
975
976 IF p_fa_query_rec.serial_number IS not null THEN
977 dbms_sql.bind_variable(l_cursor_id, ':serial_number', p_fa_query_rec.serial_number);
978 END IF;
979
980 IF p_fa_query_rec.model_number IS not null THEN
981 dbms_sql.bind_variable(l_cursor_id, ':model_number', p_fa_query_rec.model_number);
982 END IF;
983
984 IF p_fa_query_rec.tag_nuber IS not null THEN
985 dbms_sql.bind_variable(l_cursor_id, ':tag_nuber', p_fa_query_rec.tag_nuber);
986 END IF;
987
988 IF p_fa_query_rec.manufacturer_name IS not null THEN
989 dbms_sql.bind_variable(l_cursor_id, ':manufacturer_name', p_fa_query_rec.manufacturer_name);
990 END IF;
991
992 IF p_fa_query_rec.asset_key_ccid IS not null THEN
993 dbms_sql.bind_variable(l_cursor_id, ':asset_key_ccid', p_fa_query_rec.asset_key_ccid);
994 END IF;
995
996 dbms_sql.define_column(l_cursor_id, 1, l_asset_rec.asset_id);
997 dbms_sql.define_column(l_cursor_id, 2, l_asset_rec.asset_number, 30);
998 dbms_sql.define_column(l_cursor_id, 3, l_asset_rec.asset_category_id);
999 dbms_sql.define_column(l_cursor_id, 4, l_asset_rec.asset_key_ccid);
1000 dbms_sql.define_column(l_cursor_id, 5, l_asset_rec.tag_number, 30);
1001 dbms_sql.define_column(l_cursor_id, 6, l_asset_rec.asset_description, 240);
1002 dbms_sql.define_column(l_cursor_id, 7, l_asset_rec.manufacturer_name, 30);
1003 dbms_sql.define_column(l_cursor_id, 8, l_asset_rec.serial_number, 80);
1004 dbms_sql.define_column(l_cursor_id, 9, l_asset_rec.model_number, 80);
1005 dbms_sql.define_column(l_cursor_id, 10, l_asset_rec.current_units);
1006 dbms_sql.define_column(l_cursor_id, 11, l_asset_rec.book_type_code, 30);
1007 dbms_sql.define_column(l_cursor_id, 12, l_asset_rec.date_placed_in_service);
1008 dbms_sql.define_column(l_cursor_id, 13, l_asset_rec.asset_cost);
1009 dbms_sql.define_column(l_cursor_id, 14, l_asset_rec.instance_asset_id);
1010
1011 l_rows_returned := dbms_sql.execute(l_cursor_id);
1012 LOOP
1013 exit when dbms_sql.fetch_rows(l_cursor_id) = 0;
1014
1015 l_ind := l_ind + 1;
1016
1017 dbms_sql.column_value(l_cursor_id, 1, l_asset_rec.asset_id);
1018 dbms_sql.column_value(l_cursor_id, 2, l_asset_rec.asset_number);
1019 dbms_sql.column_value(l_cursor_id, 3, l_asset_rec.asset_category_id);
1020 dbms_sql.column_value(l_cursor_id, 4, l_asset_rec.asset_key_ccid);
1021 dbms_sql.column_value(l_cursor_id, 5, l_asset_rec.tag_number);
1022 dbms_sql.column_value(l_cursor_id, 6, l_asset_rec.asset_description);
1023 dbms_sql.column_value(l_cursor_id, 7, l_asset_rec.manufacturer_name);
1024 dbms_sql.column_value(l_cursor_id, 8, l_asset_rec.serial_number);
1025 dbms_sql.column_value(l_cursor_id, 9, l_asset_rec.model_number);
1026 dbms_sql.column_value(l_cursor_id, 10, l_asset_rec.current_units);
1027 dbms_sql.column_value(l_cursor_id, 11, l_asset_rec.book_type_code);
1028 dbms_sql.column_value(l_cursor_id, 12, l_asset_rec.date_placed_in_service);
1029 dbms_sql.column_value(l_cursor_id, 13, l_asset_rec.asset_cost);
1030 dbms_sql.column_value(l_cursor_id, 14, l_asset_rec.instance_asset_id);
1031
1032 exit;
1033
1034 END LOOP;
1035
1036 dbms_sql.close_cursor(l_cursor_id);
1037
1038 x_fixed_asset_rec := l_asset_rec;
1039
1040 END get_fixed_assets;
1041
1042 PROCEDURE get_instance_asset(
1043 p_instance_id IN number,
1044 p_asset_id IN number,
1045 x_inst_asset_rec OUT nocopy csi_datastructures_pub.instance_asset_rec,
1046 x_return_status OUT nocopy varchar2,
1047 x_error_message OUT nocopy varchar2)
1048 IS
1049 l_inst_asset_query_rec csi_datastructures_pub.instance_asset_query_rec;
1050 l_inst_asset_tbl csi_datastructures_pub.instance_asset_header_tbl;
1051
1052 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1053 l_msg_count number;
1054 l_msg_data varchar2(2000);
1055
1056 BEGIN
1057
1058 x_return_status := fnd_api.g_ret_sts_success;
1059
1060 l_inst_asset_query_rec.instance_id := p_instance_id;
1061 l_inst_asset_query_rec.fa_asset_id := p_asset_id;
1062
1063 debug('inside api csi_asset_pvt.get_instance_assets');
1064
1065 csi_asset_pvt.get_instance_assets(
1066 p_api_version => 1.0,
1067 p_commit => fnd_api.g_false,
1068 p_init_msg_list => fnd_api.g_true,
1069 p_validation_level => fnd_api.g_valid_level_full,
1070 p_instance_asset_query_rec => l_inst_asset_query_rec,
1071 p_resolve_id_columns => fnd_api.g_true,
1072 p_time_stamp => to_date(null),
1073 x_instance_asset_tbl => l_inst_asset_tbl,
1074 x_return_status => l_return_status,
1075 x_msg_count => l_msg_count,
1076 x_msg_data => l_msg_data);
1077
1078 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1079 RAISE fnd_api.g_exc_error;
1080 END IF;
1081
1082 IF l_inst_asset_tbl.COUNT > 0 THEN
1083 FOR l_ind IN l_inst_asset_tbl.FIRST .. l_inst_asset_tbl.LAST
1084 LOOP
1085
1086 debug(' instance_asset_id : '||l_inst_asset_tbl(l_ind).instance_asset_id);
1087 debug(' asset_id : '||l_inst_asset_tbl(l_ind).fa_asset_id);
1088 debug(' asset_quantity : '||l_inst_asset_tbl(l_ind).asset_quantity);
1089 debug(' active_start_date : '||l_inst_asset_tbl(l_ind).active_start_date);
1090 debug(' active_end_date : '||l_inst_asset_tbl(l_ind).active_end_date);
1091
1092 IF sysdate BETWEEN nvl(l_inst_asset_tbl(l_ind).active_start_date, sysdate-1)
1093 AND nvl(l_inst_asset_tbl(l_ind).active_end_date, sysdate+1)
1094 THEN
1095
1096 x_inst_asset_rec.instance_asset_id := l_inst_asset_tbl(l_ind).instance_asset_id;
1097 x_inst_asset_rec.instance_id := l_inst_asset_tbl(l_ind).instance_id;
1098 x_inst_asset_rec.asset_quantity := l_inst_asset_tbl(l_ind).asset_quantity;
1099 x_inst_asset_rec.fa_asset_id := l_inst_asset_tbl(l_ind).fa_asset_id;
1100 x_inst_asset_rec.fa_book_type_code := l_inst_asset_tbl(l_ind).fa_book_type_code;
1101 x_inst_asset_rec.fa_location_id := l_inst_asset_tbl(l_ind).fa_location_id;
1102 x_inst_asset_rec.object_version_number := l_inst_asset_tbl(l_ind).object_version_number;
1103
1104 exit;
1105
1106 END IF;
1107 END LOOP;
1108 END IF;
1109
1110 EXCEPTION
1111 WHEN fnd_api.g_exc_error THEN
1112 x_return_status := fnd_api.g_ret_sts_success;
1113 END get_instance_asset;
1114
1115
1116 PROCEDURE get_pending_additions(
1117 p_fa_query_rec IN fa_query_rec,
1118 x_fixed_asset_rec OUT nocopy fixed_asset_rec,
1119 x_return_status OUT nocopy varchar2,
1120 x_error_message OUT nocopy varchar2)
1121 IS
1122
1123 l_posting_status constant varchar2(6) := 'POSTED';
1124 l_split_merged_code constant varchar2(2) := 'MP';
1125
1126 l_stmt varchar2(2000) :=
1127 'SELECT fma.mass_addition_id, '||
1128 'fma.model_number, '||
1129 'fma.serial_number, '||
1130 'fma.manufacturer_name, '||
1131 'fma.description, '||
1132 'fma.tag_number, '||
1133 'fma.asset_key_ccid, '||
1134 'fma.asset_category_id, '||
1135 'fma.asset_number, '||
1136 'fma.date_placed_in_service, '||
1137 'fma.reviewer_comments, '||
1138 'fma.feeder_system_name, '||
1139 'cia.instance_asset_id '||
1140 'FROM fa_mass_additions fma, csi_i_assets cia, csi_item_instances cii ';
1141
1142 l_where_clause varchar2(240);
1143 l_and_clause varchar2(540);
1144
1145 l_cursor_id number;
1146 l_rows_returned number;
1147 l_ind binary_integer := 0;
1148
1149 l_asset_rec fixed_asset_rec;
1150
1151 BEGIN
1152
1153 l_where_clause := 'WHERE fma.posting_status <> :posting_status '||
1154 'AND fma.split_merged_code = :split_merged_code '||
1155 'AND cia.fa_mass_addition_id = fma.mass_addition_id '||
1156 'AND cii.instance_id = cia.instance_id ';
1157
1158 IF p_fa_query_rec.book_type_code is not null THEN
1159 l_and_clause := 'AND fma.book_type_code = :book_type_code ';
1160 END IF;
1161
1162 IF p_fa_query_rec.asset_category_id is not null THEN
1163 l_and_clause := l_and_clause || 'AND fma.asset_category_id = :asset_category_id ';
1164 END IF;
1165
1166 /*
1167 IF p_fa_query_rec.serial_number IS not null THEN
1168 l_and_clause := l_and_clause || 'AND fma.serial_number = :serial_number ';
1169 END IF;
1170 */
1171
1172 IF p_fa_query_rec.asset_description IS not null THEN
1173 l_and_clause := l_and_clause || 'AND fma.description = :asset_description ';
1174 END IF;
1175
1176 IF p_fa_query_rec.date_placed_in_service IS not null THEN
1177 l_and_clause := l_and_clause || 'AND fma.date_placed_in_service = :dpi ';
1178 END IF;
1179
1180 IF p_fa_query_rec.model_number IS not null THEN
1181 l_and_clause := l_and_clause || 'AND fma.model_number = :model_number ';
1182 END IF;
1183
1184 IF p_fa_query_rec.tag_nuber IS not null THEN
1185 l_and_clause := l_and_clause || 'AND fma.tag_number = :tag_number ';
1186 END IF;
1187
1188 IF p_fa_query_rec.manufacturer_name IS not null THEN
1189 l_and_clause := l_and_clause || 'AND fma.manfacturer_name = :manufacturer_name ';
1190 END IF;
1191
1192 IF p_fa_query_rec.asset_key_ccid IS not null THEN
1193 l_and_clause := l_and_clause || 'AND fma.asset_key_ccid = :asset_key_ccid ';
1194 END IF;
1195
1196 IF p_fa_query_rec.inventory_item_id IS not null THEN
1197 l_and_clause := l_and_clause || 'AND cii.inventory_item_id = :inventory_item_id ';
1198 END IF;
1199
1200 IF p_fa_query_rec.search_method = 'FIFO' THEN
1201 l_and_clause := l_and_clause ||
1202 'ORDER BY fma.date_placed_in_service, fma.mass_addition_id';
1203 ELSIF p_fa_query_rec.search_method = 'LIFO' THEN
1204 l_and_clause := l_and_clause ||
1205 'ORDER BY fma.date_placed_in_service desc, fma.mass_addition_id desc ';
1206 END IF;
1207
1208 l_stmt := l_stmt||l_where_clause||l_and_clause;
1209
1210 debug('fma query : '||l_stmt);
1211
1212 l_cursor_id := dbms_sql.open_cursor;
1213 dbms_sql.parse(l_cursor_id, l_stmt , dbms_sql.native);
1214
1215 -- bind variables
1216 dbms_sql.bind_variable(l_cursor_id, ':posting_status', l_posting_status);
1217 dbms_sql.bind_variable(l_cursor_id, ':split_merged_code', l_split_merged_code);
1218
1219 IF p_fa_query_rec.book_type_code is not null THEN
1220 dbms_sql.bind_variable(l_cursor_id, ':book_type_code', p_fa_query_rec.book_type_code);
1221 END IF;
1222
1223 IF p_fa_query_rec.asset_category_id is not null THEN
1224 dbms_sql.bind_variable(l_cursor_id, ':asset_category_id', p_fa_query_rec.asset_category_id);
1225 END IF;
1226
1227 IF p_fa_query_rec.asset_description IS not null THEN
1228 dbms_sql.bind_variable(l_cursor_id, ':asset_description', p_fa_query_rec.asset_description);
1229 END IF;
1230
1231 IF p_fa_query_rec.serial_number IS not null THEN
1232 dbms_sql.bind_variable(l_cursor_id, ':serial_number', p_fa_query_rec.serial_number);
1233 END IF;
1234
1235 IF p_fa_query_rec.date_placed_in_service IS not null THEN
1236 dbms_sql.bind_variable(l_cursor_id, ':dpi', p_fa_query_rec.date_placed_in_service);
1237 END IF;
1238
1239 IF p_fa_query_rec.model_number IS not null THEN
1240 dbms_sql.bind_variable(l_cursor_id, ':model_number', p_fa_query_rec.model_number);
1241 END IF;
1242
1243 IF p_fa_query_rec.tag_nuber IS not null THEN
1244 dbms_sql.bind_variable(l_cursor_id, ':tag_nuber', p_fa_query_rec.tag_nuber);
1245 END IF;
1246
1247 IF p_fa_query_rec.manufacturer_name IS not null THEN
1248 dbms_sql.bind_variable(l_cursor_id, ':manufacturer_name', p_fa_query_rec.manufacturer_name);
1249 END IF;
1250
1251 IF p_fa_query_rec.asset_key_ccid IS not null THEN
1252 dbms_sql.bind_variable(l_cursor_id, ':asset_key_ccid', p_fa_query_rec.asset_key_ccid);
1253 END IF;
1254
1255 IF p_fa_query_rec.inventory_item_id IS not null THEN
1256 dbms_sql.bind_variable(l_cursor_id, ':inventory_item_id', p_fa_query_rec.inventory_item_id);
1257 END IF;
1258
1259 dbms_sql.define_column(l_cursor_id, 1, l_asset_rec.mass_addition_id);
1260 dbms_sql.define_column(l_cursor_id, 2, l_asset_rec.model_number, 80);
1261 dbms_sql.define_column(l_cursor_id, 3, l_asset_rec.serial_number, 80);
1262 dbms_sql.define_column(l_cursor_id, 4, l_asset_rec.manufacturer_name, 30);
1263 dbms_sql.define_column(l_cursor_id, 5, l_asset_rec.asset_description, 240);
1264 dbms_sql.define_column(l_cursor_id, 6, l_asset_rec.tag_number, 30);
1265 dbms_sql.define_column(l_cursor_id, 7, l_asset_rec.asset_key_ccid);
1266 dbms_sql.define_column(l_cursor_id, 8, l_asset_rec.asset_category_id);
1267 dbms_sql.define_column(l_cursor_id, 9, l_asset_rec.asset_number, 30);
1268 dbms_sql.define_column(l_cursor_id, 10, l_asset_rec.date_placed_in_service);
1269 dbms_sql.define_column(l_cursor_id, 11, l_asset_rec.reviewer_comments, 240);
1270 dbms_sql.define_column(l_cursor_id, 12, l_asset_rec.feeder_system_name, 40);
1271 dbms_sql.define_column(l_cursor_id, 13, l_asset_rec.instance_asset_id);
1272
1273 l_rows_returned := dbms_sql.execute(l_cursor_id);
1274
1275 LOOP
1276
1277 exit when dbms_sql.fetch_rows(l_cursor_id) = 0;
1278 l_ind := l_ind + 1;
1279
1280 dbms_sql.column_value(l_cursor_id, 1, l_asset_rec.mass_addition_id);
1281 dbms_sql.column_value(l_cursor_id, 2, l_asset_rec.model_number);
1282 dbms_sql.column_value(l_cursor_id, 3, l_asset_rec.serial_number);
1283 dbms_sql.column_value(l_cursor_id, 4, l_asset_rec.manufacturer_name);
1284 dbms_sql.column_value(l_cursor_id, 5, l_asset_rec.asset_description);
1285 dbms_sql.column_value(l_cursor_id, 6, l_asset_rec.tag_number);
1286 dbms_sql.column_value(l_cursor_id, 7, l_asset_rec.asset_key_ccid);
1287 dbms_sql.column_value(l_cursor_id, 8, l_asset_rec.asset_category_id);
1288 dbms_sql.column_value(l_cursor_id, 9, l_asset_rec.asset_number);
1289 dbms_sql.column_value(l_cursor_id, 10, l_asset_rec.date_placed_in_service);
1290 dbms_sql.column_value(l_cursor_id, 11, l_asset_rec.reviewer_comments);
1291 dbms_sql.column_value(l_cursor_id, 12, l_asset_rec.feeder_system_name);
1292 dbms_sql.column_value(l_cursor_id, 13, l_asset_rec.instance_asset_id);
1293
1294 exit;
1295
1296 END LOOP;
1297
1298 dbms_sql.close_cursor(l_cursor_id);
1299
1300 x_fixed_asset_rec := l_asset_rec;
1301
1302 END get_pending_additions;
1303
1304 PROCEDURE amend_instance_asset(
1305 p_action IN varchar2,
1306 p_inst_rec IN instance_rec,
1307 p_mass_addition_id IN number,
1308 p_asset_id IN number,
1309 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
1310 x_inst_asset_rec OUT nocopy csi_datastructures_pub.instance_asset_rec,
1311 x_return_status OUT nocopy varchar2)
1312 IS
1313 l_asset_id number;
1314 l_inst_asset_rec csi_datastructures_pub.instance_asset_rec;
1315 l_lookup_tbl csi_asset_pvt.lookup_tbl;
1316 l_asset_count_rec csi_asset_pvt.asset_count_rec;
1317 l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
1318 l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl;
1319
1320 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1321 l_msg_count number;
1322 l_msg_data varchar2(2000);
1323 l_error_message varchar2(2000);
1324 BEGIN
1325
1326 x_return_status := fnd_api.g_ret_sts_success;
1327
1328 debug('inside api amend_instance_asset');
1329
1330 IF p_action = 'ADD_TO_ASSET' THEN
1331
1332 IF p_inst_rec.fa_group_by = 'ITEM' THEN
1333 l_asset_id := p_asset_id;
1334 ELSE
1335 l_asset_id := fnd_api.g_miss_num;
1336 END IF;
1337
1338 get_instance_asset(
1339 p_instance_id => p_inst_rec.instance_id,
1340 p_asset_id => l_asset_id,
1341 x_inst_asset_rec => l_inst_asset_rec,
1342 x_return_status => l_return_status,
1343 x_error_message => l_error_message);
1344
1345 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1346 RAISE fnd_api.g_exc_error;
1347 END IF;
1348
1349 IF nvl(l_inst_asset_rec.instance_asset_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1350
1351 IF p_inst_rec.fa_group_by = 'ITEM' THEN
1352
1353 SELECT asset_quantity + p_inst_rec.quantity,
1354 object_version_number
1355 INTO l_inst_asset_rec.asset_quantity,
1356 l_inst_asset_rec.object_version_number
1357 FROM csi_i_assets
1358 WHERE instance_asset_id = l_inst_asset_rec.instance_asset_id;
1359
1360 l_inst_asset_rec.fa_book_type_code := p_inst_rec.book_type_code;
1361 l_inst_asset_rec.fa_asset_id := p_asset_id;
1362 l_inst_asset_rec.fa_sync_flag := 'Y';
1363
1364 csi_asset_pvt.update_instance_asset(
1365 p_api_version => 1.0,
1366 p_commit => fnd_api.g_false,
1367 p_init_msg_list => fnd_api.g_true,
1368 p_validation_level => fnd_api.g_valid_level_full,
1369 p_instance_asset_rec => l_inst_asset_rec,
1370 p_txn_rec => px_csi_txn_rec,
1371 x_return_status => l_return_status,
1372 x_msg_count => l_msg_count,
1373 x_msg_data => l_msg_data,
1374 p_lookup_tbl => l_lookup_tbl,
1375 p_asset_count_rec => l_asset_count_rec,
1376 p_asset_id_tbl => l_asset_id_tbl,
1377 p_asset_loc_tbl => l_asset_loc_tbl);
1378
1379 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1380 RAISE fnd_api.g_exc_error;
1381 END IF;
1382
1383 debug('instance asset rec updated. instance_asset_id : '||l_inst_asset_rec.instance_asset_id);
1384
1385 ELSE
1386 -- for a serialized item instance if you find an asset association then hey
1387 -- one of your smart user has already associated it to a fixed asset
1388 -- i am not gonna flex my muscle to do all this over again
1389 NULL;
1390 END IF;
1391
1392 ELSE
1393
1394 l_inst_asset_rec.instance_asset_id := fnd_api.g_miss_num;
1395 l_inst_asset_rec.instance_id := p_inst_rec.instance_id;
1396 l_inst_asset_rec.fa_book_type_code := p_inst_rec.book_type_code;
1397 l_inst_asset_rec.fa_asset_id := p_asset_id;
1398 l_inst_asset_rec.fa_location_id := p_inst_rec.asset_location_id;
1399 l_inst_asset_rec.asset_quantity := p_inst_rec.quantity;
1400 l_inst_asset_rec.fa_mass_addition_id := p_mass_addition_id;
1401 l_inst_asset_rec.update_status := 'IN_SERVICE';
1402 l_inst_asset_rec.fa_sync_flag := 'Y';
1403
1404 csi_asset_pvt.create_instance_asset(
1405 p_api_version => 1.0,
1406 p_commit => fnd_api.g_false,
1407 p_init_msg_list => fnd_api.g_true,
1408 p_validation_level => fnd_api.g_valid_level_full,
1409 p_instance_asset_rec => l_inst_asset_rec,
1410 p_txn_rec => px_csi_txn_rec,
1411 x_return_status => l_return_status,
1412 x_msg_count => l_msg_count,
1413 x_msg_data => l_msg_data,
1414 p_lookup_tbl => l_lookup_tbl,
1415 p_asset_count_rec => l_asset_count_rec,
1416 p_asset_id_tbl => l_asset_id_tbl,
1417 p_asset_loc_tbl => l_asset_loc_tbl);
1418
1419 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1420 RAISE fnd_api.g_exc_error;
1421 END IF;
1422
1423 debug('instance asset rec created. instance_asset_id : '||l_inst_asset_rec.instance_asset_id);
1424
1425 END IF;
1426 ELSIF p_action = 'CREATE_MASS_ADDITION' THEN
1427
1428 l_inst_asset_rec.instance_id := p_inst_rec.instance_id;
1429 l_inst_asset_rec.update_status := 'IN_SERVICE';
1430 l_inst_asset_rec.fa_book_type_code := p_inst_rec.book_type_code;
1431 l_inst_asset_rec.fa_location_id := p_inst_rec.asset_location_id;
1432 l_inst_asset_rec.asset_quantity := p_inst_rec.quantity;
1433 l_inst_asset_rec.fa_mass_addition_id := p_mass_addition_id;
1434 l_inst_asset_rec.fa_sync_flag := 'N';
1435 l_inst_asset_rec.check_for_instance_expiry := fnd_api.g_false;
1436
1437 csi_asset_pvt.create_instance_asset(
1438 p_api_version => 1.0,
1439 p_commit => fnd_api.g_false,
1440 p_init_msg_list => fnd_api.g_true,
1441 p_validation_level => fnd_api.g_valid_level_full,
1442 p_instance_asset_rec => l_inst_asset_rec,
1443 p_txn_rec => px_csi_txn_rec,
1444 x_return_status => l_return_status,
1445 x_msg_count => l_msg_count,
1446 x_msg_data => l_msg_data,
1447 p_lookup_tbl => l_lookup_tbl,
1448 p_asset_count_rec => l_asset_count_rec,
1449 p_asset_id_tbl => l_asset_id_tbl,
1450 p_asset_loc_tbl => l_asset_loc_tbl);
1451
1452 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1453 RAISE fnd_api.g_exc_error;
1454 END IF;
1455
1456 debug('instance asset rec created. instance_asset_id : '||l_inst_asset_rec.instance_asset_id);
1457 x_inst_asset_rec := l_inst_asset_rec;
1458
1459 ELSIF p_action = 'ADD_TO_MASS_ADDITION' THEN
1460
1461 get_instance_asset(
1462 p_instance_id => p_inst_rec.instance_id,
1463 p_asset_id => fnd_api.g_miss_num,
1464 x_inst_asset_rec => l_inst_asset_rec,
1465 x_return_status => l_return_status,
1466 x_error_message => l_error_message);
1467
1468 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1469 RAISE fnd_api.g_exc_error;
1470 END IF;
1471
1472 IF nvl(l_inst_asset_rec.instance_asset_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1473
1474 IF p_inst_rec.fa_group_by = 'ITEM' THEN
1475
1476 SELECT asset_quantity + p_inst_rec.quantity,
1477 object_version_number
1478 INTO l_inst_asset_rec.asset_quantity,
1479 l_inst_asset_rec.object_version_number
1480 FROM csi_i_assets
1481 WHERE instance_asset_id = l_inst_asset_rec.instance_asset_id;
1482
1483 l_inst_asset_rec.fa_book_type_code := p_inst_rec.book_type_code;
1484 l_inst_asset_rec.fa_mass_addition_id := p_mass_addition_id;
1485 l_inst_asset_rec.fa_sync_flag := 'N';
1486
1487 csi_asset_pvt.update_instance_asset(
1488 p_api_version => 1.0,
1489 p_commit => fnd_api.g_false,
1490 p_init_msg_list => fnd_api.g_true,
1491 p_validation_level => fnd_api.g_valid_level_full,
1492 p_instance_asset_rec => l_inst_asset_rec,
1493 p_txn_rec => px_csi_txn_rec,
1494 x_return_status => l_return_status,
1495 x_msg_count => l_msg_count,
1496 x_msg_data => l_msg_data,
1497 p_lookup_tbl => l_lookup_tbl,
1498 p_asset_count_rec => l_asset_count_rec,
1499 p_asset_id_tbl => l_asset_id_tbl,
1500 p_asset_loc_tbl => l_asset_loc_tbl);
1501
1502 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1503 RAISE fnd_api.g_exc_error;
1504 END IF;
1505
1506 debug('instance asset rec updated. instance_asset_id : '||l_inst_asset_rec.instance_asset_id);
1507 END IF;
1508
1509 ELSE
1510
1511 l_inst_asset_rec.instance_asset_id := fnd_api.g_miss_num;
1512 l_inst_asset_rec.instance_id := p_inst_rec.instance_id;
1513 l_inst_asset_rec.fa_book_type_code := p_inst_rec.book_type_code;
1514 l_inst_asset_rec.fa_location_id := p_inst_rec.asset_location_id;
1515 l_inst_asset_rec.asset_quantity := p_inst_rec.quantity;
1516 l_inst_asset_rec.fa_mass_addition_id := p_mass_addition_id;
1517 l_inst_asset_rec.update_status := 'IN_SERVICE';
1518 l_inst_asset_rec.fa_sync_flag := 'N';
1519
1520 csi_asset_pvt.create_instance_asset(
1521 p_api_version => 1.0,
1522 p_commit => fnd_api.g_false,
1523 p_init_msg_list => fnd_api.g_true,
1524 p_validation_level => fnd_api.g_valid_level_full,
1525 p_instance_asset_rec => l_inst_asset_rec,
1526 p_txn_rec => px_csi_txn_rec,
1527 x_return_status => l_return_status,
1528 x_msg_count => l_msg_count,
1529 x_msg_data => l_msg_data,
1530 p_lookup_tbl => l_lookup_tbl,
1531 p_asset_count_rec => l_asset_count_rec,
1532 p_asset_id_tbl => l_asset_id_tbl,
1533 p_asset_loc_tbl => l_asset_loc_tbl);
1534
1535 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1536 RAISE fnd_api.g_exc_error;
1537 END IF;
1538
1539 debug('instance asset rec created. instance_asset_id : '||l_inst_asset_rec.instance_asset_id);
1540
1541 END IF;
1542
1543 END IF;
1544
1545 EXCEPTION
1546 WHEN fnd_api.g_exc_error THEN
1547 x_return_status := fnd_api.g_ret_sts_error;
1548 END amend_instance_asset;
1549
1550 PROCEDURE create_mass_addition(
1551 p_instance_rec IN instance_rec,
1552 x_mass_addition_id OUT nocopy number,
1553 x_return_status OUT nocopy varchar2,
1554 x_error_message OUT nocopy varchar2)
1555 IS
1556
1557 l_parent_posting_status varchar2(10) := 'POST' ;
1558 l_child_posting_status varchar2(10) := 'MERGED';
1559 l_parent_merge_code varchar2(2) := 'MP';
1560 l_child_merge_code varchar2(2) := 'MC';
1561
1562 l_p_mass_add_rec fa_mass_additions%rowtype;
1563 l_c_mass_add_rec fa_mass_additions%rowtype;
1564
1565 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1566 l_msg_count number;
1567 l_msg_data varchar2(2000);
1568
1569 BEGIN
1570
1571 x_return_status := fnd_api.g_ret_sts_success;
1572
1573 debug('inside api create_mass_addition');
1574
1575 savepoint create_mass_addition;
1576
1577 -- parent mass addition record
1578
1579 l_p_mass_add_rec.mass_addition_id := null;
1580 l_p_mass_add_rec.description := p_instance_rec.asset_description;
1581 l_p_mass_add_rec.asset_category_id := p_instance_rec.asset_category_id;
1582 l_p_mass_add_rec.book_type_code := p_instance_rec.book_type_code;
1583 l_p_mass_add_rec.location_id := p_instance_rec.asset_location_id;
1584 l_p_mass_add_rec.asset_key_ccid := p_instance_rec.asset_key_ccid;
1585 l_p_mass_add_rec.tag_number := p_instance_rec.tag_number;
1586 l_p_mass_add_rec.model_number := p_instance_rec.model_number;
1587 l_p_mass_add_rec.manufacturer_name := p_instance_rec.manufacturer_name;
1588 l_p_mass_add_rec.project_id := p_instance_rec.pa_project_id;
1589 l_p_mass_add_rec.task_id := p_instance_rec.pa_project_task_id;
1590 l_p_mass_add_rec.payables_code_combination_id := p_instance_rec.payables_ccid;
1591 l_p_mass_add_rec.expense_code_combination_id := p_instance_rec.deprn_expense_ccid;
1592
1593 l_p_mass_add_rec.po_number := p_instance_rec.po_number;
1594 l_p_mass_add_rec.po_vendor_id := p_instance_rec.po_vendor_id;
1595 l_p_mass_add_rec.po_distribution_id := p_instance_rec.po_distribution_id;
1596
1597 IF p_instance_rec.fa_group_by = 'ITEM' THEN
1598 l_p_mass_add_rec.payables_units := p_instance_rec.mtl_txn_qty;
1599 l_p_mass_add_rec.fixed_assets_units := p_instance_rec.mtl_txn_qty;
1600 l_p_mass_add_rec.date_placed_in_service := p_instance_rec.date_placed_in_service;
1601 ELSE
1602 l_p_mass_add_rec.payables_units := p_instance_rec.quantity;
1603 l_p_mass_add_rec.fixed_assets_units := p_instance_rec.quantity;
1604 l_p_mass_add_rec.date_placed_in_service := p_instance_rec.csi_txn_date;
1605 l_p_mass_add_rec.serial_number := p_instance_rec.serial_number;
1606 END IF;
1607
1608 l_p_mass_add_rec.feeder_system_name := 'ORACLE ENTERPRISE INSTALL BASE';
1609 l_p_mass_add_rec.queue_name := 'POST';
1610 l_p_mass_add_rec.asset_type := 'CAPITALIZED';
1611 l_p_mass_add_rec.depreciate_flag := 'YES';
1612 l_p_mass_add_rec.created_by := fnd_global.user_id;
1613 l_p_mass_add_rec.creation_date := sysdate;
1614 l_p_mass_add_rec.last_update_date := sysdate;
1615 l_p_mass_add_rec.last_update_login := fnd_global.login_id;
1616
1617 SELECT default_group_asset_id
1618 INTO l_p_mass_add_rec.group_asset_id
1619 FROM fa_category_books
1620 WHERE category_id = l_p_mass_add_rec.asset_category_id
1621 AND book_type_code = l_p_mass_add_rec.book_type_code;
1622
1623 l_p_mass_add_rec.parent_mass_addition_id := NULL;
1624 l_p_mass_add_rec.merge_parent_mass_additions_id := NULL;
1625 l_p_mass_add_rec.posting_status := l_parent_posting_status;
1626 l_p_mass_add_rec.split_merged_code := l_parent_merge_code;
1627 l_p_mass_add_rec.merged_code := l_parent_merge_code;
1628 l_p_mass_add_rec.fixed_assets_cost := 0;
1629 l_p_mass_add_rec.payables_cost := 0;
1630
1631 cse_asset_util_pkg.insert_mass_add(
1632 p_api_version => 1.0,
1633 p_commit => fnd_api.g_false,
1634 p_init_msg_list => fnd_api.g_true,
1635 p_mass_add_rec => l_p_mass_add_rec,
1636 x_return_status => l_return_status,
1637 x_msg_count => l_msg_count,
1638 x_msg_data => l_msg_data);
1639
1640 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1641 RAISE fnd_api.g_exc_error;
1642 END IF;
1643
1644 debug('mass_addition rec created. parent_mass_addition_id : '||l_p_mass_add_rec.mass_addition_id);
1645
1646 l_c_mass_add_rec := l_p_mass_add_rec;
1647
1648 l_c_mass_add_rec.mass_addition_id := null;
1649 l_c_mass_add_rec.parent_mass_addition_id := l_p_mass_add_rec.mass_addition_id;
1650 l_c_mass_add_rec.merge_parent_mass_additions_id := l_p_mass_add_rec.mass_addition_id;
1651 l_c_mass_add_rec.posting_status := l_child_posting_status;
1652 l_c_mass_add_rec.split_merged_code := l_child_merge_code;
1653 l_c_mass_add_rec.merged_code := l_child_merge_code;
1654
1655 l_c_mass_add_rec.fixed_assets_cost := p_instance_rec.asset_unit_cost *
1656 NVL( l_p_mass_add_rec.fixed_assets_units,0);
1657 l_c_mass_add_rec.payables_cost := p_instance_rec.asset_unit_cost *
1658 NVL(l_p_mass_add_rec.payables_units,0);
1659
1660 cse_asset_util_pkg.insert_mass_add(
1661 p_api_version => 1.0,
1662 p_commit => fnd_api.g_false,
1663 p_init_msg_list => fnd_api.g_true,
1664 p_mass_add_rec => l_c_mass_add_rec,
1665 x_return_status => l_return_status,
1666 x_msg_count => l_msg_count,
1667 x_msg_data => l_msg_data);
1668
1669 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1670 RAISE fnd_api.g_exc_error;
1671 END IF;
1672
1673 x_mass_addition_id := l_p_mass_add_rec.mass_addition_id;
1674
1675 EXCEPTION
1676 WHEN fnd_api.g_exc_error THEN
1677 rollback to create_mass_addition;
1678 x_return_status := fnd_api.g_ret_sts_error;
1679 x_error_message := cse_util_pkg.dump_error_stack;
1680 END create_mass_addition;
1681
1682 PROCEDURE add_to_mass_addition(
1683 p_mass_addition_id IN number,
1684 p_instance_rec IN instance_rec,
1685 x_return_status OUT nocopy varchar2,
1686 x_error_message OUT nocopy varchar2)
1687 IS
1688
1689 l_child_posting_status varchar2(10) := 'MERGED';
1690 l_child_merge_code varchar2(2) := 'MC';
1691
1692 l_c_mass_add_rec fa_mass_additions%rowtype;
1693
1694 l_asset_quantity number;
1695 l_obj_ver_num number;
1696
1697 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1698 l_msg_count number;
1699 l_msg_data varchar2(2000);
1700 l_error_message varchar2(2000);
1701
1702 BEGIN
1703
1704 l_c_mass_add_rec.mass_addition_id := null;
1705 l_c_mass_add_rec.description := p_instance_rec.asset_description;
1706 l_c_mass_add_rec.asset_category_id := p_instance_rec.asset_category_id;
1707 l_c_mass_add_rec.book_type_code := p_instance_rec.book_type_code;
1708 l_c_mass_add_rec.date_placed_in_service := p_instance_rec.date_placed_in_service;
1709 l_c_mass_add_rec.location_id := p_instance_rec.asset_location_id;
1710 l_c_mass_add_rec.asset_key_ccid := p_instance_rec.asset_key_ccid;
1711 l_c_mass_add_rec.tag_number := p_instance_rec.tag_number;
1712 l_c_mass_add_rec.model_number := p_instance_rec.model_number;
1713 l_c_mass_add_rec.manufacturer_name := p_instance_rec.manufacturer_name;
1714 l_c_mass_add_rec.project_id := p_instance_rec.pa_project_id;
1715 l_c_mass_add_rec.task_id := p_instance_rec.pa_project_task_id;
1716 l_c_mass_add_rec.payables_code_combination_id := p_instance_rec.payables_ccid;
1717 l_c_mass_add_rec.expense_code_combination_id := p_instance_rec.deprn_expense_ccid;
1718 l_c_mass_add_rec.feeder_system_name := 'ORACLE ENTERPRISE INSTALL BASE';
1719 l_c_mass_add_rec.queue_name := 'POST';
1720 l_c_mass_add_rec.asset_type := 'CAPITALIZED';
1721 l_c_mass_add_rec.depreciate_flag := 'YES';
1722 l_c_mass_add_rec.created_by := fnd_global.user_id;
1723 l_c_mass_add_rec.creation_date := sysdate;
1724 l_c_mass_add_rec.last_update_date := sysdate;
1725 l_c_mass_add_rec.last_update_login := fnd_global.login_id;
1726
1727 IF p_instance_rec.fa_group_by = 'ITEM' THEN
1728 l_c_mass_add_rec.payables_units := p_instance_rec.mtl_txn_qty;
1729 l_c_mass_add_rec.fixed_assets_units := p_instance_rec.mtl_txn_qty;
1730 ELSE
1731 l_c_mass_add_rec.payables_units := p_instance_rec.quantity;
1732 l_c_mass_add_rec.fixed_assets_units := p_instance_rec.quantity;
1733 END IF;
1734
1735 l_c_mass_add_rec.payables_cost := p_instance_rec.asset_unit_cost *
1736 NVL(l_c_mass_add_rec.payables_units,0) ;
1737 l_c_mass_add_rec.fixed_assets_cost := p_instance_rec.asset_unit_cost *
1738 NVL( l_c_mass_add_rec.fixed_assets_units,0);
1739
1740 l_c_mass_add_rec.mass_addition_id := null;
1741 l_c_mass_add_rec.parent_mass_addition_id := p_mass_addition_id;
1742 l_c_mass_add_rec.merge_parent_mass_additions_id := p_mass_addition_id;
1743 l_c_mass_add_rec.posting_status := l_child_posting_status;
1744 l_c_mass_add_rec.split_merged_code := l_child_merge_code;
1745 l_c_mass_add_rec.merged_code := l_child_merge_code;
1746
1747 cse_asset_util_pkg.insert_mass_add(
1748 p_api_version => 1.0,
1749 p_commit => fnd_api.g_false,
1750 p_init_msg_list => fnd_api.g_true,
1751 p_mass_add_rec => l_c_mass_add_rec,
1752 x_return_status => l_return_status,
1753 x_msg_count => l_msg_count,
1754 x_msg_data => l_msg_data);
1755
1756 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1757 RAISE fnd_api.g_exc_error;
1758 END IF;
1759
1760 -- when a child record is added the parent record does not get the cumulative quantity
1761 UPDATE fa_mass_additions
1762 SET payables_units = payables_units + NVL(l_c_mass_add_rec.payables_units,0),
1763 fixed_assets_units = fixed_assets_units + NVL(l_c_mass_add_rec.fixed_assets_units,0)
1764 WHERE mass_addition_id = p_mass_addition_id;
1765
1766 EXCEPTION
1767 WHEN fnd_api.g_exc_error THEN
1768 x_return_status := fnd_api.g_ret_sts_error;
1769 x_error_message := cse_util_pkg.dump_error_stack;
1770 END add_to_mass_addition;
1771
1772 PROCEDURE get_distribution_id(
1773 p_mass_add_rec IN fa_mass_additions%rowtype,
1774 x_distribution_id OUT NOCOPY number ,
1775 x_return_status OUT NOCOPY varchar2,
1776 x_error_message OUT NOCOPY varchar2 )
1777 IS
1778
1779 CURSOR dist_cur IS
1780 SELECT distribution_id,
1781 units_assigned
1782 FROM fa_distribution_history
1783 WHERE asset_id = p_mass_add_rec.asset_id
1784 AND book_type_code = p_mass_add_rec.book_type_code
1785 AND location_id = p_mass_add_rec.location_id
1786 AND code_combination_id = nvl(p_mass_add_rec.expense_code_combination_id , code_combination_id)
1787 AND nvl(assigned_to,-1) = nvl(p_mass_add_rec.assigned_to, -1)
1788 AND date_ineffective IS null;
1789
1790 l_distribution_id number := null;
1791
1792 BEGIN
1793
1794 x_return_status := fnd_api.g_ret_sts_success;
1795
1796 FOR dist_rec IN dist_cur
1797 LOOP
1798 l_distribution_id := dist_rec.distribution_id;
1799 exit;
1800 END LOOP;
1801
1802 x_distribution_id := l_distribution_id;
1803
1804 END get_distribution_id;
1805
1806
1807 PROCEDURE create_distribution(
1808 p_mass_add_rec IN fa_mass_additions%rowtype,
1809 x_return_status OUT NOCOPY varchar2,
1810 x_error_message OUT NOCOPY varchar2)
1811 IS
1812
1813 l_distribution_id number;
1814
1815 l_fa_trans_rec FA_API_TYPES.trans_rec_type;
1816 l_fa_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
1817 l_fa_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
1818
1819 l_error_message varchar2(2000);
1820 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1821 l_msg_data varchar2(2000);
1822 l_msg_count number;
1823
1824 BEGIN
1825
1826 x_return_status := fnd_api.g_ret_sts_success;
1827
1828 debug('calling get_distribution_id');
1829
1830 get_distribution_id(
1831 p_mass_add_rec => p_mass_add_rec,
1832 x_distribution_id => l_distribution_id,
1833 x_return_status => l_return_status,
1834 x_error_message => l_error_message);
1835
1836 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1837 RAISE fnd_api.g_exc_error;
1838 END IF;
1839
1840 fnd_msg_pub.initialize;
1841
1842 l_fa_trans_rec.transaction_type_code := 'UNIT ADJUSTMENT';
1843 l_fa_trans_rec.transaction_date_entered := sysdate;
1844
1845 l_fa_hdr_rec.asset_id := p_mass_add_rec.asset_id;
1846 l_fa_hdr_rec.book_type_code := p_mass_add_rec.book_type_code;
1847
1848 l_fa_dist_tbl(1).distribution_id := l_distribution_id;
1849 l_fa_dist_tbl(1).transaction_units := p_mass_add_rec.payables_units;
1850 l_fa_dist_tbl(1).assigned_to := p_mass_add_rec.assigned_to;
1851 l_fa_dist_tbl(1).expense_ccid := p_mass_add_rec.expense_code_combination_id;
1852 l_fa_dist_tbl(1).location_ccid := p_mass_add_rec.location_id;
1853
1854 debug('calling do_unit_adjustment');
1855
1856 fa_unit_adj_pub.do_unit_adjustment(
1857 p_api_version => 1.0,
1858 p_calling_fn => 'CreateDepreciableAssets',
1859 px_trans_rec => l_fa_trans_rec,
1860 px_asset_hdr_rec => l_fa_hdr_rec,
1861 px_asset_dist_tbl => l_fa_dist_tbl,
1862 x_return_status => l_return_status,
1863 x_msg_count => l_msg_count,
1864 x_msg_data => l_msg_data);
1865
1866 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1867 l_error_message := cse_util_pkg.dump_error_stack;
1868 RAISE fnd_api.g_exc_error;
1869 END IF;
1870
1871 EXCEPTION
1872 WHEN fnd_api.g_exc_error THEN
1873 x_return_status := fnd_api.g_ret_sts_error;
1874 x_error_message := l_error_message;
1875 END create_distribution;
1876
1877 PROCEDURE add_to_asset(
1878 p_asset_id IN number,
1879 p_instance_rec IN instance_rec,
1880 x_return_status OUT nocopy varchar2,
1881 x_error_message OUT nocopy varchar2)
1882 IS
1883
1884 l_mass_add_rec fa_mass_additions%rowtype;
1885
1886 l_asset_quantity number;
1887 l_obj_ver_num number;
1888
1889 l_inst_asset_rec csi_datastructures_pub.instance_asset_rec;
1890 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1891 l_error_message varchar2(2000);
1892
1893 l_msg_count number;
1894 l_msg_data varchar2(2000);
1895
1896 skip_add_to_asset exception;
1897
1898 BEGIN
1899
1900 l_mass_add_rec.mass_addition_id := null;
1901 l_mass_add_rec.asset_id := p_asset_id;
1902 l_mass_add_rec.description := p_instance_rec.asset_description;
1903 l_mass_add_rec.asset_category_id := p_instance_rec.asset_category_id;
1904 l_mass_add_rec.book_type_code := p_instance_rec.book_type_code;
1905 l_mass_add_rec.location_id := p_instance_rec.asset_location_id;
1906 l_mass_add_rec.asset_key_ccid := p_instance_rec.asset_key_ccid;
1907 l_mass_add_rec.tag_number := p_instance_rec.tag_number;
1908 l_mass_add_rec.model_number := p_instance_rec.model_number;
1909 l_mass_add_rec.manufacturer_name := p_instance_rec.manufacturer_name;
1910 l_mass_add_rec.project_id := p_instance_rec.pa_project_id;
1911 l_mass_add_rec.task_id := p_instance_rec.pa_project_task_id;
1912 l_mass_add_rec.payables_code_combination_id := p_instance_rec.payables_ccid;
1913 l_mass_add_rec.expense_code_combination_id := p_instance_rec.deprn_expense_ccid;
1914 l_mass_add_rec.feeder_system_name := 'ORACLE ENTERPRISE INSTALL BASE';
1915 l_mass_add_rec.asset_type := 'CAPITALIZED';
1916 l_mass_add_rec.depreciate_flag := 'YES';
1917 l_mass_add_rec.created_by := fnd_global.user_id;
1918 l_mass_add_rec.creation_date := sysdate;
1919 l_mass_add_rec.last_update_date := sysdate;
1920 l_mass_add_rec.last_update_login := fnd_global.login_id;
1921
1922 l_mass_add_rec.po_number := p_instance_rec.po_number;
1923 l_mass_add_rec.po_vendor_id := p_instance_rec.po_vendor_id;
1924 l_mass_add_rec.po_distribution_id := p_instance_rec.po_distribution_id;
1925
1926 IF p_instance_rec.fa_group_by = 'ITEM' THEN
1927
1928 l_mass_add_rec.payables_units := p_instance_rec.mtl_txn_qty;
1929 l_mass_add_rec.fixed_assets_units := p_instance_rec.mtl_txn_qty;
1930
1931 ELSE
1932
1933 l_mass_add_rec.payables_units := p_instance_rec.quantity;
1934 l_mass_add_rec.fixed_assets_units := p_instance_rec.quantity;
1935
1936 get_instance_asset(
1937 p_instance_id => p_instance_rec.instance_id,
1938 p_asset_id => fnd_api.g_miss_num,
1939 x_inst_asset_rec => l_inst_asset_rec,
1940 x_return_status => l_return_status,
1941 x_error_message => l_error_message);
1942
1943 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1944 RAISE fnd_api.g_exc_error;
1945 END IF;
1946
1947 IF nvl(l_inst_asset_rec.instance_asset_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1948 debug('skipping the add to asset for serialized item instance that already has an asset link.');
1949 RAISE skip_add_to_asset;
1950 END IF;
1951
1952 END IF;
1953
1954 l_mass_add_rec.payables_cost := p_instance_rec.asset_unit_cost *
1955 NVL(l_mass_add_rec.payables_units,0);
1956 l_mass_add_rec.fixed_assets_cost := p_instance_rec.asset_unit_cost *
1957 NVL(l_mass_add_rec.fixed_assets_units,0);
1958
1959 l_mass_add_rec.posting_status := 'POST';
1960 l_mass_add_rec.queue_name := 'ADD TO ASSET';
1961 l_mass_add_rec.add_to_asset_id := p_asset_id;
1962
1963 SELECT date_placed_in_service
1964 INTO l_mass_add_rec.date_placed_in_service
1965 FROM fa_books
1966 WHERE asset_id = p_asset_id
1967 AND book_type_code = p_instance_rec.book_type_code
1968 AND date_ineffective is null;
1969
1970 cse_asset_util_pkg.insert_mass_add(
1971 p_api_version => 1.0,
1972 p_commit => fnd_api.g_false,
1973 p_init_msg_list => fnd_api.g_true,
1974 p_mass_add_rec => l_mass_add_rec,
1975 x_return_status => l_return_status,
1976 x_msg_count => l_msg_count,
1977 x_msg_data => l_msg_data);
1978
1979 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1980 RAISE fnd_api.g_exc_error;
1981 END IF;
1982
1983 debug('fa_mass_addition rec created. parent_mass_addition_id : '||l_mass_add_rec.mass_addition_id);
1984
1985 create_distribution(
1986 p_mass_add_rec => l_mass_add_rec,
1987 x_return_status => l_return_status,
1988 x_error_message => l_error_message);
1989
1990 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1991 RAISE fnd_api.g_exc_error;
1992 END IF;
1993
1994 EXCEPTION
1995 WHEN skip_add_to_asset THEN
1996 null;
1997 WHEN fnd_api.g_exc_error THEN
1998 x_return_status := fnd_api.g_ret_sts_error;
1999 x_error_message := cse_util_pkg.dump_error_stack;
2000 END add_to_asset;
2001
2002 PROCEDURE create_depreciable_assets(
2003 errbuf OUT NOCOPY VARCHAR2,
2004 retcode OUT NOCOPY NUMBER,
2005 p_inventory_item_id IN NUMBER,
2006 p_organization_id IN NUMBER)
2007 IS
2008
2009 l_pending_status varchar2(30) := 'PENDING';
2010 l_csi_txn_rec csi_datastructures_pub.transaction_rec;
2011 l_ts_tbl txn_status_tbl;
2012 l_ts_ind binary_integer := 0;
2013
2014 CURSOR csi_pending_txn_cur IS
2015 SELECT ct.transaction_type_id,
2016 ct.transaction_id,
2017 ct.transaction_date,
2018 ct.inv_material_transaction_id,
2019 ct.source_dist_ref_id2,
2020 ct.source_dist_ref_id1
2021 FROM csi_transactions ct
2022 WHERE ct.transaction_type_id IN (117, 129, 128, 105, 112, 118, 119, 133, 132, 73) --Add WIP Assembly Completion for bug 7489949
2023 AND ct.transaction_status_code = l_pending_status
2024 AND ct.inv_material_transaction_id is not null
2025 AND exists (
2026 SELECT 1 FROM mtl_material_transactions mmt
2027 WHERE mmt.transaction_id = ct.inv_material_transaction_id
2028 AND mmt.inventory_item_id = nvl(p_inventory_item_id, mmt.inventory_item_id)
2029 AND mmt.organization_id = nvl(p_organization_id, mmt.organization_id))
2030 ORDER BY ct.inv_material_transaction_id;
2031
2032 -- eib supported transactions for fixed asset creation
2033 ------------------------------------------------------------
2034 -- 117 - ('MISC_RECEIPT') - depreciable items
2035 -- 129 - ('ACCT_ALIAS_RECEIPT') - depreciable items
2036 -- 128 - ('ACCT_RECEIPT') - depreciable items
2037 -- 105 - ('PO_RECEIPT_INTO_PROJECT') - depreciable items
2038 -- 112 - ('PO_RECEIPT_INTO_INVENTORY') - depreciable items
2039 -- 118 - ('PHYSICAL_INVENTORY') - depreciable items
2040 -- 119 - ('CYCLE_COUNT_ADJUSTMENT' - depreciable items
2041 -- 133 - ('MISC_ISSUE_HZ_LOC') - normal items
2042 -- 132 - ('ISSUE_TO_HZ_LOC') - normal items
2043 -------------------------------------------------------------
2044
2045 l_inventory_item_id number;
2046 l_organization_id number;
2047 l_mtl_txn_type_id number;
2048 l_mtl_txn_type_name varchar2(80);
2049 l_mtl_txn_date date;
2050 l_mmt_quantity number;
2051
2052 l_serial_code number;
2053 l_primary_uom_code varchar2(6);
2054 l_asset_creation_code varchar2(1);
2055 l_eam_item_type number;
2056 l_subinventory_code varchar2(30);
2057 l_location_type_code varchar2(30);
2058 l_location_id number;
2059 l_instance_id number;
2060 l_quantity number;
2061 l_pa_project_id number;
2062 l_pa_project_task_id number;
2063 l_distribution_acct_id number;
2064 l_ship_to_location_id number;
2065
2066 l_depreciable_flag varchar2(1);
2067 l_redeploy_flag varchar2(1);
2068 l_fa_qry_rec fa_query_rec;
2069 l_fixed_asset_rec fixed_asset_rec;
2070 l_pending_fa_rec fixed_asset_rec;
2071
2072 l_item varchar2(80);
2073 l_item_description varchar2(240);
2074 l_fa_group_by varchar2(30);
2075 l_mass_addition_id number;
2076 l_fa_action varchar2(30);
2077 l_instance_asset_rec csi_datastructures_pub.instance_asset_rec;
2078
2079 CURSOR srl_cur(p_mtl_txn_id IN number) IS
2080 SELECT mut.serial_number serial_number,
2081 to_char(null) lot_number,
2082 1 quantity
2083 FROM mtl_unit_transactions mut
2084 WHERE mut.transaction_id = p_mtl_txn_id
2085 UNION
2086 SELECT mut.serial_number serial_number,
2087 mtln.lot_number lot_number,
2088 1 quantity
2089 FROM mtl_transaction_lot_numbers mtln,
2090 mtl_unit_transactions mut
2091 WHERE mtln.transaction_id = p_mtl_txn_id
2092 AND mut.transaction_id = mtln.serial_transaction_id;
2093
2094 CURSOR nsrl_inst_cur(p_csi_txn_id IN number, p_inventory_item_id IN number) IS
2095 SELECT cii.instance_id,
2096 cii.lot_number,
2097 cii.location_type_code,
2098 cii.location_id,
2099 cii.instance_usage_code,
2100 cii.quantity,
2101 nvl(ciih.old_quantity,0) old_quantity,
2102 ciih.new_quantity
2103 FROM csi_item_instances_h ciih,
2104 csi_item_instances cii
2105 WHERE ciih.transaction_id = p_csi_txn_id
2106 AND cii.instance_id = ciih.instance_id
2107 AND cii.inventory_item_id = p_inventory_item_id
2108 AND nvl(ciih.new_quantity, 0) - nvl(ciih.old_quantity,0) > 0;
2109
2110 l_inst_tbl instance_tbl;
2111 inst_ind binary_integer := 0;
2112
2113 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2114 l_error_message varchar2(2000);
2115 l_err_inst_rec instance_rec;
2116
2117 BEGIN
2118
2119 cse_util_pkg.set_debug;
2120
2121 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
2122 csi_gen_utility_pvt.populate_install_param_rec;
2123 END IF;
2124
2125 l_fa_group_by := csi_datastructures_pub.g_install_param_rec.fa_creation_group_by;
2126
2127 l_csi_txn_rec.transaction_type_id := 123; -- instance_asset_tieback
2128 l_csi_txn_rec.source_transaction_date := sysdate;
2129 l_csi_txn_rec.source_group_ref_id := fnd_global.conc_request_id;
2130 l_csi_txn_rec.transaction_status_code := 'COMPLETE';
2131
2132 FOR csi_txn_rec IN csi_pending_txn_cur
2133 LOOP
2134
2135 l_ts_ind := l_ts_ind + 1;
2136 l_ts_tbl(l_ts_ind).csi_txn_id := csi_txn_rec.transaction_id;
2137 l_ts_tbl(l_ts_ind).processed_flag := 'N';
2138 l_ts_tbl(l_ts_ind).valid_txn_flag := 'N';
2139
2140 BEGIN
2141
2142 SAVEPOINT create_depreciable_assets;
2143
2144 debug('====================* BEGIN CREATE ASSET TRANSACTION *====================');
2145 debug(' csi transaction_id : '|| csi_txn_rec.transaction_id);
2146
2147 l_inst_tbl.delete;
2148 inst_ind := 0;
2149
2150 SELECT inventory_item_id,
2151 organization_id,
2152 transaction_type_id,
2153 transaction_date,
2154 subinventory_code,
2155 abs(primary_quantity),
2156 source_project_id,
2157 source_task_id,
2158 distribution_account_id,
2159 ship_to_location_id,
2160 transaction_quantity
2161 INTO l_inventory_item_id,
2162 l_organization_id,
2163 l_mtl_txn_type_id,
2164 l_mtl_txn_date,
2165 l_subinventory_code,
2166 l_quantity,
2167 l_pa_project_id,
2168 l_pa_project_task_id,
2169 l_distribution_acct_id,
2170 l_ship_to_location_id,
2171 l_mmt_quantity
2172 FROM mtl_material_transactions
2173 WHERE transaction_id = csi_txn_rec.inv_material_transaction_id;
2174
2175 SELECT transaction_type_name
2176 INTO l_mtl_txn_type_name
2177 FROM mtl_transaction_types
2178 WHERE transaction_type_id = l_mtl_txn_type_id;
2179
2180 SELECT serial_number_control_code,
2181 primary_uom_code,
2182 asset_creation_code,
2183 description,
2184 concatenated_segments,
2185 nvl(eam_item_type, 0)
2186 INTO l_serial_code,
2187 l_primary_uom_code,
2188 l_asset_creation_code,
2189 l_item_description,
2190 l_item,
2191 l_eam_item_type
2192 FROM mtl_system_items_kfv
2193 WHERE inventory_item_id = l_inventory_item_id
2194 AND organization_id = l_organization_id;
2195
2196 debug(' csi_txn_type_id : '||csi_txn_rec.transaction_type_id);
2197 debug(' inventory_item_id : '||l_inventory_item_id);
2198 debug(' organization_id : '||l_organization_id);
2199
2200 -- for non serialized, just treat it as item grouping always
2201 IF l_serial_code in (1, 6) THEN
2202 l_fa_group_by := 'ITEM';
2203 END IF;
2204
2205 IF nvl(l_asset_creation_code,'0') in ('1', 'Y') THEN
2206 l_depreciable_flag := 'Y';
2207 ELSE
2208 l_depreciable_flag := 'N';
2209 END IF;
2210
2211 debug(' asset_creation_code : '||l_asset_creation_code);
2212 debug(' depreciable_flag : '||l_depreciable_flag);
2213
2214 -- only for depreciable item txn or for issue to hz txn of normal items
2215 IF (l_depreciable_flag = 'Y'
2216 AND
2217 csi_txn_rec.transaction_type_id NOT IN (132, 133)
2218 AND
2219 l_mmt_quantity > 0)
2220 OR
2221 (l_depreciable_flag = 'N' AND csi_txn_rec.transaction_type_id in (132, 133) AND l_eam_item_type <> 1)
2222 THEN
2223
2224 l_ts_tbl(l_ts_ind).valid_txn_flag := 'Y';
2225
2226 debug(' item_name : '||l_item);
2227 debug(' item_description : '||l_item_description);
2228 debug(' csi_txn_date : '||csi_txn_rec.transaction_date);
2229 debug(' mtl_txn_id : '||csi_txn_rec.inv_material_transaction_id);
2230 debug(' mtl_txn_type_id : '||l_mtl_txn_type_id);
2231 debug(' mtl_txn_type_name : '||l_mtl_txn_type_name);
2232 debug(' mtl_txn_date : '||l_mtl_txn_date);
2233
2234 -- transactions that receive in to inventory location
2235 IF csi_txn_rec.transaction_type_id IN (117, 129, 128, 112, 73) THEN
2236
2237 l_location_type_code := 'INVENTORY';
2238
2239 SELECT location_id
2240 INTO l_location_id
2241 FROM mtl_secondary_inventories
2242 WHERE organization_id = l_organization_id
2243 AND secondary_inventory_name = l_subinventory_code;
2244
2245 IF l_location_id IS NULL THEN
2246 SELECT location_id
2247 INTO l_location_id
2248 FROM hr_all_organization_units
2249 WHERE organization_id = l_organization_id;
2250 END IF;
2251
2252 ELSIF csi_txn_rec.transaction_type_id IN (132, 133, 105) THEN
2253
2254 l_location_type_code := 'HZ_LOCATIONS';
2255
2256 IF csi_txn_rec.transaction_type_id = 105 THEN
2257
2258 SELECT deliver_to_location_id
2259 INTO l_location_id
2260 FROM rcv_transactions
2261 WHERE transaction_id = csi_txn_rec.source_dist_ref_id2;
2262
2263 ELSE
2264 l_location_id := l_ship_to_location_id;
2265 END IF;
2266
2267 END IF;
2268
2269 IF l_serial_code IN (2, 5) THEN
2270
2271 FOR srl_rec IN srl_cur(csi_txn_rec.inv_material_transaction_id)
2272 LOOP
2273
2274 SELECT instance_id
2275 INTO l_instance_id
2276 FROM csi_item_instances
2277 WHERE inventory_item_id = l_inventory_item_id
2278 AND serial_number = srl_rec.serial_number;
2279
2280 get_redeploy_flag(
2281 p_instance_id => l_instance_id,
2282 p_transaction_date => csi_txn_rec.transaction_date,
2283 x_redeploy_flag => l_redeploy_flag);
2284
2285 IF transaction_pending(csi_txn_rec.transaction_id, l_instance_id) THEN
2286 fnd_message.set_name('CSE', 'CSE_PRIOR_TXN_PENDING');
2287 fnd_msg_pub.add;
2288 l_error_message := cse_util_pkg.dump_error_stack;
2289 RAISE fnd_api.g_exc_error;
2290 END IF;
2291
2292 IF l_redeploy_flag = 'N' THEN
2293
2294 inst_ind := inst_ind + 1;
2295
2296 l_inst_tbl(inst_ind).instance_id := l_instance_id;
2297 l_inst_tbl(inst_ind).csi_txn_id := csi_txn_rec.transaction_id;
2298 l_inst_tbl(inst_ind).csi_txn_type_id := csi_txn_rec.transaction_type_id;
2299 l_inst_tbl(inst_ind).csi_txn_date := csi_txn_rec.transaction_date;
2300 l_inst_tbl(inst_ind).mtl_txn_id := csi_txn_rec.inv_material_transaction_id;
2301 l_inst_tbl(inst_ind).mtl_txn_date := l_mtl_txn_date;
2302 l_inst_tbl(inst_ind).mtl_txn_qty := l_quantity;
2303 l_inst_tbl(inst_ind).quantity := 1;
2304 l_inst_tbl(inst_ind).inventory_item_id := l_inventory_item_id;
2305 l_inst_tbl(inst_ind).organization_id := l_organization_id;
2306 l_inst_tbl(inst_ind).subinventory_code := l_subinventory_code;
2307 l_inst_tbl(inst_ind).primary_uom_code := l_primary_uom_code;
2308 l_inst_tbl(inst_ind).serial_number := srl_rec.serial_number;
2309 l_inst_tbl(inst_ind).lot_number := srl_rec.lot_number;
2310 l_inst_tbl(inst_ind).pa_project_id := l_pa_project_id;
2311 l_inst_tbl(inst_ind).pa_project_task_id := l_pa_project_task_id;
2312 l_inst_tbl(inst_ind).location_type_code := l_location_type_code;
2313 l_inst_tbl(inst_ind).location_id := l_location_id;
2314 l_inst_tbl(inst_ind).depreciable_flag := l_depreciable_flag;
2315 l_inst_tbl(inst_ind).item := l_item;
2316 l_inst_tbl(inst_ind).item_description := l_item_description;
2317 l_inst_tbl(inst_ind).mtl_dist_acct_id := l_distribution_acct_id;
2318 l_inst_tbl(inst_ind).fa_group_by := l_fa_group_by;
2319
2320 IF csi_txn_rec.transaction_type_id in (105, 112) THEN
2321 l_inst_tbl(inst_ind).po_distribution_id := csi_txn_rec.source_dist_ref_id1;
2322 l_inst_tbl(inst_ind).rcv_txn_id := csi_txn_rec.source_dist_ref_id2;
2323 END IF;
2324
2325 END IF; -- redeploy check
2326
2327 END LOOP; -- mtl loop
2328
2329 ELSE
2330
2331 FOR nsrl_inst_rec IN nsrl_inst_cur(csi_txn_rec.transaction_id, l_inventory_item_id)
2332 LOOP
2333
2334 inst_ind := inst_ind + 1;
2335
2336 l_inst_tbl(inst_ind).instance_id := nsrl_inst_rec.instance_id;
2337 l_inst_tbl(inst_ind).csi_txn_id := csi_txn_rec.transaction_id;
2338 l_inst_tbl(inst_ind).csi_txn_type_id := csi_txn_rec.transaction_type_id;
2339 l_inst_tbl(inst_ind).csi_txn_date := csi_txn_rec.transaction_date;
2340 l_inst_tbl(inst_ind).mtl_txn_id := csi_txn_rec.inv_material_transaction_id;
2341 l_inst_tbl(inst_ind).mtl_txn_date := l_mtl_txn_date;
2342 l_inst_tbl(inst_ind).mtl_txn_qty := l_quantity;
2343 l_inst_tbl(inst_ind).quantity := l_quantity;
2344 l_inst_tbl(inst_ind).inventory_item_id := l_inventory_item_id;
2345 l_inst_tbl(inst_ind).organization_id := l_organization_id;
2346 l_inst_tbl(inst_ind).subinventory_code := l_subinventory_code;
2347 l_inst_tbl(inst_ind).primary_uom_code := l_primary_uom_code;
2348 l_inst_tbl(inst_ind).serial_number := null;
2349 l_inst_tbl(inst_ind).lot_number := nsrl_inst_rec.lot_number;
2350 l_inst_tbl(inst_ind).pa_project_id := l_pa_project_id;
2351 l_inst_tbl(inst_ind).pa_project_task_id := l_pa_project_task_id;
2352 l_inst_tbl(inst_ind).location_type_code := l_location_type_code;
2353 l_inst_tbl(inst_ind).location_id := l_location_id;
2354 l_inst_tbl(inst_ind).depreciable_flag := l_depreciable_flag;
2355 l_inst_tbl(inst_ind).redeploy_flag :='N' ;
2356 l_inst_tbl(inst_ind).item := l_item;
2357 l_inst_tbl(inst_ind).item_description := l_item_description;
2358 l_inst_tbl(inst_ind).mtl_dist_acct_id := l_distribution_acct_id;
2359 l_inst_tbl(inst_ind).fa_group_by := 'ITEM';
2360
2361 IF csi_txn_rec.transaction_type_id in (105, 112) THEN
2362 l_inst_tbl(inst_ind).po_distribution_id := csi_txn_rec.source_dist_ref_id1;
2363 l_inst_tbl(inst_ind).rcv_txn_id := csi_txn_rec.source_dist_ref_id2;
2364 END IF;
2365
2366 END LOOP;
2367 END IF;
2368
2369 IF l_inst_tbl.COUNT > 0 THEN
2370 -- derive asset specific attribs
2371 derive_asset_attribs(
2372 px_instance_tbl => l_inst_tbl,
2373 x_return_status => l_return_status,
2374 x_error_message => l_error_message);
2375
2376 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2377 l_err_inst_rec := l_inst_tbl(1);
2378 RAISE fnd_api.g_exc_error;
2379 END IF;
2380
2381 dump_inst_tbl(p_inst_tbl => l_inst_tbl);
2382
2383 -- follow asset flow
2384 FOR l_ind IN l_inst_tbl.FIRST .. l_inst_tbl.LAST
2385 LOOP
2386
2387 l_fa_qry_rec := null;
2388 -- Bug#6318642
2389 -- l_mass_addition_id := null;
2390
2391 IF ( (l_ind = 1 and l_fa_group_by = 'ITEM') OR ( l_fa_group_by = 'ITEM_SERIAL') )
2392 THEN
2393 l_mass_addition_id := null;
2394 END IF ;
2395
2396 l_fa_qry_rec.asset_id := null;
2397 l_fa_qry_rec.inventory_item_id := l_inst_tbl(l_ind).inventory_item_id;
2398 l_fa_qry_rec.book_type_code := l_inst_tbl(l_ind).book_type_code;
2399 l_fa_qry_rec.asset_category_id := l_inst_tbl(l_ind).asset_category_id;
2400 l_fa_qry_rec.asset_description := l_inst_tbl(l_ind).asset_description;
2401 l_fa_qry_rec.date_placed_in_service := l_inst_tbl(l_ind).date_placed_in_service;
2402 l_fa_qry_rec.model_number := l_inst_tbl(l_ind).model_number;
2403 l_fa_qry_rec.tag_nuber := l_inst_tbl(l_ind).tag_number;
2404 l_fa_qry_rec.manufacturer_name := l_inst_tbl(l_ind).manufacturer_name;
2405 l_fa_qry_rec.asset_key_ccid := l_inst_tbl(l_ind).asset_key_ccid;
2406 l_fa_qry_rec.search_method := l_inst_tbl(l_ind).search_method;
2407
2408 IF l_fa_group_by = 'ITEM' THEN
2409 IF l_ind = 1 THEN
2410 get_fixed_assets(
2411 p_fa_query_rec => l_fa_qry_rec,
2412 x_fixed_asset_rec => l_fixed_asset_rec,
2413 x_return_status => l_return_status,
2414 x_error_message => l_error_message);
2415
2416 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2417 l_err_inst_rec := l_inst_tbl(l_ind);
2418 RAISE fnd_api.g_exc_error;
2419 END IF;
2420
2421 IF l_fixed_asset_rec.asset_id is not null THEN
2422
2423 debug(' fixed asset found. asset id : '||l_fixed_asset_rec.asset_id);
2424
2425 l_fa_action := 'ADD_TO_ASSET';
2426
2427 add_to_asset(
2428 p_asset_id => l_fixed_asset_rec.asset_id,
2429 p_instance_rec => l_inst_tbl(l_ind),
2430 x_return_status => l_return_status,
2431 x_error_message => l_error_message);
2432
2433 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2434 l_err_inst_rec := l_inst_tbl(l_ind);
2435 RAISE fnd_api.g_exc_error;
2436 END IF;
2437
2438 ELSE
2439
2440 debug(' fixed asset not found. look for pending mass addition');
2441
2442 get_pending_additions(
2443 p_fa_query_rec => l_fa_qry_rec,
2444 x_fixed_asset_rec => l_pending_fa_rec,
2445 x_return_status => l_return_status,
2446 x_error_message => l_error_message);
2447
2448 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2449 l_err_inst_rec := l_inst_tbl(l_ind);
2450 RAISE fnd_api.g_exc_error;
2451 END IF;
2452
2453 IF l_pending_fa_rec.mass_addition_id is not null THEN
2454
2455 debug('pending add found. mass addtion id : '||
2456 l_pending_fa_rec.mass_addition_id);
2457
2458 l_fa_action := 'ADD_TO_MASS_ADDITION';
2459
2460 -- Bug#6318642
2461 l_mass_addition_id := l_pending_fa_rec.mass_addition_id ;
2462
2463 add_to_mass_addition(
2464 p_mass_addition_id => l_pending_fa_rec.mass_addition_id,
2465 p_instance_rec => l_inst_tbl(l_ind),
2466 x_return_status => l_return_status,
2467 x_error_message => l_error_message);
2468
2469 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2470 l_err_inst_rec := l_inst_tbl(l_ind);
2471 RAISE fnd_api.g_exc_error;
2472 END IF;
2473
2474 ELSE
2475
2476 debug(' pending mass addition not found. create mass addition record');
2477
2478 l_fa_action := 'CREATE_MASS_ADDITION';
2479
2480 create_mass_addition(
2481 p_instance_rec => l_inst_tbl(l_ind),
2482 x_mass_addition_id => l_mass_addition_id,
2483 x_return_status => l_return_status,
2484 x_error_message => l_error_message);
2485
2486 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2487 l_err_inst_rec := l_inst_tbl(l_ind);
2488 RAISE fnd_api.g_exc_error;
2489 END IF;
2490
2491 END IF; -- pending mass_addition is not null
2492
2493 END IF; -- asset id is not null
2494
2495 END IF; -- first record only
2496
2497 ELSE -- group by is ITEM_SERIAL
2498
2499 get_instance_asset(
2500 p_instance_id => l_inst_tbl(l_ind).instance_id,
2501 p_asset_id => fnd_api.g_miss_num,
2502 x_inst_asset_rec => l_instance_asset_rec,
2503 x_return_status => l_return_status,
2504 x_error_message => l_error_message);
2505
2506 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2507 RAISE fnd_api.g_exc_error;
2508 END IF;
2509
2510 IF nvl(l_instance_asset_rec.instance_asset_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
2511 THEN
2512
2513 l_fa_action := 'CREATE_MASS_ADDITION';
2514
2515 create_mass_addition(
2516 p_instance_rec => l_inst_tbl(l_ind),
2517 x_mass_addition_id => l_mass_addition_id,
2518 x_return_status => l_return_status,
2519 x_error_message => l_error_message);
2520
2521 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2522 l_err_inst_rec := l_inst_tbl(l_ind);
2523 RAISE fnd_api.g_exc_error;
2524 END IF;
2525
2526 ELSE
2527 l_fa_action := 'NONE';
2528 END IF;
2529
2530 END IF; -- fa creation group by ITEM/ITEM_SERIAL
2531
2532 IF l_fa_action <> 'NONE' THEN
2533
2534 l_csi_txn_rec.transaction_id := fnd_api.g_miss_num;
2535 l_csi_txn_rec.source_header_ref := 'CSI_TXN_ID';
2536 l_csi_txn_rec.source_header_ref_id := csi_txn_rec.transaction_id;
2537
2538 IF l_mass_addition_id is not null THEN
2539 l_csi_txn_rec.source_line_ref := 'MASS_ADD_ID';
2540 l_csi_txn_rec.source_line_ref_id := l_mass_addition_id;
2541 END IF;
2542
2543 amend_instance_asset(
2544 p_action => l_fa_action,
2545 p_inst_rec => l_inst_tbl(l_ind),
2546 p_mass_addition_id => l_mass_addition_id,
2547 p_asset_id => l_fixed_asset_rec.asset_id,
2548 px_csi_txn_rec => l_csi_txn_rec,
2549 x_inst_asset_rec => l_instance_asset_rec,
2550 x_return_status => l_return_status);
2551
2552 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2553 RAISE fnd_api.g_exc_error;
2554 END IF;
2555 END IF;
2556
2557 END LOOP; -- loop thru instances
2558
2559 complete_csi_txn(
2560 p_csi_txn_id => csi_txn_rec.transaction_id,
2561 x_return_status => l_return_status,
2562 x_error_message => l_error_message);
2563
2564 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2565 RAISE fnd_api.g_exc_error;
2566 END IF;
2567
2568 debug('csi transaction interfaced to fa successfully.');
2569
2570 l_ts_tbl(l_ts_ind).processed_flag := 'Y';
2571
2572 END IF; -- instances found for transaction
2573
2574 ELSE
2575
2576 IF csi_txn_rec.transaction_type_id not in (132, 133) THEN
2577 -- complete the invalid transaction
2578 complete_csi_txn(
2579 p_csi_txn_id => csi_txn_rec.transaction_id,
2580 x_return_status => l_return_status,
2581 x_error_message => l_error_message);
2582 END IF;
2583
2584 END IF; -- depreciable item txn or issue to hz loc txn
2585
2586 COMMIT WORK;
2587
2588 EXCEPTION
2589 WHEN fnd_api.g_exc_error THEN
2590 debug(' error message : '||l_error_message);
2591
2592 l_ts_tbl(l_ts_ind).processed_flag := 'E';
2593 l_ts_tbl(l_ts_ind).error_message := l_error_message;
2594
2595 ROLLBACK TO create_depreciable_assets;
2596 log_error(
2597 p_instance_rec => l_err_inst_rec,
2598 p_error_message => l_error_message);
2599 WHEN others THEN
2600 l_error_message := substr(sqlerrm, 1, 240);
2601 debug(' error message : '||l_error_message);
2602
2603 l_ts_tbl(l_ts_ind).processed_flag := 'E';
2604 l_ts_tbl(l_ts_ind).error_message := l_error_message;
2605
2606 ROLLBACK TO create_depreciable_assets;
2607 log_error(
2608 p_instance_rec => l_err_inst_rec,
2609 p_error_message => l_error_message);
2610 END;
2611
2612 debug('====================* END CREATE ASSET TRANSACTION *====================');
2613
2614 END LOOP;
2615
2616 asset_creation_report(p_txn_status_tbl => l_ts_tbl);
2617
2618 EXCEPTION
2619 WHEN others THEN
2620 retcode := 1;
2621 errbuf := sqlerrm;
2622 END create_depreciable_assets;
2623
2624
2625 PROCEDURE find_distribution(
2626 p_asset_query_rec IN OUT NOCOPY cse_datastructures_pub.asset_query_rec
2627 , p_mass_add_rec IN fa_mass_additions%ROWTYPE
2628 , x_new_dist OUT NOCOPY NUMBER
2629 , x_return_status OUT NOCOPY VARCHAR2
2630 , x_error_msg OUT NOCOPY VARCHAR2 )
2631 IS
2632 l_distribution_id NUMBER ;
2633 l_api_name VARCHAR2(100):= 'CSE_ASSET_CREATION_PKG.find_distribution';
2634 CURSOR dist_cur IS
2635 SELECT distribution_id
2636 ,book_type_code
2637 ,location_id
2638 ,code_combination_id
2639 ,assigned_to
2640 ,units_assigned
2641 FROM fa_distribution_history
2642 WHERE asset_id = p_asset_query_rec.asset_id
2643 AND book_type_code = NVL(p_asset_query_rec.book_type_code,book_type_code)
2644 AND location_id = NVL(p_mass_add_rec.location_id , location_id)
2645 AND code_combination_id = NVL(p_mass_add_rec.expense_code_combination_id , code_combination_id)
2646 AND NVL(assigned_to, -1) = NVL(p_mass_add_rec.assigned_to, -1)
2647 AND date_ineffective IS NULL;
2648
2649 CURSOR dist_cur1 IS
2650 SELECT distribution_id
2651 ,book_type_code
2652 ,location_id
2653 ,code_combination_id
2654 ,assigned_to
2655 FROM fa_distribution_history
2656 WHERE asset_id = p_asset_query_rec.asset_id
2657 AND book_type_code = NVL(p_asset_query_rec.book_type_code,book_type_code)
2658 AND date_ineffective IS NULL ;
2659
2660 BEGIN
2661 debug('Begin - find distribution');
2662 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
2663
2664 ---Initialize x_new_dist to TRUE (1) Indicating that matching distribution
2665 ---has not been found
2666 x_new_dist := 1 ;
2667
2668 OPEN dist_cur ;
2669 FETCH dist_cur INTO p_asset_query_rec.distribution_id,
2670 p_asset_query_rec.book_type_code,
2671 p_asset_query_rec.location_id,
2672 p_asset_query_rec.deprn_expense_ccid,
2673 p_asset_query_rec.employee_id ,
2674 p_asset_query_rec.current_units ;
2675
2676 ---Matching Distribution is found, so there is no need for new distribution.
2677 ---0 IS FALSE
2678 IF dist_cur%FOUND
2679 THEN
2680 debug('FA Dist ID : In dist_cur'||p_asset_query_rec.distribution_id);
2681 x_new_dist := 0 ;
2682 END IF ;
2683 CLOSE dist_cur ;
2684
2685
2686 ---1 IS TRUE
2687 IF x_new_dist = 1
2688 THEN
2689 OPEN dist_cur1 ;
2690 FETCH dist_cur1 INTO p_asset_query_rec.distribution_id,
2691 p_asset_query_rec.book_type_code,
2692 p_asset_query_rec.location_id,
2693 p_asset_query_rec.deprn_expense_ccid,
2694 p_asset_query_rec.employee_id ;
2695 CLOSE dist_cur1 ;
2696 END IF ;
2697
2698 EXCEPTION
2699 WHEN OTHERS
2700 THEN
2701 x_return_status := FND_API.G_RET_STS_ERROR ;
2702 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
2703 fnd_message.set_token('API_NAME',l_api_name);
2704 fnd_message.set_token('SQL_ERROR',SQLERRM);
2705 x_error_msg := fnd_message.get;
2706 END find_distribution ;
2707
2708 PROCEDURE create_fa_distribution(
2709 p_asset_query_rec IN cse_datastructures_pub.asset_query_rec
2710 , p_mass_add_rec IN fa_mass_additions%ROWTYPE
2711 , x_return_status OUT NOCOPY VARCHAR2
2712 , x_error_msg OUT NOCOPY VARCHAR2 )
2713 IS
2714 l_asset_query_rec cse_datastructures_pub.asset_query_rec ;
2715 x_new_dist NUMBER;
2716 x_new_from_dist_id NUMBER;
2717 x_new_to_dist_id NUMBER;
2718 e_error EXCEPTION ;
2719 l_api_name VARCHAR2(100):= 'CSE_ASSET_CREATION_PKG.create_fa_distribution';
2720
2721 BEGIN
2722 debug('Begin - create distribution');
2723 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
2724 l_asset_query_rec := p_asset_query_rec ;
2725
2726 ---07/24
2727 ---As find asset may find a distribution which may
2728 ---NOT be same as distribution in p_mass_add_rec.
2729 ---Initialize distribution to NULL.
2730
2731 l_asset_query_rec.distribution_id := NULL;
2732
2733 IF l_asset_query_rec.distribution_id IS NULL
2734 THEN
2735 cse_asset_creation_pkg.find_distribution(
2736 l_asset_query_rec
2737 , p_mass_add_rec
2738 , x_new_dist
2739 , x_return_status
2740 , x_error_msg );
2741
2742 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS
2743 THEN
2744 RAISE e_error ;
2745 END IF ;
2746 END IF ;
2747
2748 IF l_asset_query_rec.distribution_id IS NOT NULL
2749 THEN
2750
2751 cse_ifa_trans_pkg.adjust_fa_distribution(
2752 l_asset_query_rec.asset_id
2753 ,l_asset_query_rec.book_type_code
2754 ,p_mass_add_rec.payables_units
2755 ,l_asset_query_rec.location_id
2756 ,l_asset_query_rec.deprn_expense_ccid
2757 ,l_asset_query_rec.deprn_employee_id
2758 ,l_asset_query_rec.distribution_id
2759 ,x_return_status
2760 ,x_error_msg );
2761 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS
2762 THEN
2763 RAISE e_error ;
2764 END IF ;
2765 END IF ;
2766
2767 ---Needs a transfer
2768 IF x_new_dist = 1 THEN
2769 cse_ifa_trans_pkg.transfer_fa_distribution(
2770 l_asset_query_rec.asset_id
2771 ,l_asset_query_rec.book_type_code
2772 ,p_mass_add_rec.payables_units
2773 ,l_asset_query_rec.location_id
2774 ,l_asset_query_rec.deprn_expense_ccid
2775 ,l_asset_query_rec.deprn_employee_id
2776 ,p_mass_add_rec.location_id
2777 ,p_mass_add_rec.expense_code_combination_id
2778 ,p_mass_add_rec.assigned_to
2779 ,x_new_from_dist_id
2780 ,x_new_to_dist_id
2781 ,x_return_status
2782 ,x_error_msg );
2783 END IF;
2784
2785 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS
2786 THEN
2787 RAISE e_error ;
2788 END IF ;
2789 EXCEPTION
2790 WHEN e_error
2791 THEN
2792 x_return_status := FND_API.G_RET_STS_ERROR ;
2793 x_error_msg := x_error_msg ;
2794 WHEN OTHERS
2795 THEN
2796 x_return_status := FND_API.G_RET_STS_ERROR ;
2797 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
2798 fnd_message.set_token('API_NAME',l_api_name);
2799 fnd_message.set_token('SQL_ERROR',SQLERRM);
2800 x_error_msg := fnd_message.get;
2801 END create_fa_distribution;
2802
2803 PROCEDURE find_asset(
2804 p_asset_query_rec IN OUT NOCOPY cse_datastructures_pub.asset_query_rec,
2805 p_distribution_tbl OUT NOCOPY cse_datastructures_pub.distribution_tbl,
2806 x_return_status OUT NOCOPY VARCHAR2,
2807 x_error_msg OUT NOCOPY VARCHAR2 )
2808 IS
2809 l_sql_stmt VARCHAR2(6000);
2810 l_order_by_clause VARCHAR2(1000);
2811 l_asset_id NUMBER;
2812 l_search_method VARCHAR2(4);
2813 l_asset_number VARCHAR2(15) := NULL ;
2814 l_category_id NUMBER;
2815 l_book_type_code VARCHAR2(15) := NULL ;
2816 l_date_placed_in_service DATE;
2817 l_asset_key_ccid NUMBER;
2818 l_tag_number VARCHAR2(15) := NULL ;
2819 l_description VARCHAR2(80) := NULL ;
2820 l_manufacturer_name VARCHAR2(30) := NULL ;
2821 l_serial_number VARCHAR2(35) := NULL ;
2822 l_model_number VARCHAR2(40) := NULL ;
2823 l_location_id NUMBER;
2824 l_employee_id NUMBER;
2825 l_deprn_expense_ccid NUMBER;
2826 l_inventory_item_id NUMBER;
2827 l_code_combination_id NUMBER;
2828 x_msg_count NUMBER ;
2829 x_msg_data VARCHAR2(2000) := NULL ;
2830 l_total_units NUMBER :=0;
2831 l_location_units NUMBER :=0;
2832 l_unit_ratio NUMBER;
2833 i NUMBER ;
2834 e_error EXCEPTION;
2835 l_cost NUMBER;
2836 l_total_cost NUMBER;
2837 l_mtl_cost NUMBER ;
2838 l_non_mtl_cost NUMBER ;
2839 l_mtl_ratio NUMBER ;
2840
2841 l_api_name VARCHAR2(100) := 'CSE_ASSET_CREATION_PKG.find_asset';
2842
2843 CURSOR dist_history_cur IS
2844 SELECT distribution_id
2845 ,location_id
2846 ,assigned_to
2847 ,code_combination_id
2848 ,units_assigned
2849 FROM fa_distribution_history
2850 WHERE asset_id = p_asset_query_rec.asset_id
2851 AND book_type_code = p_asset_query_rec.book_type_code
2852 AND location_id = NVL(p_asset_query_rec.location_id,location_id)
2853 AND code_combination_id = NVL(p_asset_query_rec.deprn_expense_ccid,code_combination_id)
2854 AND NVL(assigned_to,-1) = NVL(p_asset_query_rec.employee_id,NVL(assigned_to,-1))
2855 AND date_ineffective IS NULL ;
2856
2857 CURSOR asset_cost_cur IS
2858 SELECT DECODE(attribute15,cse_asset_util_pkg.G_MTL_INDICATOR,SUM(fixed_assets_cost),0)
2859 Material_cost ,
2860 DECODE(attribute15,cse_asset_util_pkg.G_MTL_INDICATOR,0,SUM(fixed_assets_cost))
2861 Non_Material_cost
2862 FROM fa_asset_invoices
2863 WHERE date_ineffective IS NULL
2864 AND asset_id = p_asset_query_rec.asset_id
2865 GROUP BY attribute15 ;
2866
2867 CURSOR fa_add_lifo_cur IS
2868 SELECT fab.asset_id,
2869 fab.asset_number,
2870 fab.asset_category_id,
2871 fab.asset_key_ccid,
2872 fab.tag_number,
2873 fab.description,
2874 fab.manufacturer_name,
2875 fab.serial_number,
2876 fab.model_number,
2877 fab.current_units,
2878 cii.inventory_item_id,
2879 fb.book_type_code,
2880 fb.date_placed_in_service,
2881 fb.cost
2882 FROM csi_item_instances cii,
2883 csi_i_assets cia,
2884 fa_books fb,
2885 fa_additions fab
2886 WHERE cii.inventory_item_id = NVL(p_asset_query_rec.inventory_item_id, cii.inventory_item_id)
2887 AND cii.instance_id = cia.instance_id
2888 AND cia.fa_asset_id = fab.asset_id
2889 AND cia.fa_book_type_code = fb.book_type_code
2890 AND TRUNC(fb.date_placed_in_service) =
2891 TRUNC(NVL(p_asset_query_rec.date_placed_in_service, fb.date_placed_in_service))
2892 AND fb.book_type_code = NVL(p_asset_query_rec.book_type_code, fb.book_type_code)
2893 AND fb.date_ineffective IS NULL
2894 AND fb.asset_id = fab.asset_id
2895 AND NVL(fab.model_number, '!@#^') = NVL(p_asset_query_rec.model_number, NVL(fab.model_number, '!@#^') )
2896 AND NVL(fab.serial_number, '!@#^') = NVL(p_asset_query_rec.serial_number, '!@#^')
2897 AND NVL(fab.manufacturer_name, '!@#^') = NVL(p_asset_query_rec.manufacturer_name,NVL(fab.manufacturer_name, '!@#^') )
2898 AND NVL(fab.tag_number, '!@#^') = NVL(p_asset_query_rec.tag_number, NVL(fab.tag_number, '!@#^') )
2899 AND NVL(fab.asset_key_ccid, -1) = NVL(p_asset_query_rec.asset_key_ccid,NVL(fab.asset_key_ccid, -1) )
2900 AND fab.asset_category_id = NVL(p_asset_query_rec.category_id,fab.asset_category_id)
2901 AND fab.asset_number = NVL(p_asset_query_rec.asset_number,fab.asset_number)
2902 AND fab.asset_id = NVL(p_asset_query_rec.asset_id,fab.asset_id)
2903 ORDER BY fb.date_placed_in_service DESC, fab.asset_id DESC ;
2904
2905 CURSOR fa_add_fifo_cur IS
2906 SELECT fab.asset_id
2907 ,fab.asset_number
2908 ,fab.asset_category_id
2909 ,fab.asset_key_ccid
2910 ,fab.tag_number
2911 ,fab.description
2912 ,fab.manufacturer_name
2913 ,fab.serial_number
2914 ,fab.model_number
2915 ,fab.current_units
2916 ,cii.inventory_item_id
2917 ,fb.book_type_code
2918 ,fb.date_placed_in_service
2919 ,fb.cost
2920 FROM csi_item_instances cii
2921 ,csi_i_assets cia
2922 ,fa_books fb
2923 ,fa_additions fab
2924 WHERE cii.inventory_item_id = NVL(p_asset_query_rec.inventory_item_id,
2925 cii.inventory_item_id)
2926 AND cii.instance_id = cia.instance_id
2927 AND cia.fa_asset_id = fab.asset_id
2928 AND cia.fa_book_type_code = fb.book_type_code
2929 AND TRUNC(fb.date_placed_in_service) =
2930 TRUNC(NVL(p_asset_query_rec.date_placed_in_service, fb.date_placed_in_service))
2931 AND fb.book_type_code = NVL(p_asset_query_rec.book_type_code, fb.book_type_code)
2932 AND fb.date_ineffective IS NULL
2933 AND fb.asset_id = fab.asset_id
2934 AND NVL(fab.model_number, '!@#^') = NVL(p_asset_query_rec.model_number, NVL(fab.model_number, '!@#^') )
2935 AND NVL(fab.serial_number, '!@#^') = NVL(p_asset_query_rec.serial_number, '!@#^')
2936 AND NVL(fab.manufacturer_name, '!@#^') = NVL(p_asset_query_rec.manufacturer_name,NVL(fab.manufacturer_name, '!@#^') )
2937 AND NVL(fab.tag_number, '!@#^') = NVL(p_asset_query_rec.tag_number, NVL(fab.tag_number, '!@#^') )
2938 AND NVL(fab.asset_key_ccid, -1) = NVL(p_asset_query_rec.asset_key_ccid,NVL(fab.asset_key_ccid, -1) )
2939 AND fab.asset_category_id = NVL(p_asset_query_rec.category_id,fab.asset_category_id)
2940 AND fab.asset_number = NVL(p_asset_query_rec.asset_number,fab.asset_number)
2941 AND fab.asset_id = NVL(p_asset_query_rec.asset_id,fab.asset_id)
2942 ORDER BY fb.date_placed_in_service , fab.asset_id ;
2943
2944 BEGIN
2945
2946 debug('Begin - find asset');
2947 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
2948 l_asset_number := p_asset_query_rec.asset_number;
2949 l_asset_id := p_asset_query_rec.asset_id;
2950 l_book_type_code := p_asset_query_rec.book_type_code ;
2951 l_serial_number := UPPER(p_asset_query_rec.serial_number);
2952
2953 IF l_asset_id IS NOT NULL
2954 AND l_book_type_code IS NOT NULL
2955 THEN
2956 debug('Searching based on Asset ID abd Book Type alone');
2957 ---Don't serach on following, asset_id and booktype is fine.
2958 l_category_id := NULL ;
2959 l_date_placed_in_service := NULL ;
2960 l_asset_key_ccid := NULL ;
2961 l_tag_number := NULL ;
2962 l_description := NULL ;
2963 l_manufacturer_name := NULL ;
2964 l_model_number := NULL ;
2965 ELSE
2966 l_category_id := p_asset_query_rec.category_id ;
2967 l_date_placed_in_service := p_asset_query_rec.date_placed_in_service ;
2968 l_asset_key_ccid := p_asset_query_rec.asset_key_ccid;
2969 l_tag_number := p_asset_query_rec.tag_number;
2970 l_description := p_asset_query_rec.description;
2971 l_manufacturer_name := p_asset_query_rec.manufacturer_name;
2972 l_model_number := p_asset_query_rec.model_number;
2973 END IF ;
2974
2975 l_location_id := p_asset_query_rec.location_id;
2976 l_deprn_expense_ccid := p_asset_query_rec.deprn_expense_ccid;
2977 l_inventory_item_id := p_asset_query_rec.inventory_item_id;
2978
2979 IF l_asset_number = FND_API.G_MISS_CHAR
2980 THEN
2981 debug('l_asset_number :'|| 'NULL');
2982 ELSE
2983 debug('l_asset_number :'|| p_asset_query_rec.asset_number);
2984 END IF ;
2985 debug('l_asset_id :'|| p_asset_query_rec.asset_id);
2986 debug('l_category_id :'|| l_category_id );
2987 debug('l_book_type_code :'|| l_book_type_code );
2988 debug('l_date_placed_in_service :'|| l_date_placed_in_service) ;
2989 debug('l_asset_key_ccid :'|| l_asset_key_ccid);
2990
2991 IF l_tag_number = FND_API.G_MISS_CHAR
2992 THEN
2993 debug('l_tag_number :'|| 'NULL');
2994 ELSE
2995 debug('l_tag_number :'|| l_tag_number);
2996 END IF ;
2997 IF l_description = FND_API.G_MISS_CHAR
2998 THEN
2999 debug('l_description :'|| 'NULL');
3000 ELSE
3001 debug('l_description :'|| l_description);
3002 END IF ;
3003
3004 IF l_manufacturer_name = FND_API.G_MISS_CHAR
3005 THEN
3006 debug('l_manufacturer_name :'|| 'NULL');
3007 ELSE
3008 debug('l_manufacturer_name :'|| l_manufacturer_name);
3009 END IF ;
3010
3011 IF l_serial_number = FND_API.G_MISS_CHAR
3012 THEN
3013 debug('l_serial_number :'|| 'NULL');
3014 ELSE
3015 debug('l_serial_number :'|| l_serial_number);
3016 END IF ;
3017
3018 IF l_model_number = FND_API.G_MISS_CHAR
3019 THEN
3020 debug('l_model_number :'|| 'NULL');
3021 ELSE
3022 debug('l_model_number :'|| l_model_number);
3023 END IF ;
3024 debug('l_location_id :'|| l_location_id);
3025 debug('l_deprn_expense_ccid :'|| l_deprn_expense_ccid);
3026 debug('l_inventory_item_id :'|| l_inventory_item_id);
3027
3028
3029 IF p_asset_query_rec.search_method = cse_datastructures_pub.G_LIFO_SEARCH
3030 THEN
3031 OPEN fa_add_lifo_cur ;
3032 FETCH fa_add_lifo_cur INTO p_asset_query_rec.asset_id
3033 ,p_asset_query_rec.asset_number
3034 ,p_asset_query_rec.category_id
3035 ,p_asset_query_rec.asset_key_ccid
3036 ,p_asset_query_rec.tag_number
3037 ,p_asset_query_rec.description
3038 ,p_asset_query_rec.manufacturer_name
3039 ,p_asset_query_rec.serial_number
3040 ,p_asset_query_rec.model_number
3041 ,l_total_units
3042 ,p_asset_query_rec.inventory_item_id
3043 ,p_asset_query_rec.book_type_code
3044 ,p_asset_query_rec.date_placed_in_service
3045 ,l_cost ;
3046
3047 IF fa_add_lifo_cur%NOTFOUND
3048 THEN
3049 debug('Asset NOT Found ');
3050 p_asset_query_rec.asset_id := NULL ;
3051 END IF ;
3052 CLOSE fa_add_lifo_cur ;
3053
3054 ELSE
3055 OPEN fa_add_fifo_cur ;
3056 FETCH fa_add_fifo_cur INTO p_asset_query_rec.asset_id
3057 ,p_asset_query_rec.asset_number
3058 ,p_asset_query_rec.category_id
3059 ,p_asset_query_rec.asset_key_ccid
3060 ,p_asset_query_rec.tag_number
3061 ,p_asset_query_rec.description
3062 ,p_asset_query_rec.manufacturer_name
3063 ,p_asset_query_rec.serial_number
3064 ,p_asset_query_rec.model_number
3065 ,l_total_units
3066 ,p_asset_query_rec.inventory_item_id
3067 ,p_asset_query_rec.book_type_code
3068 ,p_asset_query_rec.date_placed_in_service
3069 ,l_cost ;
3070
3071 IF fa_add_fifo_cur%NOTFOUND
3072 THEN
3073 debug('Asset NOT Found ');
3074 p_asset_query_rec.asset_id := NULL ;
3075 END IF ;
3076 CLOSE fa_add_fifo_cur ;
3077
3078 END IF;
3079
3080
3081
3082
3083 IF p_asset_query_rec.asset_id IS NOT NULL
3084 THEN
3085 debug('Asset Found , ID is :'||p_asset_query_rec.asset_id);
3086 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3087
3088 OPEN asset_cost_cur ;
3089 FETCH asset_cost_cur into l_mtl_cost ,l_non_mtl_cost ;
3090 CLOSE asset_cost_cur ;
3091 l_total_cost := NVL(l_mtl_cost,0)+NVL(l_non_mtl_cost,0);
3092 debug('Total Cost :'||NVL(l_total_cost,0));
3093
3094 ---Modified 10-17
3095 IF l_total_cost = 0
3096 THEN
3097 l_mtl_ratio := 1 ;
3098 ELSE
3099 l_mtl_ratio := l_mtl_cost/l_total_cost ;
3100 END IF ;
3101
3102 debug('l_mtl_ratio : '|| l_mtl_ratio);
3103 p_asset_query_rec.current_mtl_cost := l_cost*l_mtl_ratio ;
3104 p_asset_query_rec.current_non_mtl_cost := l_cost - p_asset_query_rec.current_mtl_cost ;
3105
3106 l_location_units := 0;
3107 i := 0;
3108
3109 FOR dist_history_rec IN dist_history_cur
3110 LOOP
3111 i := i+1 ;
3112 p_distribution_tbl(i).asset_id := p_asset_query_rec.asset_id ;
3113 p_distribution_tbl(i).book_type_code := p_asset_query_rec.book_type_code ;
3114 p_distribution_tbl(i).distribution_id := dist_history_rec.distribution_id ;
3115 p_distribution_tbl(i).location_id := dist_history_rec.location_id ;
3116 p_distribution_tbl(i).employee_id := dist_history_rec.assigned_to ;
3117 p_distribution_tbl(i).deprn_expense_ccid := dist_history_rec.code_combination_id ;
3118 p_distribution_tbl(i).current_units := dist_history_rec.units_assigned ;
3119 p_distribution_tbl(i).pending_ret_units := 0;
3120 l_location_units := l_location_units + dist_history_rec.units_assigned ;
3121 END LOOP ;
3122
3123 debug('l_total_units : '|| l_total_units);
3124 debug('l_location_units : '|| l_location_units);
3125
3126 -- 10/12
3127 -- l_unit_ratio := l_location_units/l_total_units ;
3128 -- p_asset_query_rec.current_mtl_cost :=
3129 -- p_asset_query_rec.current_mtl_cost * l_unit_ratio ;
3130 -- p_asset_query_rec.current_non_mtl_cost :=
3131 -- p_asset_query_rec.current_non_mtl_cost * l_unit_ratio ;
3132 --
3133
3134 ---08/28 changed to total_units instead of location_units
3135 ----p_asset_query_rec.current_units := l_location_units ;
3136 p_asset_query_rec.current_units := l_total_units ;
3137
3138 cse_asset_util_pkg.get_pending_retirements(p_asset_query_rec,
3139 p_distribution_tbl,
3140 x_return_status,
3141 x_error_msg);
3142
3143 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3144 THEN
3145 RAISE e_error ;
3146 END IF;
3147
3148 cse_asset_util_pkg.get_pending_adjustments(p_asset_query_rec,
3149 x_return_status,
3150 x_error_msg);
3151
3152 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3153 THEN
3154 RAISE e_error;
3155 END IF;
3156
3157 ELSE
3158 p_asset_query_rec.asset_id := NULL;
3159 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3160 END IF;
3161
3162 EXCEPTION
3163 WHEN e_error
3164 THEN
3165 x_return_status := fnd_api.G_RET_STS_ERROR ;
3166 --Log Error Here.
3167 debug('IN e_error:'||x_error_msg);
3168 WHEN OTHERS
3169 THEN
3170 x_return_status := fnd_api.G_RET_STS_ERROR ;
3171 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
3172 fnd_message.set_token('API_NAME',l_api_name);
3173 fnd_message.set_token('SQL_ERROR',SQLERRM);
3174 x_error_msg := fnd_message.get;
3175
3176 debug(x_error_msg);
3177 END find_asset;
3178
3179 PROCEDURE adjust_asset(
3180 p_asset_query_rec IN OUT NOCOPY cse_datastructures_pub.asset_query_rec,
3181 p_mass_add_rec IN OUT NOCOPY fa_mass_additions%ROWTYPE,
3182 p_mtl_percent IN NUMBER,
3183 x_return_status OUT NOCOPY VARCHAR2,
3184 x_error_msg OUT NOCOPY VARCHAR2 )
3185 IS
3186 l_mass_add_rec fa_mass_additions%ROWTYPE;
3187 l_mass_ext_trf_rec fa_mass_external_transfers%ROWTYPE;
3188
3189 x_msg_count NUMBER;
3190 x_msg_data VARCHAR2(2000);
3191 l_group_asset_id NUMBER ;
3192 l_mass_external_retire_id NUMBER ;
3193 l_prorate_convention fa_mass_ext_retirements.retirement_prorate_convention%TYPE;
3194 l_batch_name fa_mass_ext_retirements.batch_name%TYPE ;
3195 l_init_ext_ret_rec fa_mass_ext_retirements%ROWTYPE ;
3196 l_ext_ret_rec fa_mass_ext_retirements%ROWTYPE ;
3197 l_sysdate DATE := SYSDATE ;
3198
3199 l_api_name VARCHAR2(100) := 'CSE_ASSET_CREATION_PKG.adjust_asset';
3200 l_total_fa_units NUMBER ;
3201
3202 CURSOR fa_asset_units (c_asset_id IN NUMBER) IS
3203 SELECT fad.current_units
3204 FROM fa_additions fad
3205 WHERE fad.asset_id = c_asset_id ;
3206
3207 CURSOR dpi_for_ipv (c_asset_id IN NUMBER, c_book_type_code IN VARCHAR2) IS
3208 SELECT date_placed_in_service
3209 FROM fa_books
3210 WHERE asset_id = c_asset_id
3211 AND book_type_code = c_book_type_code ;
3212
3213 CURSOR get_group_asset_id_cur (c_asset_category_id IN NUMBER, c_book_type_code IN VARCHAR2) IS
3214 SELECT default_group_asset_id
3215 FROM fa_category_books
3216 WHERE category_id = c_asset_category_id
3217 AND book_type_code = c_book_type_code ;
3218
3219 CURSOR prorate_convention_cur (c_book_type_code IN VARCHAR2, c_asset_id IN NUMBER) IS
3220 SELECT fcgd.retirement_prorate_convention
3221 FROM fa_category_book_defaults fcgd
3222 ,fa_books fb
3223 ,fa_additions_b fab
3224 WHERE fb.date_placed_in_service BETWEEN fcgd.start_dpis AND
3225 NVL(fcgd.end_dpis, fb.date_placed_in_service)
3226 AND fb.date_ineffective IS NULL
3227 AND fb.book_type_code = fcgd.book_type_code
3228 AND fb.asset_id = fab.asset_id
3229 AND fcgd.book_type_code = c_book_type_code
3230 AND fcgd.category_id = fab.asset_category_id
3231 AND fab.asset_id = c_asset_id ;
3232
3233 BEGIN
3234
3235 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
3236 debug('inside api cse_asset_creation_pkg.adjust_asset');
3237 debug(' asset_id : '||p_asset_query_rec.asset_id);
3238 debug(' units_to_be_adjusted : '||p_mass_add_rec.payables_units);
3239
3240 l_mass_add_rec := p_mass_add_rec ;
3241 OPEN fa_asset_units (p_asset_query_rec.asset_id ) ;
3242 FETCH fa_asset_units INTO l_total_fa_units ;
3243 CLOSE fa_asset_units ;
3244
3245 l_total_fa_units := NVL(l_total_fa_units,0);
3246 debug(' total_fa_units : '||l_total_fa_units);
3247
3248 IF nvl(l_total_fa_units,0) - abs(nvl(p_mass_add_rec.payables_units,0)) > 0 THEN
3249
3250 OPEN get_group_asset_id_cur (p_mass_add_rec.asset_category_id,p_mass_add_rec.book_type_code );
3251 FETCH get_group_asset_id_cur INTO l_group_asset_id;
3252 CLOSE get_group_asset_id_cur;
3253
3254 IF l_group_asset_id IS NOT NULL THEN
3255 l_mass_add_rec.group_asset_id := l_group_asset_id;
3256 END IF ;
3257 l_mass_add_rec.add_to_asset_id := p_asset_query_rec.asset_id ;
3258 l_mass_add_rec.posting_status := 'POST' ;
3259 l_mass_add_rec.queue_name := 'ADD TO ASSET';
3260
3261 ---FOR IPV
3262 IF NVL(p_mass_add_rec.reviewer_comments,'!#$') ='IPV' THEN
3263 OPEN dpi_for_ipv (l_mass_add_rec.add_to_asset_id, l_mass_add_rec.book_type_code) ;
3264 FETCH dpi_for_ipv INTO l_mass_add_rec.date_placed_in_service ;
3265 CLOSE dpi_for_ipv ;
3266 END IF ;
3267
3268 IF p_mtl_percent <> 0 THEN
3269
3270 l_mass_add_rec.fixed_assets_cost := p_mass_add_rec.fixed_assets_cost*p_mtl_percent ;
3271 l_mass_add_rec.payables_cost := p_mass_add_rec.payables_cost*p_mtl_percent ;
3272 l_mass_add_rec.attribute14 := cse_asset_util_pkg.G_MTL_INDICATOR ;
3273
3274 cse_asset_util_pkg.insert_mass_add(
3275 1.0,
3276 fnd_api.g_false,
3277 fnd_api.g_true,
3278 l_mass_add_rec,
3279 x_return_status,
3280 x_msg_count,
3281 x_msg_data);
3282
3283 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
3284 x_error_msg := x_msg_data ;
3285 RAISE fnd_api.g_exc_error;
3286 END IF ;
3287
3288 END IF ; ---Material Cost
3289
3290 IF p_mtl_percent <> 1 THEN
3291
3292 l_mass_add_rec.fixed_assets_cost := p_mass_add_rec.fixed_assets_cost*(1 - p_mtl_percent) ;
3293 l_mass_add_rec.payables_cost := p_mass_add_rec.payables_cost*(1 - p_mtl_percent) ;
3294 l_mass_add_rec.attribute14 := cse_asset_util_pkg.g_non_mtl_indicator;
3295
3296 cse_asset_util_pkg.insert_mass_add(
3297 1.0,
3298 fnd_api.g_false,
3299 fnd_api.g_true,
3300 l_mass_add_rec,
3301 x_return_status,
3302 x_msg_count,
3303 x_msg_data );
3304
3305 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
3306 x_error_msg := x_msg_data ;
3307 RAISE fnd_api.g_exc_error ;
3308 END IF ;
3309
3310 END IF ; --Non-material Cost
3311
3312 -- NON IPV Adjustment
3313 IF NVL(p_mass_add_rec.reviewer_comments,'!#$') <> 'IPV' THEN
3314
3315 p_asset_query_rec.location_id := l_mass_add_rec.location_id ;
3316 p_asset_query_rec.deprn_expense_ccid := l_mass_add_rec.expense_code_combination_id ;
3317 p_asset_query_rec.employee_id := l_mass_add_rec.assigned_to ;
3318
3319 create_fa_distribution (
3320 p_asset_query_rec,
3321 p_mass_add_rec,
3322 x_return_status,
3323 x_error_msg );
3324
3325 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
3326 RAISE fnd_api.g_exc_error ;
3327 END IF ;
3328 ELSE
3329 NULL ;
3330 debug('This is IPV Cost Adjustment ONLY');
3331 END IF ; --IPV
3332
3333 ELSE --retirement
3334
3335 debug('need to retire this asset');
3336
3337 OPEN prorate_convention_cur ( p_asset_query_rec.book_type_code, p_asset_query_rec.asset_id ) ;
3338 FETCH prorate_convention_cur INTO l_prorate_convention ;
3339 CLOSE prorate_convention_cur ;
3340
3341 SELECT fa_mass_ext_retirements_s.nextval
3342 INTO l_mass_external_retire_id
3343 FROM dual ;
3344
3345 l_batch_name := 'BATCH'||TO_CHAR(l_mass_external_retire_id) ;
3346
3347 l_ext_ret_rec.batch_name := l_batch_name;
3348 l_ext_ret_rec.mass_external_retire_id := l_mass_external_retire_id;
3349 l_ext_ret_rec.book_type_code := p_asset_query_rec.book_type_code;
3350 l_ext_ret_rec.review_status := 'POST';
3351 l_ext_ret_rec.retirement_type_code := 'EXTRAORDINARY';
3352 l_ext_ret_rec.asset_id := p_asset_query_rec.asset_id;
3353 l_ext_ret_rec.date_retired := l_sysdate;
3354 l_ext_ret_rec.date_effective := l_sysdate;
3355 l_ext_ret_rec.cost_retired := ABS(p_mass_add_rec.fixed_assets_cost);
3356 l_ext_ret_rec.units := ABS(p_mass_add_rec.payables_units );
3357 l_ext_ret_rec.cost_of_removal := 0;
3358 l_ext_ret_rec.proceeds_of_sale := 0;
3359 l_ext_ret_rec.calc_gain_loss_flag := 'N' ;
3360 l_ext_ret_rec.created_by := fnd_global.user_id;
3361 l_ext_ret_rec.creation_date := l_sysdate;
3362 l_ext_ret_rec.last_updated_by := fnd_global.user_id;
3363 l_ext_ret_rec.last_update_date := l_sysdate;
3364 l_ext_ret_rec.last_update_login := fnd_global.login_id;
3365 l_ext_ret_rec.retirement_prorate_convention := l_prorate_convention ;
3366
3367 cse_asset_adjust_pkg.insert_retirement(
3368 l_ext_ret_rec,
3369 x_return_status,
3370 x_error_msg) ;
3371
3372 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
3373 debug('Insert into Retirements table failed ');
3374 RAISE fnd_api.g_exc_error ;
3375 END IF ;
3376
3377 END IF ; --retirement
3378
3379 EXCEPTION
3380 WHEN fnd_api.g_exc_error THEN
3381 x_return_status := FND_API.G_RET_STS_ERROR ;
3382 x_error_msg := x_error_msg ;
3383 WHEN OTHERS THEN
3384 x_return_status := fnd_api.G_RET_STS_ERROR ;
3385 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
3386 fnd_message.set_token('API_NAME',l_api_name);
3387 fnd_message.set_token('SQL_ERROR',SQLERRM);
3388 x_error_msg := fnd_message.get;
3389 END adjust_asset;
3390
3391
3392 PROCEDURE create_asset(
3393 p_inst_tbl IN instance_tbl,
3394 x_return_status OUT nocopy varchar2,
3395 x_err_inst_rec OUT nocopy instance_rec)
3396 IS
3397 l_inst_rec instance_rec;
3398 l_fa_qry_rec fa_query_rec;
3399 l_fixed_asset_rec fixed_asset_rec;
3400 l_pending_fa_rec fixed_asset_rec;
3401
3402 l_mass_addition_id number;
3403 l_fa_action varchar2(30);
3404
3405 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
3406 l_error_message varchar2(2000);
3407
3408 BEGIN
3409
3410 IF p_inst_tbl.COUNT > 0 THEN
3411
3412 FOR l_ind IN p_inst_tbl.FIRST .. p_inst_tbl.LAST
3413 LOOP
3414
3415 l_inst_rec := p_inst_tbl(l_ind);
3416
3417 l_fa_qry_rec := null;
3418
3419 l_fa_qry_rec.asset_id := null;
3420 l_fa_qry_rec.inventory_item_id := l_inst_rec.inventory_item_id;
3421 l_fa_qry_rec.book_type_code := l_inst_rec.book_type_code;
3422 l_fa_qry_rec.asset_category_id := l_inst_rec.asset_category_id;
3423 l_fa_qry_rec.asset_description := l_inst_rec.asset_description;
3424 l_fa_qry_rec.date_placed_in_service := l_inst_rec.date_placed_in_service;
3425 l_fa_qry_rec.model_number := l_inst_rec.model_number;
3426 l_fa_qry_rec.tag_nuber := l_inst_rec.tag_number;
3427 l_fa_qry_rec.manufacturer_name := l_inst_rec.manufacturer_name;
3428 l_fa_qry_rec.asset_key_ccid := l_inst_rec.asset_key_ccid;
3429 l_fa_qry_rec.search_method := l_inst_rec.search_method;
3430
3431 get_fixed_assets(
3432 p_fa_query_rec => l_fa_qry_rec,
3433 x_fixed_asset_rec => l_fixed_asset_rec,
3434 x_return_status => l_return_status,
3435 x_error_message => l_error_message);
3436
3437 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3438 RAISE fnd_api.g_exc_error;
3439 END IF;
3440
3441 IF l_fixed_asset_rec.asset_id is not null THEN
3442
3443 debug(' fixed asset found. asset id : '||l_fixed_asset_rec.asset_id);
3444
3445 l_fa_action := 'ADD_TO_ASSET';
3446
3447 add_to_asset(
3448 p_asset_id => l_fixed_asset_rec.asset_id,
3449 p_instance_rec => l_inst_rec,
3450 x_return_status => l_return_status,
3451 x_error_message => l_error_message);
3452
3453 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3454 RAISE fnd_api.g_exc_error;
3455 END IF;
3456
3457 ELSE
3458
3459 debug(' fixed asset not found. look for pending mass addition');
3460
3461 get_pending_additions(
3462 p_fa_query_rec => l_fa_qry_rec,
3463 x_fixed_asset_rec => l_pending_fa_rec,
3464 x_return_status => l_return_status,
3465 x_error_message => l_error_message);
3466
3467 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3468 RAISE fnd_api.g_exc_error;
3469 END IF;
3470
3471 IF l_pending_fa_rec.mass_addition_id is not null THEN
3472
3473 debug('pending add found. mass addtion id : '|| l_pending_fa_rec.mass_addition_id);
3474
3475 l_fa_action := 'ADD_TO_MASS_ADDITION';
3476
3477 add_to_mass_addition(
3478 p_mass_addition_id => l_pending_fa_rec.mass_addition_id,
3479 p_instance_rec => l_inst_rec,
3480 x_return_status => l_return_status,
3481 x_error_message => l_error_message);
3482
3483 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3484 RAISE fnd_api.g_exc_error;
3485 END IF;
3486
3487 ELSE
3488
3489 debug(' pending mass addition not found. create mass addition record');
3490
3491 l_fa_action := 'CREATE_MASS_ADDITION';
3492
3493 create_mass_addition(
3494 p_instance_rec => l_inst_rec,
3495 x_mass_addition_id => l_mass_addition_id,
3496 x_return_status => l_return_status,
3497 x_error_message => l_error_message);
3498
3499 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3500 RAISE fnd_api.g_exc_error;
3501 END IF;
3502
3503 END IF; -- pending mass_addition is not null
3504
3505 END IF; -- asset id is not null
3506
3507 END LOOP;
3508 END IF;
3509 EXCEPTION
3510 WHEN fnd_api.g_exc_error THEN
3511 x_return_status := fnd_api.g_ret_sts_error;
3512 x_err_inst_rec := l_inst_rec;
3513 END create_asset;
3514
3515 END cse_asset_creation_pkg;