4
1 PACKAGE BODY FARX_RR AS
2 /* $Header: FARXRRB.pls 120.8 2011/04/13 10:20:19 rmandali ship $ */
3
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 g_log_level_rec fa_api_types.log_level_rec_type;
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,
83 mr.segment25_high, mr.segment26_high, mr.segment27_high, mr.segment28_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,
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
157 -- nvl(ah.transaction_header_id_out, th.transaction_header_id + 1) >
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
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
254 CURSOR book_info1 IS
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.
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
293 -- to store ADJUSTMENT transaction header id for redefault transaction.
294 h_adj_thid NUMBER(15);
295
296 -- cursor to get the book information.
297 -- book_info2 is used when inherit_deprn_rules_flag = 'YES' and
298 -- when redefault transaction was actually performed and caused
299 -- changes in depreciation rules(a separate ADJUSTMENT transaction
300 -- is recorded in fa_transaction_headers in this case.)
301 CURSOR book_info2 IS
302 SELECT bk.book_type_code,
303 bk.prorate_convention_code,
304 bk.ceiling_name,
305 bk.deprn_method_code,
306 bk.life_in_months,
307 bk.basic_rate,
308 bk.adjusted_rate,
309 bk.bonus_rule,
310 bk.production_capacity,
311 bk.unit_of_measure,
312 bk.depreciate_flag,
313 bk.allowed_deprn_limit,
314 bk.allowed_deprn_limit_amount,
315 bk.percent_salvage_value
316 FROM fa_books bk
317 WHERE bk.asset_id = a_tbl(a_index).asset_id
318 AND bk.book_type_code = a_tbl(a_index).book_type_code
319 AND bk.transaction_header_id_in = h_adj_thid;
320
321 -- cursor to fetch accounting flexfield structure.
322 CURSOR get_acct_flex_struct IS
323 SELECT accounting_flex_structure
324 FROM fa_book_controls
325 WHERE book_type_code = a_tbl(a_index).book_type_code;
326
327 h_request_id NUMBER;
328 h_msg_count NUMBER;
329 h_msg_data VARCHAR2(2000) := NULL;
330 h_review_status_d VARCHAR2(80); /* Modified for Bug 10363987 */
331 h_current_status_d VARCHAR2(80); /* Modified for Bug 10363987 */
332 h_cat_flex_struct NUMBER;
333 h_acct_flex_struct NUMBER;
334 h_cat_segs FA_RX_SHARED_PKG.Seg_Array;
335 h_acct_segs FA_RX_SHARED_PKG.Seg_Array;
336 h_category_id NUMBER(15) := NULL;
337 h_concat_cat VARCHAR2(220);
338 h_debug_flag VARCHAR2(3) := 'NO';
339 -- to keep track of the last asset id that entered the mass_reclass_assets
340 -- cursor loop.
341 h_last_asset NUMBER(15) := NULL;
342 -- indicates whether the book information was found. used only when
343 -- redefault option was set to YES.
344 h_bk_info_found BOOLEAN;
345 -- exception raised from this module and child modules.
346 mrcl_failure EXCEPTION;
347 -- Commit results per every 200 assets.
348 h_commit_level NUMBER := 200;
349 /* do not need these variables as per bug 8402286
350 need to remove large rollback segment
351 rbs_name VARCHAR2(30);
352 sql_stmt VARCHAR2(101);
353 */
354
355 BEGIN
356
357 -- Initialize message stacks.
358 FA_SRVR_MSG.Init_Server_Message;
359 FA_DEBUG_PKG.Initialize;
360
361 -- Set debug flag.
362 IF (g_print_debug) THEN
363 h_debug_flag := 'YES';
364 END IF;
365
366 /* Fix for BUG# 1302611 where the rbs_name variable was being
367 interpreted as a literal string rather than using the
368 value of the variable
369 */
370
371
372 /* Bug 8402286 removing LARGE ROLLBACK SEGMENT
373 -- Set large rollback segment.
374 fnd_profile.get('FA_LARGE_ROLLBACK_SEGMENT', rbs_name);
375 IF (rbs_name is not null) THEN
376 sql_stmt := 'Set Transaction Use Rollback Segment '|| rbs_name;
377 execute immediate sql_stmt;
378 END IF;
379 */
380 -- Initialize global variables.
381 -- (These are session specific variables, and thus values need to
382 -- be re-initialized.)
383 a_tbl.delete;
387 -- Get concurrent request id for the mass reclass review request.
384 a_index := 0;
385 g_asset_count := 0;
386
388 -- h_request_id is used when request_id is inserted into the interface
389 -- table, fa_mass_reclass_itf.
390 -- Need to fetch request id from fnd_global package instead of fa_mass_reclass
391 -- table, since fa_mass_reclass table stores the latest request id for
392 -- the Preview or Run requests only.
393 h_request_id := fnd_global.conc_request_id;
394
395 -- Fetch mass reclass record information.
396 OPEN mass_reclass;
397 FETCH mass_reclass INTO mr_rec;
398 CLOSE mass_reclass;
399
400 /*===========================================================================
401 Delete rows previously inserted into the interface table with the same
402 request id, if there is any.
403 ===========================================================================*/
404 DELETE FROM fa_mass_reclass_itf
405 WHERE request_id = h_request_id;
406 COMMIT;
407
408 /*===========================================================================
409 Check to make sure current status is 'COMPLETED'
410 ===========================================================================*/
411 OPEN get_status;
412 FETCH get_status INTO h_review_status_d, h_current_status_d;
413 CLOSE get_status;
414
415 IF (h_review_status_d <> h_current_status_d) THEN
416 -- Re-using message for mass reclass program.
417 FA_SRVR_MSG.Add_Message(
418 CALLING_FN => 'FARX_RR.Review_Reclass',
419 NAME => 'FA_MASSRCL_WRONG_STATUS',
420 TOKEN1 => 'CURRENT',
421 VALUE1 => h_current_status_d,
422 TOKEN2 => 'RUNNING',
423 VALUE2 => h_review_status_d);
424 -- Review will complete with error status.
425 RAISE mrcl_failure;
426 END IF;
427
428 /*===========================================================================
429 Insert review records into the interface table.
430 ===========================================================================*/
431
432 /* Get category flex structure. */
433 OPEN get_cat_flex_struct;
434 FETCH get_cat_flex_struct INTO h_cat_flex_struct;
435 CLOSE get_cat_flex_struct;
436
437 /* Fetch asset-book pairs from mass_reclass_assets cursor and insert them
438 into the interface table, fa_mass_reclass_itf. */
439 IF (mr_rec.redefault_flag = 'NO') THEN
440 OPEN mass_reclass_assets1;
441
442 LOOP
443 -- Fetch the asset-book pair into the pl/sql table.
444 a_index := a_index + 1;
445 FETCH mass_reclass_assets1 INTO a_tbl(a_index);
446 EXIT WHEN mass_reclass_assets1%NOTFOUND;
447
448 /* Convert results into appropriate formats. */
449 -- Get category in concatenated string format.
450 IF (a_tbl(a_index).category_id <> h_category_id OR
451 h_category_id IS NULL) THEN
452 FA_RX_SHARED_PKG.Concat_Category(
453 struct_id => h_cat_flex_struct,
454 ccid => a_tbl(a_index).category_id,
455 concat_string => a_tbl(a_index).category,
456 segarray => h_cat_segs);
457 -- Keep track of last category_id.
458 h_category_id := a_tbl(a_index).category_id;
459 h_concat_cat := a_tbl(a_index).category;
460 ELSE
461 a_tbl(a_index).category := h_concat_cat;
462 END IF;
463
464 -- Get numbers in rounded figures.
465 FA_MASS_REC_UTILS_PKG.Convert_Formats(
466 X_Life_In_Months => a_tbl(a_index).life_in_months,
467 X_Basic_Rate => a_tbl(a_index).basic_rate,
468 X_Adjusted_Rate => a_tbl(a_index).adjusted_rate,
469 X_Allowed_Deprn_Limit => a_tbl(a_index).allowed_deprn_limit,
470 X_Percent_Salvage_Val => a_tbl(a_index).percent_salvage_val,
471 X_Life => a_tbl(a_index).life,
472 X_Basic_Rate_Pct => a_tbl(a_index).basic_rate_pct,
473 X_Adjusted_Rate_Pct => a_tbl(a_index).adjusted_rate_pct,
474 X_Deprn_Limit_Pct => a_tbl(a_index).deprn_limit_pct,
475 X_Salvage_Val_Pct => a_tbl(a_index).salvage_val_pct,
476 p_log_level_rec => g_log_level_rec
477 );
478
479 -- Get cost and reserve accounts in concatenated formats.
480 OPEN get_acct_flex_struct;
481 FETCH get_acct_flex_struct INTO h_acct_flex_struct;
482 CLOSE get_acct_flex_struct;
483
484 IF (a_tbl(a_index).cost_acct_ccid IS NOT NULL) THEN
485 FA_RX_SHARED_PKG.Concat_Acct (
486 struct_id => h_acct_flex_struct,
487 ccid => a_tbl(a_index).cost_acct_ccid,
488 concat_string => a_tbl(a_index).cost_acct,
489 segarray => h_acct_segs);
490 END IF;
491
492 IF (a_tbl(a_index).deprn_rsv_acct_ccid IS NOT NULL) THEN
493 FA_RX_SHARED_PKG.Concat_Acct (
494 struct_id => h_acct_flex_struct,
495 ccid => a_tbl(a_index).deprn_rsv_acct_ccid,
496 concat_string => a_tbl(a_index).deprn_rsv_acct,
497 segarray => h_acct_segs);
498 END IF;
499
500 -- Update last asset processed and the asset count.
501 IF (a_tbl(a_index).asset_id <> h_last_asset OR
502 h_last_asset IS NULL) THEN
503 h_last_asset := a_tbl(a_index).asset_id;
504 g_asset_count := g_asset_count + 1;
505 END IF;
506
507 /* Insert asset records into the interface table, FA_MASS_RECLASS_ITF,
508 at every 200 assets and re-initialize the counter and the asset table. */
509 -- If the 200th asset belongs to more than one book, only the information
513 FOR i IN 1 .. a_index LOOP
510 -- for the first book of this asset will be inserted into the table.
511 -- The rest will be taken care of in the next insertion.
512 IF (g_asset_count = h_commit_level) THEN
514 FA_MASS_REC_UTILS_PKG.Insert_Itf(
515 X_Report_Type => 'REVIEW',
516 X_Request_Id => h_request_id,
517 X_Mass_Reclass_Id => X_Mass_Reclass_Id,
518 X_Asset_Rec => a_tbl(i),
519 X_New_Category => NULL,
520 X_Last_Update_Date => mr_rec.last_update_date,
521 X_Last_Updated_By => mr_rec.last_updated_by,
522 X_Created_By => mr_rec.created_by,
523 X_Creation_Date => mr_rec.creation_date,
524 X_Last_Update_Login => mr_rec.last_update_login,
525 p_log_level_rec => g_log_level_rec
526 );
527 END LOOP;
528 a_tbl.delete;
529 g_asset_count := 0;
530 a_index := 0;
531 -- Also re-initialize h_last_asset so that g_asset_count
532 -- is incremented to 1 at the next loop entry as in the former
533 -- insertion.
534 h_last_asset := NULL;
535 COMMIT WORK;
536 END IF;
537
538 END LOOP;
539
540 CLOSE mass_reclass_assets1;
541
542 -----------------------------------------------------------------------------------
543
544 ELSIF (mr_rec.redefault_flag = 'YES') THEN
545 OPEN mass_reclass_assets2;
546
547 LOOP
548 -- Fetch the asset-book pair into the pl/sql table.
549 a_index := a_index + 1;
550 FETCH mass_reclass_assets2
551 INTO a_tbl(a_index).asset_id, a_tbl(a_index).asset_number,
552 a_tbl(a_index).description,
553 a_tbl(a_index).book_type_code, a_tbl(a_index).category_id,
554 a_tbl(a_index).cost_acct_ccid, a_tbl(a_index).deprn_rsv_acct_ccid,
555 h_rcl_thid;
556 EXIT WHEN mass_reclass_assets2%NOTFOUND;
557
558 /* Fetch the remaining book information. */
559 -- First, check if ADJUSTMENT actually occurred.
560 OPEN get_adjust_thid;
561 FETCH get_adjust_thid INTO h_adj_thid;
562 IF get_adjust_thid%notfound THEN
563 -- No ADJUSTMENT transaction recorded.
564 -- Get the book info at the time of RECLASS transaction.
565 CLOSE get_adjust_thid;
566 OPEN book_info1;
567 FETCH book_info1
568 INTO a_tbl(a_index).book_type_code, a_tbl(a_index).convention,
569 a_tbl(a_index).ceiling, a_tbl(a_index).method,
570 a_tbl(a_index).life_in_months, a_tbl(a_index).basic_rate,
571 a_tbl(a_index).adjusted_rate, a_tbl(a_index).bonus_rule,
572 a_tbl(a_index).capacity, a_tbl(a_index).unit_of_measure,
573 a_tbl(a_index).depreciate_flag,
574 a_tbl(a_index).allowed_deprn_limit,
575 a_tbl(a_index).deprn_limit_amt,
576 a_tbl(a_index).percent_salvage_val;
577 IF book_info1%notfound THEN
578 -- This book was not open at the time of reclass transaction.
579 -- It opened at the time after this transaction.
580 CLOSE book_info1;
581 h_bk_info_found := FALSE; -- Book info not found.
582 -- Decrement the index so that the next asset-book record
583 -- overwrites on this position.
584 a_index := a_index - 1;
585 ELSE
586 CLOSE book_info1;
587 h_bk_info_found := TRUE;
588 END IF;
589 ELSE
590 CLOSE get_adjust_thid;
591 OPEN book_info2;
592 FETCH book_info2
593 INTO a_tbl(a_index).book_type_code, a_tbl(a_index).convention,
594 a_tbl(a_index).ceiling, a_tbl(a_index).method,
595 a_tbl(a_index).life_in_months, a_tbl(a_index).basic_rate,
596 a_tbl(a_index).adjusted_rate, a_tbl(a_index).bonus_rule,
597 a_tbl(a_index).capacity, a_tbl(a_index).unit_of_measure,
598 a_tbl(a_index).depreciate_flag,
599 a_tbl(a_index).allowed_deprn_limit,
600 a_tbl(a_index).deprn_limit_amt,
601 a_tbl(a_index).percent_salvage_val;
602 CLOSE book_info2;
603 h_bk_info_found := TRUE;
604 END IF;
605
606 /* Process the remaining only if book info was found. */
607
608 IF h_bk_info_found THEN
609 /* Convert results into appropriate formats. */
610 -- Get category in concatenated string format.
611 IF (a_tbl(a_index).category_id <> h_category_id OR
612 h_category_id IS NULL) THEN
613 FA_RX_SHARED_PKG.Concat_Category(
614 struct_id => h_cat_flex_struct,
615 ccid => a_tbl(a_index).category_id,
616 concat_string => a_tbl(a_index).category,
617 segarray => h_cat_segs);
618 -- Keep track of last category_id.
619 h_category_id := a_tbl(a_index).category_id;
620 h_concat_cat := a_tbl(a_index).category;
621 ELSE
622 a_tbl(a_index).category := h_concat_cat;
623 END IF;
624
625 -- Get numbers in rounded figures.
626 FA_MASS_REC_UTILS_PKG.Convert_Formats(
627 X_Life_In_Months => a_tbl(a_index).life_in_months,
628 X_Basic_Rate => a_tbl(a_index).basic_rate,
629 X_Adjusted_Rate => a_tbl(a_index).adjusted_rate,
630 X_Allowed_Deprn_Limit => a_tbl(a_index).allowed_deprn_limit,
631 X_Percent_Salvage_Val => a_tbl(a_index).percent_salvage_val,
632 X_Life => a_tbl(a_index).life,
633 X_Basic_Rate_Pct => a_tbl(a_index).basic_rate_pct,
634 X_Adjusted_Rate_Pct => a_tbl(a_index).adjusted_rate_pct,
635 X_Deprn_Limit_Pct => a_tbl(a_index).deprn_limit_pct,
639
636 X_Salvage_Val_Pct => a_tbl(a_index).salvage_val_pct,
637 p_log_level_rec => g_log_level_rec
638 );
640 -- Get cost and reserve accounts in concatenated formats.
641 OPEN get_acct_flex_struct;
642 FETCH get_acct_flex_struct INTO h_acct_flex_struct;
643 CLOSE get_acct_flex_struct;
644
645 IF (a_tbl(a_index).cost_acct_ccid IS NOT NULL) THEN
646 FA_RX_SHARED_PKG.Concat_Acct (
647 struct_id => h_acct_flex_struct,
648 ccid => a_tbl(a_index).cost_acct_ccid,
649 concat_string => a_tbl(a_index).cost_acct,
650 segarray => h_acct_segs);
651 END IF;
652
653 IF (a_tbl(a_index).deprn_rsv_acct_ccid IS NOT NULL) THEN
654 FA_RX_SHARED_PKG.Concat_Acct (
655 struct_id => h_acct_flex_struct,
656 ccid => a_tbl(a_index).deprn_rsv_acct_ccid,
657 concat_string => a_tbl(a_index).deprn_rsv_acct,
658 segarray => h_acct_segs);
659 END IF;
660
661 -- Update last asset processed and the asset count.
662 IF (a_tbl(a_index).asset_id <> h_last_asset OR
663 h_last_asset IS NULL) THEN
664 h_last_asset := a_tbl(a_index).asset_id;
665 g_asset_count := g_asset_count + 1;
666 END IF;
667
668 /* Insert asset records into the interface table, FA_MASS_RECLASS_ITF,
669 at every 200 assets and re-initialize the counter and the asset table. */
670 -- If the 200th asset belongs to more than one book, only the information
671 -- for the first book of this asset will be inserted into the table.
672 -- The rest will be taken care of in the next insertion.
673 IF (g_asset_count = h_commit_level) THEN
674 FOR i IN 1 .. a_index LOOP
675 FA_MASS_REC_UTILS_PKG.Insert_Itf(
676 X_Report_Type => 'REVIEW',
677 X_Request_Id => h_request_id,
678 X_Mass_Reclass_Id => X_Mass_Reclass_Id,
679 X_Asset_Rec => a_tbl(i),
680 X_New_Category => NULL,
681 X_Last_Update_Date => mr_rec.last_update_date,
682 X_Last_Updated_By => mr_rec.last_updated_by,
683 X_Created_By => mr_rec.created_by,
684 X_Creation_Date => mr_rec.creation_date,
685 X_Last_Update_Login => mr_rec.last_update_login,
686 p_log_level_rec => g_log_level_rec
687 );
688 END LOOP;
689 a_tbl.delete;
690 g_asset_count := 0;
691 a_index := 0;
692 -- Also re-initialize h_last_asset so that g_asset_count
693 -- is incremented to 1 at the next loop entry as in the former
694 -- insertion.
695 h_last_asset := NULL;
696 COMMIT WORK;
697 END IF;
698
699 END IF; /* IF h_bk_info_found */
700
701 END LOOP;
702
703 CLOSE mass_reclass_assets2;
704
705 END IF; /* IF (mr_rec.redefault_flag = 'NO') */
706
707 /* Insert the remaining asset records into the interface table. */
708 -- Up to a_index - 1, to account for the extra increment taken for a_index
709 -- when no more rows were found in the cursor loop.
710 FOR i IN 1 .. (a_index - 1) LOOP
711 FA_MASS_REC_UTILS_PKG.Insert_Itf(
712 X_Report_Type => 'REVIEW',
713 X_Request_Id => h_request_id,
714 X_Mass_Reclass_Id => X_Mass_Reclass_Id,
715 X_Asset_Rec => a_tbl(i),
716 X_New_Category => NULL,
717 X_Last_Update_Date => mr_rec.last_update_date,
718 X_Last_Updated_By => mr_rec.last_updated_by,
719 X_Created_By => mr_rec.created_by,
720 X_Creation_Date => mr_rec.creation_date,
721 X_Last_Update_Login => mr_rec.last_update_login,
722 p_log_level_rec => g_log_level_rec
723 );
724 END LOOP;
725 a_tbl.delete;
726 g_asset_count := 0;
727 a_index := 0;
728 fa_rx_conc_mesg_pkg.log('');
729 fnd_message.set_name('OFA', 'FA_MASSRCL_CHG_RVW');
730 h_msg_data := fnd_message.get;
731 fa_rx_conc_mesg_pkg.log(h_msg_data);
732 fa_rx_conc_mesg_pkg.log('');
733 COMMIT WORK;
734
735 errbuf := ''; -- No error.
736 retcode := 0; -- Completed normally.
737
738 EXCEPTION
739 WHEN mrcl_failure THEN
740 retcode := 2; -- Completed with error.
741
742 -- Reset global variable values.
743 a_tbl.delete;
744 a_index := 0;
745 g_asset_count := 0;
746 /* A fatal error has occurred. Rollback transaction. */
747 ROLLBACK WORK;
748 /* Delete rows inserted into the interface table. */
749 DELETE FROM fa_mass_reclass_itf
750 WHERE request_id = h_request_id;
751 /* Commit changes. */
752 COMMIT WORK;
753 /* Retrieve message log and write result to log and output. */
754 IF (X_RX_Flag = 'YES') THEN
755 FND_MSG_PUB.Count_And_Get(
756 p_count => h_msg_count,
757 p_data => h_msg_data);
758 FA_SRVR_MSG.Write_Msg_Log(
759 msg_count => h_msg_count,
760 msg_data => h_msg_data);
761 IF (h_debug_flag = 'YES') THEN
762 FA_DEBUG_PKG.Write_Debug_Log;
763 END IF;
764 END IF;
765 WHEN OTHERS THEN
766 retcode := 2; -- Completed with error.
767 IF SQLCODE <> 0 THEN
768 FA_SRVR_MSG.Add_SQL_Error(
769 CALLING_FN => 'FARX_RP.Preview_Reclass');
770 END IF;
771
775 g_asset_count := 0;
772 -- Reset global variable values.
773 a_tbl.delete;
774 a_index := 0;
776 --g_total_assets := 0;
777 /* A fatal error has occurred. Rollback transaction. */
778 ROLLBACK WORK;
779 /* Delete rows inserted into the interface table. */
780 DELETE FROM fa_mass_reclass_itf
781 WHERE request_id = h_request_id;
782 /* Commit changes. */
783 COMMIT WORK;
784 /* Retrieve message log and write result to log and output. */
785 IF (X_RX_Flag = 'YES') THEN
786 FND_MSG_PUB.Count_And_Get(
787 p_count => h_msg_count,
788 p_data => h_msg_data);
789 FA_SRVR_MSG.Write_Msg_Log(
790 msg_count => h_msg_count,
791 msg_data => h_msg_data);
792 IF (h_debug_flag = 'YES') THEN
793 FA_DEBUG_PKG.Write_Debug_Log;
794 END IF;
795 END IF;
796 END Review_Reclass;
797
798
799 END FARX_RR;
[Home] [Help]