[Home] [Help]
PACKAGE BODY: APPS.CSE_FA_TXN_PKG
Source
1 PACKAGE BODY cse_fa_txn_pkg AS
2 /* $Header: CSEASTXB.pls 120.7.12020000.3 2012/12/28 16:53:38 dsingire ship $ */
3
4
5 l_debug varchar2(1) := NVL(fnd_profile.value('cse_debug_option'),'N');
6
7 PROCEDURE debug( p_message IN varchar2) IS
8 BEGIN
9 IF l_debug = 'Y' THEN
10 cse_debug_pub.add(p_message);
11 IF nvl(fnd_global.conc_request_id, -1) <> -1 THEN
12 fnd_file.put_line(fnd_file.log, p_message);
13 END IF;
14 END IF;
15 EXCEPTION
16 WHEN others THEN
17 null;
18 END debug;
19
20 PROCEDURE Update_IB_Instance (
21 p_instance_id IN NUMBER,
22 p_asset_quantity IN NUMBER,
23 p_Default_inst_status IN VARCHAR2,
24 p_active_end_date IN DATE,
25 px_csi_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
26 ) IS
27 l_def_Instance_status_id NUMBER;
28 l_h_Instance_status_id NUMBER;
29 l_h_instance_usage_code VARCHAR2(30);
30 l_Return_Status VARCHAR2(1);
31 l_msg_index NUMBER;
32 l_Msg_Count NUMBER;
33 l_Msg_Data VARCHAR2(2000);
34
35 l_serial_number VARCHAR2(1000);
36 l_instance_qty NUMBER;
37 l_instance_end_date DATE;
38 l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
39 l_location_tbl csi_item_instance_pvt.location_tbl;
40 l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
41 l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
42 l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
43
44 l_u_instance_rec csi_datastructures_pub.instance_rec;
45 l_u_parties_tbl csi_datastructures_pub.party_tbl;
46 l_u_pty_accts_tbl csi_datastructures_pub.party_account_tbl;
47 l_u_org_units_tbl csi_datastructures_pub.organization_units_tbl;
48 l_u_ea_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
49 l_u_pricing_tbl csi_datastructures_pub.pricing_attribs_tbl;
50 l_u_assets_tbl csi_datastructures_pub.instance_asset_tbl;
51 l_instance_ids_list csi_datastructures_pub.id_tbl;
52 px_oks_txn_inst_tbl oks_ibint_pub.txn_instance_tbl;
53 px_child_inst_tbl csi_item_instance_grp.child_inst_tbl;
54 l_u_csi_txn_rec csi_datastructures_pub.transaction_rec;
55 l_Error_Message VARCHAR2(2000);
56
57
58 BEGIN
59 debug('Inside api cse_fa_txn_pkg.Update_IB_Instance');
60 l_u_instance_rec := CSE_UTIL_PKG.Init_Instance_Update_Rec;
61 l_u_csi_txn_rec := px_csi_txn_rec ;
62 l_u_instance_rec.instance_id := p_instance_id;
63
64 debug(' Instance ID : '||l_u_instance_rec.instance_id);
65 SELECT object_version_number, serial_number, quantity, active_end_date
66 INTO l_u_instance_rec.object_version_number, l_serial_number, l_instance_qty, l_instance_end_date
67 FROM csi_item_instances
68 WHERE instance_id = l_u_instance_rec.instance_id;
69
70 debug(' Serial Number : '||l_serial_number);
71 debug(' Quantity : '||l_instance_qty);
72 debug(' Instance End Date : '||l_instance_end_date);
73
74 BEGIN
75 SELECT old_inst_usage_code
76 INTO l_h_instance_usage_code
77 FROM csi_item_instances_h cih, csi_transactions ct
78 WHERE cih.instance_id = l_u_instance_rec.instance_id
79 AND cih.transaction_id = ct.transaction_id
80 AND ct.TRANSACTION_TYPE_ID = 104
81 AND old_inst_usage_code IS NOT NULL
82 AND rownum = 1
83 ORDER BY instance_history_id DESC;
84 EXCEPTION
85 WHEN OTHERS THEN
86 NULL;
87 END;
88
89 debug(' Instance Usage Code : '||l_h_instance_usage_code);
90
91 BEGIN
92 SELECT old_instance_status_id
93 INTO l_h_Instance_status_id
94 FROM csi_item_instances_h cih, csi_transactions ct
95 WHERE cih.instance_id = l_u_instance_rec.instance_id
96 AND cih.transaction_id = ct.transaction_id
97 AND ct.TRANSACTION_TYPE_ID = 104
98 AND old_instance_status_id IS NOT NULL
99 AND rownum = 1
100 ORDER BY instance_history_id DESC;
101 EXCEPTION
102 WHEN OTHERS THEN
103 NULL;
104 END;
105
106 debug(' Instance Usage Code : '||l_h_instance_usage_code);
107
108 BEGIN
109 SELECT instance_status_id
110 INTO l_def_Instance_status_id
111 FROM csi_instance_statuses
112 WHERE name = fnd_profile.value('CSI_DEFAULT_INSTANCE_STATUS')
113 AND ROWNUM =1 ;
114 EXCEPTION
115 WHEN OTHERS THEN
116 NULL;
117 END;
118 IF p_Default_inst_status = 'Y' THEN
119 l_u_instance_rec.Instance_status_id := NVL(l_h_Instance_status_id,l_def_Instance_status_id) ;
120 END IF;
121
122 IF l_h_instance_usage_code IS NOT NULL THEN
123 l_u_instance_rec.instance_usage_code := l_h_instance_usage_code;
124 END IF;
125
126 IF l_serial_number IS NULL AND l_instance_end_date IS NULL THEN
127 l_u_instance_rec.quantity := NVl(l_instance_qty,0) + NVL(p_asset_quantity,0);
128 END IF;
129
130 l_u_instance_rec.active_end_date := p_active_end_date;
131 IF (l_debug = 'Y') THEN
132 debug('Calling API csi_item_instance_pub.update_item_instance');
133 debug(' instance_id : '||l_u_instance_rec.instance_id);
134 debug(' active_end_date : '||to_char(l_u_instance_rec.active_end_date,'dd-mon-rrrr'));
135 debug(' instance_usage_code : '||l_u_instance_rec.instance_usage_code);
136 debug(' Instance_status_id : '||l_u_instance_rec.Instance_status_id);
137 debug(' Transaction id : '||l_u_csi_txn_rec.transaction_id );
138 debug(' quantity : '||l_u_instance_rec.quantity );
139 debug(' p_asset_quantity : '||p_asset_quantity );
140 debug(' l_instance_qty : '||l_instance_qty );
141 END IF;
142 csi_item_instance_pvt.update_item_instance(
143 p_api_version => 1.0,
144 p_commit => fnd_api.g_false,
145 p_init_msg_list => fnd_api.g_true,
146 p_validation_level => fnd_api.g_valid_level_full,
147 p_instance_rec => l_u_instance_rec,
148 p_txn_rec => l_u_csi_txn_rec,
149 x_instance_id_lst => l_instance_ids_list,
150 x_return_status => l_return_status,
151 x_msg_count => l_msg_count,
152 x_msg_data => l_msg_data,
153 p_item_attribute_tbl => l_item_attribute_tbl,
154 p_location_tbl => l_location_tbl,
155 p_generic_id_tbl => l_generic_id_tbl,
156 p_lookup_tbl => l_lookup_tbl,
157 p_ins_count_rec => l_ins_count_rec,
158 p_oks_txn_inst_tbl => px_oks_txn_inst_tbl,
159 p_child_inst_tbl => px_child_inst_tbl);
160
161 IF (l_debug = 'Y') THEN
162 debug('After Updating Item Instance Status :'|| l_return_status);
163 END IF;
164 IF l_return_status <> fnd_api.g_ret_sts_success THEN
165
166 l_msg_index := 1;
167 l_Error_Message:=l_msg_data;
168 WHILE l_msg_count > 0 LOOP
169 l_Error_Message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE);
170 IF (l_debug = 'Y') THEN
171 debug(' l_Error_Message '||l_Error_Message);
172 END IF;
173 l_msg_index := l_msg_index + 1;
174 l_Msg_Count := l_Msg_Count - 1;
175 END LOOP;
176 RAISE fnd_api.g_exc_error;
177 END IF;
178 EXCEPTION
179 when fnd_api.g_exc_error THEN
180 debug('in g_exc_error '||cse_util_pkg.dump_error_stack);
181 when others then
182 debug('in others - '||sqlerrm);
183 END Update_IB_Instance;
184
185 PROCEDURE create_inst_asset(
186 px_inst_asset_rec IN OUT nocopy csi_datastructures_pub.instance_asset_rec,
187 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
188 x_return_status OUT nocopy varchar2)
189 IS
190 l_lookup_tbl csi_asset_pvt.lookup_tbl;
191 l_asset_count_rec csi_asset_pvt.asset_count_rec;
192 l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
193 l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl;
194
195 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
196 l_msg_count number;
197 l_msg_data varchar2(2000);
198 BEGIN
199 x_return_status := fnd_api.g_ret_sts_success;
200
201 px_inst_asset_rec.fa_sync_flag := 'Y';
202
203 csi_asset_pvt.create_instance_asset(
204 p_api_version => 1.0 ,
205 p_commit => fnd_api.g_false,
206 p_init_msg_list => fnd_api.g_true,
207 p_validation_level => fnd_api.g_valid_level_full,
208 p_instance_asset_rec => px_inst_asset_rec,
209 p_txn_rec => px_csi_txn_rec,
210 x_return_status => l_return_status,
211 x_msg_count => l_msg_count,
212 x_msg_data => l_msg_data ,
213 p_lookup_tbl => l_lookup_tbl,
214 p_asset_count_rec => l_asset_count_rec,
215 p_asset_id_tbl => l_asset_id_tbl,
216 p_asset_loc_tbl => l_asset_loc_tbl);
217
218 IF l_return_status <> fnd_api.g_ret_sts_success THEN
219 RAISE fnd_api.g_exc_error;
220 END IF;
221
222 EXCEPTION
223 WHEN fnd_api.g_exc_error THEN
224 x_return_status := fnd_api.g_ret_sts_error;
225 END create_inst_asset;
226
227 PROCEDURE update_inst_asset(
228 px_inst_asset_rec IN OUT nocopy csi_datastructures_pub.instance_asset_rec,
229 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
230 x_return_status OUT nocopy varchar2)
231 IS
232
233 l_lookup_tbl csi_asset_pvt.lookup_tbl;
234 l_asset_count_rec csi_asset_pvt.asset_count_rec;
235 l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
236 l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl;
237
238 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
239 l_msg_count number;
240 l_msg_data varchar2(2000);
241 BEGIN
242
243 x_return_status := fnd_api.g_ret_sts_success;
244
245 IF nvl(px_inst_asset_rec.instance_asset_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
246
247 px_inst_asset_rec.fa_sync_flag := 'Y';
248
249 SELECT object_version_number
250 INTO px_inst_asset_rec.object_version_number
251 FROM csi_i_assets
252 WHERE instance_asset_id = px_inst_asset_rec.instance_asset_id;
253
254 csi_asset_pvt.update_instance_asset (
255 p_api_version => 1.0,
256 p_commit => fnd_api.g_false,
257 p_init_msg_list => fnd_api.g_true,
258 p_validation_level => fnd_api.g_valid_level_full,
259 p_instance_asset_rec => px_inst_asset_rec,
260 p_txn_rec => px_csi_txn_rec,
261 x_return_status => l_return_status,
262 x_msg_count => l_msg_count,
263 x_msg_data => l_msg_data,
264 p_lookup_tbl => l_lookup_tbl,
265 p_asset_count_rec => l_asset_count_rec,
266 p_asset_id_tbl => l_asset_id_tbl,
267 p_asset_loc_tbl => l_asset_loc_tbl);
268
269 IF l_return_status <> fnd_api.g_ret_sts_success THEN
270 RAISE fnd_api.g_exc_error;
271 END IF;
272
273 END IF;
274
275 EXCEPTION
276 WHEN fnd_api.g_exc_error THEN
277 x_return_status := fnd_api.g_ret_sts_error;
278 END update_inst_asset;
279
280
281 FUNCTION total_inst_asset_qty(
282 p_inst_asset_tbl IN csi_datastructures_pub.instance_asset_header_tbl)
283 RETURN number
284 IS
285 l_total_qty number := 0;
286 BEGIN
287 IF p_inst_asset_tbl.COUNT > 0 THEN
288 FOR l_ind IN p_inst_asset_tbl.FIRST .. p_inst_asset_tbl.LAST
289 LOOP
290 l_total_qty := l_total_qty + p_inst_asset_tbl(l_ind).asset_quantity;
291 END LOOP;
292 END IF;
293 RETURN l_total_qty;
294 END total_inst_asset_qty;
295
296
297 PROCEDURE reinstate_inst_asset(
298 p_inst_asset_rec IN csi_datastructures_pub.instance_asset_header_rec,
299 p_units IN number,
300 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
301 x_return_status OUT nocopy varchar2)
302 IS
303
304 l_inst_asset_qry_rec csi_datastructures_pub.instance_asset_query_rec;
305 l_inst_asset_tbl csi_datastructures_pub.instance_asset_header_tbl;
306 l_time_stamp date := null;
307
308 l_total_inst_asset_qty number := 0;
309 l_inst_asset_rec csi_datastructures_pub.instance_asset_rec;
310
311 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
312 l_msg_count number;
313 l_msg_data varchar2(2000);
314
315 BEGIN
316 x_return_status := fnd_api.g_ret_sts_success;
317 debug('In reinstate_inst_asset ');
318 -- check if there is any instance asset record with in_service
319 l_inst_asset_qry_rec.fa_asset_id := p_inst_asset_rec.fa_asset_id;
320 l_inst_asset_qry_rec.fa_book_type_code := p_inst_asset_rec.fa_book_type_code;
321 l_inst_asset_qry_rec.fa_location_id := p_inst_asset_rec.fa_location_id;
322 l_inst_asset_qry_rec.update_status := 'IN_SERVICE';
323
324 csi_asset_pvt.get_instance_assets(
325 p_api_version => 1.0,
326 p_commit => fnd_api.g_false,
327 p_init_msg_list => fnd_api.g_true,
328 p_validation_level => fnd_api.g_valid_level_full,
329 p_instance_asset_query_rec => l_inst_asset_qry_rec,
330 p_resolve_id_columns => fnd_api.g_false,
331 p_time_stamp => l_time_stamp,
332 x_instance_asset_tbl => l_inst_asset_tbl,
333 x_return_status => l_return_status,
334 x_msg_count => l_msg_count,
335 x_msg_data => l_msg_data);
336
337 IF l_return_status <> fnd_api.g_ret_sts_success THEN
338 RAISE fnd_api.g_exc_error;
339 END IF;
340 debug(' l_inst_asset_tbl.COUNT : '||l_inst_asset_tbl.COUNT);
341 IF l_inst_asset_tbl.COUNT > 0 THEN
342
343 IF l_inst_asset_tbl.COUNT = 1 THEN
344
345 l_inst_asset_rec.instance_asset_id := l_inst_asset_tbl(1).instance_asset_id;
346 l_inst_asset_rec.asset_quantity := l_inst_asset_tbl(1).asset_quantity + p_units;
347 l_inst_asset_rec.check_for_instance_expiry := fnd_api.G_FALSE ; --Added for bug 13459669
348 debug(' Updating instance_asset_id : '||l_inst_asset_tbl(1).instance_asset_id);
349 update_inst_asset(
350 px_inst_asset_rec => l_inst_asset_rec,
351 px_csi_txn_rec => px_csi_txn_rec,
352 x_return_status => l_return_status);
353
354 IF l_return_status <> fnd_api.g_ret_sts_success THEN
355 RAISE fnd_api.g_exc_error;
356 END IF;
357 /* SELECT citdv.instance_id,citdv.*
358 FROM csi_i_assets_h ciah,
359 csi_item_instances_h ciih,
360 csi_transactions ct
361 WHERE ciah.transaction_id = ciih.transaction_id
362 AND citdv.transaction_type_id = 104
363 AND ciah.instance_asset_id = 75478
364
365 */
366 --Update_IB_Instance Added for bug 13459669
367 debug(' Update_IB_Instance : '||l_inst_asset_tbl(1).Instance_Id);
368 Update_IB_Instance (
369 p_instance_id => l_inst_asset_tbl(1).Instance_Id,
370 p_asset_quantity => p_units ,
371 p_Default_inst_status => 'Y',
372 p_active_end_date => NULL,
373 px_csi_txn_rec => px_csi_txn_rec
374 );
375 ELSE
376 null;
377 END IF;
378
379 ELSE
380
381 l_inst_asset_rec.instance_asset_id := p_inst_asset_rec.instance_asset_id;
382 l_inst_asset_rec.update_status := 'IN_SERVICE';
383 l_inst_asset_rec.asset_quantity := p_units;
384 l_inst_asset_rec.active_end_date := NULL; --Added for bug 13459669
385 l_inst_asset_rec.check_for_instance_expiry := fnd_api.G_FALSE ; --Added for bug 13459669
386 debug(' Updating instance_asset_id : '||l_inst_asset_rec.instance_asset_id);
387 update_inst_asset(
388 px_inst_asset_rec => l_inst_asset_rec,
389 px_csi_txn_rec => px_csi_txn_rec,
390 x_return_status => l_return_status);
391
392 IF l_return_status <> fnd_api.g_ret_sts_success THEN
393 RAISE fnd_api.g_exc_error;
394 END IF;
395 --Update_IB_Instance Added for bug 13459669
396 debug(' Update_IB_Instance : '||l_inst_asset_rec.Instance_Id);
397 Update_IB_Instance (
398 p_instance_id => l_inst_asset_rec.Instance_Id,
399 p_asset_quantity => p_units ,
400 p_Default_inst_status => 'Y',
401 p_active_end_date => NULL,
402 px_csi_txn_rec => px_csi_txn_rec
403 );
404 END IF;
405
406 EXCEPTION
407 WHEN fnd_api.g_exc_error THEN
408 x_return_status := fnd_api.g_ret_sts_error;
409 END reinstate_inst_asset;
410
411 PROCEDURE retire_inst_asset(
412 p_inst_asset_id IN number,
413 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
414 x_return_status OUT nocopy varchar2)
415 IS
416
417 l_inst_asset_rec csi_datastructures_pub.instance_asset_rec;
418 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
419
420 BEGIN
421 x_return_status := fnd_api.g_ret_sts_success;
422
423 l_inst_asset_rec.instance_asset_id := p_inst_asset_id;
424 l_inst_asset_rec.update_status := 'RETIRED';
425 l_inst_asset_rec.active_end_date := sysdate;
426 l_inst_asset_rec.check_for_instance_expiry := fnd_api.g_false;
427
428 update_inst_asset(
429 px_inst_asset_rec => l_inst_asset_rec,
430 px_csi_txn_rec => px_csi_txn_rec,
431 x_return_status => l_return_status);
432
433 IF l_return_status <> fnd_api.g_ret_sts_success THEN
434 RAISE fnd_api.g_exc_error;
435 END IF;
436
437 EXCEPTION
438 WHEN fnd_api.g_exc_error THEN
439 x_return_status := fnd_api.g_ret_sts_error;
440 END retire_inst_asset;
441
442 PROCEDURE split_inst_asset(
443 p_inst_asset_rec IN csi_datastructures_pub.instance_asset_header_rec,
444 p_quantity IN number,
445 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
446 x_new_inst_asset_id OUT nocopy number,
447 x_return_status OUT nocopy varchar2)
448 IS
449 l_old_asset_qty number;
450 l_inst_asset_rec csi_datastructures_pub.instance_asset_rec;
451 l_lookup_tbl csi_asset_pvt.lookup_tbl;
452 l_asset_count_rec csi_asset_pvt.asset_count_rec;
453 l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
454 l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl;
455
456 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
457 l_msg_count number;
458 l_msg_data varchar2(2000);
459 BEGIN
460
461 x_return_status := fnd_api.g_ret_sts_success;
462
463 l_old_asset_qty := p_inst_asset_rec.asset_quantity - p_quantity;
464
465 l_inst_asset_rec.instance_asset_id := p_inst_asset_rec.instance_asset_id;
466 l_inst_asset_rec.asset_quantity := l_old_asset_qty;
467
468 update_inst_asset(
469 px_inst_asset_rec => l_inst_asset_rec,
470 px_csi_txn_rec => px_csi_txn_rec,
471 x_return_status => l_return_status);
472
473 IF l_return_status <> fnd_api.g_ret_sts_success THEN
474 RAISE fnd_api.g_exc_error;
475 END IF;
476
477 l_inst_asset_rec := null;
478 l_inst_asset_rec.instance_asset_id := fnd_api.g_miss_num;
479 l_inst_asset_rec.instance_id := p_inst_asset_rec.instance_id;
480 l_inst_asset_rec.fa_asset_id := p_inst_asset_rec.fa_asset_id;
481 l_inst_asset_rec.fa_book_type_code := p_inst_asset_rec.fa_book_type_code;
482 l_inst_asset_rec.fa_location_id := p_inst_asset_rec.fa_location_id;
483 l_inst_asset_rec.asset_quantity := p_quantity;
484 l_inst_asset_rec.update_status := 'IN_SERVICE';
485 l_inst_asset_rec.fa_sync_flag := 'Y';
486
487 create_inst_asset(
488 px_inst_asset_rec => l_inst_asset_rec,
489 px_csi_txn_rec => px_csi_txn_rec,
490 x_return_status => l_return_status);
491
492 IF l_return_status <> fnd_api.g_ret_sts_success THEN
493 RAISE fnd_api.g_exc_error;
494 END IF;
495
496 EXCEPTION
497 WHEN fnd_api.g_exc_error THEN
498 x_return_status := fnd_api.g_ret_sts_error;
499 END split_inst_asset;
500
501 PROCEDURE asset_retirement(
502 p_instance_id IN NUMBER,
503 p_book_type_code IN VARCHAR2,
504 p_asset_id IN NUMBER,
505 p_units IN NUMBER,
506 p_trans_date IN DATE,
507 p_trans_by IN NUMBER,
508 px_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
509 x_return_status OUT NOCOPY VARCHAR2,
510 x_error_message OUT NOCOPY VARCHAR2)
511 IS
512
513 l_inst_asset_qry_rec csi_datastructures_pub.instance_asset_query_rec;
514 l_inst_asset_tbl csi_datastructures_pub.instance_asset_header_tbl;
515 l_time_stamp date := null;
516
517 l_new_inst_asset_id number;
518 l_total_inst_asset_qty number := 0;
519
520 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
521 l_msg_count number;
522 l_msg_data varchar2(2000);
523
524 BEGIN
525
526 x_return_status := fnd_api.g_ret_sts_success;
527
528 debug('inside api cse_fa_txn_pkg.asset_retirement');
529
530 l_inst_asset_qry_rec.instance_id := p_instance_id;
531 l_inst_asset_qry_rec.fa_asset_id := p_asset_id;
532 l_inst_asset_qry_rec.fa_book_type_code := p_book_type_code;
533 l_inst_asset_qry_rec.update_status := 'IN_SERVICE';
534
535 csi_asset_pvt.get_instance_assets(
536 p_api_version => 1.0,
537 p_commit => fnd_api.g_false,
538 p_init_msg_list => fnd_api.g_true,
539 p_validation_level => fnd_api.g_valid_level_full,
540 p_instance_asset_query_rec => l_inst_asset_qry_rec,
541 p_resolve_id_columns => fnd_api.g_false,
542 p_time_stamp => l_time_stamp,
543 x_instance_asset_tbl => l_inst_asset_tbl,
544 x_return_status => l_return_status,
545 x_msg_count => l_msg_count,
546 x_msg_data => l_msg_data);
547
548 IF l_return_status <> fnd_api.g_ret_sts_success THEN
549 RAISE fnd_api.g_exc_error;
550 END IF;
551
552 IF l_inst_asset_tbl.COUNT > 0 THEN
553
554 IF nvl(px_txn_rec.transaction_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
555 px_txn_rec.transaction_date := sysdate;
556 --px_txn_rec.source_transaction_date := sysdate; -- Commented for bug 16054641(16048495)
557 px_txn_rec.transaction_type_id := 104;
558 px_txn_rec.source_line_ref := 'ASSET_ID';
559 px_txn_rec.source_line_ref_id := p_asset_id;
560 px_txn_rec.source_group_ref_id := fnd_global.conc_request_id;
561 px_txn_rec.transaction_status_code := cse_datastructures_pub.g_complete;
562 px_txn_rec.transaction_quantity := p_units;
563 END IF;
564
565 IF l_inst_asset_tbl.COUNT = 1 THEN
566 IF l_inst_asset_tbl(1).asset_quantity > p_units THEN
567
568 split_inst_asset(
569 p_inst_asset_rec => l_inst_asset_tbl(1),
570 p_quantity => p_units,
571 px_csi_txn_rec => px_txn_rec,
572 x_new_inst_asset_id => l_new_inst_asset_id,
573 x_return_status => l_return_status);
574
575 IF l_return_status <> fnd_api.g_ret_sts_success THEN
576 RAISE fnd_api.g_exc_error;
577 END IF;
578
579 retire_inst_asset(
580 p_inst_asset_id => l_new_inst_asset_id,
581 px_csi_txn_rec => px_txn_rec,
582 x_return_status => l_return_status);
583
584 IF l_return_status <> fnd_api.g_ret_sts_success THEN
585 RAISE fnd_api.g_exc_error;
586 END IF;
587
588 ELSE
589
590 retire_inst_asset(
591 p_inst_asset_id => l_inst_asset_tbl(1).instance_asset_id,
592 px_csi_txn_rec => px_txn_rec,
593 x_return_status => l_return_status);
594
595 IF l_return_status <> fnd_api.g_ret_sts_success THEN
596 RAISE fnd_api.g_exc_error;
597 END IF;
598
599 END IF;
600
601 ELSE -- quantity > 1
602 -- try and see if the retirement units match with the total inst asset quantity
603 l_total_inst_asset_qty := total_inst_asset_qty(l_inst_asset_tbl);
604
605 IF l_total_inst_asset_qty <= p_units THEN
606 FOR l_ind IN l_inst_asset_tbl.FIRST .. l_inst_asset_tbl.LAST
607 LOOP
608
609 retire_inst_asset(
610 p_inst_asset_id => l_inst_asset_tbl(l_ind).instance_asset_id,
611 px_csi_txn_rec => px_txn_rec,
612 x_return_status => l_return_status);
613
614 IF l_return_status <> fnd_api.g_ret_sts_success THEN
615 RAISE fnd_api.g_exc_error;
616 END IF;
617
618 END LOOP;
619
620 ELSE
621 null;
622 -- could not figure out which one to retire.
623 END IF;
624
625 END IF;
626 END IF;
627
628 EXCEPTION
629 WHEN fnd_api.g_exc_error THEN
630 x_return_status := fnd_api.g_ret_sts_error;
631 END asset_retirement;
632
633 PROCEDURE asset_reinstatement(
634 p_retirement_id IN NUMBER,
635 p_book_type_code IN VARCHAR2,
636 p_asset_id IN NUMBER,
637 p_units IN NUMBER,
638 p_trans_date IN DATE,
639 p_trans_by IN NUMBER,
640 x_return_status OUT NOCOPY VARCHAR2,
641 x_error_message OUT NOCOPY VARCHAR2)
642 IS
643
644 l_inst_asset_qry_rec csi_datastructures_pub.instance_asset_query_rec;
645 l_inst_asset_tbl csi_datastructures_pub.instance_asset_header_tbl;
646 l_csi_txn_rec csi_datastructures_pub.transaction_rec;
647 l_time_stamp date := null;
648 l_new_inst_asset_id number;
649
650 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
651 l_msg_count number;
652 l_msg_data varchar2(2000);
653
654 CURSOR dist_cur(p_retirement_id IN number) IS
655 SELECT distribution_id,
656 units_assigned,
657 transaction_units,
658 location_id,
659 assigned_to
660 FROM fa_distribution_history
661 WHERE retirement_id = p_retirement_id;
662
663 BEGIN
664 x_return_status := fnd_api.g_ret_sts_success;
665 debug('inside api cse_asset_txn_pkg.asset_reinstatement');
666 FOR dist_rec IN dist_cur(p_retirement_id)
667 LOOP
668
669 l_inst_asset_qry_rec.fa_asset_id := p_asset_id;
670 l_inst_asset_qry_rec.fa_book_type_code := p_book_type_code;
671 l_inst_asset_qry_rec.update_status := 'RETIRED';
672 l_inst_asset_qry_rec.fa_location_id := dist_rec.location_id;
673
674 csi_asset_pvt.get_instance_assets(
675 p_api_version => 1.0,
676 p_commit => fnd_api.g_false,
677 p_init_msg_list => fnd_api.g_true,
678 p_validation_level => fnd_api.g_valid_level_full,
679 p_instance_asset_query_rec => l_inst_asset_qry_rec,
680 p_resolve_id_columns => fnd_api.g_false,
681 p_time_stamp => l_time_stamp,
682 x_instance_asset_tbl => l_inst_asset_tbl,
683 x_return_status => l_return_status,
684 x_msg_count => l_msg_count,
685 x_msg_data => l_msg_data);
686
687 IF l_return_status <> fnd_api.g_ret_sts_success THEN
688 RAISE fnd_api.g_exc_error;
689 END IF;
690 debug('l_inst_asset_tbl.COUNT : '||l_inst_asset_tbl.COUNT);
691 IF l_inst_asset_tbl.COUNT > 0 THEN
692
693 l_csi_txn_rec.transaction_id := fnd_api.g_miss_num;
694 l_csi_txn_rec.transaction_date := sysdate;
695 l_csi_txn_rec.source_transaction_date := sysdate;
696 l_csi_txn_rec.transaction_type_id := 103;
697 l_csi_txn_rec.source_line_ref := 'ASSET_ID';
698 l_csi_txn_rec.source_line_ref_id := p_asset_id;
699 l_csi_txn_rec.transaction_status_code := cse_datastructures_pub.g_complete;
700 l_csi_txn_rec.transaction_quantity := p_units;
701
702 IF l_inst_asset_tbl.COUNT = 1 THEN
703
704 reinstate_inst_asset(
705 p_inst_asset_rec => l_inst_asset_tbl(1),
706 p_units => p_units,
707 px_csi_txn_rec => l_csi_txn_rec,
708 x_return_status => l_return_status);
709
710 IF l_return_status <> fnd_api.g_ret_sts_success THEN
711 RAISE fnd_api.g_exc_error;
712 END IF;
713
714 ELSE
715
716 -- just reinstate one of the retired instance asset
717 reinstate_inst_asset(
718 p_inst_asset_rec => l_inst_asset_tbl(1),
719 p_units => p_units,
720 px_csi_txn_rec => l_csi_txn_rec,
721 x_return_status => l_return_status);
722
723 IF l_return_status <> fnd_api.g_ret_sts_success THEN
724 RAISE fnd_api.g_exc_error;
725 END IF;
726
727 END IF;
728 END IF;
729
730 END LOOP;
731
732 EXCEPTION
733 WHEN fnd_api.g_exc_error THEN
734 x_return_status := fnd_api.g_ret_sts_error;
735 END asset_reinstatement;
736
737
738 PROCEDURE populate_retirement_interface(
739 p_csi_txn_id IN number,
740 p_asset_id IN number,
741 p_book_type_code IN varchar2,
742 p_fa_location_id IN number,
743 p_proceeds_of_sale IN number,
744 p_cost_of_removal IN number,
745 p_retirement_units IN number,
746 p_retirement_date IN date,
747 x_return_status OUT nocopy varchar2)
748 IS
749 l_ext_ret_rec fa_mass_ext_retirements%ROWTYPE;
750 l_batch_name varchar2(30);
751 l_mass_ext_retire_id number;
752 l_prorate_convention varchar2(20);
753
754 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
755 l_error_message varchar2(2000);
756
757 CURSOR prorate_conv_cur(p_asset_id number,p_book_type_code varchar2) IS
758 SELECT fcbd.retirement_prorate_convention
759 FROM fa_category_book_defaults fcbd,
760 fa_books fb,
761 fa_additions_b fab
762 WHERE fab.asset_id = p_asset_id
763 AND fb.asset_id = fab.asset_id
764 and fb.book_type_code = p_book_type_code
765 AND fb.date_ineffective is null
766 AND fcbd.book_type_code = fb.book_type_code
767 AND fcbd.category_id = fab.asset_category_id;
768
769 CURSOR fa_dist_cur(p_asset_id number,p_book_type_code varchar2, p_fa_location_id number) IS
770 SELECT distribution_id,
771 assigned_to,
772 units_assigned
773 FROM fa_distribution_history
774 WHERE asset_id = p_asset_id
775 AND book_type_code = p_book_type_code
776 AND location_id = p_fa_location_id
777 AND date_ineffective is null;
778
779 l_units_retired number;
780
781 BEGIN
782
783 x_return_status := fnd_api.g_ret_sts_success;
784
785 debug('inside api cse_asset_txn_pkg.populate_retirement_interface');
786
787 l_batch_name := 'CSE-'||p_csi_txn_id;
788
789 debug(' batch_name : '||l_batch_name);
790
791 FOR prorate_conv_rec IN prorate_conv_cur(p_asset_id, p_book_type_code)
792 LOOP
793 l_prorate_convention := prorate_conv_rec.retirement_prorate_convention;
794 END LOOP;
795
796 debug(' prorate_convention : '||l_prorate_convention);
797
798 l_units_retired := p_retirement_units;
799
800 FOR fa_dist_rec IN fa_dist_cur(p_asset_id, p_book_type_code, p_fa_location_id)
801 LOOP
802
803 l_units_retired := l_units_retired - fa_dist_rec.units_assigned;
804
805 SELECT fa_mass_ext_retirements_s.nextval
806 INTO l_mass_ext_retire_id
807 FROM sys.dual ;
808
809 debug(' fa_distribution_id : '||fa_dist_rec.distribution_id);
810 debug(' mass_ext_retire_id : '||l_mass_ext_retire_id);
811
812 l_ext_ret_rec.mass_external_retire_id := l_mass_ext_retire_id;
813 l_ext_ret_rec.retirement_prorate_convention := l_prorate_convention;
814 l_ext_ret_rec.batch_name := l_batch_name;
815 l_ext_ret_rec.book_type_code := p_book_type_code;
816 l_ext_ret_rec.review_status := 'POST';
817 l_ext_ret_rec.retirement_type_code := 'EXTRAORDINARY';
818 l_ext_ret_rec.asset_id := p_asset_id;
819 l_ext_ret_rec.date_retired := p_retirement_date;
820 l_ext_ret_rec.date_effective := p_retirement_date;
821 l_ext_ret_rec.units := fa_dist_rec.units_assigned;
822 l_ext_ret_rec.cost_of_removal := p_cost_of_removal;
823 l_ext_ret_rec.proceeds_of_sale := p_proceeds_of_sale;
824 l_ext_ret_rec.calc_gain_loss_flag := 'N';
825 l_ext_ret_rec.created_by := fnd_global.user_id;
826 l_ext_ret_rec.creation_date := sysdate;
827 l_ext_ret_rec.last_updated_by := fnd_global.user_id;
828 l_ext_ret_rec.last_update_date := sysdate;
829 l_ext_ret_rec.last_update_login := fnd_global.login_id;
830 l_ext_ret_rec.distribution_id := fa_dist_rec.distribution_id ;
831
832 cse_asset_adjust_pkg.insert_retirement(
833 p_ext_ret_rec => l_ext_ret_rec,
834 x_return_status => l_return_status,
835 x_error_msg => l_error_message);
836
837 IF l_return_status <> fnd_api.g_ret_sts_success THEN
838 RAISE fnd_api.g_exc_error;
839 END IF;
840
841 EXIT when l_units_retired <= 0;
842
843 END LOOP;
844
845 EXCEPTION
846 WHEN fnd_api.g_exc_error THEN
847 x_return_status := fnd_api.g_ret_sts_error;
848 END populate_retirement_interface;
849
850
851 END cse_fa_txn_pkg;