1 PACKAGE BODY FARX_RR AS
2 /* $Header: FARXRRB.pls 120.5 2006/03/21 20:26:17 dfred ship $ */
3
4
5 -- Mass reclass record from fa_mass_reclass table.
6 mr_rec FA_MASS_REC_UTILS_PKG.mass_reclass_rec;
7
8 -- Table of asset records.
9 -- (Stores book_type_code as well, and thus one asset will appear multiple
10 -- times if the asset belongs to multiple books.)
11 a_tbl FA_MASS_REC_UTILS_PKG.asset_table;
12 -- Index into the asset table, a_tbl.
13 a_index NUMBER := 0;
14
15 -- Number of assets(disregaring book_type_code) stored in a_tbl.
16 -- Reset at every 200 assets.
17 g_asset_count NUMBER := 0;
18
19 /* a_index <> g_asset_count if asset belongs to more than one book. */
20
21 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
22
23
24 /*====================================================================================+
25 | PROCEDURE Review_Reclass |
26 +=====================================================================================*/
27
28 PROCEDURE Review_Reclass(
29 X_Mass_Reclass_Id IN NUMBER,
30 X_RX_Flag IN VARCHAR2 := 'NO',
31 retcode OUT NOCOPY NUMBER,
32 errbuf OUT NOCOPY VARCHAR2) IS
33
34 -- cursor to fetch the current and review status
35 CURSOR get_status IS
36 SELECT lu_rev.meaning,
37 lu_curr.meaning
38 FROM fa_lookups lu_rev,
39 fa_lookups lu_curr
40 WHERE lu_rev.lookup_type = 'MASS_TRX_STATUS' AND
41 lu_rev.lookup_code = 'COMPLETED'
42 AND lu_curr.lookup_type = 'MASS_TRX_STATUS' AND
43 lu_curr.lookup_code = mr_rec.status;
44
45 -- cursor to get category flexfield structure.
46 CURSOR get_cat_flex_struct IS
47 SELECT category_flex_structure
48 FROM fa_system_controls;
49
50 -- cursor to fetch mass reclass record from fa_mass_reclass
51 CURSOR mass_reclass IS
52 SELECT mr.mass_reclass_id,
53 mr.book_type_code,
54 mr.transaction_date_entered,
55 mr.concurrent_request_id,
56 mr.status,
57 mr.asset_type,
58 mr.location_id,
59 mr.employee_id,
60 mr.asset_key_id,
61 mr.from_cost,
62 mr.to_cost,
63 mr.from_asset_number,
64 mr.to_asset_number,
65 mr.from_date_placed_in_service,
66 mr.to_date_placed_in_service,
67 mr.from_category_id,
68 mr.to_category_id,
69 mr.segment1_low, mr.segment2_low, mr.segment3_low, mr.segment4_low,
70 mr.segment5_low, mr.segment6_low, mr.segment7_low, mr.segment8_low,
71 mr.segment9_low, mr.segment10_low, mr.segment11_low, mr.segment12_low,
72 mr.segment13_low, mr.segment14_low, mr.segment15_low, mr.segment16_low,
73 mr.segment17_low, mr.segment18_low, mr.segment19_low, mr.segment20_low,
74 mr.segment21_low, mr.segment22_low, mr.segment23_low, mr.segment24_low,
75 mr.segment25_low, mr.segment26_low, mr.segment27_low, mr.segment28_low,
76 mr.segment29_low, mr.segment30_low,
77 mr.segment1_high, mr.segment2_high, mr.segment3_high, mr.segment4_high,
78 mr.segment5_high, mr.segment6_high, mr.segment7_high, mr.segment8_high,
79 mr.segment9_high, mr.segment10_high, mr.segment11_high, mr.segment12_high,
80 mr.segment13_high, mr.segment14_high, mr.segment15_high, mr.segment16_high,
81 mr.segment17_high, mr.segment18_high, mr.segment19_high, mr.segment20_high,
82 mr.segment21_high, mr.segment22_high, mr.segment23_high, mr.segment24_high,
83 mr.segment25_high, mr.segment26_high, mr.segment27_high, mr.segment28_high,
84 mr.segment29_high, mr.segment30_high,
85 mr.include_fully_rsvd_flag,
86 mr.copy_cat_desc_flag,
87 mr.inherit_deprn_rules_flag,
88 mr.amortize_flag,
89 mr.created_by,
90 mr.creation_date,
91 mr.last_updated_by,
92 mr.last_update_login,
93 mr.last_update_date
94 FROM fa_mass_reclass mr
95 WHERE mass_reclass_id = X_Mass_Reclass_Id;
96
97 -- asset-book pairs that were reclassified by mass reclass program.
98 -- use cursor 1 if inherit_deprn_rules_flag = 'NO'
99 CURSOR mass_reclass_assets1 IS
100 SELECT ad.asset_id,
101 ad.asset_number,
102 ad.description,
103 bk.book_type_code,
104 ah.category_id,
105 NULL, -- category in concatenated format.
106 bk.prorate_convention_code,
107 bk.ceiling_name,
108 bk.deprn_method_code,
109 bk.life_in_months,
110 NULL, -- in converted format.
111 bk.basic_rate,
112 NULL, -- in converted format.
113 bk.adjusted_rate,
114 NULL, -- in converted format.
115 bk.bonus_rule,
116 bk.production_capacity,
117 bk.unit_of_measure,
118 bk.depreciate_flag,
119 bk.allowed_deprn_limit,
120 NULL,
121 bk.allowed_deprn_limit_amount,
122 bk.percent_salvage_value,
123 NULL,
124 -- for cost account
125 decode(ah.asset_type, 'CIP', cb.wip_cost_account_ccid,
126 cb.asset_cost_account_ccid),
127 NULL,
128 -- for reserve account
129 decode(ah.asset_type, 'CIP', NULL, cb.reserve_account_ccid),
130 NULL
131 FROM fa_category_books cb,
132 fa_book_controls bc,
133 fa_books bk,
134 fa_asset_history ah,
135 fa_additions ad,
136 fa_transaction_headers th
137 /* mr_rec.conc_request_id will correspond to the request id
138 for the last time the transaction was "run" by the mass
139 reclass program.
140
141 BMR: this is no longer true - see BUG# 2371326
142 for rerunnability we will show all assets/trxs
143
144 WHERE th.mass_reference_id = mr_rec.conc_request_id
145 */
146 WHERE th.mass_transaction_id = mr_rec.mass_reclass_id
147 AND ad.asset_id = th.asset_id
148 AND ah.asset_id = th.asset_id
149 -- transaction_type_code = 'RECLASS' if transaction is after the period
150 -- the asset was added.
151 -- use transaction_header_id comparison, since there could be more than
152 -- one transaction in the period the asset is added.
153 --AND ((th.transaction_type_code = 'RECLASS' AND
154 -- ah.transaction_header_id_in = th.transaction_header_id) OR
155 -- (th.transaction_type_code <> 'RECLASS' AND
156 -- ah.transaction_header_id_in < th.transaction_header_id AND
157 -- nvl(ah.transaction_header_id_out, th.transaction_header_id + 1) >
158 -- th.transaction_header_id))
159 AND ah.transaction_header_id_in =
160 decode(th.transaction_type_code, 'RECLASS',
161 th.transaction_header_id, ah.transaction_header_id_in)
162 AND ah.transaction_header_id_in <= th.transaction_header_id
163 AND nvl(ah.transaction_header_id_out, th.transaction_header_id + 1) >
164 th.transaction_header_id
165 -- Only corporate book is stored in fa_transaction_headers in case
166 -- of basic reclass only(without redefault.)
167 AND bk.asset_id = th.asset_id
168 AND bk.book_type_code = bc.book_type_code
169 AND bc.book_class IN ('CORPORATE', 'TAX')
170 AND bc.distribution_source_book = th.book_type_code
171 -- Get the book row at the time of reclass run. Need to figure out
172 -- the book row by comparing transaction_header_id's, since only
173 -- basic reclass transaction is recorded in fa_transaction_headers
174 -- table, when redefault is not performed.
175 AND bk.transaction_header_id_in < th.transaction_header_id
176 AND nvl(bk.transaction_header_id_out, th.transaction_header_id + 1) >
177 th.transaction_header_id
178 AND cb.category_id = mr_rec.to_category_id
179 AND cb.book_type_code = bk.book_type_code
180 ORDER BY ad.asset_number, bk.book_type_code;
181
182 -- asset-book pairs that were reclassified by mass reclass program.
183 -- use cursor 2 if inherit_deprn_rules_flag = 'YES'
184 -- the rest of the book information for the asset comes from
185 -- cursor book_info1 or 2.
186 CURSOR mass_reclass_assets2 IS
187 SELECT ad.asset_id,
188 ad.asset_number,
189 ad.description,
190 bk.book_type_code,
191 ah.category_id,
192 -- for cost account
193 decode(ah.asset_type, 'CIP', cb.wip_cost_account_ccid,
194 cb.asset_cost_account_ccid),
195 -- for reserve account
196 decode(ah.asset_type, 'CIP', NULL, cb.reserve_account_ccid),
197 th.transaction_header_id
198 FROM fa_category_books cb,
199 fa_book_controls bc,
200 fa_books bk,
201 fa_asset_history ah,
202 fa_additions ad,
203 fa_transaction_headers th
204 /* mr_rec.conc_request_id will correspond to the request id
205 for the last time the transaction was "run" by the mass
206 reclass program.
207 BMR: this is no longer true - see BUG# 2371326
208 for rerunnability we will show all assets/trxs
209
210 WHERE th.mass_reference_id = mr_rec.conc_request_id
211 */
212 WHERE th.mass_transaction_id = mr_rec.mass_reclass_id
213 -- there are two transactions, 'RECLASS' and 'ADJUSTMENT'
214 AND ((th.transaction_type_code||'' = 'RECLASS') OR
215 (th.transaction_subtype = 'RECLASS'))
216 AND ad.asset_id = th.asset_id
217 AND ah.asset_id = th.asset_id
218 -- transaction_type_code = 'RECLASS' if transaction is after the period
219 -- the asset was added.
220 -- use transaction_header_id comparison, since there could be more than
221 -- one transaction in the period the asset is added.
222 --AND ((th.transaction_type_code = 'RECLASS' AND
223 -- ah.transaction_header_id_in = th.transaction_header_id) OR
224 -- (th.transaction_type_code <> 'RECLASS' AND
225 -- ah.transaction_header_id_in < th.transaction_header_id AND
226 -- nvl(ah.transaction_header_id_out, th.transaction_header_id + 1) >
227 -- th.transaction_header_id))
228 AND ah.transaction_header_id_in =
229 decode(th.transaction_type_code, 'RECLASS',
230 th.transaction_header_id, ah.transaction_header_id_in)
231 AND ah.transaction_header_id_in <= th.transaction_header_id
232 AND nvl(ah.transaction_header_id_out, th.transaction_header_id + 1) >
233 th.transaction_header_id
234 AND bk.asset_id = th.asset_id
235 AND bk.book_type_code = bc.book_type_code
236 AND bc.book_class IN ('CORPORATE', 'TAX')
237 AND bc.distribution_source_book = th.book_type_code
238 -- to select only one book row per book. selects all the currently
239 -- effective books.
240 AND bk.date_ineffective IS NULL
241 AND cb.category_id = mr_rec.to_category_id
242 AND cb.book_type_code = bk.book_type_code
243 ORDER BY ad.asset_number, bk.book_type_code;
244
245 -- to store th.transaction_header_id in the cursor above.
246 -- transaction header id for RECLASS transaction.
247 h_rcl_thid NUMBER(15);
248
249 -- cursor to get the book information.
250 -- book_info1 is used when inherit_deprn_rules_flag = 'YES' but there
251 -- was no actual change made in the depreciation rules
252 -- (because rules remain the same.) fetch the book information
253 -- at the time of reclass transaction.
254 CURSOR book_info1 IS
255 SELECT bk.book_type_code,
256 bk.prorate_convention_code,
257 bk.ceiling_name,
258 bk.deprn_method_code,
259 bk.life_in_months,
260 bk.basic_rate,
261 bk.adjusted_rate,
262 bk.bonus_rule,
263 bk.production_capacity,
264 bk.unit_of_measure,
265 bk.depreciate_flag,
266 bk.allowed_deprn_limit,
267 bk.allowed_deprn_limit_amount,
268 bk.percent_salvage_value
269 FROM fa_books bk
270 WHERE bk.asset_id = a_tbl(a_index).asset_id
271 AND bk.book_type_code = a_tbl(a_index).book_type_code
272 -- Get the book row at the time of reclass run. Need to figure out
273 -- the book row by comparing transaction_header_id's, since only
274 -- basic reclass transaction is recorded in fa_transaction_headers
275 -- table, when redefault is not performed or when rules remain the same.
276 AND bk.transaction_header_id_in < h_rcl_thid
277 AND nvl(bk.transaction_header_id_out, h_rcl_thid + 1) > h_rcl_thid;
278
279 -- cursor to get ADJUSTMENT transaction header id for redefault transaction.
280 CURSOR get_adjust_thid IS
281 SELECT transaction_header_id
282 FROM fa_transaction_headers
283 -- BMR: BUG# 2371326
284 -- for rerunnability we will show all assets/trxs
285 -- WHERE mass_reference_id = mr_rec.conc_request_id
286 WHERE mass_transaction_id = mr_rec.mass_reclass_id
287 AND asset_id = a_tbl(a_index).asset_id
288 AND book_type_code = a_tbl(a_index).book_type_code
289 AND transaction_type_code||'' IN
290 ('ADDITION', 'CIP ADDITION', 'ADJUSTMENT', 'CIP ADJUSTMENT','GROUP ADDITION','GROUP ADJUSTMENT');
291
292 -- to store ADJUSTMENT transaction header id for redefault transaction.
293 h_adj_thid NUMBER(15);
294
295 -- cursor to get the book information.
296 -- book_info2 is used when inherit_deprn_rules_flag = 'YES' and
297 -- when redefault transaction was actually performed and caused
298 -- changes in depreciation rules(a separate ADJUSTMENT transaction
299 -- is recorded in fa_transaction_headers in this case.)
300 CURSOR book_info2 IS
301 SELECT bk.book_type_code,
302 bk.prorate_convention_code,
303 bk.ceiling_name,
304 bk.deprn_method_code,
305 bk.life_in_months,
306 bk.basic_rate,
307 bk.adjusted_rate,
308 bk.bonus_rule,
309 bk.production_capacity,
310 bk.unit_of_measure,
311 bk.depreciate_flag,
312 bk.allowed_deprn_limit,
313 bk.allowed_deprn_limit_amount,
314 bk.percent_salvage_value
315 FROM fa_books bk
316 WHERE bk.asset_id = a_tbl(a_index).asset_id
317 AND bk.book_type_code = a_tbl(a_index).book_type_code
318 AND bk.transaction_header_id_in = h_adj_thid;
319
320 -- cursor to fetch accounting flexfield structure.
321 CURSOR get_acct_flex_struct IS
322 SELECT accounting_flex_structure
323 FROM fa_book_controls
324 WHERE book_type_code = a_tbl(a_index).book_type_code;
325
326 h_request_id NUMBER;
327 h_msg_count NUMBER;
328 h_msg_data VARCHAR2(2000) := NULL;
329 h_review_status_d VARCHAR2(10);
330 h_current_status_d VARCHAR2(10);
331 h_cat_flex_struct NUMBER;
332 h_acct_flex_struct NUMBER;
333 h_cat_segs FA_RX_SHARED_PKG.Seg_Array;
334 h_acct_segs FA_RX_SHARED_PKG.Seg_Array;
335 h_category_id NUMBER(15) := NULL;
336 h_concat_cat VARCHAR2(220);
337 h_debug_flag VARCHAR2(3) := 'NO';
338 -- to keep track of the last asset id that entered the mass_reclass_assets
339 -- cursor loop.
340 h_last_asset NUMBER(15) := NULL;
341 -- indicates whether the book information was found. used only when
342 -- redefault option was set to YES.
343 h_bk_info_found BOOLEAN;
344 -- exception raised from this module and child modules.
345 mrcl_failure EXCEPTION;
346 -- Commit results per every 200 assets.
347 h_commit_level NUMBER := 200;
348
349 BEGIN
350
351 -- Initialize message stacks.
352 FA_SRVR_MSG.Init_Server_Message;
356 IF (g_print_debug) THEN
353 FA_DEBUG_PKG.Initialize;
354
355 -- Set debug flag.
357 h_debug_flag := 'YES';
358 END IF;
359
360 /* Fix for BUG# 1302611 where the rbs_name variable was being
361 interpreted as a literal string rather than using the
362 value of the variable
363 */
364
365 -- Initialize global variables.
366 -- (These are session specific variables, and thus values need to
367 -- be re-initialized.)
368 a_tbl.delete;
369 a_index := 0;
370 g_asset_count := 0;
371
372 -- Get concurrent request id for the mass reclass review request.
373 -- h_request_id is used when request_id is inserted into the interface
374 -- table, fa_mass_reclass_itf.
375 -- Need to fetch request id from fnd_global package instead of fa_mass_reclass
376 -- table, since fa_mass_reclass table stores the latest request id for
377 -- the Preview or Run requests only.
378 h_request_id := fnd_global.conc_request_id;
379
380 -- Fetch mass reclass record information.
381 OPEN mass_reclass;
382 FETCH mass_reclass INTO mr_rec;
383 CLOSE mass_reclass;
384
385 /*===========================================================================
386 Delete rows previously inserted into the interface table with the same
387 request id, if there is any.
388 ===========================================================================*/
389 DELETE FROM fa_mass_reclass_itf
390 WHERE request_id = h_request_id;
391 COMMIT;
392
393 /*===========================================================================
394 Check to make sure current status is 'COMPLETED'
395 ===========================================================================*/
396 OPEN get_status;
397 FETCH get_status INTO h_review_status_d, h_current_status_d;
398 CLOSE get_status;
399
400 IF (h_review_status_d <> h_current_status_d) THEN
401 -- Re-using message for mass reclass program.
402 FA_SRVR_MSG.Add_Message(
403 CALLING_FN => 'FARX_RR.Review_Reclass',
404 NAME => 'FA_MASSRCL_WRONG_STATUS',
405 TOKEN1 => 'CURRENT',
406 VALUE1 => h_current_status_d,
407 TOKEN2 => 'RUNNING',
408 VALUE2 => h_review_status_d);
409 -- Review will complete with error status.
410 RAISE mrcl_failure;
411 END IF;
412
413 /*===========================================================================
414 Insert review records into the interface table.
415 ===========================================================================*/
416
417 /* Get category flex structure. */
418 OPEN get_cat_flex_struct;
419 FETCH get_cat_flex_struct INTO h_cat_flex_struct;
420 CLOSE get_cat_flex_struct;
421
422 /* Fetch asset-book pairs from mass_reclass_assets cursor and insert them
423 into the interface table, fa_mass_reclass_itf. */
424 IF (mr_rec.redefault_flag = 'NO') THEN
425 OPEN mass_reclass_assets1;
426
427 LOOP
428 -- Fetch the asset-book pair into the pl/sql table.
429 a_index := a_index + 1;
430 FETCH mass_reclass_assets1 INTO a_tbl(a_index);
431 EXIT WHEN mass_reclass_assets1%NOTFOUND;
432
433 /* Convert results into appropriate formats. */
434 -- Get category in concatenated string format.
435 IF (a_tbl(a_index).category_id <> h_category_id OR
436 h_category_id IS NULL) THEN
437 FA_RX_SHARED_PKG.Concat_Category(
438 struct_id => h_cat_flex_struct,
439 ccid => a_tbl(a_index).category_id,
440 concat_string => a_tbl(a_index).category,
441 segarray => h_cat_segs);
442 -- Keep track of last category_id.
443 h_category_id := a_tbl(a_index).category_id;
444 h_concat_cat := a_tbl(a_index).category;
445 ELSE
446 a_tbl(a_index).category := h_concat_cat;
447 END IF;
448
449 -- Get numbers in rounded figures.
450 FA_MASS_REC_UTILS_PKG.Convert_Formats(
451 X_Life_In_Months => a_tbl(a_index).life_in_months,
452 X_Basic_Rate => a_tbl(a_index).basic_rate,
453 X_Adjusted_Rate => a_tbl(a_index).adjusted_rate,
454 X_Allowed_Deprn_Limit => a_tbl(a_index).allowed_deprn_limit,
455 X_Percent_Salvage_Val => a_tbl(a_index).percent_salvage_val,
456 X_Life => a_tbl(a_index).life,
457 X_Basic_Rate_Pct => a_tbl(a_index).basic_rate_pct,
458 X_Adjusted_Rate_Pct => a_tbl(a_index).adjusted_rate_pct,
459 X_Deprn_Limit_Pct => a_tbl(a_index).deprn_limit_pct,
460 X_Salvage_Val_Pct => a_tbl(a_index).salvage_val_pct
461 );
462
463 -- Get cost and reserve accounts in concatenated formats.
464 OPEN get_acct_flex_struct;
465 FETCH get_acct_flex_struct INTO h_acct_flex_struct;
466 CLOSE get_acct_flex_struct;
467
468 IF (a_tbl(a_index).cost_acct_ccid IS NOT NULL) THEN
469 FA_RX_SHARED_PKG.Concat_Acct (
470 struct_id => h_acct_flex_struct,
471 ccid => a_tbl(a_index).cost_acct_ccid,
472 concat_string => a_tbl(a_index).cost_acct,
473 segarray => h_acct_segs);
474 END IF;
475
479 ccid => a_tbl(a_index).deprn_rsv_acct_ccid,
476 IF (a_tbl(a_index).deprn_rsv_acct_ccid IS NOT NULL) THEN
477 FA_RX_SHARED_PKG.Concat_Acct (
478 struct_id => h_acct_flex_struct,
480 concat_string => a_tbl(a_index).deprn_rsv_acct,
481 segarray => h_acct_segs);
482 END IF;
483
484 -- Update last asset processed and the asset count.
485 IF (a_tbl(a_index).asset_id <> h_last_asset OR
486 h_last_asset IS NULL) THEN
487 h_last_asset := a_tbl(a_index).asset_id;
488 g_asset_count := g_asset_count + 1;
489 END IF;
490
491 /* Insert asset records into the interface table, FA_MASS_RECLASS_ITF,
492 at every 200 assets and re-initialize the counter and the asset table. */
493 -- If the 200th asset belongs to more than one book, only the information
494 -- for the first book of this asset will be inserted into the table.
495 -- The rest will be taken care of in the next insertion.
496 IF (g_asset_count = h_commit_level) THEN
497 FOR i IN 1 .. a_index LOOP
498 FA_MASS_REC_UTILS_PKG.Insert_Itf(
499 X_Report_Type => 'REVIEW',
500 X_Request_Id => h_request_id,
501 X_Mass_Reclass_Id => X_Mass_Reclass_Id,
502 X_Asset_Rec => a_tbl(i),
503 X_New_Category => NULL,
504 X_Last_Update_Date => mr_rec.last_update_date,
505 X_Last_Updated_By => mr_rec.last_updated_by,
506 X_Created_By => mr_rec.created_by,
507 X_Creation_Date => mr_rec.creation_date,
508 X_Last_Update_Login => mr_rec.last_update_login
509 );
510 END LOOP;
511 a_tbl.delete;
512 g_asset_count := 0;
513 a_index := 0;
514 -- Also re-initialize h_last_asset so that g_asset_count
515 -- is incremented to 1 at the next loop entry as in the former
516 -- insertion.
517 h_last_asset := NULL;
518 COMMIT WORK;
519 END IF;
520
521 END LOOP;
522
523 CLOSE mass_reclass_assets1;
524
525 -----------------------------------------------------------------------------------
526
527 ELSIF (mr_rec.redefault_flag = 'YES') THEN
528 OPEN mass_reclass_assets2;
529
530 LOOP
531 -- Fetch the asset-book pair into the pl/sql table.
532 a_index := a_index + 1;
533 FETCH mass_reclass_assets2
534 INTO a_tbl(a_index).asset_id, a_tbl(a_index).asset_number,
535 a_tbl(a_index).description,
536 a_tbl(a_index).book_type_code, a_tbl(a_index).category_id,
537 a_tbl(a_index).cost_acct_ccid, a_tbl(a_index).deprn_rsv_acct_ccid,
538 h_rcl_thid;
539 EXIT WHEN mass_reclass_assets2%NOTFOUND;
540
541 /* Fetch the remaining book information. */
542 -- First, check if ADJUSTMENT actually occurred.
543 OPEN get_adjust_thid;
544 FETCH get_adjust_thid INTO h_adj_thid;
545 IF get_adjust_thid%notfound THEN
546 -- No ADJUSTMENT transaction recorded.
547 -- Get the book info at the time of RECLASS transaction.
548 CLOSE get_adjust_thid;
549 OPEN book_info1;
550 FETCH book_info1
551 INTO a_tbl(a_index).book_type_code, a_tbl(a_index).convention,
552 a_tbl(a_index).ceiling, a_tbl(a_index).method,
553 a_tbl(a_index).life_in_months, a_tbl(a_index).basic_rate,
554 a_tbl(a_index).adjusted_rate, a_tbl(a_index).bonus_rule,
555 a_tbl(a_index).capacity, a_tbl(a_index).unit_of_measure,
556 a_tbl(a_index).depreciate_flag,
557 a_tbl(a_index).allowed_deprn_limit,
558 a_tbl(a_index).deprn_limit_amt,
559 a_tbl(a_index).percent_salvage_val;
560 IF book_info1%notfound THEN
561 -- This book was not open at the time of reclass transaction.
562 -- It opened at the time after this transaction.
563 CLOSE book_info1;
564 h_bk_info_found := FALSE; -- Book info not found.
565 -- Decrement the index so that the next asset-book record
566 -- overwrites on this position.
567 a_index := a_index - 1;
568 ELSE
569 CLOSE book_info1;
570 h_bk_info_found := TRUE;
571 END IF;
572 ELSE
573 CLOSE get_adjust_thid;
574 OPEN book_info2;
575 FETCH book_info2
576 INTO a_tbl(a_index).book_type_code, a_tbl(a_index).convention,
577 a_tbl(a_index).ceiling, a_tbl(a_index).method,
578 a_tbl(a_index).life_in_months, a_tbl(a_index).basic_rate,
579 a_tbl(a_index).adjusted_rate, a_tbl(a_index).bonus_rule,
580 a_tbl(a_index).capacity, a_tbl(a_index).unit_of_measure,
581 a_tbl(a_index).depreciate_flag,
582 a_tbl(a_index).allowed_deprn_limit,
583 a_tbl(a_index).deprn_limit_amt,
584 a_tbl(a_index).percent_salvage_val;
585 CLOSE book_info2;
586 h_bk_info_found := TRUE;
587 END IF;
588
589 /* Process the remaining only if book info was found. */
590
591 IF h_bk_info_found THEN
592 /* Convert results into appropriate formats. */
593 -- Get category in concatenated string format.
594 IF (a_tbl(a_index).category_id <> h_category_id OR
595 h_category_id IS NULL) THEN
599 concat_string => a_tbl(a_index).category,
596 FA_RX_SHARED_PKG.Concat_Category(
597 struct_id => h_cat_flex_struct,
598 ccid => a_tbl(a_index).category_id,
600 segarray => h_cat_segs);
601 -- Keep track of last category_id.
602 h_category_id := a_tbl(a_index).category_id;
603 h_concat_cat := a_tbl(a_index).category;
604 ELSE
605 a_tbl(a_index).category := h_concat_cat;
606 END IF;
607
608 -- Get numbers in rounded figures.
609 FA_MASS_REC_UTILS_PKG.Convert_Formats(
610 X_Life_In_Months => a_tbl(a_index).life_in_months,
611 X_Basic_Rate => a_tbl(a_index).basic_rate,
612 X_Adjusted_Rate => a_tbl(a_index).adjusted_rate,
613 X_Allowed_Deprn_Limit => a_tbl(a_index).allowed_deprn_limit,
614 X_Percent_Salvage_Val => a_tbl(a_index).percent_salvage_val,
615 X_Life => a_tbl(a_index).life,
616 X_Basic_Rate_Pct => a_tbl(a_index).basic_rate_pct,
617 X_Adjusted_Rate_Pct => a_tbl(a_index).adjusted_rate_pct,
618 X_Deprn_Limit_Pct => a_tbl(a_index).deprn_limit_pct,
619 X_Salvage_Val_Pct => a_tbl(a_index).salvage_val_pct
620 );
621
622 -- Get cost and reserve accounts in concatenated formats.
623 OPEN get_acct_flex_struct;
624 FETCH get_acct_flex_struct INTO h_acct_flex_struct;
625 CLOSE get_acct_flex_struct;
626
627 IF (a_tbl(a_index).cost_acct_ccid IS NOT NULL) THEN
628 FA_RX_SHARED_PKG.Concat_Acct (
629 struct_id => h_acct_flex_struct,
630 ccid => a_tbl(a_index).cost_acct_ccid,
631 concat_string => a_tbl(a_index).cost_acct,
632 segarray => h_acct_segs);
633 END IF;
634
635 IF (a_tbl(a_index).deprn_rsv_acct_ccid IS NOT NULL) THEN
636 FA_RX_SHARED_PKG.Concat_Acct (
637 struct_id => h_acct_flex_struct,
638 ccid => a_tbl(a_index).deprn_rsv_acct_ccid,
639 concat_string => a_tbl(a_index).deprn_rsv_acct,
640 segarray => h_acct_segs);
641 END IF;
642
643 -- Update last asset processed and the asset count.
644 IF (a_tbl(a_index).asset_id <> h_last_asset OR
645 h_last_asset IS NULL) THEN
646 h_last_asset := a_tbl(a_index).asset_id;
647 g_asset_count := g_asset_count + 1;
648 END IF;
649
650 /* Insert asset records into the interface table, FA_MASS_RECLASS_ITF,
651 at every 200 assets and re-initialize the counter and the asset table. */
652 -- If the 200th asset belongs to more than one book, only the information
653 -- for the first book of this asset will be inserted into the table.
654 -- The rest will be taken care of in the next insertion.
655 IF (g_asset_count = h_commit_level) THEN
656 FOR i IN 1 .. a_index LOOP
657 FA_MASS_REC_UTILS_PKG.Insert_Itf(
658 X_Report_Type => 'REVIEW',
659 X_Request_Id => h_request_id,
660 X_Mass_Reclass_Id => X_Mass_Reclass_Id,
661 X_Asset_Rec => a_tbl(i),
662 X_New_Category => NULL,
663 X_Last_Update_Date => mr_rec.last_update_date,
664 X_Last_Updated_By => mr_rec.last_updated_by,
665 X_Created_By => mr_rec.created_by,
666 X_Creation_Date => mr_rec.creation_date,
667 X_Last_Update_Login => mr_rec.last_update_login
668 );
669 END LOOP;
670 a_tbl.delete;
671 g_asset_count := 0;
672 a_index := 0;
673 -- Also re-initialize h_last_asset so that g_asset_count
674 -- is incremented to 1 at the next loop entry as in the former
675 -- insertion.
676 h_last_asset := NULL;
677 COMMIT WORK;
678 END IF;
679
680 END IF; /* IF h_bk_info_found */
681
682 END LOOP;
683
684 CLOSE mass_reclass_assets2;
685
686 END IF; /* IF (mr_rec.redefault_flag = 'NO') */
687
688 /* Insert the remaining asset records into the interface table. */
689 -- Up to a_index - 1, to account for the extra increment taken for a_index
690 -- when no more rows were found in the cursor loop.
691 FOR i IN 1 .. (a_index - 1) LOOP
692 FA_MASS_REC_UTILS_PKG.Insert_Itf(
693 X_Report_Type => 'REVIEW',
694 X_Request_Id => h_request_id,
695 X_Mass_Reclass_Id => X_Mass_Reclass_Id,
696 X_Asset_Rec => a_tbl(i),
697 X_New_Category => NULL,
698 X_Last_Update_Date => mr_rec.last_update_date,
699 X_Last_Updated_By => mr_rec.last_updated_by,
700 X_Created_By => mr_rec.created_by,
701 X_Creation_Date => mr_rec.creation_date,
702 X_Last_Update_Login => mr_rec.last_update_login
703 );
704 END LOOP;
705 a_tbl.delete;
706 g_asset_count := 0;
707 a_index := 0;
708 fa_rx_conc_mesg_pkg.log('');
709 fnd_message.set_name('OFA', 'FA_MASSRCL_CHG_RVW');
710 h_msg_data := fnd_message.get;
711 fa_rx_conc_mesg_pkg.log(h_msg_data);
712 fa_rx_conc_mesg_pkg.log('');
713 COMMIT WORK;
714
715 errbuf := ''; -- No error.
716 retcode := 0; -- Completed normally.
717
718 EXCEPTION
719 WHEN mrcl_failure THEN
720 retcode := 2; -- Completed with error.
721
722 -- Reset global variable values.
723 a_tbl.delete;
724 a_index := 0;
725 g_asset_count := 0;
726 /* A fatal error has occurred. Rollback transaction. */
727 ROLLBACK WORK;
728 /* Delete rows inserted into the interface table. */
729 DELETE FROM fa_mass_reclass_itf
730 WHERE request_id = h_request_id;
731 /* Commit changes. */
732 COMMIT WORK;
733 /* Retrieve message log and write result to log and output. */
734 IF (X_RX_Flag = 'YES') THEN
735 FND_MSG_PUB.Count_And_Get(
736 p_count => h_msg_count,
737 p_data => h_msg_data);
738 FA_SRVR_MSG.Write_Msg_Log(
739 msg_count => h_msg_count,
740 msg_data => h_msg_data);
741 IF (h_debug_flag = 'YES') THEN
742 FA_DEBUG_PKG.Write_Debug_Log;
743 END IF;
744 END IF;
745 WHEN OTHERS THEN
746 retcode := 2; -- Completed with error.
747 IF SQLCODE <> 0 THEN
748 FA_SRVR_MSG.Add_SQL_Error(
749 CALLING_FN => 'FARX_RP.Preview_Reclass');
750 END IF;
751
752 -- Reset global variable values.
753 a_tbl.delete;
754 a_index := 0;
755 g_asset_count := 0;
756 --g_total_assets := 0;
757 /* A fatal error has occurred. Rollback transaction. */
758 ROLLBACK WORK;
759 /* Delete rows inserted into the interface table. */
760 DELETE FROM fa_mass_reclass_itf
761 WHERE request_id = h_request_id;
762 /* Commit changes. */
763 COMMIT WORK;
764 /* Retrieve message log and write result to log and output. */
765 IF (X_RX_Flag = 'YES') THEN
766 FND_MSG_PUB.Count_And_Get(
767 p_count => h_msg_count,
768 p_data => h_msg_data);
769 FA_SRVR_MSG.Write_Msg_Log(
770 msg_count => h_msg_count,
771 msg_data => h_msg_data);
772 IF (h_debug_flag = 'YES') THEN
773 FA_DEBUG_PKG.Write_Debug_Log;
774 END IF;
775 END IF;
776 END Review_Reclass;
777
778
779 END FARX_RR;
[Home] [Help]