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