[Home] [Help]
PACKAGE BODY: APPS.FA_CUA_HR_RETIREMENTS_PKG
Source
1 PACKAGE BODY FA_CUA_HR_RETIREMENTS_PKG AS
2 /* $Header: FACHRMRMB.pls 120.7 2009/08/20 14:18:31 bridgway ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6 -- ----------------------------------------------------------------
7 -- This function will return TRUE if the asset belongs to any batch
8 -- in mass_update_batches or retirement_batches with a pending
9 -- or rejected status. Else it returns FALSE
10 -- calling_function: ADDITION to be called from mass_additions
11 -- x_node_id = Parent_node_id
12 -- x_asset_id = asset_id
13 -- x_attribute = NULL
14 -- TRANSACTION to be called from trigger
15 -- APPS.IFA_TRANSACTION_HEADERS_HR_BRI
16 -- x_book_type_code = NEW.book_type_code
17 -- x_asset_id = NEW.asset_id
18 -- x_attribute= NULL
19 -- HIERARCHY to be called from any hierarchy process
20 -- x_asset_id = asset_id
21 -- x_attribute = attribute_name
22 -- DEPRECIAITION to be called from tirgger
23 -- IFA_BOOK_CONTROLS_BRU
24 -- book_type_code is passed
25 -- rest are null
26 -- CONCURRENT to be called when a conc. request
27 -- to create batch_transactions.
28 -- This function is called from forms with
29 -- x_conc_request_id as null OR within conc_request
30 -- with the x_conc_request_id as it request_id
31 -- -----------------------------------------------------------------
32
33 FUNCTION check_pending_batch( x_calling_function IN VARCHAR2,
34 x_book_type_code IN VARCHAR2,
35 x_event_code IN VARCHAR2 DEFAULT NULL,
36 x_asset_id IN NUMBER DEFAULT NULL,
37 x_node_id IN NUMBER DEFAULT NULL,
38 x_category_id IN NUMBER DEFAULT NULL,
39 x_attribute IN VARCHAR2 DEFAULT NULL,
40 x_conc_request_id IN NUMBER DEFAULT NULL,
41 x_status IN OUT NOCOPY VARCHAR2
42 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
43
44 v_dummy NUMBER:=0;
45 v_node_id NUMBER;
46 pending_batch varchar2(17):= 'CUA_PENDING_BATCH';
47 pending_book varchar2(16):= 'CUA_PENDING_BOOK';
48 -- check for following batch status
49 -- 'P' - Pending
50 -- 'E' - Rejected after submitted as conc_req - Create Batch Txns
51 -- 'R' - Rejected after submitted as conc_proc - Mass Update Batches
52 -- 'N' - New when the batch needs to be created - initial status of the batch
53 -- 'IP' - In Process when a batch is in process
54
55 CURSOR C_check_batch_for_all IS
56 select 1
57 from dual
58 where exists ( select 'x'
59 from fa_mass_update_batch_headers
60 where status_code IN ('P', 'E', 'R', 'N', 'IP')
61 and book_type_code = x_book_type_code
62 and ( x_conc_request_id is null OR
63 nvl(concurrent_request_id,0) <> x_conc_request_id ) );
64
65
66 CURSOR C_check_batch_headers IS
67 select 1
68 from dual
69 where exists ( select 'x'
70 from fa_mass_update_batch_headers hdr
71 where hdr.status_code IN ('P', 'E', 'R', 'N', 'IP')
72 and hdr.book_type_code = x_book_type_code
73 and ( x_conc_request_id is null OR
74 nvl(hdr.concurrent_request_id,0) <> x_conc_request_id )
75 and ( hdr.event_code IN ( 'CHANGE_NODE_PARENT', 'CHANGE_NODE_ATTRIBUTE',
76 'CHANGE_NODE_RULE_SET', 'CHANGE_CATEGORY_RULE_SET',
77 'HR_MASS_TRANSFER')
78 OR ( hdr.event_code in ( 'CHANGE_CATEGORY_LIFE', 'CHANGE_CATEGORY_LIFE_END_DATE')
79 and ( x_event_code IN ('CHANGE_CATEGORY_LIFE', 'CHANGE_CATEGORY_LIFE_END_DATE',
80 'CHANGE_ASSET_CATEGORY' ) and
81 to_number(hdr.source_entity_key_value) = x_category_id )
82 or (x_event_code IN ( 'CHANGE_NODE_PARENT', 'CHANGE_NODE_ATTRIBUTE',
83 'CHANGE_NODE_RULE_SET', 'CHANGE_CATEGORY_RULE_SET',
84 'CHANGE_ASSET_PARENT', 'HR_MASS_TRANSFER' ) )
85 )
86
87 OR ( hdr.event_code IN ( 'CHANGE_ASSET_PARENT','CHANGE_ASSET_LEASE','CHANGE_ASSET_CATEGORY')
88 and (( x_event_code IN ( 'CHANGE_ASSET_PARENT', 'CHANGE_ASSET_LEASE',
89 'CHANGE_ASSET_CATEGORY') and
90 to_number(hdr.source_entity_key_value) = x_asset_id )
91 OR x_event_code IN ( 'CHANGE_NODE_PARENT', 'CHANGE_NODE_ATTRIBUTE',
92 'CHANGE_NODE_RULE_SET', 'CHANGE_CATEGORY_RULE_SET',
93 'CHANGE_CATEGORY_LIFE', 'CHANGE_CATEGORY_LIFE_END_DATE',
94 'HR_MASS_TRANSFER' )
95 ) )
96 )
97 );
98
99 CURSOR C_check_batch_for_addition IS
100 select 1
101 from dual
102 where exists ( select 'x'
103 from fa_mass_update_batch_headers a
104 where a.status_code IN ('P', 'E', 'R', 'N', 'IP')
105 and a.book_type_code = x_book_type_code
106 and a.event_code IN ( 'CHANGE_NODE_PARENT', 'CHANGE_NODE_ATTRIBUTE',
107 'CHANGE_NODE_RULE_SET', 'CHANGE_CATEGORY_RULE_SET',
108 'HR_MASS_TRANSFER', 'CHANGE_CATEGORY_LIFE',
109 'CHANGE_CATEGORY_LIFE_END_DATE') );
110
111 CURSOR C_check_batch_for_ata IS
112 select 1
113 from dual
114 where exists ( select 'x'
115 from fa_mass_update_batch_headers a
116 where a.status_code IN ('P', 'E', 'R', 'N', 'IP')
117 and a.book_type_code = x_book_type_code
118 and ( a.event_code IN ( 'CHANGE_NODE_PARENT', 'CHANGE_NODE_ATTRIBUTE',
119 'CHANGE_NODE_RULE_SET', 'CHANGE_CATEGORY_RULE_SET',
120 'HR_MASS_TRANSFER') or
121 (a.event_code IN ( 'CHANGE_CATEGORY_LIFE', 'CHANGE_CATEGORY_LIFE_END_DATE') and
122 to_number(a.source_entity_key_value) = x_category_id ) or
123 (a.event_code IN ( 'CHANGE_ASSET_PARENT','CHANGE_ASSET_LEASE',
124 'CHANGE_ASSET_CATEGORY') and
125 to_number(a.source_entity_key_value) = x_asset_id )
126 ) );
127
128 CURSOR C_check_batch_for_transfers IS
129 select 1
130 from dual
131 where exists ( select 'x'
132 from fa_mass_update_batch_headers a
133 where a.status_code IN ('P', 'E', 'R', 'N', 'IP')
134 and a.book_type_code = x_book_type_code
135 and ( a.event_code IN ( 'CHANGE_NODE_PARENT', 'CHANGE_NODE_ATTRIBUTE',
136 'CHANGE_NODE_RULE_SET', 'CHANGE_CATEGORY_RULE_SET',
137 'HR_MASS_TRANSFER', 'CHANGE_CATEGORY_LIFE',
138 'CHANGE_CATEGORY_LIFE_END_DATE') or
139 ( a.event_code IN ( 'CHANGE_ASSET_PARENT','CHANGE_ASSET_LEASE',
140 'CHANGE_ASSET_CATEGORY') and
141 to_number(a.source_entity_key_value) = x_asset_id )
142 ) );
143
144 /*
145 CURSOR C_check_batch IS
146 select 1
147 from dual
148 where exists ( select 'x'
149 from fa_mass_update_batch_headers a
150 where a.status_code IN ('P', 'R')
151 AND EXISTS ( select 'x'
152 from fa_mass_update_batch_details b
153 where a.batch_id = b.batch_id )
154 AND ( ( (source_entity_key_value = x_node_id AND
155 event_code = 'CHANGE_NODE_ATTRIBUTE')
156 OR (source_attribute_old_id = x_node_id AND
157 event_code = 'CHANGE_NODE_PARENT')
158 )
159 OR ( event_code IN ( 'CHANGE_CATEGORY_LIFE', 'CHANGE_CATEGORY_LIFE_END_DATE') AND
160 to_number(a.source_entity_key_value) = (select asset_category_id
161 from fa_additions
162 where asset_id = x_asset_id )
163 )
164 )
165 );
166 **/
167
168 CURSOR C_check_hr_retirement IS
169 select 1
170 from dual
171 where exists ( select 'X'
172 from fa_hr_retirement_details
173 -- where status_code = 'P' -- msiddiqu 15-feb-2001
174 where status_code IN ('P', 'IP')
175 and asset_id = nvl(x_asset_id, asset_id)
176 and book_type_code = x_book_type_code
177 and ( x_conc_request_id is null OR
178 nvl(concurrent_request_id,0) <> x_conc_request_id ) );
179
180 -- TRUE if txn allowed - FALSE otherwise
181 FUNCTION check_book_stats ( x_book in varchar2 ,
182 x_status in out nocopy varchar2 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
183
184 l_txn_status boolean:= FALSE;
185 l_api_version number := 1;
186 l_init_msg_list varchar2(1) := FND_API.G_FALSE;
187 l_commit varchar2(1) := FND_API.G_FALSE;
188 l_validation_level number := FND_API.G_VALID_LEVEL_FULL;
189 l_return_status varchar2(1) := FND_API.G_FALSE;
190 l_msg_count number := 0;
191 l_msg_data varchar2(512);
192 l_trans_rec FA_API_TYPES.trans_rec_type;
193 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
194 l_asset_retire_rec FA_API_TYPES.asset_retire_rec_type;
195 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
196 l_subcomp_tbl FA_API_TYPES.subcomp_tbl_type;
197 l_inv_tbl FA_API_TYPES.inv_tbl_type;
198 BEGIN
199
200 -- bugfix2250373
201 if NOT FA_CHK_BOOKSTS_PKG.faxcbs(
202 X_book => X_book,
203 X_submit => TRUE,
204 X_start => FALSE,
205 X_asset_id => 0,
206 X_trx_type => 'TRANSFER',
207 X_txn_status => l_txn_status,
208 X_close_period => 0 , p_log_level_rec => p_log_level_rec) then
209 x_status := 'CUA_BOOK_IN_USE';
210 return FALSE;
211 end if;
212
213
214 /*
215 -- commented for bug2250373
216 if ( NOT FA_CHK_BOOKSTS_PKG.faxcbsx( x_book , p_log_level_rec => p_log_level_rec)) then
217 x_status := 'CUA_BOOK_IN_USE';
218 return FALSE;
219 end if;
220 */
221 return TRUE;
222
223 END check_book_stats;
224
225 BEGIN
226 v_dummy:= 0;
227 x_status := null;
228
229 IF x_calling_function = 'MASS_ADDITION' then
230
231 if x_event_code = 'ADDITION' then
232 OPEN c_check_batch_for_addition ;
233 FETCH c_check_batch_for_addition INTO v_dummy;
234 CLOSE c_check_batch_for_addition;
235
236 if(v_dummy = 1) then
237 x_status := pending_batch;
238 return TRUE;
239 end if;
240
241 elsif x_event_code = 'ADD_TO_ASSET' then
242
243 OPEN c_check_batch_for_ata;
244 FETCH c_check_batch_for_ata INTO v_dummy;
245 CLOSE c_check_batch_for_ata;
246
247 if(v_dummy = 1) then
248 x_status := pending_batch;
249 return TRUE;
250 end if;
251 end if;
252 ELSIF x_calling_function = 'CUA_EXT_TRANSFER' then
253
254 if ( NOT check_book_stats ( x_book_type_code
255 , x_status
256 , p_log_level_rec )) then
257 return TRUE;
258 end if;
259
260 open c_check_batch_for_transfers;
261 fetch c_check_batch_for_transfers into v_dummy;
262 close c_check_batch_for_transfers;
263 if(v_dummy = 1) then
264 x_status := pending_batch;
265 return TRUE;
266 end if;
267
268 ELSIF x_calling_function IN ( 'CUA_HR_RETIREMENTS') then
269
270 if ( NOT check_book_stats ( x_book_type_code
271 , x_status
272 , p_log_level_rec)) then
273 return TRUE;
274 end if;
275
276 open c_check_batch_for_all;
277 fetch c_check_batch_for_all into v_dummy;
278 close c_check_batch_for_all;
279 if(v_dummy = 1) then
280 x_status := pending_batch;
281 return TRUE;
282 end if;
283 ELSIF x_calling_function IN ('CUA_EXT_RETIREMENTS', 'MASS_RETIREMENT') then
284
285 open c_check_batch_for_all;
286 fetch c_check_batch_for_all into v_dummy;
287 close c_check_batch_for_all;
288 if(v_dummy = 1) then
289 x_status := pending_batch;
290 return TRUE;
291 end if;
292 ELSIF x_calling_function In ('HIERARCHY', 'TRANSACTION', 'DEPRECIATION' ) then
293
294 -- added headers table to join in the select so that the discarded batch headers
295 -- are excluded -- msiddiqu bugfix 1659510
296 v_dummy := 0;
297
298 IF (x_attribute is null) THEN
299
300 select 1
301 into v_dummy
302 from dual
303 where exists
304 (
305 select 'X'
306 from fa_mass_update_batch_headers a,
307 fa_mass_update_batch_details b
308 where a.status_code <> 'C'
309 and a.event_code <> 'HR_REINSTATEMENT' -- bugfix for 891822 msiddiqu 25-APR-2001
310 and a.batch_id = b.batch_id
311 and b.status_code in ('P','R') -- uncommented for bugfix 1613882
312 -- where x_attribute IS NULL
313 -- where status_code = 'P' -- commented for bugfix 1613882
314 and b.asset_id = nvl(x_asset_id, b.asset_id)
315 and b.book_type_code = x_book_type_code
316 );
317 ELSIF (x_attribute = 'ASSET_KEY') THEN
318
319 select 1
320 into v_dummy
321 from dual
322 where exists
323 (
324 select 'X'
325 from fa_mass_update_batch_headers a,
326 fa_mass_update_batch_details b
327 -- where x_attribute IS NOT NULL
328 -- where x_attribute = 'ASSET_KEY'
329 where a.status_code <> 'C'
330 and a.batch_id = b.batch_id
331 and b.attribute_name IN ('ASSET_KEY', 'CATEGORY')
332 and b.status_code in ( 'P', 'R') -- bugfix 1613882
333 -- and status_code = 'P'
334 and b.asset_id = nvl(x_asset_id, b.asset_id)
335 and b.book_type_code = x_book_type_code
336 );
337
338 ELSIF (x_attribute = 'DISTRIBUTION') THEN
339
340 select 1
341 into v_dummy
342 from dual
343 where exists
344 (
345 select 'X'
346 from fa_mass_update_batch_headers a,
347 fa_mass_update_batch_details b
348 -- where x_attribute IS NOT NULL
349 -- where x_attribute = 'DISTRIBUTION'
350 where a.status_code <> 'C'
351 and a.batch_id = b.batch_id
352 and b.attribute_name IN ('DISTRIBUTION', 'CATEGORY')
353 and b.asset_id = nvl(x_asset_id, b.asset_id)
354 and b.book_type_code = x_book_type_code
355 );
356
357 ELSIF (x_attribute = 'LEASE_NUMBER') THEN
358
359 select 1
360 into v_dummy
361 from dual
362 where exists
363 (
364 select 'X'
365 from fa_mass_update_batch_headers a,
366 fa_mass_update_batch_details b
367 -- where x_attribute IS NOT NULL
368 -- where x_attribute = 'LEASE_NUMBER'
369 where a.status_code <> 'C'
370 and a.batch_id = b.batch_id
371 and b.attribute_name IN ('LEASE_NUMBER', 'CATEGORY')
372 -- and status_code = 'P' -- bugfix 1613882
373 and b.status_code in ( 'P', 'R')
374 and b.asset_id = nvl(x_asset_id, b.asset_id)
375 and b.book_type_code = x_book_type_code
376 );
377 ELSIF (x_attribute = 'LIFE_END_DATE') THEN
378
379 select 1
380 into v_dummy
381 from dual
382 where exists
383 (
384 select 'X'
385 from fa_mass_update_batch_headers a,
386 fa_mass_update_batch_details b
387 -- where x_attribute IS NOT NULL
388 -- where x_attribute = 'LIFE_END_DATE'
389 where a.status_code <> 'C'
390 and a.batch_id = b.batch_id
391 and b.attribute_name IN ('CATEGORY', 'LEASE_NUMBER', 'LIFE_END_DATE')
392 -- and status_code = 'P' -- bugfix 1613882
393 and b.status_code in ( 'P', 'R')
394 and b.asset_id = nvl(x_asset_id, b.asset_id)
395 and b.book_type_code = x_book_type_code
396 );
397 ELSIF (x_attribute = 'CATEGORY') THEN
398
399 select 1
400 into v_dummy
401 from dual
402 where exists
403 (
404 select 'X'
405 from fa_mass_update_batch_headers a,
406 fa_mass_update_batch_details b
407 -- where x_attribute IS NOT NULL
408 -- where x_attribute = 'CATEGORY'
409 -- if category check for all attributes
410 where -- status_code = 'P' -- bugfix 1613882
411 a.status_code <> 'C'
412 and a.batch_id = b.batch_id
413 and b.status_code in ( 'P', 'R')
414 and b.asset_id = nvl(x_asset_id, b.asset_id)
415 and b.book_type_code = x_book_type_code
416 );
417
418 ELSE
419 v_dummy := 0;
420 END IF;
421
422 if(v_dummy = 1) then
423 x_status := 'CUA_ASSET_IN_USE';
424 return TRUE;
425 end if;
426
427 ELSIF x_calling_function = 'CONCURRENT' then
428
429 OPEN C_check_batch_headers;
430 FETCH c_check_batch_headers INTO v_dummy;
431 CLOSE c_check_batch_headers;
432 if v_dummy = 1 then
433 x_status := pending_batch;
434 return TRUE;
435 end if;
436 END IF; -- x_calling_function
437
438 -- check for hr_retirements
439 if x_calling_function = 'MASS_ADDITION' and x_event_code = 'ADDITION' then
440 -- skip check for retirements for a new addition
441 null;
442 else
443 OPEN c_check_hr_retirement;
444 FETCH c_check_hr_retirement INTO v_dummy;
445 CLOSE c_check_hr_retirement;
446 if(v_dummy = 1) then
447 x_status := pending_book;
448 return TRUE;
449 end if;
450 end if;
451
452 x_status:= null;
453 return FALSE; -- no pending batch
454
455 EXCEPTION
456 WHEN OTHERS THEN
457 return FALSE;
458 END check_pending_batch;
459
460 -- ---------------------------------------------------
461 -- This procedure insert passed in information into
462 -- fa_hr_retirement_headers table
463 -- and returns a batch_id
464 -- ----------------------------------------------------
465 PROCEDURE insert_hr_retirement_hdrs(
466 x_event_code IN VARCHAR2
467 , x_book_type_code IN VARCHAR2
468 , x_status IN VARCHAR2
469 , x_node_entity_id IN NUMBER
470 , x_rejection_reason_code IN VARCHAR2
471 , x_retirement_method IN VARCHAR2
472 , x_retirement_type_code IN VARCHAR2
473 , x_proceeds_of_sale IN NUMBER
474 , x_cost_of_removal IN NUMBER
475 , x_retire_date IN DATE
476 , x_prorate_by IN VARCHAR2
477 , x_retire_by IN VARCHAR2
478 , x_retirement_amount IN NUMBER
479 , x_retirement_percent IN NUMBER
480 , x_allow_partial_retire_flg IN VARCHAR2
481 , x_retire_units_flg IN VARCHAR2
482 , x_created_by IN NUMBER
483 , x_creation_date IN DATE
484 , x_last_updated_by IN NUMBER
485 , x_last_update_date IN DATE
486 , x_last_update_login IN NUMBER
487 , x_concurrent_request_id IN NUMBER
488 , x_batch_id IN OUT NOCOPY NUMBER
489 , x_transaction_name IN VARCHAR2
490 , x_attribute_category IN VARCHAR2
491 , x_attribute1 IN VARCHAR2
492 , x_attribute2 IN VARCHAR2
493 , x_attribute3 IN VARCHAR2
494 , x_attribute4 IN VARCHAR2
495 , x_attribute5 IN VARCHAR2
496 , x_attribute6 IN VARCHAR2
497 , x_attribute7 IN VARCHAR2
498 , x_attribute8 IN VARCHAR2
499 , x_attribute9 IN VARCHAR2
500 , x_attribute10 IN VARCHAR2
501 , x_attribute11 IN VARCHAR2
502 , x_attribute12 IN VARCHAR2
503 , x_attribute13 IN VARCHAR2
504 , x_attribute14 IN VARCHAR2
505 , x_attribute15 IN VARCHAR2
506 , TH_attribute_category IN VARCHAR2
507 , TH_attribute1 IN VARCHAR2
508 , TH_attribute2 IN VARCHAR2
509 , TH_attribute3 IN VARCHAR2
510 , TH_attribute4 IN VARCHAR2
511 , TH_attribute5 IN VARCHAR2
512 , TH_attribute6 IN VARCHAR2
513 , TH_attribute7 IN VARCHAR2
514 , TH_attribute8 IN VARCHAR2
515 , TH_attribute9 IN VARCHAR2
516 , TH_attribute10 IN VARCHAR2
517 , TH_attribute11 IN VARCHAR2
518 , TH_attribute12 IN VARCHAR2
519 , TH_attribute13 IN VARCHAR2
520 , TH_attribute14 IN VARCHAR2
521 , TH_attribute15 IN VARCHAR2
522 , x_err_code IN OUT NOCOPY VARCHAR2
523 , x_err_stage IN OUT NOCOPY VARCHAR2
524 , x_err_stack IN OUT NOCOPY VARCHAR2 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
525
526 CURSOR C1 IS
527 select fa_hr_retirement_hdrs_s.nextval
528 from dual;
529 v_old_err_stack VARCHAR2(640);
530 BEGIN
531 x_err_code:= '0';
532 v_old_err_stack := x_err_stack;
533 x_err_stack:= x_err_stack||'-> Insert_hr_retirement_hdrs';
534
535 x_err_stage:= 'Cursor C1';
536 Open C1;
537 fetch C1 into x_batch_id;
538 close C1;
539
540 x_err_stage:= 'Inserting retirement_headers';
541 Insert into fa_hr_retirement_headers(
542 event_code
543 , book_type_code
544 , status_code
545 , asset_hierarchy_id
546 , rejection_reason_code
547 , retirement_method
548 , retirement_type_code
549 , retire_date
550 , prorate_by
551 , retire_by
552 , retirement_amount
553 , retirement_percent
554 , allow_partial_retire_flag
555 , retire_units_flag
556 , created_by
557 , creation_date
558 , last_updated_by
559 , last_update_date
560 , last_update_login
561 , concurrent_request_id
562 , batch_id
563 , transaction_name
564 , attribute_category
565 , attribute1
566 , attribute2
567 , attribute3
568 , attribute4
569 , attribute5
570 , attribute6
571 , attribute7
572 , attribute8
573 , attribute9
574 , attribute10
575 , attribute11
576 , attribute12
577 , attribute13
578 , attribute14
579 , attribute15
580 , th_attribute_category
581 , th_attribute1
582 , th_attribute2
583 , th_attribute3
584 , th_attribute4
585 , th_attribute5
586 , th_attribute6
587 , th_attribute7
588 , th_attribute8
589 , th_attribute9
590 , th_attribute10
591 , th_attribute11
592 , th_attribute12
593 , th_attribute13
594 , th_attribute14
595 , th_attribute15
596 , proceeds_of_sale
597 , cost_of_removal
598 )
599 values(
600 x_event_code
601 , x_book_type_code
602 , x_status
603 , x_node_entity_id
604 , x_rejection_reason_code
605 , x_retirement_method
606 , x_retirement_type_code
607 , x_retire_date
608 , x_prorate_by
609 , x_retire_by
610 , x_retirement_amount
611 , x_retirement_percent
612 , x_allow_partial_retire_flg
613 , x_retire_units_flg
614 , x_created_by
615 , x_creation_date
616 , x_last_updated_by
620 , x_batch_id
617 , x_last_update_date
618 , x_last_update_login
619 , x_concurrent_request_id
621 , x_transaction_name
622 , x_attribute_category
623 , x_attribute1
624 , x_attribute2
625 , x_attribute3
626 , x_attribute4
627 , x_attribute5
628 , x_attribute6
629 , x_attribute7
630 , x_attribute8
631 , x_attribute9
632 , x_attribute10
633 , x_attribute11
634 , x_attribute12
635 , x_attribute13
636 , x_attribute14
637 , x_attribute15
638 , TH_attribute_category
639 , TH_attribute1
640 , TH_attribute2
641 , TH_attribute3
642 , TH_attribute4
643 , TH_attribute5
644 , TH_attribute6
645 , TH_attribute7
646 , TH_attribute8
647 , TH_attribute9
648 , TH_attribute10
649 , TH_attribute11
650 , TH_attribute12
651 , TH_attribute13
652 , TH_attribute14
653 , TH_attribute15
654 , x_proceeds_of_sale
655 , x_cost_of_removal );
656
657 x_err_stack:= v_old_err_stack;
658
659 EXCEPTION
660 when others then
661 -- x_err_code:= sqlerrm;
662 x_err_code:= substrb(sqlerrm,1,240);
663 END insert_hr_retirement_hdrs;
664
665 -- --------------------------------------------------
666 -- This function insert retirement details
667 -- into FA_HR_RETIREMENT_DETAILS table
668 -- --------------------------------------------------
669 PROCEDURE insert_hr_retirement_dtls(
670 x_batch_id IN NUMBER
671 , x_book_type_code IN VARCHAR2
672 , x_asset_id IN NUMBER
673 , x_date_placed_in_service IN DATE
674 , x_current_cost IN NUMBER
675 , x_cost_retired IN NUMBER
676 , x_current_units IN NUMBER
677 , x_units_retired IN NUMBER
678 , x_prorate_percent IN NUMBER
679 , x_retirement_convention_code IN VARCHAR2
680 , x_status_code IN VARCHAR2
681 , x_rejection_reason IN VARCHAR2
682 , x_proceeds_of_sale IN NUMBER
683 , x_cost_of_removal IN NUMBER
684 , x_created_by IN NUMBER
685 , x_creation_date IN DATE
686 , x_last_updated_by IN NUMBER
687 , x_last_update_date IN DATE
688 , x_last_update_login IN NUMBER
689 , x_concurrent_request_id IN NUMBER
690 , x_err_code IN OUT NOCOPY VARCHAR2
691 , x_err_stage IN OUT NOCOPY VARCHAR2
692 , x_err_stack IN OUT NOCOPY VARCHAR2 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
693
694 v_old_err_stack VARCHAr2(640);
695 BEGIN
696 x_err_code := '0';
697 v_old_err_stack := x_err_stack;
698 x_err_stack := x_err_stack||'->'||'insert_fa_hr_retirement_dtls';
699
700 insert into fa_hr_retirement_details(
701 batch_id
702 , book_type_code
703 , asset_id
704 , date_placed_in_service
705 , current_cost
706 , cost_retired
707 , current_units
708 , units_retired
709 , prorate_percent
710 , retirement_convention_code
711 , status_code
712 , rejection_reason
713 , proceeds_of_sale
714 , cost_of_removal
715 , created_by
716 , creation_date
717 , last_updated_by
718 , last_update_date
719 , last_update_login
720 , concurrent_request_id )
721 values (
722 x_batch_id
723 , x_book_type_code
724 , x_asset_id
725 , x_date_placed_in_service
726 , x_current_cost
727 , x_cost_retired
728 , x_current_units
729 , x_units_retired
730 , x_prorate_percent
731 , x_retirement_convention_code
732 , x_status_code
733 , x_rejection_reason
734 , x_proceeds_of_sale
735 , x_cost_of_removal
736 , x_created_by
737 , x_creation_date
738 , x_last_updated_by
739 , x_last_update_date
740 , x_last_update_login
741 , x_concurrent_request_id );
742
743 x_err_stack:= v_old_err_stack;
744 EXCEPTION
745 when others then
746 x_err_code:= substrb(sqlerrm,1,240);
747 -- x_err_code := sqlerrm;
748 END insert_hr_retirement_dtls;
749
750
751 -- --------------------------------------------------
752 --
753 -- --------------------------------------------------
754 PROCEDURE generate_retirement_batch(
755 x_event_code IN VARCHAR2
756 , x_book_type_code IN VARCHAR2
757 , x_node_entity_id IN NUMBER
758 , x_retirement_method IN VARCHAR2
759 , x_retirement_type_code IN VARCHAR2
760 , x_proceeds_of_sale IN NUMBER
761 , x_cost_of_removal IN NUMBER
762 , x_retire_date IN DATE
763 , x_prorate_by IN VARCHAR2
764 , x_retire_by IN VARCHAR2
765 , x_retirement_amount IN NUMBER
766 , x_retirement_percent IN NUMBER
767 , x_allow_partial_retire IN VARCHAR2
768 , x_retire_units IN VARCHAR2
769 , x_batch_id IN OUT NOCOPY NUMBER
770 , x_transaction_name IN VARCHAR2 DEFAULT NULL
771 , x_attribute_category IN VARCHAR2 DEFAULT NULL
772 , x_attribute1 IN VARCHAR2 DEFAULT NULL
773 , x_attribute2 IN VARCHAR2 DEFAULT NULL
774 , x_attribute3 IN VARCHAR2 DEFAULT NULL
775 , x_attribute4 IN VARCHAR2 DEFAULT NULL
776 , x_attribute5 IN VARCHAR2 DEFAULT NULL
777 , x_attribute6 IN VARCHAR2 DEFAULT NULL
778 , x_attribute7 IN VARCHAR2 DEFAULT NULL
779 , x_attribute8 IN VARCHAR2 DEFAULT NULL
780 , x_attribute9 IN VARCHAR2 DEFAULT NULL
781 , x_attribute10 IN VARCHAR2 DEFAULT NULL
782 , x_attribute11 IN VARCHAR2 DEFAULT NULL
783 , x_attribute12 IN VARCHAR2 DEFAULT NULL
784 , x_attribute13 IN VARCHAR2 DEFAULT NULL
785 , x_attribute14 IN VARCHAR2 DEFAULT NULL
786 , x_attribute15 IN VARCHAR2 DEFAULT NULL
787 , TH_attribute_category IN VARCHAR2 DEFAULT NULL
788 , TH_attribute1 IN VARCHAR2 DEFAULT NULL
789 , TH_attribute2 IN VARCHAR2 DEFAULT NULL
790 , TH_attribute3 IN VARCHAR2 DEFAULT NULL
791 , TH_attribute4 IN VARCHAR2 DEFAULT NULL
792 , TH_attribute5 IN VARCHAR2 DEFAULT NULL
793 , TH_attribute6 IN VARCHAR2 DEFAULT NULL
794 , TH_attribute7 IN VARCHAR2 DEFAULT NULL
795 , TH_attribute8 IN VARCHAR2 DEFAULT NULL
796 , TH_attribute9 IN VARCHAR2 DEFAULT NULL
797 , TH_attribute10 IN VARCHAR2 DEFAULT NULL
798 , TH_attribute11 IN VARCHAR2 DEFAULT NULL
799 , TH_attribute12 IN VARCHAR2 DEFAULT NULL
800 , TH_attribute13 IN VARCHAR2 DEFAULT NULL
801 , TH_attribute14 IN VARCHAR2 DEFAULT NULL
802 , TH_attribute15 IN VARCHAR2 DEFAULT NULL
803 , x_err_code IN OUT NOCOPY VARCHAR2
804 , x_err_stage IN OUT NOCOPY VARCHAR2
805 , x_err_stack IN OUT NOCOPY VARCHAR2 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
806
807 v_old_err_stack VARCHAR2(630);
808 v_sysdate DATE;
809 v_created_by NUMBER;
810 v_last_update_login NUMBER;
811 v_last_updated_by NUMBER;
812 v_conc_request_id NUMBER:= NULL;
813 v_rejection_reason_code VARCHAR2(30):= NULL;
814 i binary_integer:=0;
815 v_asset_attr_tab FA_CUA_DERIVE_ASSET_ATTR_PKG.asset_tabtype;
816 v_dummy NUMBER;
817 v_stop BOOLEAN:= FALSE;
818 v_cost_retired NUMBER;
819 v_current_units NUMBER;
820 v_units_retired NUMBER;
821 v_current_cost NUMBER;
822 v_cost_remaining NUMBER;
823 v_units_remaining NUMBER;
824 v_cost_per_unit NUMBER;
825 v_total_cost NUMBER:= 0;
826 v_total_units NUMBER:= 0;
827 v_tot_cost_retired NUMBER:=0;
828 v_tot_units_retired NUMBER:= 0;
829 v_prorate_percent NUMBER;
830 v_precision NUMBER;
831 v_asset_id NUMBER;
832 v_last_cost_retired NUMBER;
833 v_retirement_convention_code VARCHAR2(30);
834
835 v_asset_pos NUMBER;
836 v_asset_cor NUMBER;
837
838 TYPE ret_asset_rec_type IS RECORD (
839 asset_id fa_hr_retirement_details.asset_id%TYPE default null,
840 dpis fa_hr_retirement_details.date_placed_in_service%TYPE default null,
841 cost fa_hr_retirement_details.current_cost%TYPE default null,
842 cost_retired fa_hr_retirement_details.cost_retired%TYPE default null,
843 units fa_hr_retirement_details.current_units%TYPE default null,
844 units_retired fa_hr_retirement_details.units_retired%TYPE default null,
845 prorate_percent fa_hr_retirement_details.prorate_percent%TYPE default null,
846 ret_prorate_conv fa_hr_retirement_details.retirement_convention_code%TYPE default null,
847 proceeds_of_sale fa_hr_retirement_details.proceeds_of_sale%TYPE default null,
848 cost_of_removal fa_hr_retirement_details.cost_of_removal%TYPE default null );
849
850
851 TYPE ret_asset_tbl_type IS TABLE OF ret_asset_rec_type index by binary_integer;
852
853
854 ret_tab ret_asset_tbl_type;
855
856 -- cursor to get the total_cost and total_units of the qualified assets
857 -- NOTE: Any changes to this cursor must also be reflected in C_qualified assets
858 -- Both the cursors must be same
859 CURSOR C_get_totals IS
860 select sum(fab.cost) total_cost
861 , sum(fah.units) total_units
862 from ( select asset_id
863 from fa_asset_hierarchy
864 where asset_id IS NOT NULL
865 start with asset_hierarchy_id = x_node_entity_id
866 connect by prior asset_hierarchy_id = parent_hierarchy_id ) hr
867 , fa_asset_history fah
868 , fa_category_book_defaults fcbd
869 , fa_books fab
870 , fa_additions faa
871 where hr.asset_id = faa.asset_id
872 AND faa.asset_id = fab.asset_id
873 AND fab.book_type_code = x_book_type_code
874 -- AND fab.cost > 0
875 AND faa.asset_id = fah.asset_id
876 AND fah.date_ineffective IS NULL
877 AND faa.asset_category_id = fcbd.category_id
878 AND fab.book_type_code = fcbd.book_type_code
879 AND fab.date_placed_in_service
880 BETWEEN fcbd.start_dpis
881 AND nvl(TO_DATE(fcbd.end_dpis, 'DD-MM-YYYY'),
882 TO_DATE('31-12-4712', 'DD-MM-YYYY'))
883 AND EXISTS (SELECT 'X'
884 FROM FA_TRANSACTION_HEADERS fth
885 WHERE fth.asset_id = fab.asset_id
886 AND fth.book_type_code = fab.book_type_code
887 AND (fth.transaction_date_entered <= x_Retire_Date
888 AND fth.transaction_type_code not in ('FULL RETIREMENT',
889 'REINSTATEMENT')))
890 AND EXISTS ( SELECT 'X'
891 FROM fa_distribution_history fad
892 , gl_code_combinations gcc
893 WHERE fad.asset_id = faa.asset_id
894 AND fad.code_combination_id = gcc.code_combination_id
895 AND fad.date_ineffective IS NULL )
896 AND NOT EXISTS ( select 'X' --'PROCESSED RETIREMENT'
897 from fa_retirements frt,
898 fa_books fb
899 where frt.asset_id = fab.asset_id
900 AND frt.asset_id = fb.asset_id
901 AND frt.transaction_header_id_out is NULL
902 AND frt.status = 'PROCESSED'
903 AND frt.book_type_code = fb.book_type_code
904 AND fb.period_counter_fully_retired is NOT NULL
905 AND fb.transaction_header_id_in =
906 frt.transaction_header_id_in
907 AND fb.date_ineffective IS NULL )
908 AND faa.asset_type IN ('CIP', 'CAPITALIZED', 'EXPENSED')
909 AND fab.date_ineffective IS NULL ;
910
911 -- cursor to check that a batch in a pending status does
912 -- not exist with certain attribute changes, for the asset
913 -- to be retired. If so do not allow the asset to retire.
914 -- Also this will list oldest assets first
915 -- based on date_placed_in_service
916 -- NOTE: This cursor should be same as c_get_totals
917 -- Any changes should be reflected in c_get_totals
918 CURSOR C_qualified_assets IS
919 select faa.asset_id
920 , faa.asset_number
921 , fab.cost
922 , fab.date_placed_in_service
923 , fcbd.retirement_prorate_convention ret_conv
924 , fah.units
925 , fab.itc_amount
926 , fab.itc_amount_id
927 from ( select asset_id
928 from fa_asset_hierarchy
929 where asset_id IS NOT NULL
930 start with asset_hierarchy_id = x_node_entity_id
931 connect by prior asset_hierarchy_id = parent_hierarchy_id ) hr
932 , fa_asset_history fah
933 , fa_category_book_defaults fcbd
934 , fa_books fab
935 , fa_additions faa
936 where hr.asset_id = faa.asset_id
937 AND faa.asset_id = fab.asset_id
938 AND fab.book_type_code = x_book_type_code
939 -- AND fab.cost > 0
940 AND faa.asset_id = fah.asset_id
941 AND fah.date_ineffective IS NULL
942 AND faa.asset_category_id = fcbd.category_id
943 AND fab.book_type_code = fcbd.book_type_code
944 AND fab.date_placed_in_service
945 BETWEEN fcbd.start_dpis
946 AND nvl(TO_DATE(fcbd.end_dpis, 'DD-MM-YYYY'),
947 TO_DATE('31-12-4712', 'DD-MM-YYYY'))
948 AND EXISTS (SELECT 'X'
949 FROM FA_TRANSACTION_HEADERS fth
950 WHERE fth.asset_id = fab.asset_id
951 AND fth.book_type_code = fab.book_type_code
952 AND (fth.transaction_date_entered <= x_Retire_Date
953 AND fth.transaction_type_code not in ('FULL RETIREMENT',
954 'REINSTATEMENT')))
955 AND EXISTS ( SELECT 'X'
956 FROM fa_distribution_history fad
957 , gl_code_combinations gcc
958 WHERE fad.asset_id = faa.asset_id
959 AND fad.code_combination_id = gcc.code_combination_id
960 AND fad.date_ineffective IS NULL )
961 AND NOT EXISTS ( select 'X' --'PROCESSED RETIREMENT'
962 from fa_retirements frt,
963 fa_books fb
964 where frt.asset_id = fab.asset_id
965 AND frt.asset_id = fb.asset_id
966 AND frt.transaction_header_id_out is NULL
967 AND frt.status = 'PROCESSED'
968 AND frt.book_type_code = fb.book_type_code
969 AND fb.period_counter_fully_retired is NOT NULL
970 AND fb.transaction_header_id_in =
971 frt.transaction_header_id_in
972 AND fb.date_ineffective IS NULL )
973 AND faa.asset_type IN ('CIP', 'CAPITALIZED', 'EXPENSED')
974 AND fab.date_ineffective IS NULL
975 ORDER BY 4 asc;
976
977 CURSOR c_currency_info IS
978 select --sob.currency_code
979 fc.precision
980 --, fc.extended_precision
981 --, fc.minimum_accountable_unit
982 from gl_sets_of_books sob,
983 fa_book_controls fbc,
984 fnd_currencies fc
985 where fc.currency_code = sob.currency_code
986 and fc.enabled_flag = 'Y'
987 and fbc.book_type_code = x_book_type_code
988 and fbc.set_of_books_id = sob.set_of_books_id;
989
990 BEGIN
991 x_err_code := '0';
992 v_old_err_stack := x_err_stack;
993 x_err_stack := x_err_stack||'->'||'generate_retirement_batch';
994
995 x_err_stage:= 'Initializing Parameters';
996 v_sysdate:= sysdate;
997 v_conc_request_id := fnd_global.conc_request_id;
998 v_created_by:= nvl(TO_NUMBER(fnd_profile.value('USER_ID')),-1);
999 v_last_updated_by:= v_created_by;
1000 v_last_update_login:= nvl(TO_NUMBER(fnd_profile.value('LOGIN_ID')),-1);
1001
1002 x_err_stack:= x_err_stack||'->'||'Insert_hr_retirement_hdrs';
1003 insert_hr_retirement_hdrs (
1004 x_event_code
1005 , x_book_type_code
1006 , 'IP'
1007 , x_node_entity_id
1008 , v_rejection_reason_code
1009 , x_retirement_method
1010 , x_retirement_type_code
1011 , x_proceeds_of_sale
1012 , x_cost_of_removal
1013 , x_retire_date
1014 , x_prorate_by
1015 , x_retire_by
1016 , x_retirement_amount
1017 , x_retirement_percent
1018 , x_allow_partial_retire
1019 , x_retire_units
1020 , v_created_by
1021 , v_sysdate -- creation_date
1022 , v_last_updated_by
1023 , v_sysdate -- last_update_date
1024 , v_last_update_login
1025 , v_conc_request_id
1026 , x_batch_id
1027 , x_transaction_name
1028 , x_attribute_category
1029 , x_attribute1
1030 , x_attribute2
1031 , x_attribute3
1032 , x_attribute4
1033 , x_attribute5
1034 , x_attribute6
1035 , x_attribute7
1036 , x_attribute8
1037 , x_attribute9
1038 , x_attribute10
1039 , x_attribute11
1040 , x_attribute12
1041 , x_attribute13
1042 , x_attribute14
1043 , x_attribute15
1044 , TH_attribute_category
1045 , TH_attribute1
1046 , TH_attribute2
1047 , TH_attribute3
1048 , TH_attribute4
1049 , TH_attribute5
1050 , TH_attribute6
1051 , TH_attribute7
1052 , TH_attribute8
1053 , TH_attribute9
1054 , TH_attribute10
1055 , TH_attribute11
1056 , TH_attribute12
1057 , TH_attribute13
1058 , TH_attribute14
1059 , TH_attribute15
1060 , x_err_code
1061 , x_err_stage
1062 , x_err_stack
1063 , p_log_level_rec );
1064
1065 if(x_err_code <> '0') then
1066 return;
1067 end if;
1068
1069
1070 x_err_stack:= x_err_stack||'->'||'c_currency_open';
1071 open c_currency_info;
1072 fetch c_currency_info into v_precision;
1073 close c_currency_info;
1074
1075 -- store the total_cost and total_units for future use
1076 x_err_stack:= x_err_stack||'->'||'c_get_totals';
1077 open c_get_totals;
1078 fetch c_get_totals into v_total_cost, v_total_units;
1079 Close c_get_totals;
1080
1081 if(x_prorate_by = 'COST') then
1082 v_cost_remaining:= nvl(x_retirement_amount, 0);
1086
1083 elsif (x_prorate_by = 'UNITS') then
1084 v_units_remaining:= nvl(x_retirement_amount, 0);
1085 end if;
1087 FOR qualified_asset_rec IN C_qualified_assets LOOP
1088 if x_retirement_method = 'FIFO' then
1089 if x_prorate_by = 'COST' then
1090 if x_retire_by = 'AMOUNT' then
1091 if(qualified_asset_rec.cost <= v_cost_remaining) then
1092 -- completely retire asset
1093 v_cost_retired:= qualified_asset_rec.cost;
1094 v_cost_remaining:= v_cost_remaining - v_cost_retired;
1095 if(x_retire_units = 'Y' ) then
1096 v_units_retired := qualified_asset_rec.units;
1097 else
1098 v_units_retired:= 0;
1099 end if;
1100 elsif( x_allow_partial_retire = 'Y' AND
1101 qualified_asset_rec.cost > v_cost_remaining ) then
1102 -- partially retire an asset
1103 v_cost_retired:= v_cost_remaining;
1104 v_cost_remaining:= 0;
1105 if(x_retire_units = 'Y' ) then
1106 v_units_retired := ROUND( ( (v_cost_remaining /qualified_asset_rec.cost)
1107 * qualified_asset_rec.units) , v_precision );
1108 else
1109 v_units_retired:= 0;
1110 end if;
1111 --v_current_units:= qualified_asset_rec.units;
1112 else
1113 v_cost_retired:= 0;
1114 v_units_retired:= 0;
1115 end if;
1116 elsif x_retire_by = 'PERCENT' then
1117 -- first determine the net cost after applying percentage
1118 -- this determines the pool to be used for retirement
1119 -- subtract total_cost retired to keep it running amount
1120 v_cost_remaining:= ROUND(x_retirement_percent
1121 * v_total_cost/100, v_precision)
1122 - v_tot_cost_retired ;
1123 if(qualified_asset_rec.cost <= v_cost_remaining) then
1124 -- completely retire asset
1125 v_cost_retired:= qualified_asset_rec.cost;
1126 v_cost_remaining:= v_cost_remaining - v_cost_retired;
1127 if(x_retire_units = 'Y' ) then
1128 v_units_retired := qualified_asset_rec.units;
1129 else
1130 v_units_retired:= 0;
1131 end if;
1132 elsif( x_allow_partial_retire = 'Y' AND
1133 qualified_asset_rec.cost > v_cost_remaining ) then
1134 -- partially retire an asset
1135 v_cost_retired:= v_cost_remaining;
1136 v_cost_remaining:= 0;
1137 if(x_retire_units = 'Y' ) then
1138 v_units_retired := ROUND( ( (v_cost_remaining /qualified_asset_rec.cost)
1139 * qualified_asset_rec.units) , v_precision );
1140 else
1141 v_units_retired:= 0;
1142 end if;
1143 else
1144 v_cost_retired:= 0;
1145 v_units_retired:= 0;
1146 end if;
1147 end if; -- x_retire_by
1148 elsif x_prorate_by = 'UNITS' then -- x_retirement_amount= total_units
1149 if x_retire_by = 'AMOUNT' then
1150 if( qualified_asset_rec.units <= v_units_remaining ) then
1151 -- completely retire asset and units
1152 v_cost_retired:= qualified_asset_rec.cost;
1153 v_units_retired:= qualified_asset_rec.units;
1154 v_units_remaining:= v_units_remaining - v_units_retired;
1155 elsif( qualified_asset_rec.units > v_units_remaining
1156 AND x_allow_partial_retire = 'Y' ) then
1157 -- partially retire an asset
1158 v_units_retired:= v_units_remaining;
1159 v_cost_retired:= ROUND( ( (v_units_remaining/qualified_asset_rec.units)
1160 * qualified_asset_rec.cost), v_precision);
1161 v_units_remaining:= 0;
1162 else
1163 v_cost_retired:= 0;
1164 v_units_retired:= 0;
1165 end if;
1166 elsif x_retire_by = 'PERCENT' then
1167 v_units_remaining:= ROUND( x_retirement_percent
1168 * v_total_units/100, v_precision )
1169 - v_tot_units_retired;
1170 if(v_units_remaining >= qualified_asset_rec.units ) then
1171 v_cost_retired:= qualified_asset_rec.cost;
1172 v_units_retired:= qualified_asset_rec.units;
1173 v_units_remaining:= v_units_remaining - v_units_retired;
1174 elsif (x_allow_partial_retire = 'Y' AND
1175 qualified_asset_rec.units > v_units_remaining ) then
1176 -- partially retire an asset
1177 v_units_retired:= v_units_remaining;
1178 v_cost_retired:= ROUND( ( (v_units_remaining/qualified_asset_rec.units)
1179 * qualified_asset_rec.cost), v_precision);
1180 v_units_remaining:= 0;
1181 else
1182 v_cost_retired:= 0;
1183 v_units_retired:= 0;
1184 end if;
1185 end if; --x_retire_by
1186 end if; -- x_prorate_by
1187 elsif x_retirement_method = 'PRORATE' then
1188 if x_prorate_by = 'COST' then
1189 if x_retire_by = 'PERCENT' then
1190 -- in this case an asset is always partially retired
1191
1195 v_units_retired:= ROUND( qualified_asset_rec.units *
1192 v_cost_retired:= ROUND( qualified_asset_rec.cost *
1193 x_retirement_percent /100, v_precision);
1194 if( x_retire_units = 'Y') then
1196 x_retirement_percent /100, v_precision);
1197 else
1198 v_units_retired:= 0;
1199 end if;
1200 elsif x_retire_by = 'AMOUNT' then
1201 v_prorate_percent:= ROUND(qualified_asset_rec.cost * 100
1202 /v_total_cost, v_precision);
1203 v_cost_retired:= ROUND( x_retirement_amount * v_prorate_percent/100, v_precision );
1204 if(v_cost_retired > qualified_asset_rec.cost) then
1205 v_cost_retired:= qualified_asset_rec.cost;
1206 end if;
1207 if( x_retire_units = 'Y') then
1208 v_units_retired:= ROUND( ( v_cost_retired * qualified_asset_rec.units
1209 /qualified_asset_rec.cost), v_precision );
1210 else
1211 v_units_retired:= 0;
1212 end if;
1213
1214 end if; --x_retire_by
1215 elsif x_prorate_by = 'UNITS' then
1216 if x_retire_by = 'PERCENT' then
1217 -- in this case an asset is always partially retired
1218 v_cost_retired:= ROUND( qualified_asset_rec.cost
1219 * nvl(x_retirement_percent, 0)/100, v_precision);
1220 v_units_retired:= ROUND( qualified_asset_rec.units
1221 * nvl(x_retirement_percent, 0)/100, v_precision);
1222 elsif x_retire_by = 'AMOUNT' then
1223 v_prorate_percent:= ROUND( (qualified_asset_rec.units * 100
1224 /v_total_units), v_precision );
1225
1226 v_units_retired:= ROUND( x_retirement_amount * v_prorate_percent/100, v_precision );
1227 if(v_units_retired > qualified_asset_rec.units ) then
1228 v_units_retired:= qualified_asset_rec.units;
1229 end if;
1230 v_cost_retired:= ROUND( qualified_asset_rec.cost * v_units_retired
1231 /qualified_asset_rec.units ,v_precision);
1232 end if; -- x_retire_by
1233 end if; -- x_prorate_by
1234 end if;
1235
1236 -- if current_cost is zero then allow to insert
1237 -- if cost_retired is zero and current_cost is not zero, do not insert
1238 if( qualified_asset_rec.cost <> 0 and v_cost_retired = 0) then
1239 null;
1240 else
1241 -- for enhancement 988193
1242 i := i+1;
1243
1244 ret_tab(i).asset_id := qualified_asset_rec.asset_id;
1245 ret_tab(i).dpis := qualified_asset_rec.date_placed_in_service;
1246 ret_tab(i).cost := nvl(qualified_asset_rec.cost, 0);
1247 ret_tab(i).cost_retired := nvl(v_cost_retired, 0);
1248 ret_tab(i).units := nvl(qualified_asset_rec.units, 0);
1249 ret_tab(i).units_retired := nvl(v_units_retired, 0);
1250 ret_tab(i).prorate_percent := nvl(v_prorate_percent, 0);
1251 ret_tab(i).ret_prorate_conv := qualified_asset_rec.ret_conv;
1252
1253 v_tot_cost_retired:= v_tot_cost_retired + v_cost_retired;
1254 v_tot_units_retired:= v_tot_units_retired + v_units_retired;
1255 v_cost_retired:= 0;
1256 v_units_retired:= 0;
1257 v_prorate_percent:= 0;
1258 end if;
1259
1260 if(x_prorate_by = 'FIFO') then
1261 if( ( x_prorate_by = 'COST' AND v_cost_remaining <= 0) OR
1262 ( x_prorate_by = 'UNITS' AND v_units_remaining <= 0) ) then
1263 exit;
1264 end if;
1265 end if;
1266
1267 END LOOP;
1268
1269 -- get the total_cost being retired to spread the pos and cor amounts
1270 if ( nvl(x_proceeds_of_sale, 0) <> 0 or
1271 nvl(x_cost_of_removal, 0 ) <> 0 ) then
1272
1273 v_tot_cost_retired := 0;
1274 FOR i in 1..ret_tab.count LOOP
1275 v_tot_cost_retired := v_tot_cost_retired + ret_tab(i).cost_retired;
1276 END LOOP;
1277 end if;
1278
1279 i:= 0;
1280 FOR i in 1..ret_tab.count LOOP
1281 if ( nvl(x_proceeds_of_sale, 0) <> 0 ) then
1282 ret_tab(i).proceeds_of_sale :=
1283 x_proceeds_of_sale * ( ret_tab(i).cost_retired/v_tot_cost_retired );
1284 end if;
1285
1286 if ( nvl(x_cost_of_removal, 0 ) <> 0 ) then
1287 ret_tab(i).cost_of_removal :=
1288 x_cost_of_removal * ( ret_tab(i).cost_retired/v_tot_cost_retired);
1289 end if;
1290
1291 x_err_stack:= x_err_stack||'->'||'Insert_hr_retirement_dtls';
1292 insert_hr_retirement_dtls(
1293 x_batch_id
1294 , x_book_type_code
1295 , ret_tab(i).asset_id
1296 , ret_tab(i).dpis
1297 , ret_tab(i).cost
1298 , ret_tab(i).cost_retired
1299 , ret_tab(i).units
1300 , ret_tab(i).units_retired
1301 , ret_tab(i).prorate_percent
1302 , ret_tab(i).ret_prorate_conv
1303 , 'IP' --x_status_code
1304 , v_rejection_reason_code
1305 , ret_tab(i).proceeds_of_sale
1306 , ret_tab(i).cost_of_removal
1307 , v_created_by
1308 , v_sysdate --v_creation_date
1309 , v_last_updated_by
1310 , v_sysdate --v_last_update_date
1311 , v_last_update_login
1312 , v_conc_request_id
1313 , x_err_code
1314 , x_err_stage
1315 , x_err_stack
1316 , p_log_level_rec );
1317
1318
1319 /** commneted out for enhancement 988193
1320
1321 x_err_stack:= x_err_stack||'->'||'Insert_hr_retirement_dtls';
1322 insert_hr_retirement_dtls(
1323 x_batch_id
1324 , x_book_type_code
1325 , qualified_asset_rec.asset_id
1326 , qualified_asset_rec.date_placed_in_service
1327 , nvl(qualified_asset_rec.cost, 0)
1328 , nvl(v_cost_retired, 0)
1329 , nvl(qualified_asset_rec.units, 0)
1330 , nvl(v_units_retired, 0)
1331 , nvl(v_prorate_percent, 0)
1332 , qualified_asset_rec.retirement_prorate_convention
1333 , 'IP' --x_status_code
1334 , v_rejection_reason_code
1335 , nvl(v_asset_pos, 0)
1336 , nvl(v_asset_cor, 0)
1337 , v_created_by
1338 , v_sysdate --v_creation_date
1339 , v_last_updated_by
1340 , v_sysdate --v_last_update_date
1341 , v_last_update_login
1342 , v_conc_request_id
1343 , x_err_code
1344 , x_err_stage
1345 , x_err_stack );
1346
1347 end if;
1348 **/
1349
1350 END LOOP;
1351
1352 commit;
1353
1354 EXCEPTION
1355 when others then
1356
1357 x_err_code:= substrb(sqlerrm, 1, 240);
1358 raise;
1359 END generate_retirement_batch;
1360
1361
1362 PROCEDURE conc_request( ERRBUF OUT NOCOPY VARCHAR2,
1363 RETCODE OUT NOCOPY VARCHAR2,
1364 x_from_batch_num IN NUMBER,
1365 x_to_batch_num IN NUMBER ) IS
1366 v_conc_request_id NUMBER;
1367 v_dummy VARCHAR2(1):= 'N';
1368 v_ret_value NUMBER;
1369
1370 CURSOR c_hrh IS
1371 select *
1372 from fa_hr_retirement_headers
1373 where batch_id >= nvl(x_from_batch_num, batch_id )
1374 AND batch_id <= nvl(x_to_batch_num, batch_id)
1375 AND status_code IN ('IP', 'P', 'RC')
1376 order by creation_date
1377 FOR UPDATE NOWAIT;
1378
1379 BEGIN
1380 RETCODE:= '0';
1381 BEGIN
1382 v_conc_request_id := fnd_global.conc_request_id;
1383
1384 for hrh_rec IN c_hrh LOOP
1385 update fa_hr_retirement_headers
1386 set status_code = 'IP'
1387 where batch_id = hrh_rec.batch_id;
1388
1389 update fa_hr_retirement_details
1390 set status_code = 'IP'
1391 where batch_id = hrh_rec.batch_id;
1392
1393 commit;
1394
1395 post_hr_retirements ( hrh_rec.batch_id
1396 , hrh_rec.retire_date
1397 , hrh_rec.retirement_type_code
1398 , hrh_rec.transaction_name
1399 , hrh_rec.attribute_category
1400 , hrh_rec.attribute1
1401 , hrh_rec.attribute2
1402 , hrh_rec.attribute3
1403 , hrh_rec.attribute4
1404 , hrh_rec.attribute5
1405 , hrh_rec.attribute6
1406 , hrh_rec.attribute7
1407 , hrh_rec.attribute8
1408 , hrh_rec.attribute9
1409 , hrh_rec.attribute10
1410 , hrh_rec.attribute11
1411 , hrh_rec.attribute12
1412 , hrh_rec.attribute13
1413 , hrh_rec.attribute14
1414 , hrh_rec.attribute15
1415 , hrh_rec.TH_attribute_category
1416 , hrh_rec.TH_attribute1
1417 , hrh_rec.TH_attribute2
1418 , hrh_rec.TH_attribute3
1419 , hrh_rec.TH_attribute4
1420 , hrh_rec.TH_attribute5
1421 , hrh_rec.TH_attribute6
1422 , hrh_rec.TH_attribute7
1423 , hrh_rec.TH_attribute8
1424 , hrh_rec.TH_attribute9
1425 , hrh_rec.TH_attribute10
1426 , hrh_rec.TH_attribute11
1427 , hrh_rec.TH_attribute12
1428 , hrh_rec.TH_attribute13
1429 , hrh_rec.TH_attribute14
1430 , hrh_rec.TH_attribute15
1431 , v_conc_request_id
1432 , g_log_level_rec);
1433
1434 -- check if there are any line unprocessed or Rejected
1435 v_dummy := 'N';
1436
1437 Begin
1438 select 'Y'
1439 into v_dummy
1440 from fa_hr_retirement_details
1441 where batch_id = hrh_rec.batch_id
1442 and status_code in ('IP','R')
1443 and rownum = 1;
1444 Exception
1445 When others then
1446 null;
1447 End ;
1448
1449 if v_dummy = 'Y' then
1450 v_dummy:= 'N';
1451 update fa_hr_retirement_headers
1452 set status_code = 'R' -- Rejected Processed
1453 , concurrent_request_id = v_conc_request_ID
1454 , last_updated_by = fnd_global.login_id
1455 , last_update_date = sysdate
1456 , last_update_login = fnd_global.login_id
1457 where batch_id = hrh_rec.batch_id;
1458
1459 Update fa_hr_retirement_details
1460 set status_code = 'P'
1461 where status_code <> 'R'
1462 and batch_id = hrh_rec.batch_id;
1463 else
1464 update fa_hr_retirement_headers
1465 set status_code = 'CP' -- Completetly Processed
1466 , concurrent_request_id = v_conc_request_ID
1467 , last_updated_by = fnd_global.login_id
1468 , last_update_date = sysdate
1469 , last_update_login = fnd_global.login_id
1470 where batch_id = hrh_rec.batch_id;
1471 end if;
1472
1473 commit;
1474
1475 END LOOP;
1476
1477 EXCEPTION
1481 END;
1478 WHEN OTHERS THEN
1479 RETCODE := '2';
1480 ERRBUF := substrb(SQLERRM, 1, 240);
1482
1483 if RETCODE = '0' then
1484 commit;
1485 end if;
1486
1487 END conc_request;
1488
1489
1490
1491 PROCEDURE post_hr_retirements ( x_batch_id IN NUMBER
1492 , x_retire_date IN DATE
1493 , x_retirement_type_code IN VARCHAR2
1494 , x_transaction_name IN VARCHAR2
1495 , x_attribute_category IN VARCHAR2
1496 , x_attribute1 IN VARCHAR2
1497 , x_attribute2 IN VARCHAR2
1498 , x_attribute3 IN VARCHAR2
1499 , x_attribute4 IN VARCHAR2
1500 , x_attribute5 IN VARCHAR2
1501 , x_attribute6 IN VARCHAR2
1502 , x_attribute7 IN VARCHAR2
1503 , x_attribute8 IN VARCHAR2
1504 , x_attribute9 IN VARCHAR2
1505 , x_attribute10 IN VARCHAR2
1506 , x_attribute11 IN VARCHAR2
1507 , x_attribute12 IN VARCHAR2
1508 , x_attribute13 IN VARCHAR2
1509 , x_attribute14 IN VARCHAR2
1510 , x_attribute15 IN VARCHAR2
1511 , TH_attribute_category IN VARCHAR2
1512 , TH_attribute1 IN VARCHAR2
1513 , TH_attribute2 IN VARCHAR2
1514 , TH_attribute3 IN VARCHAR2
1515 , TH_attribute4 IN VARCHAR2
1516 , TH_attribute5 IN VARCHAR2
1517 , TH_attribute6 IN VARCHAR2
1518 , TH_attribute7 IN VARCHAR2
1519 , TH_attribute8 IN VARCHAR2
1520 , TH_attribute9 IN VARCHAR2
1521 , TH_attribute10 IN VARCHAR2
1522 , TH_attribute11 IN VARCHAR2
1523 , TH_attribute12 IN VARCHAR2
1524 , TH_attribute13 IN VARCHAR2
1525 , TH_attribute14 IN VARCHAR2
1526 , TH_attribute15 IN VARCHAR2
1527 , x_conc_request_id IN NUMBER, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
1528
1529 CURSOR c_hrd IS
1530 select batch_id,
1531 asset_id,
1532 cost_retired,
1533 current_cost,
1534 current_units,
1535 units_retired,
1536 book_type_code,
1537 status_code,
1538 retirement_convention_code
1539 from fa_hr_retirement_details
1540 where batch_id = x_batch_id
1541 order by date_placed_in_service asc;
1542
1543 CURSOR c_dist_lines(x_book_type_code IN VARCHAR2
1544 , x_asset_id IN NUMBER ) IS
1545 select distribution_id
1546 , code_combination_id
1547 , units_assigned
1548 , location_id
1549 , assigned_to
1550 , date_effective
1551 , transaction_header_id_in
1552 from fa_distribution_history
1553 where book_type_code = x_book_type_code
1554 and asset_id = x_asset_id
1555 and date_ineffective IS NULL;
1556 --order by date_effective asc;
1557
1558 lv_it_rowid rowid;
1559 lv_ret_id number;
1560 lv_sl_cost_retired number;
1561 lv_sl_count number := 0;
1562 lv_new_inv_txn_id number;
1563
1564 -- variables for validation
1565 lv_cost number;
1566 lv_current_units number;
1567 lv_current_period_counter number;
1568 lv_asset_added_pc number;
1569 lv_current_fiscal_year number;
1570 lv_stl_life_in_months number;
1571 lv_val_count number;
1572 lv_asset_id NUMBER;
1573
1574 lv_date_retired date;
1575 lv_fy_start_date date;
1576 lv_fy_end_date date;
1577 lv_cal_per_close_date date;
1578 lv_cal_per_open_date date;
1579 lv_max_txn_date_entered date;
1580
1581 lv_book_class varchar2(15);
1582 lv_asset_type varchar2(11);
1583 lv_ret_prorate_convention varchar2(10);
1584 lv_use_stl_ret_flag varchar2(3);
1585 lv_stl_method_code varchar2(4);
1586 lv_message varchar2(50);
1587 lv_app varchar2(3);
1588 Validation_Error exception;
1589 Duplicate_Req exception;
1590
1591 v_sysdate DATe;
1592 v_old_err_stack VARCHAR2(640);
1593 v_rejection_reason VARCHAR2(80);
1594 v_varchar_dummy VARCHAR2(80);
1595 v_error_flag VARCHAR2(1);
1596 v_err_code VARCHAR2(640);
1597 v_err_stack VARCHAR2(640);
1598 v_err_stage VARCHAR2(640);
1599 v_message_name VARCHAR2(240);
1600 v_retire_flag VARCHAR2(1);
1601
1602 v_retirement_id NUMBER;
1603 v_conc_request_id NUMBER;
1604 v_user NUMBER;
1605 v_last_update_login NUMBER;
1606 v_number_dummy NUMBER(15);
1607 v_date_dummy DATE;
1608 v_units_retired NUMBER;
1609 v_transaction_header_id NUMBER;
1610 v_distribution_id NUMBER;
1611 v_book_header_id NUMBER;
1612 v_units_remaining NUMBER;
1613 v_count number:=0;
1614 v_no_of_dist_lines number:= 0;
1615 v_transaction_units number:= NULL;
1619 l_dist_trans_rec FA_API_TYPES.trans_rec_type;
1616 v_running_units number:=0;
1617
1618 l_trans_rec FA_API_TYPES.trans_rec_type;
1620 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
1621 l_asset_retire_rec FA_API_TYPES.asset_retire_rec_type;
1622 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
1623 l_subcomp_tbl FA_API_TYPES.subcomp_tbl_type;
1624 l_inv_tbl FA_API_TYPES.inv_tbl_type;
1625
1626 /* misc info */
1627 l_api_version number := 1;
1628 l_init_msg_list varchar2(1) := FND_API.G_FALSE;
1629 l_commit varchar2(1) := FND_API.G_TRUE;
1630 l_validation_level number := FND_API.G_VALID_LEVEL_FULL;
1631 l_return_status varchar2(1) := FND_API.G_FALSE;
1632 l_msg_count number := 0;
1633 l_msg_data varchar2(512);
1634
1635
1636 TYPE ErrorRecTyp IS RECORD(
1637 rejection_reason VARCHAR2(250) );
1638
1639 TYPE ErrorTabTyp IS TABLE OF ErrorRecTyp
1640 INDEX BY BINARY_INTEGER;
1641
1642 v_Error_Tab ErrorTabTyp; -- error table
1643
1644 v_encoded_message varchar2(640);
1645 v_app_short_name varchar2(3);
1646
1647 error_found exception;
1648
1649 BEGIN
1650 v_err_code := '0';
1651
1652 -- initializing parameters
1653 v_sysdate:= sysdate;
1654 v_user:= nvl(TO_NUMBER(fnd_profile.value('USER_ID')),-1);
1655 v_last_update_login:= nvl(TO_NUMBER(fnd_profile.value('LOGIN_ID')),-1);
1656
1657 FOR hrd_rec IN C_hrd LOOP
1658
1659 --initialize for each loop
1660 v_varchar_dummy:= null;
1661 v_retirement_id := NULL;
1662 v_number_dummy:= null;
1663 v_date_dummy:= null;
1664 v_transaction_header_id := null;
1665 v_book_header_id:= null;
1666 v_count := 0;
1667
1668
1669 BEGIN
1670 /* set required but null parameters to default values */
1671 if x_retire_date is not null then
1672 lv_date_retired:= x_retire_date;
1673 end if;
1674
1675 if hrd_rec.Retirement_Convention_code is not null then
1676 lv_ret_prorate_convention:= hrd_rec.Retirement_Convention_code;
1677 end if;
1678
1679
1680 l_asset_hdr_rec.asset_id := hrd_rec.Asset_Id;
1681 l_asset_hdr_rec.book_type_code := hrd_rec.Book_Type_Code;
1682 l_asset_retire_rec.date_retired := v_date_dummy;
1683 l_asset_retire_rec.cost_retired := hrd_rec.Cost_Retired;
1684 l_asset_retire_rec.retirement_prorate_convention:= hrd_rec.Retirement_Convention_code;
1685 l_asset_retire_rec.units_retired := hrd_rec.units_retired;
1686 l_asset_retire_rec.desc_flex.Attribute1 := x_Attribute1;
1687 l_asset_retire_rec.desc_flex.Attribute2 := x_Attribute2;
1688 l_asset_retire_rec.desc_flex.Attribute3 := x_Attribute3;
1689 l_asset_retire_rec.desc_flex.Attribute4 := x_Attribute4;
1690 l_asset_retire_rec.retirement_type_code := x_Retirement_Type_Code;
1691 l_asset_retire_rec.desc_flex.Attribute5 := x_Attribute5;
1692 l_asset_retire_rec.desc_flex.Attribute6 := x_Attribute6;
1693 l_asset_retire_rec.desc_flex.Attribute7 := x_Attribute7;
1694 l_asset_retire_rec.desc_flex.Attribute8 := x_Attribute8;
1695 l_asset_retire_rec.desc_flex.Attribute9 := x_Attribute9;
1696 l_asset_retire_rec.desc_flex.Attribute10 := x_Attribute10;
1697 l_asset_retire_rec.desc_flex.Attribute11 := x_Attribute11;
1698 l_asset_retire_rec.desc_flex.Attribute12 := x_Attribute12;
1699 l_asset_retire_rec.desc_flex.Attribute13 := x_Attribute13;
1700 l_asset_retire_rec.desc_flex.Attribute14 := x_Attribute14;
1701 l_asset_retire_rec.desc_flex.Attribute15 := x_Attribute15;
1702 l_asset_retire_rec.desc_flex.attribute_category_code := x_Attribute_Category;
1703 l_trans_rec.desc_flex.Attribute1 := TH_Attribute1;
1704 l_trans_rec.desc_flex.Attribute2 := TH_Attribute2;
1705 l_trans_rec.desc_flex.Attribute3 := TH_Attribute3;
1706 l_trans_rec.desc_flex.Attribute4 := TH_Attribute4;
1707 l_trans_rec.desc_flex.Attribute5 := TH_Attribute5;
1708 l_trans_rec.desc_flex.Attribute6 := TH_Attribute6;
1709 l_trans_rec.desc_flex.Attribute7 := TH_Attribute7;
1710 l_trans_rec.desc_flex.Attribute8 := TH_Attribute8;
1711 l_trans_rec.desc_flex.Attribute9 := TH_Attribute9;
1712 l_trans_rec.desc_flex.Attribute10 := TH_Attribute10;
1713 l_trans_rec.desc_flex.Attribute11 := TH_Attribute11;
1714 l_trans_rec.desc_flex.Attribute12 := TH_Attribute12;
1715 l_trans_rec.desc_flex.Attribute13 := TH_Attribute13;
1716 l_trans_rec.desc_flex.Attribute14 := TH_Attribute14;
1717 l_trans_rec.desc_flex.Attribute15 := TH_Attribute15;
1718 l_trans_rec.desc_flex.attribute_category_code := TH_Attribute_Category;
1719 l_trans_rec.transaction_name := x_transaction_name;
1720
1721 if (nvl( hrd_rec.units_retired, 0) <> 0 AND
1722 hrd_rec.units_retired < hrd_rec.current_units ) then
1723
1724 -- partially retire the units
1725 -- 1. transfer out the dist line
1726 -- 2. call retire process
1727
1728 -- then retire the oldest distribution first
1729 FOR dl_rec in c_dist_lines(hrd_rec.book_type_code,
1730 hrd_rec.asset_id ) LOOP
1731 v_count:= v_count +1;
1732 if (v_no_of_dist_lines > 0) and (v_count = v_no_of_dist_lines) then
1733 -- Last Dist line to Adjust
1734 -- Therefore assign it the Remaining Units
1735 v_transaction_units := hrd_rec.units_retired - v_running_units;
1736 else
1737 v_transaction_units := (dl_rec.units_assigned/hrd_rec.current_units)*hrd_rec.units_retired;
1738 -- v_transaction_units := round(v_transaction_units,2);
1739 end if;
1740
1741 v_running_units := v_running_units + v_transaction_units;
1742
1743 v_varchar_dummy:= NULL;
1744 v_number_dummy:= NULL;
1745 if(v_transaction_units <>0 ) then
1746 l_asset_dist_tbl(v_count).distribution_id := dl_rec.distribution_id;
1747 l_asset_dist_tbl(v_count).transaction_units := v_transaction_units;
1748 end if;
1749
1750 END LOOP;
1751
1752 elsif (hrd_rec.units_retired = hrd_rec.current_units) OR ( nvl(hrd_rec.units_retired, 0) = 0 ) then
1753 -- fully retire the asset
1754 -- call retire package
1755 null;
1756 else
1757 v_error_flag:= 'Y';
1758 fnd_message.set_name ('OFA','FA_RET_UNITS_TOO_BIG');
1759 v_error_tab(hrd_rec.asset_id).rejection_reason := substrb(fnd_message.get, 1, 240);
1760 raise error_found;
1761 end if;
1762
1763 FA_RETIREMENT_PUB.do_retirement(
1764 p_api_version => l_api_version
1765 ,p_init_msg_list => l_init_msg_list
1766 ,p_commit => l_commit
1767 ,p_validation_level => l_validation_level
1768 ,p_calling_fn => 'FA_CUA_HR_RETIRMENTS_PKG.Partial_Unit_Retire'
1769 ,x_return_status => l_return_status
1770 ,x_msg_count => l_msg_count
1771 ,x_msg_data => l_msg_data
1772
1773 ,px_trans_rec => l_trans_rec
1774 ,px_dist_trans_rec => l_dist_trans_rec
1775 ,px_asset_hdr_rec => l_asset_hdr_rec
1776 ,px_asset_retire_rec => l_asset_retire_rec
1777 ,p_asset_dist_tbl => l_asset_dist_tbl
1778 ,p_subcomp_tbl => l_subcomp_tbl
1779 ,p_inv_tbl => l_inv_tbl);
1780
1781
1782 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1783
1784 raise error_found;
1785 end if;
1786
1787 EXCEPTION
1788 WHEN ERROR_FOUND THEN
1789 v_encoded_message:= fnd_message.get_encoded;
1790 fnd_message.parse_encoded( v_encoded_message
1791 , v_app_short_name
1792 , v_message_name );
1793
1794 if ( v_app_short_name IS NOT NULL) AND
1795 (substrb(v_app_short_name, 1, 3) IN ('CUA', 'OFA' ) ) then
1796 fnd_message.set_encoded(v_encoded_message);
1797 v_error_tab(hrd_rec.asset_id).rejection_reason := substrb(fnd_message.get, 1, 240);
1798 else
1799 v_error_tab(hrd_rec.asset_id).rejection_reason := substrb(sqlerrm,1,240);
1800 end if;
1801
1802
1803 WHEN OTHERS THEN
1804 v_error_flag := 'Y';
1805 v_encoded_message:= NULL;
1806 v_app_short_name:= NULL;
1807 v_message_name:= NULL;
1808
1809 v_encoded_message:= fnd_message.get_encoded;
1810 fnd_message.parse_encoded( v_encoded_message
1811 , v_app_short_name
1812 , v_message_name );
1813
1814 if ( v_app_short_name IS NOT NULL) AND
1815 (substrb(v_app_short_name, 1, 3) IN ('CUA', 'OFA' ) ) then
1816 fnd_message.set_encoded(v_encoded_message);
1817 v_error_tab(hrd_rec.asset_id).rejection_reason := substrb(fnd_message.get, 1, 240);
1818 else
1819 v_error_tab(hrd_rec.asset_id).rejection_reason := substrb(sqlerrm,1,240);
1820 end if;
1821
1822 END;
1823
1824 update fa_hr_retirement_details
1825 set retirement_id = v_retirement_id
1826 where batch_id = hrd_rec.batch_id
1827 and asset_id = hrd_rec.asset_id;
1828
1829 END LOOP;
1830
1831 if(v_error_flag = 'Y') then
1832 rollback;
1833
1834 FOR hrd_rec in c_hrd LOOP
1835 if v_error_tab.exists(hrd_rec.asset_id) then
1836 UPDATE fa_hr_retirement_details
1837 SET status_code = 'R'
1838 , rejection_reason = v_error_tab(hrd_rec.asset_id).rejection_reason
1839 , concurrent_request_id = x_conc_request_id
1840 , last_updated_by = v_user
1841 , last_update_date = v_sysdate
1842 , last_update_login = v_last_update_login
1843 WHERE asset_id = hrd_rec.asset_id
1844 and batch_id = x_batch_id;
1845 end if;
1846 END LOOP;
1847 else
1848 -- if successfull
1849 UPDATE fa_hr_retirement_details
1850 SET status_code = 'A'
1851 , rejection_reason = null
1852 , concurrent_request_id = x_conc_request_id
1853 -- , retirement_id = v_retirement_id
1854 , last_updated_by = v_user
1855 , last_update_date = v_sysdate
1856 , last_update_login = v_last_update_login
1857 WHERE batch_id = x_batch_id;
1858 end if;
1859
1860 COMMIT;
1861 Exception
1862 WHEN OTHERS THEN
1863 null;
1864 END post_hr_retirements;
1865
1866
1867 END FA_CUA_HR_RETIREMENTS_PKG;