[Home] [Help]
PACKAGE BODY: APPS.FA_CUA_MASS_UPDATE1_PKG
Source
1 PACKAGE BODY FA_CUA_MASS_UPDATE1_PKG as
2 /* $Header: FACMUP1MB.pls 120.3 2005/06/24 22:03:24 bridgway ship $*/
3
4 ----------------------------------------------------------------
5 PROCEDURE CALC_LIFE_ENDDATE( x_prorate_date DATE,
6 x_end_date IN OUT NOCOPY DATE,
7 x_prorate_convention_code IN VARCHAR2,
8 x_life IN NUMBER,
9 x_err_code IN OUT NOCOPY VARCHAR2,
10 x_err_stage IN OUT NOCOPY VARCHAR2,
11 x_err_stack IN OUT NOCOPY VARCHAR2 ) IS
12 /*
13 CURSOR C IS
14 select conv.prorate_date
15 from fa_conventions conv
16 where conv.prorate_convention_code = x_prorate_convention_code
17 and add_months(x_prorate_date,x_life)
18 between conv.start_date and conv.end_date;
19 */
20 CURSOR C IS
21 select add_months(x_prorate_date,x_life)
22 from dual;
23
24 BEGIN
25 open c;
26 fetch c into x_end_date;
27 close c;
28 END;
29
30
31 ----------------------------------------------------------------
32 FUNCTION GET_END_DATE(x_book_type_code VARCHAR2,
33 x_prorate_date DATE,
34 x_life NUMBER) return date is
35 /** commneted for bugfix 1055453 */
36 /* this cursor displays life_end_date incorrectly ( to the last period_date defined )
37 if the life fell beyond existing calendar periods
38 CURSOR C IS
39 select max(cp.end_date)
40 from fa_calendar_periods cp,
41 fa_calendar_types ct,
42 fa_book_controls bc
43 where bc.book_type_code = X_book_type_code and
44 bc.date_ineffective is null and
45 ct.calendar_type = bc.prorate_calendar and
46 cp.calendar_type = ct.calendar_type and
47 cp.end_date <= add_months(x_prorate_date,x_life);
48
49 */
50
51 -- added the following cursor for bugfix 1055453
52 CURSOR C IS
53 select add_months(x_prorate_date,x_life)
54 from dual;
55
56 l_end_date date;
57 Begin
58 open c;
59 fetch c into l_end_date;
60 close c;
61
62 return l_end_date;
63 End;
64
65 ----------------------------------------------------------------
66 PROCEDURE CALC_LIFE(X_book_type_code VARCHAR2,
67 x_prorate_date DATE,
68 x_end_date DATE,
69 x_deprn_method IN VARCHAR2,
70 x_life IN OUT NOCOPY number,
71 x_err_code in out nocopy varchar2 ,
72 x_err_stage in out nocopy varchar2 ,
73 x_err_stack in out nocopy varchar2 ) IS
74 -- bugfix 1210531
75 -- to improve peroformance do not use this cursor
76
77 CURSOR GET_LIFE IS
78 select /*+ ordered */ round
79 (nvl(sum
80 (decode (bc.deprn_allocation_code,'E',
81 1/ct.number_per_fiscal_year,
82 (cp.end_date + 1 - cp.start_date) /
83 (fy.end_date + 1 - fy.start_date))),0) * 12, 0)
84 from fa_calendar_periods cp,
85 fa_calendar_types ct,
86 fa_book_controls bc,
87 fa_fiscal_year fy
88 where bc.book_type_code = X_book_type_code and
89 bc.date_ineffective is null and
90 ct.calendar_type = bc.prorate_calendar and
91 ct.fiscal_year_name = bc.fiscal_year_name
92 and cp.calendar_type = ct.calendar_type and
93 ( (cp.start_date >= x_prorate_date and
94 cp.end_date <= x_end_date)
95 or
96 (cp.start_date <= x_prorate_date
97 and cp.end_date >= x_prorate_date
98 and cp.start_date <= x_end_date
99 and cp.end_date <= x_end_date))
100 and fy.fiscal_year_name = bc.fiscal_year_name
101 and fy.start_date <= cp.start_date
102 and fy.end_date >= cp.end_date;
103
104 CURSOR GET_RSR IS
105 select rate_source_rule
106 from fa_methods
107 where method_code = x_deprn_method;
108
109 l_rsr varchar2(20);
110
111 CURSOR CHECK_METHOD_EXISTS IS
112 select 'X'
113 from fa_methods
114 where method_code = x_deprn_method
115 and nvl(life_in_months,0) = x_life;
116
117 l_dummy varchar2(1);
118 l_old_stack varchar2(600);
119 l_rowid rowid;
120 l_method_id number;
121
122 BEGIN
123
124
125 x_err_code :='0';
126 l_old_stack := x_err_stack ;
127 x_err_stack := x_err_stack||'CALC_LIFE';
128
129 -- Return Life as Zero if either of the dates is null. Therefore not possible to do calcs
130 if (x_prorate_date is null) or (x_end_date is null) then
131 x_life :=0;
132 return;
133 end if;
134
135 -- bugfix 1210531
136 -- instead of the cursor use months_between logic
137 /*
138 OPEN GET_LIFE;
139
140 FETCH GET_LIFE INTO x_life;
141 if GET_LIFE%NOTFOUND THEN
142 x_life :=1;
143 end if;
144 CLOSE GET_LIFE;
145 */
146
147 x_life := ceil(months_between(x_end_date, x_prorate_date));
148 if nvl(x_life, 0) < 0 then
149 x_life:= 1;
150 end if;
151
152
153 -- If Calendars are not setup OR some other problem then return life = 1 month
154 select decode(x_life,null,1,0,1,x_life)
155 into x_life
156 from dual;
157
158 OPEN GET_RSR;
159 FETCH GET_RSR INTO l_rsr;
160 CLOSE GET_RSR;
161
162 OPEN CHECK_METHOD_EXISTS;
163 FETCH CHECK_METHOD_EXISTS into l_dummy;
164 IF CHECK_METHOD_EXISTS%NOTFOUND THEN
165 if l_rsr = 'TABLE' then
166 x_err_code := 'FA_REVAL_NO_METH_LIFE';
167 return;
168 else
169 FA_METHODS_PKG.Insert_Row(
170 X_Rowid => l_rowid,
171 X_Method_Id => l_method_id,
172 X_Method_Code => x_deprn_method,
173 X_Life_In_Months => x_life,
174 X_Depreciate_Lastyear_Flag => 'YES',
175 X_STL_Method_Flag => 'YES',
176 X_Rate_Source_Rule => 'CALCULATED',
177 X_Deprn_Basis_Rule => 'COST',
178 X_Prorate_Periods_Per_Year => NULL,
179 X_Name => 'Straight-Line',
180 X_Last_Update_Date => sysdate,
181 X_Last_Updated_By => FND_GLOBAL.LOGIN_ID,
182 X_Created_By => FND_GLOBAL.LOGIN_ID,
183 X_Creation_Date => sysdate,
184 X_Last_Update_Login => FND_GLOBAL.LOGIN_ID,
185 X_Attribute1 => null,
186 X_Attribute2 => null,
187 X_Attribute3 => null,
188 X_Attribute4 => null,
189 X_Attribute5 => null,
190 X_Attribute6 => null,
191 X_Attribute7 => null,
192 X_Attribute8 => null,
193 X_Attribute9 => null,
194 X_Attribute10 => null,
195 X_Attribute11 => null,
196 X_Attribute12 => null,
197 X_Attribute13 => null,
198 X_Attribute14 => null,
199 X_Attribute15 => null,
200 X_Attribute_Category_Code => null,
201 X_Calling_Fn => 'CALC_LIFE');
202
203 end if;
204
205 CLOSE CHECK_METHOD_EXISTS;
206
207 end if;
208
209 x_err_stack := l_old_stack ;
210
211 END ;
212
213
214 ----------------------------------------------------------------
215 PROCEDURE Val_Reclass(X_Old_Cat_Id NUMBER,
216 X_New_Cat_Id NUMBER,
217 X_Asset_Id NUMBER,
218 X_Asset_Type VARCHAR2,
219 X_Old_Cap_Flag VARCHAR2,
220 X_Old_Cat_Type VARCHAR2,
221 x_new_cap_flag VARCHAR2,
222 X_New_Cat_Type IN OUT NOCOPY VARCHAR2,
223 X_Lease_Id NUMBER,
224 x_err_code in out nocopy varchar2 ,
225 x_err_stage in out nocopy varchar2 ,
226 x_err_stack in out nocopy varchar2 ) IS
227
228 ll_corp_book varchar2(15);
229 ll_count number;
230 ll_mesg varchar2(50);
231 l_old_stack varchar2(600);
232
233 BEGIN
234 x_err_code := '0';
235 l_old_stack := x_err_stack ;
236 x_err_stack := x_err_stack||'->Updating Asset Catgeory';
237
238 -- find corporate book
239 x_err_stage := 'find corporate book';
240
241 select bc.book_type_code into ll_corp_book
242 from fa_books bk, fa_book_controls bc
243 where bc.book_class = 'CORPORATE'
244 and bk.asset_id = X_Asset_Id
245 and bk.book_type_code = bc.book_type_code
246 and bk.date_ineffective is null;
247
248 -- validate new category
249 x_err_stage := 'validate new category';
250
251 if X_ASSET_TYPE = 'CIP' then
252 -- check that CIP accounts are set up
253 select count(*)
254 into ll_count
255 from fa_category_books
256 where category_id = X_new_cat_ID
257 and book_type_code = ll_corp_book
258 and cip_cost_acct is not null
259 and cip_clearing_acct is not null;
260 --
261 if ll_count = 0 then
262 x_err_code := 'FA_SHARED_NO_CIP_ACCOUNTS';
263 return;
264 end if;
265 end if;
266
267 -- check if cat is set up in this book
268 select count(*)
269 into ll_count
270 from fa_category_books
271 where book_type_code = ll_corp_book and
272 category_id = X_new_cat_ID;
273 --
274 if ll_count = 0 then
275 x_err_code := 'FA_BOOK_CAT_NOT_SET_UP';
276 return;
277 end if;
278
279 -- both categories must be capitalized or expensed types
280 --
281 if X_Old_Cap_Flag = 'YES' then
282 if x_new_cap_flag = 'NO' then
283 x_err_code := 'FA_ADD_RECLS_TO_CAP_ASSET';
284 return;
285 end if;
286 elsif X_Old_Cap_Flag = 'NO' then
287 if x_new_cap_flag = 'YES' then
288 x_err_code := 'FA_ADD_RECLS_TO_EXPENSE';
289 return;
290 end if;
291 end if;
292 -- also check lease stuff
293 if X_Old_Cat_Type = 'LEASE' and X_New_Cat_Type <> 'LEASE' then
294 select count(*) into ll_count
295 from fa_additions
296 where lease_id = X_Lease_Id
297 and asset_category_id in
298 (select category_id from fa_categories
299 where category_type = 'LEASEHOLD IMPROVEMENT');
300 --
301 if ll_count > 0 then
302 x_err_code := 'FA_ADD_DELETE_LHOLD_BEFORE_RCL';
303 return;
304 end if;
305 --
306 select count(*) into ll_count
307 from fa_leases
308 where lease_id = X_Lease_Id;
309 --
310 if ll_count > 0 then
311 x_err_code := 'FA_ADD_DELETE_LEASE_BEFORE_RCL';
312 return;
313 end if;
314 end if;
315 --
316 -- no pending retirements
317
318 x_err_stage := 'no pending retirements';
319
320 select count(*)
321 into ll_count
322 from fa_retirements
323 where asset_id = X_Asset_Id
324 and status in ('PENDING', 'REINSTATE', 'PARTIAL');
325 --
326 if ll_count > 0 then
327 x_err_code := 'FA_RET_PENDING_RETIREMENTS';
328 return;
329 end if;
330 --
331
332 x_err_stack := l_old_stack;
333
334 EXCEPTION
335 WHEN others THEN
336 x_err_code := substr(sqlcode, 1, 240);
337 return;
338
339 END Val_Reclass;
340
341 ----------------------------------------------------------------
342 Procedure update_category
343 (x_asset_id in number,
344 x_old_cat_id in number,
345 x_new_cat_id in number,
346 x_err_code in out nocopy varchar2 ,
347 x_err_stage in out nocopy varchar2 ,
348 x_err_stack in out nocopy varchar2 ) IS
349
350 --l_status Boolean := TRUE;
351 l_old_stack varchar2(600);
352 --l_thid number;
353 --l_new_capitalize_flag varchar2(10);
354 --l_new_Category_Type varchar2(30);
355 --l_cat_flex_struct NUMBER;
356 --l_cat_segs FA_RX_SHARED_PKG.Seg_Array;
357 --l_asset_rec fa_additions_v%rowtype;
358
359 --l_return_status BOOLEAN;
360 --l_return_status2 BOOLEAN;
361 --l_return_status3 BOOLEAN;
362
363 l_api_version CONSTANT number := 1.0;
364 l_msg_list VARCHAR2(5) := FND_API.G_FALSE;
365 l_commit_flag VARCHAR2(5) := FND_API.G_FALSE;
366 l_validation_level VARCHAR2(5) := FND_API.G_VALID_LEVEL_FULL;
367 l_debug_flag VARCHAR2(5) := FND_API.G_FALSE;
368 l_calling_fn VARCHAR2(20) := 'update_category';
369
370 l_trans_rec FA_API_TYPES.trans_rec_type;
371 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
372 l_asset_cat_rec_new FA_API_TYPES.asset_cat_rec_type;
373 l_recl_opt_rec FA_API_TYPES.reclass_options_rec_type;
374 l_count NUMBER;
375 l_return_status VARCHAR2(100);
376 l_msg_count NUMBER := 0;
377 l_msg_data VARCHAR2(4000);
378
379 Begin
380
381 x_err_code := '0';
382 l_old_stack := x_err_stack ;
383 x_err_stack := x_err_stack||'->Updating Asset Catgeory';
384
385 select bc.book_type_code
386 into l_asset_hdr_rec.book_type_code
387 from fa_books bk,
388 fa_book_controls bc
389 where bk.asset_id = x_asset_id
390 and bk.book_type_code = bc.book_type_code
391 and bk.transaction_header_id_out is null
392 and bc.book_class = 'CORPORATE';
393
394 l_asset_hdr_rec.asset_id := x_asset_id;
395 l_asset_cat_rec_new.category_id := x_new_cat_id;
396 x_err_stage := 'Performing the Recalssification';
397
398 -- BMR: validation is removed because most of the variables wer based
399 -- on selects not need for api call
400
401 FA_RECLASS_PUB.do_reclass (
402 p_api_version => l_api_version,
403 p_init_msg_list => l_msg_list,
404 p_commit => l_commit_flag,
405 p_validation_level => l_validation_level,
406 p_calling_fn => l_calling_fn,
407 x_return_status => l_return_status,
408 x_msg_count => l_msg_count,
409 x_msg_data => l_msg_data,
410 -- api parameters
411 px_trans_rec => l_trans_rec,
412 px_asset_hdr_rec => l_asset_hdr_rec,
413 px_asset_cat_rec_new => l_asset_cat_rec_new,
414 p_recl_opt_rec => l_recl_opt_rec );
415
416 if not(l_return_status <> 'S') then
417 x_err_code := substr(fnd_msg_pub.get(fnd_msg_pub.G_FIRST, fnd_api.G_TRUE), 1, 512);
418 end if;
419
420 x_err_stack := l_old_stack;
421
422 Exception
423 When others then
424 x_err_code := substr(sqlcode,1, 240);
425 rollback;
426 return;
427
428 End;
429
430
431 ---------------------------------------------------------------
432 PROCEDURE do_transfer(
433 p_asset_id in number,
434 p_book_type_code in varchar2,
435 p_new_hr_dist_set_id in number,
436 p_transaction_date in date,
437 x_err_code out nocopy varchar2 ) IS
438
439 CURSOR GET_DIST_HIST IS
440 SELECT
441 DH.ROWID,
442 DH.DISTRIBUTION_ID,
443 DH.CODE_COMBINATION_ID,
444 DH.UNITS_ASSIGNED,
445 DH.LOCATION_ID,
446 DH.DATE_EFFECTIVE,
447 DH.ASSIGNED_TO,
448 DH.TRANSACTION_HEADER_ID_IN
449 FROM
450 FA_DISTRIBUTION_HISTORY DH,
451 FA_BOOK_CONTROLS BC
452 WHERE
453 DH.ASSET_ID = p_asset_id AND
454 DH.BOOK_TYPE_CODE = BC.DISTRIBUTION_SOURCE_BOOK AND
455 BC.BOOK_TYPE_CODE = p_book_type_code AND
456 DH.DATE_INEFFECTIVE IS NULL AND
457 DH.RETIREMENT_ID IS NULL;
458
459 CURSOR get_hr_dist( p_txn_units in number ) is
460 SELECT
461 (DH.DISTRIBUTION_LINE_PERCENTAGE/100)* p_txn_units new_units,
462 DH.CODE_COMBINATION_ID expense_ccid,
463 DH.LOCATION_ID,
464 DH.ASSIGNED_TO
465 FROM
466 FA_HIERARCHY_DISTRIBUTIONS DH,
467 FA_ADDITIONS A
468 WHERE
469 A.ASSET_ID = p_asset_id AND
470 DH.DIST_SET_ID = p_new_hr_dist_set_id;
471
472
473 l_return_status varchar2(1);
474 l_msg_count number:= 0;
475 l_msg_data varchar2(4000);
476
477 l_trans_rec fa_api_types.trans_rec_type;
478 l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
479
480 l_asset_dist_tbl fa_api_types.asset_dist_tbl_type;
481 i binary_integer;
482 tfr_error EXCEPTION;
483 Begin
484
485 l_asset_hdr_rec.asset_id := p_asset_id;
486 l_asset_hdr_rec.book_type_code := p_book_type_code;
487
488 l_trans_rec.transaction_date_entered := p_transaction_date;
489
490 -- source distribution
491 for dist_hist_rec in get_dist_hist loop
492 l_asset_dist_tbl.delete;
493 i := 0;
494 i := i+1;
495 l_asset_dist_tbl(i).distribution_id := dist_hist_rec.distribution_id;
496 l_asset_dist_tbl(i).transaction_units := dist_hist_rec.units_assigned * -1;
497
498
499 -- destination distribution
500 for dist_set_rec in get_hr_dist(dist_hist_rec.units_assigned) loop
501 i := i+1;
502 l_asset_dist_tbl(i).distribution_id := NULL;
503 l_asset_dist_tbl(i).transaction_units := dist_set_rec.new_units;
504 l_asset_dist_tbl(i).assigned_to := dist_set_rec.assigned_to;
505 l_asset_dist_tbl(i).expense_ccid := dist_set_rec.expense_ccid;
506 l_asset_dist_tbl(i).location_ccid := dist_set_rec.location_id;
507 end loop;
508
509 l_msg_count := null;
510 l_msg_data := null;
511 l_return_status := null;
512 FA_TRANSFER_PUB.do_transfer(
513 p_api_version => 1.0,
514 p_init_msg_list => FND_API.G_FALSE,
515 p_commit => FND_API.G_FALSE,
516 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
517 p_calling_fn => NULL,
518 x_return_status => l_return_status,
519 x_msg_count => l_msg_count,
520 x_msg_data => l_msg_data,
521 px_trans_rec => l_trans_rec,
522 px_asset_hdr_rec => l_asset_hdr_rec,
523 px_asset_dist_tbl => l_asset_dist_tbl);
524
525 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
526 x_err_code := substr(fnd_msg_pub.get(fnd_msg_pub.G_FIRST, fnd_api.G_TRUE), 1, 512);
527 return;
528 end if;
529 end loop;
530
531 End;
532
533
534 -- ------------------------------------------
535 PROCEDURE do_adjustment( px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
536 px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
537 x_new_life IN NUMBER,
538 p_amortize_flag IN VARCHAR2,
539 x_err_code OUT NOCOPY VARCHAR2 ) IS
540
541 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
542 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
543 l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
544 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
545 l_inv_tbl FA_API_TYPES.inv_tbl_type;
546 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
547 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
548 l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
549 l_inv_rec FA_API_TYPES.inv_rec_type;
550 l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
551 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
552
553 l_return_status VARCHAR2(1);
554 l_mesg_count number := 0;
555 l_mesg_len number;
556 l_mesg varchar2(4000);
557
558 Cursor C_books is
559 select basic_rate,
560 adjusted_rate,
561 production_capacity,
562 deprn_method_code
563 from fa_books
564 where asset_id = px_asset_hdr_rec.asset_id
565 and book_type_code = px_asset_hdr_rec.book_type_code
566 and date_ineffective is null;
567
568 BEGIN
569
570
571 if p_amortize_flag = 'NO' then
572 px_trans_rec.amortization_start_date := null;
573 px_trans_rec.transaction_subtype := 'EXPENSED';
574 else
575 px_trans_rec.transaction_subtype := 'AMORTIZED';
576 end if;
577
578 l_asset_fin_rec_adj.life_in_months := x_new_life;
579
580 FA_ADJUSTMENT_PUB.do_adjustment
581 (p_api_version => 1.0,
582 p_init_msg_list => FND_API.G_FALSE,
583 p_commit => FND_API.G_FALSE,
584 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
585 x_return_status => l_return_status,
586 x_msg_count => l_mesg_count,
587 x_msg_data => l_mesg,
588 p_calling_fn => 'fa_cua_mass_update1_pkg.do_adjustment',
589 px_trans_rec => px_trans_rec,
590 px_asset_hdr_rec => px_asset_hdr_rec,
591 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
592 x_asset_fin_rec_new => l_asset_fin_rec_new,
593 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
594 px_inv_trans_rec => l_inv_trans_rec,
595 px_inv_tbl => l_inv_tbl,
596 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
597 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
598 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
599 p_group_reclass_options_rec => l_group_reclass_options_rec );
600
601
602
603 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
604 x_err_code := substr(fnd_msg_pub.get(fnd_msg_pub.G_FIRST, fnd_api.G_TRUE), 1, 512);
605 return;
606 end if;
607
608 EXCEPTION
609 when others then
610 x_err_code := substr(sqlerrm, 1, 200);
611 return;
612
613 END do_adjustment;
614
615 -- -------------------------------------------------------------
616 -- Process_Conc
617 -- Called by the concurrent program to process batches. This
618 -- procedure serves as a wrapper to the Process procedure.
619 -- -------------------------------------------------------------
620
621 PROCEDURE Proc_Conc( ERRBUF OUT NOCOPY VARCHAR2,
622 RETCODE OUT NOCOPY VARCHAR2,
623 x_from_batch_number IN NUMBER,
624 x_to_batch_number IN NUMBER) IS
625
626 l_dummy varchar2(1);
627 l_request_id number;
628 l_ret_value number;
629
630 CURSOR l_batch_csr IS
631 SELECT batch_id,
632 decode(amortize_flag,'Y','YES','NO') amortize_flag,
633 amortization_date,
634 transaction_name,
635 attribute_category,
636 attribute1,
637 attribute2,
638 attribute3,
639 attribute4,
640 attribute5,
641 attribute6,
642 attribute7,
643 attribute8,
644 attribute9,
645 attribute10,
646 attribute11,
647 attribute12,
648 attribute13,
649 attribute14,
650 attribute15
651 FROM fa_mass_update_batch_headers
652 WHERE status_code in ('IP','P','PP','R')
653 AND batch_number >= nvl(X_from_Batch_number,batch_number)
654 AND batch_number <= nvl(X_to_batch_number,batch_number)
655 order by creation_date;
656 --FOR UPDATE NOWAIT;
657
658 l_trans_rec FA_API_TYPES.trans_rec_type;
659
660 BEGIN
661
662 retcode := '0';
663 BEGIN
664
665 l_trans_rec.mass_reference_id := fnd_global.conc_request_id;
666 l_trans_rec.who_info.last_update_date := sysdate;
667 l_trans_rec.who_info.last_updated_by := fnd_global.user_id;
668 l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by;
669 l_trans_rec.who_info.creation_date := sysdate;
670 l_trans_rec.who_info.last_update_login := fnd_global.login_id;
671
672
673 FOR l_batch_rec IN l_batch_csr LOOP
674
675 update fa_mass_update_batch_headers
676 set status_code = 'IP'
677 where batch_id = l_batch_rec.batch_id;
678
679 -- assign attribute values to global_variables
680 fa_cua_asset_apis.g_process_batch := 'Y';
681 fa_cua_asset_apis.g_transaction_name := l_batch_rec.transaction_name;
682 fa_cua_asset_apis.g_attribute_category := l_batch_rec.attribute_category;
683 fa_cua_asset_apis.g_attribute1 := l_batch_rec.attribute1;
684 fa_cua_asset_apis.g_attribute2 := l_batch_rec.attribute2;
685 fa_cua_asset_apis.g_attribute3 := l_batch_rec.attribute3;
686 fa_cua_asset_apis.g_attribute4 := l_batch_rec.attribute4;
687 fa_cua_asset_apis.g_attribute5 := l_batch_rec.attribute5;
688 fa_cua_asset_apis.g_attribute6 := l_batch_rec.attribute6;
689 fa_cua_asset_apis.g_attribute7 := l_batch_rec.attribute7;
690 fa_cua_asset_apis.g_attribute8 := l_batch_rec.attribute8;
691 fa_cua_asset_apis.g_attribute9 := l_batch_rec.attribute9;
692 fa_cua_asset_apis.g_attribute10 := l_batch_rec.attribute10;
693 fa_cua_asset_apis.g_attribute11 := l_batch_rec.attribute11;
694 fa_cua_asset_apis.g_attribute12 := l_batch_rec.attribute12;
695 fa_cua_asset_apis.g_attribute13 := l_batch_rec.attribute13;
696 fa_cua_asset_apis.g_attribute14 := l_batch_rec.attribute14;
697 fa_cua_asset_apis.g_attribute15 := l_batch_rec.attribute15;
698
699 l_trans_rec.transaction_name := l_batch_rec.transaction_name;
700 l_trans_rec.desc_flex.attribute_category_code := l_batch_rec.attribute_category;
701 l_trans_rec.desc_flex.attribute1 := l_batch_rec.attribute1;
702 l_trans_rec.desc_flex.attribute2 := l_batch_rec.attribute2;
703 l_trans_rec.desc_flex.attribute3 := l_batch_rec.attribute3;
704 l_trans_rec.desc_flex.attribute4 := l_batch_rec.attribute4;
705 l_trans_rec.desc_flex.attribute5 := l_batch_rec.attribute5;
706 l_trans_rec.desc_flex.attribute6 := l_batch_rec.attribute6;
707 l_trans_rec.desc_flex.attribute7 := l_batch_rec.attribute7;
708 l_trans_rec.desc_flex.attribute8 := l_batch_rec.attribute8;
709 l_trans_rec.desc_flex.attribute9 := l_batch_rec.attribute9;
710 l_trans_rec.desc_flex.attribute10 := l_batch_rec.attribute10;
711 l_trans_rec.desc_flex.attribute11 := l_batch_rec.attribute11;
712 l_trans_rec.desc_flex.attribute12 := l_batch_rec.attribute12;
713 l_trans_rec.desc_flex.attribute13 := l_batch_rec.attribute13;
714 l_trans_rec.desc_flex.attribute14 := l_batch_rec.attribute14;
715 l_trans_rec.desc_flex.attribute15 := l_batch_rec.attribute15;
716 l_trans_rec.amortization_start_date := l_batch_rec.amortization_date;
717
718 process_batch( px_trans_rec => l_trans_rec,
719 p_batch_id => l_batch_rec.batch_id,
720 p_amortize_flag => l_batch_rec.amortize_flag);
721
722
723 -- check if there are any line unprocessed or Rejected
724 l_dummy := 'N';
725
726 Begin
727 select 'Y'
728 into l_dummy
729 from fa_mass_update_batch_details
730 where batch_id = l_batch_rec.batch_id
731 and status_code in ('P','R')
732 and rownum = 1;
733 Exception
734 When others then
735 null;
736 End ;
737
738 if l_dummy = 'Y' then
739
740 update fa_mass_update_batch_headers
741 set status_code = 'R' -- Rejected Processed
742 , concurrent_request_id = l_Request_ID
743 , last_updated_by = fnd_global.login_id
744 , last_update_date = sysdate
745 , last_update_login = fnd_global.login_id
746 where batch_id = l_batch_rec.batch_id;
747 else
748 update fa_mass_update_batch_headers
749 set status_code = 'CP' -- Completetly Processed
750 , concurrent_request_id = l_Request_ID
751 , last_updated_by = fnd_global.login_id
752 , last_update_date = sysdate
753 , last_update_login = fnd_global.login_id
754 where batch_id = l_batch_rec.batch_id;
755 end if;
756 END LOOP;
757 commit;
758
759 EXCEPTION
760 WHEN OTHERS THEN
761 RETCODE := '2';
762 ERRBUF := SQLERRM;
763 END;
764
765
766
767 if RETCODE = '0' then
768 l_ret_value := fnd_request.submit_request('CUA','CUAMUPR',null,null,FALSE, l_request_id);
769 if (l_ret_value = 0) then
770 ERRBUF := 'Failed to Submit Mass Update report';
771 else
772 commit;
773 end if;
774 end if;
775
776
777 END Proc_Conc;
778
779
780 -- -------------------------------------------------------------
781 -- Process
782 -- This procedure can be called online using the Mass Update
783 -- Batches form or as a concurrent program.
784 --
785 -- Commit is performed once all the Asset records are processed
786 -- in a batch.
787 -- If even one asset record is rejected then the Batch Status is
788 -- Set to Rejected and changes for accepted assets is rolled back
789 -- as if they had not been processed at all
790 -- -------------------------------------------------------------
791
792 PROCEDURE process_batch( px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
793 p_batch_id IN NUMBER ,
794 p_amortize_flag IN VARCHAR2 ) IS
795 l_asset_id number;
796 l_book varchar2(15);
797 l_dummy varchar2(1);
798 l_status boolean;
799
800 l_transaction_date date;
801
802 l_err_code varchar2(600);
803 l_err_stage varchar2(600);
804 l_err_stack varchar2(600);
805 l_error_flag varchar2(1) := 'N'; -- Flag to indicate if any asset record has errored.
806 h_mesg_str varchar2(2000);
807
808 -- -----------------------
809 -- Cursor declarations
810 -- -----------------------
811
812 CURSOR l_get_books is
813 SELECT distinct book_type_code
814 from fa_mass_update_batch_details
815 WHERE batch_id = p_Batch_ID
816 and status_code in ('P','R')
817 and apply_flag = 'Y';
818
819 CURSOR l_get_assets_csr is
820 SELECT distinct asset_id
821 from fa_mass_update_batch_details
822 WHERE batch_id = p_Batch_ID
823 and book_type_code = l_book
824 and status_code in ('P','R')
825 and apply_flag = 'Y';
826
827
828 l_message_name varchar2(240);
829
830
831 -- We create a new record type to store the error information
832 -- for each batch line that fails to validate. A PL/SQL table
833 -- will be used to store all the failed lines. We need to do
834 -- this because we want to process all the lines even if an
835 -- error has occured in one of the lines
836 --
837 TYPE ErrorRecTyp IS RECORD(
838 attribute_name VARCHAR2(30),
839 rejection_reason VARCHAR2(250) );
840
841 TYPE ErrorTabTyp IS TABLE OF ErrorRecTyp
842 INDEX BY BINARY_INTEGER;
843
844 l_Error_Tab ErrorTabTyp; -- error table
845 l_return_status BOOLEAN;
846
847 error_exp exception;
848 l_attribute_name varchar2(30);
849 BEGIN
850
851 fnd_message.set_name('OFA','FA_LIFE_CHANGE_EXCEPTION');
852 fnd_message.set_token('AMORT_DATE',to_char(px_trans_rec.amortization_start_date,'DD/MM/YYYY'));
853 h_mesg_str := fnd_message.get;
854 fa_rx_conc_mesg_pkg.out(h_mesg_str);
855
856 FOR l_get_book_rec IN l_get_books LOOP
857
858 l_book := l_get_book_rec.book_type_code;
859
860 select greatest(calendar_period_open_date,
861 least(sysdate, calendar_period_close_date))
862 into l_transaction_date
863 from fa_deprn_periods
864 where book_type_code = l_book
865 and period_close_date is null;
866
867 FOR l_asset_rec IN l_get_assets_csr LOOP
868 l_asset_id := l_asset_rec.asset_id;
869
870 l_err_code := '0';
871
872 /* moved this update here for bugfix 1389275 */
873 -- Changes the status of the Asset detail records to In Process
874 update fa_mass_update_batch_details
875 set status_code = 'IP'
876 where batch_id = p_batch_id
877 and asset_id = l_asset_id
878 and book_type_code = l_book;
879
880 if not FA_TRX_APPROVAL_PKG.faxcat(
881 X_book => l_book,
882 X_asset_id => l_asset_id,
883 X_trx_type => 'RECLASS',
884 X_trx_date => l_transaction_date,
885 X_init_message_flag=> 'YES') then
886
887 l_err_code := substr(fnd_msg_pub.get(fnd_msg_pub.G_FIRST, fnd_api.G_TRUE), 1, 512);
888
889 if nvl(l_err_code, '0') = '0' then
890 -- check_pending_batches procedure returned false;
891 l_err_code := 'CUA_PENDING_BOOK';
892 end if;
893
894 else
895 px_trans_rec.transaction_date_entered:= l_transaction_date;
896 process_asset( px_trans_rec => px_trans_rec,
897 p_batch_id => p_batch_id,
898 p_asset_id => l_asset_id,
899 p_book => l_book,
900 p_amortize_flag => p_amortize_flag,
901 x_err_code => l_err_code,
902 x_err_attr_name => l_attribute_name );
903
904 if l_err_code <> '0' then
905 l_error_flag := 'Y';
906
907 if ( (substrb(l_err_code, 1,3) = 'FA_') OR
908 (substrb(l_err_code, 1,3) = 'CUA')) then
909
910 if (substrb(l_err_code, 1,3) = 'FA_') then
911 fnd_message.set_name ('OFA',l_err_code);
912 l_message_name := substrb(fnd_message.get,1,240);
913 end if;
914
915 if (substrb(l_err_code, 1,3) = 'CUA') then
916 fnd_message.set_name ('CUA',l_err_code);
917 l_message_name := substrb(fnd_message.get,1,240);
918 end if;
919 else
920 if substr(l_err_code,1,1) = '-' then
921 l_message_name := substrb(sqlerrm(l_err_code),1,240);
922 else
923 l_message_name := l_err_code;
924 end if;
925 end if;
926
927 -- Populate Table with rejection reasons
928 l_error_tab(l_asset_id).attribute_name:= l_attribute_name;
929 l_error_tab(l_asset_id).rejection_reason := l_message_name;
930 end if;
931 end if;
932
933 END LOOP;
934 END LOOP;
935
936 if l_error_flag = 'Y' then
937 rollback;
938
939 FOR l_get_book_rec IN l_get_books LOOP
940 l_book := l_get_book_rec.book_type_code;
941
942 FOR l_asset_rec IN l_get_assets_csr LOOP
943 l_asset_id := l_asset_rec.asset_id;
944
945 if l_error_tab.exists(l_asset_id) then
946 update fa_mass_update_batch_details
947 set rejection_reason = l_error_tab(l_asset_id).rejection_reason,
948 concurrent_request_id = px_trans_rec.mass_reference_id,
949 status_code = 'R',
950 last_updated_by = px_trans_rec.who_info.last_updated_by,
951 last_update_date = px_trans_rec.who_info.last_update_date,
952 last_update_login = px_trans_rec.who_info.last_update_login
953 where asset_id = l_asset_id
954 and book_type_code = l_book
955 and batch_id = p_batch_id
956 and attribute_name = nvl(l_attribute_name, attribute_name)
957 and status_code in ('P','R') -- since rollback will revert the update to 'IP'
958 and nvl(apply_flag,'N') = 'Y';
959 end if;
960
961 END LOOP;
962 END LOOP;
963
964 else -- No Asset record failed. Therefore update the status to Accepted for all records
965 update fa_mass_update_batch_details
966 set rejection_reason = null,
967 concurrent_request_id = px_trans_rec.mass_reference_id,
968 status_code = 'A',
969 last_updated_by = px_trans_rec.who_info.last_updated_by,
970 last_update_date = px_trans_rec.who_info.last_update_date,
971 last_update_login = px_trans_rec.who_info.last_update_login
972 where batch_id = p_batch_id
973 and status_code = 'IP' -- changed to 'IP'from ( 'P', 'R')- bugfix 1389275
974 and nvl(apply_flag,'N') = 'Y';
975 end if;
976
977 End;
978
979
980 PROCEDURE process_asset( px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
981 p_batch_id IN NUMBER,
982 p_asset_id IN NUMBER,
983 p_book IN VARCHAR2,
984 p_amortize_flag IN VARCHAR2,
985 x_err_code OUT NOCOPY VARCHAR2,
986 x_err_attr_name OUT NOCOPY VARCHAR2 ) IS
987
988 l_asset_id number;
989 l_new_life number;
990 l_old_life number;
991 l_amortization_date date;
992 prior_date_effective DATE;
993 check_prior_addition number;
994 check_flag varchar2(2);
995 l_dummy varchar2(1);
996 l_amortize_flag varchar2(3) ;
997
998 CURSOR l_get_Lines_csr IS
999 SELECT attribute_name,
1000 attribute_old_id,
1001 attribute_new_id,
1002 derived_from_entity_id,
1003 derived_from_entity,
1004 status_code
1005 FROM fa_mass_update_batch_details
1006 WHERE batch_id = p_batch_id
1007 and asset_id = p_asset_id
1008 and book_type_code = p_book
1009 AND apply_flag = 'Y'
1010 AND STATUS_CODE in ('IP', 'P', 'R') -- added 'IP' as part of 138927
1011 order by decode(attribute_name,
1012 'LIFE_END_DATE',1,
1013 'SERIAL_NUMBER',2,
1014 'ASSET_KEY',3,
1015 'LEASE_NUMBER',4,
1016 'DISTRIBUTION',5,
1017 'CATEGORY',6)
1018 FOR UPDATE NOWAIT;
1019
1020 h_status boolean;
1021
1022 -- api variables
1023 l_return_status VARCHAR2(100);
1024 l_msg_count NUMBER:= 0;
1025 l_msg_data VARCHAR2(4000);
1026 l_api_version CONSTANT number := 1.0;
1027 l_msg_list VARCHAR2(5) := FND_API.G_FALSE;
1028 l_commit_flag VARCHAR2(5) := FND_API.G_FALSE;
1029 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
1030 l_calling_fn VARCHAR2(20) := 'ASSET HIERARACHY';
1031
1032 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
1033 l_asset_cat_rec_new FA_API_TYPES.asset_cat_rec_type;
1034 l_recl_opt_rec FA_API_TYPES.reclass_options_rec_type;
1035
1036 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
1037 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
1038 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
1039 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
1040
1041 -- Adjustment parameters
1042 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
1043 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
1044 l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
1045 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
1046 l_inv_tbl FA_API_TYPES.inv_tbl_type;
1047 l_inv_rate_tbl FA_API_TYPES.inv_rate_tbl_type;
1048 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
1049 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
1050 l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
1051 l_inv_rec FA_API_TYPES.inv_rec_type;
1052 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
1053
1054 l_request_id number(15);
1055
1056 l_update_attribute varchar2(1):= 'N';
1057
1058 l_err_stage varchar2(600);
1059 l_err_stack varchar2(600);
1060 Begin
1061
1062
1063 x_err_code := '0';
1064 -- Intialize Header variables
1065 l_asset_desc_rec := null;
1066 l_asset_hdr_rec := null;
1067 l_asset_hdr_rec.asset_id := p_asset_id;
1068 l_asset_hdr_rec.book_type_code := p_book;
1069 px_trans_rec.calling_interface := 'ASSET_HIERARCHY';
1070
1071 FOR l_get_lines_rec in l_get_lines_csr LOOP
1072
1073 -- cleanup for each loop
1074 fa_cua_asset_apis.g_derive_from_entity := null;
1075 fa_cua_asset_apis.g_derive_from_entity_value := null;
1076 l_update_attribute := 'N';
1077
1078 if l_get_lines_rec.attribute_name = 'CATEGORY' then
1079 if (l_get_lines_rec.attribute_old_id <> l_get_lines_rec.attribute_new_id)
1080 and (l_get_lines_rec.attribute_new_id is not null) then
1081
1082 update_category( p_asset_id,
1083 to_number(l_get_lines_rec.attribute_old_id) ,
1084 to_number(l_get_lines_rec.attribute_new_id),
1085 x_err_code,l_err_stage,l_err_stack);
1086 if x_err_code <> '0' then
1087 x_err_attr_name := l_get_lines_rec.attribute_name;
1088 return;
1089 end if;
1090
1091 end if;
1092 end if;
1093
1094 if l_get_lines_rec.attribute_name = 'LEASE_NUMBER' then
1095 if (l_get_lines_rec.attribute_old_id <> l_get_lines_rec.attribute_new_id) or
1096 (l_get_lines_rec.attribute_old_id is null) then
1097
1098 l_asset_desc_rec.lease_id := to_number(l_get_lines_rec.attribute_new_id);
1099 l_update_attribute := 'Y';
1100
1101 end if;
1102 end if;
1103
1104 if l_get_lines_rec.attribute_name = 'SERIAL_NUMBER' then
1105 if (l_get_lines_rec.attribute_old_id <> l_get_lines_rec.attribute_new_id) or
1106 (l_get_lines_rec.attribute_old_id is null) then
1107
1108 l_asset_desc_rec.serial_number := l_get_lines_rec.attribute_new_id;
1109 l_update_attribute := 'Y';
1110
1111 end if;
1112 end if;
1113
1114 if l_get_lines_rec.attribute_name = 'ASSET_KEY' then
1115 if (l_get_lines_rec.attribute_old_id <> l_get_lines_rec.attribute_new_id) or
1116 (l_get_lines_rec.attribute_old_id is null) then
1117
1118 l_asset_desc_rec.asset_key_ccid := to_number(l_get_lines_rec.attribute_new_id);
1119 l_update_attribute := 'Y';
1120
1121 end if;
1122 end if;
1123
1124
1125 if l_update_attribute = 'Y' then
1126 FA_ASSET_DESC_PUB.update_desc(
1127 p_api_version => l_api_version,
1128 p_init_msg_list => l_msg_list,
1129 p_commit => l_commit_flag,
1130 p_validation_level => l_validation_level,
1131 x_return_status => l_return_status,
1132 x_msg_count => l_msg_count,
1133 x_msg_data => l_msg_data,
1134 p_calling_fn => 'fa_cua_mass_update1_pkg.process_asset_batch',
1135 px_trans_rec => px_trans_rec,
1136 px_asset_hdr_rec => l_asset_hdr_rec,
1137 px_asset_desc_rec_new => l_asset_desc_rec,
1138 px_asset_cat_rec_new => l_asset_cat_rec);
1139
1140 if ( l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1141 x_err_code := substr(fnd_msg_pub.get(fnd_msg_pub.G_FIRST, fnd_api.G_TRUE), 1, 512);
1142 x_err_attr_name := l_get_lines_rec.attribute_name;
1143 return;
1144 end if;
1145 end if;
1146
1147 if l_get_lines_rec.attribute_name = 'DISTRIBUTION' then
1148 if ( l_get_lines_rec.attribute_old_id <> l_get_lines_rec.attribute_new_id) or
1149 ( (l_get_lines_rec.attribute_old_id is null)
1150 and (l_get_lines_rec.attribute_new_id is not null) ) then
1151
1152 do_transfer(
1153 p_asset_id => p_asset_id,
1154 p_book_type_code => p_book,
1155 p_new_hr_dist_set_id => to_number(l_get_lines_rec.attribute_new_id),
1156 p_transaction_date => nvl(px_trans_rec.amortization_start_date,
1157 px_trans_rec.transaction_date_entered ),
1158 x_err_code => x_err_code );
1159 if x_err_code <> '0' then
1160 x_err_attr_name := l_get_lines_rec.attribute_name;
1161 return;
1162 end if;
1163 end if;
1164 end if;
1165
1166 --
1167 if l_get_lines_rec.attribute_name = 'LIFE_END_DATE' then
1168 if (l_get_lines_rec.attribute_old_id <> l_get_lines_rec.attribute_new_id) or
1169 (l_get_lines_rec.attribute_old_id is null) then
1170 l_new_life := to_number(l_get_lines_rec.attribute_new_id);
1171 l_old_life := to_number(l_get_lines_rec.attribute_old_id);
1172
1173 l_amortization_date := px_trans_rec.amortization_start_date;
1174
1175 -- If Asset has not started depreciating and user is trying to make an Amortized
1176 -- Adjustment, it would fail. Also check if there are no Amortized Changes.
1177 -- Therefore proactively set the Adjustment to Expensed
1178 -- so that the Life Change can go through.
1179
1180 -- assign Life Derivation info to global variables. The global variables
1181 -- are refernced from DB trigger on FA_TRANSACTION_HEADERS to populate
1182 -- FA_LIFE_DERIVATION_INFO;
1183
1184 fa_cua_asset_apis.g_derive_from_entity := l_get_lines_rec.derived_from_entity;
1185 fa_cua_asset_apis.g_derive_from_entity_value := l_get_lines_rec.derived_from_entity_id;
1186
1187 do_adjustment ( px_trans_rec => px_trans_rec,
1188 px_asset_hdr_rec => l_asset_hdr_rec,
1189 x_new_life => l_new_life,
1190 p_amortize_flag => p_amortize_flag,
1191 x_err_code => x_err_code );
1192 if x_err_code <> '0' then
1193 x_err_attr_name := l_get_lines_rec.attribute_name;
1194 return ;
1195 end if;
1196 end if;
1197 end if;
1198
1199 -- Now restore to Pending/Rejected
1200 update fa_mass_update_batch_details
1201 set status_code = l_get_lines_rec.status_code
1202 where batch_id = p_batch_id
1203 and asset_id = p_asset_id
1204 and book_type_code = p_book;
1205
1206 END LOOP;
1207
1208 Exception
1209 when OTHERS then
1210 x_err_code := substr(sqlerrm, 1, 240);
1211 End process_asset;
1212
1213
1214
1215 END FA_CUA_MASS_UPDATE1_PKG;