4 -- Mass reclass record from fa_mass_reclass table.
1 PACKAGE BODY FARX_RP AS
2 /* $Header: FARXRPB.pls 120.10.12020000.2 2012/07/23 10:06:11 rmandali ship $ */
3
5 mr_rec FA_MASS_REC_UTILS_PKG.mass_reclass_rec;
6
7 -- Table of asset records.
8 -- (Stores book_type_code as well, and thus one asset will appear multiple
9 -- times if the asset belongs to multiple books.)
10 a_tbl FA_MASS_REC_UTILS_PKG.asset_table;
11 -- Index into the asset table, a_tbl.
12 a_index NUMBER := 0;
13
14 -- Number of assets(disregaring book_type_code) stored in a_tbl.
15 -- Reset at every 200 assets.
16 g_asset_count NUMBER := 0;
17 -- Total number of assets to be printed in report.
18 --g_total_assets NUMBER := 0;
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 g_log_level_rec fa_api_types.log_level_rec_type;
24
25 /*============================================================================+
26 | PROCEDURE Preview_Reclass |
27 +=============================================================================*/
28
29 PROCEDURE Preview_Reclass(
30 X_Mass_Reclass_Id IN NUMBER,
31 X_RX_Flag IN VARCHAR2 := 'NO',
32 retcode OUT NOCOPY NUMBER,
33 errbuf OUT NOCOPY VARCHAR2) IS
34
35 -- cursor to fetch the current and preview status
36 CURSOR get_status IS
37 SELECT lu_prev.meaning,
38 lu_curr.meaning
39 FROM fa_lookups lu_prev,
40 fa_lookups lu_curr
41 WHERE lu_prev.lookup_type = 'MASS_TRX_STATUS' AND
42 lu_prev.lookup_code = 'PREVIEW'
43 AND lu_curr.lookup_type = 'MASS_TRX_STATUS' AND
44 lu_curr.lookup_code = mr_rec.status;
45
46 -- cursor to get category flex structure.
47 CURSOR get_cat_flex_struct IS
48 SELECT category_flex_structure
49 FROM fa_system_controls;
50
51 -- cursor to fetch mass reclass record from fa_mass_reclass
52 CURSOR mass_reclass IS
53 SELECT mr.mass_reclass_id,
54 mr.book_type_code,
55 mr.transaction_date_entered,
56 mr.concurrent_request_id,
57 mr.status,
58 mr.asset_type,
59 mr.location_id,
60 mr.employee_id,
61 mr.asset_key_id,
62 mr.from_cost,
63 mr.to_cost,
64 mr.from_asset_number,
65 mr.to_asset_number,
66 mr.from_date_placed_in_service,
67 mr.to_date_placed_in_service,
68 mr.from_category_id,
69 mr.to_category_id,
70 mr.segment1_low, mr.segment2_low, mr.segment3_low, mr.segment4_low,
71 mr.segment5_low, mr.segment6_low, mr.segment7_low, mr.segment8_low,
72 mr.segment9_low, mr.segment10_low, mr.segment11_low, mr.segment12_low,
73 mr.segment13_low, mr.segment14_low, mr.segment15_low, mr.segment16_low,
74 mr.segment17_low, mr.segment18_low, mr.segment19_low, mr.segment20_low,
75 mr.segment21_low, mr.segment22_low, mr.segment23_low, mr.segment24_low,
76 mr.segment25_low, mr.segment26_low, mr.segment27_low, mr.segment28_low,
77 mr.segment29_low, mr.segment30_low,
78 mr.segment1_high, mr.segment2_high, mr.segment3_high, mr.segment4_high,
79 mr.segment5_high, mr.segment6_high, mr.segment7_high, mr.segment8_high,
80 mr.segment9_high, mr.segment10_high, mr.segment11_high, mr.segment12_high,
81 mr.segment13_high, mr.segment14_high, mr.segment15_high, mr.segment16_high,
82 mr.segment17_high, mr.segment18_high, mr.segment19_high, mr.segment20_high,
83 mr.segment21_high, mr.segment22_high, mr.segment23_high, mr.segment24_high,
84 mr.segment25_high, mr.segment26_high, mr.segment27_high, mr.segment28_high,
85 mr.segment29_high, mr.segment30_high,
86 mr.include_fully_rsvd_flag,
87 mr.copy_cat_desc_flag,
88 mr.inherit_deprn_rules_flag,
89 mr.amortize_flag,
90 mr.created_by,
91 mr.creation_date,
92 mr.last_updated_by,
93 mr.last_update_login,
94 mr.last_update_date
95 FROM fa_mass_reclass mr
96 WHERE mass_reclass_id = X_Mass_Reclass_Id;
97
98 -- assets that meet the user's selection criteria.
99 -- some assets selected by this cursor are discarded in the validation engine.
100 CURSOR mass_reclass_assets IS
101 SELECT ad.asset_id,
102 ad.asset_number,
103 ad.description,
104 ad.asset_category_id
105 FROM gl_code_combinations gc,
106 fa_distribution_history dh,
107 fa_book_controls bc,
108 fa_books bk,
109 fa_additions ad
110 WHERE ad.asset_type = nvl(mr_rec.asset_type, ad.asset_type)
111 AND ad.asset_number >= nvl(mr_rec.from_asset_number, ad.asset_number)
112 AND ad.asset_number <= nvl(mr_rec.to_asset_number, ad.asset_number)
113 AND nvl(ad.asset_key_ccid, -9999) = nvl(mr_rec.asset_key_id,
114 nvl(ad.asset_key_ccid, -9999))
115 AND ad.asset_category_id = nvl(mr_rec.from_category_id, ad.asset_category_id)
116 AND bk.book_type_code = mr_rec.book_type_code
120 AND bk.asset_id = ad.asset_id
117 AND bk.book_type_code = bc.book_type_code
118 -- corp book should be currently effective.
119 AND nvl(bc.date_ineffective, sysdate+1) > sysdate
121 AND NVL(bk.Disabled_flag, 'N') = 'N' --HH
122 AND bk.date_ineffective IS NULL -- pick the most recent row.
123 -- dpis, exp acct, employee, location, cost range: selection criteria
124 -- for corporate book only.
125 AND bk.date_placed_in_service >= nvl(mr_rec.from_dpis,
126 bk.date_placed_in_service)
127 AND bk.date_placed_in_service <= nvl(mr_rec.to_dpis,
128 bk.date_placed_in_service)
129 AND bk.cost >= nvl(mr_rec.from_cost, bk.cost)
130 AND bk.cost <= nvl(mr_rec.to_cost, bk.cost)
131 AND dh.asset_id = ad.asset_id
132 AND nvl(dh.assigned_to, -9999) = nvl(mr_rec.employee_id, nvl(dh.assigned_to, -9999))
133 AND dh.location_id = nvl(mr_rec.location_id, dh.location_id)
134 AND dh.date_ineffective IS NULL -- pick only the active distributions.
135 AND dh.code_combination_id = gc.code_combination_id
136 -- cannot avoid the use of OR, since gc.segment1 can be null.
137 -- cannot use nvl(gc.segment1, 'NULL') for comparison, since
138 -- the value 'NULL' may fall between the range accidentally.
139 -- may break the OR to UNION later.
140 -- rule-based optimizer transforms OR to UNION ALL automatically
141 -- when it sees it being more efficient. since the columns
142 -- in OR are not indexed, transforming to UNION ALL has
143 -- no gain in performance and using OR is unavoidable here
144 -- for the correctness of the program.
145 AND ((gc.segment1 between nvl(mr_rec.segment1_low, gc.segment1)
146 and nvl(mr_rec.segment1_high, gc.segment1)) OR
147 (mr_rec.segment1_low IS NULL and mr_rec.segment1_high IS NULL))
148 AND ((gc.segment2 between nvl(mr_rec.segment2_low, gc.segment2)
149 and nvl(mr_rec.segment2_high, gc.segment2)) OR
150 (mr_rec.segment2_low IS NULL and mr_rec.segment2_high IS NULL))
151 AND ((gc.segment3 between nvl(mr_rec.segment3_low, gc.segment3)
152 and nvl(mr_rec.segment3_high, gc.segment3)) OR
153 (mr_rec.segment3_low IS NULL and mr_rec.segment3_high IS NULL))
154 AND ((gc.segment4 between nvl(mr_rec.segment4_low, gc.segment4)
155 and nvl(mr_rec.segment4_high, gc.segment4)) OR
156 (mr_rec.segment4_low IS NULL and mr_rec.segment4_high IS NULL))
157 AND ((gc.segment5 between nvl(mr_rec.segment5_low, gc.segment5)
158 and nvl(mr_rec.segment5_high, gc.segment5)) OR
159 (mr_rec.segment5_low IS NULL and mr_rec.segment5_high IS NULL))
160 AND ((gc.segment6 between nvl(mr_rec.segment6_low, gc.segment6)
161 and nvl(mr_rec.segment6_high, gc.segment6)) OR
162 (mr_rec.segment6_low IS NULL and mr_rec.segment6_high IS NULL))
163 AND ((gc.segment7 between nvl(mr_rec.segment7_low, gc.segment7)
164 and nvl(mr_rec.segment7_high, gc.segment7)) OR
165 (mr_rec.segment7_low IS NULL and mr_rec.segment7_high IS NULL))
166 AND ((gc.segment8 between nvl(mr_rec.segment8_low, gc.segment8)
167 and nvl(mr_rec.segment8_high, gc.segment8)) OR
168 (mr_rec.segment8_low IS NULL and mr_rec.segment8_high IS NULL))
169 AND ((gc.segment9 between nvl(mr_rec.segment9_low, gc.segment9)
170 and nvl(mr_rec.segment9_high, gc.segment9)) OR
171 (mr_rec.segment9_low IS NULL and mr_rec.segment9_high IS NULL))
172 AND ((gc.segment10 between nvl(mr_rec.segment10_low, gc.segment10)
173 and nvl(mr_rec.segment10_high, gc.segment10)) OR
174 (mr_rec.segment10_low IS NULL and mr_rec.segment10_high IS NULL))
175 AND ((gc.segment11 between nvl(mr_rec.segment11_low, gc.segment11)
176 and nvl(mr_rec.segment11_high, gc.segment11)) OR
177 (mr_rec.segment11_low IS NULL and mr_rec.segment11_high IS NULL))
178 AND ((gc.segment12 between nvl(mr_rec.segment12_low, gc.segment12)
179 and nvl(mr_rec.segment12_high, gc.segment12)) OR
180 (mr_rec.segment12_low IS NULL and mr_rec.segment12_high IS NULL))
181 AND ((gc.segment13 between nvl(mr_rec.segment13_low, gc.segment13)
182 and nvl(mr_rec.segment13_high, gc.segment13)) OR
183 (mr_rec.segment13_low IS NULL and mr_rec.segment13_high IS NULL))
184 AND ((gc.segment14 between nvl(mr_rec.segment14_low, gc.segment14)
185 and nvl(mr_rec.segment14_high, gc.segment14)) OR
186 (mr_rec.segment14_low IS NULL and mr_rec.segment14_high IS NULL))
187 AND ((gc.segment15 between nvl(mr_rec.segment15_low, gc.segment15)
188 and nvl(mr_rec.segment15_high, gc.segment15)) OR
189 (mr_rec.segment15_low IS NULL and mr_rec.segment15_high IS NULL))
190 AND ((gc.segment16 between nvl(mr_rec.segment16_low, gc.segment16)
191 and nvl(mr_rec.segment16_high, gc.segment16)) OR
192 (mr_rec.segment16_low IS NULL and mr_rec.segment16_high IS NULL))
193 AND ((gc.segment17 between nvl(mr_rec.segment17_low, gc.segment17)
194 and nvl(mr_rec.segment17_high, gc.segment17)) OR
195 (mr_rec.segment17_low IS NULL and mr_rec.segment17_high IS NULL))
196 AND ((gc.segment18 between nvl(mr_rec.segment18_low, gc.segment18)
197 and nvl(mr_rec.segment18_high, gc.segment18)) OR
198 (mr_rec.segment18_low IS NULL and mr_rec.segment18_high IS NULL))
199 AND ((gc.segment19 between nvl(mr_rec.segment19_low, gc.segment19)
200 and nvl(mr_rec.segment19_high, gc.segment19)) OR
201 (mr_rec.segment19_low IS NULL and mr_rec.segment19_high IS NULL))
202 AND ((gc.segment20 between nvl(mr_rec.segment20_low, gc.segment20)
203 and nvl(mr_rec.segment20_high, gc.segment20)) OR
204 (mr_rec.segment20_low IS NULL and mr_rec.segment20_high IS NULL))
205 AND ((gc.segment21 between nvl(mr_rec.segment21_low, gc.segment21)
206 and nvl(mr_rec.segment21_high, gc.segment21)) OR
207 (mr_rec.segment21_low IS NULL and mr_rec.segment21_high IS NULL))
208 AND ((gc.segment22 between nvl(mr_rec.segment22_low, gc.segment22)
209 and nvl(mr_rec.segment22_high, gc.segment22)) OR
210 (mr_rec.segment22_low IS NULL and mr_rec.segment22_high IS NULL))
211 AND ((gc.segment23 between nvl(mr_rec.segment23_low, gc.segment23)
212 and nvl(mr_rec.segment23_high, gc.segment23)) OR
216 (mr_rec.segment24_low IS NULL and mr_rec.segment24_high IS NULL))
213 (mr_rec.segment23_low IS NULL and mr_rec.segment23_high IS NULL))
214 AND ((gc.segment24 between nvl(mr_rec.segment24_low, gc.segment24)
215 and nvl(mr_rec.segment24_high, gc.segment24)) OR
217 AND ((gc.segment25 between nvl(mr_rec.segment25_low, gc.segment25)
218 and nvl(mr_rec.segment25_high, gc.segment25)) OR
219 (mr_rec.segment25_low IS NULL and mr_rec.segment25_high IS NULL))
220 AND ((gc.segment26 between nvl(mr_rec.segment26_low, gc.segment26)
221 and nvl(mr_rec.segment26_high, gc.segment26)) OR
222 (mr_rec.segment26_low IS NULL and mr_rec.segment26_high IS NULL))
223 AND ((gc.segment27 between nvl(mr_rec.segment27_low, gc.segment27)
224 and nvl(mr_rec.segment27_high, gc.segment27)) OR
225 (mr_rec.segment27_low IS NULL and mr_rec.segment27_high IS NULL))
226 AND ((gc.segment28 between nvl(mr_rec.segment28_low, gc.segment28)
227 and nvl(mr_rec.segment28_high, gc.segment28)) OR
228 (mr_rec.segment28_low IS NULL and mr_rec.segment28_high IS NULL))
229 AND ((gc.segment29 between nvl(mr_rec.segment29_low, gc.segment29)
230 and nvl(mr_rec.segment29_high, gc.segment29)) OR
231 (mr_rec.segment29_low IS NULL and mr_rec.segment29_high IS NULL))
232 AND ((gc.segment30 between nvl(mr_rec.segment30_low, gc.segment30)
233 and nvl(mr_rec.segment30_high, gc.segment30)) OR
234 (mr_rec.segment30_low IS NULL and mr_rec.segment30_high IS NULL))
235 -- more check is done on retired asset in reclass validation engine.
236 -- more check is done on reserved asset in Check_Criteria function.
237 AND bk.period_counter_fully_retired IS NULL
238 ORDER BY ad.asset_number;
239
240 h_request_id NUMBER;
241 h_msg_count NUMBER;
242 h_msg_data VARCHAR2(2000) := NULL;
243 h_preview_status_d VARCHAR2(80);
244 h_current_status_d VARCHAR2(80);
245 h_status BOOLEAN := FALSE;
246 -- to keep track of the last asset id that entered the mass_reclass_assets
247 -- cursor loop. we need this to avoid DISTINCT in the SELECT statement
248 -- for mass_reclass_assets cursor. asset may be selected multiple times
249 -- if it is multi-distributed and if more than one distribution lines
250 -- meet the reclass selection criteria(if at least one distribution line
251 -- meets user criteria, the asset is selected for reclass.)
252 h_last_asset NUMBER(15) := NULL;
253 h_cat_flex_struct NUMBER;
254 /* Bug#14062713: Increased variable size */
255 h_new_concat_cat VARCHAR2(1000); -- new category in concatenated string.
256 h_cat_segs FA_RX_SHARED_PKG.Seg_Array;
257 h_debug_flag VARCHAR2(3) := 'NO';
258 -- exception raised from this module and child modules.
259 mrcl_failure EXCEPTION;
260 h_dummy VARCHAR2(30);
261 -- Commit results per every 200 assets.
262 h_commit_level NUMBER := 200;
263 /* do not need these variables as per bug 8402286
264 need to remove large rollback segment
265 rbs_name VARCHAR2(30);
266 sql_stmt VARCHAR2(101);
267 */
268
269 BEGIN
270
271 -- Initialize message stacks.
272 FA_SRVR_MSG.Init_Server_Message;
273
274 FA_DEBUG_PKG.Initialize;
275
276 FA_DEBUG_PKG.SET_DEBUG_FLAG;
277
278 -- Set debug flag.
279 IF (g_print_debug) THEN
280 h_debug_flag := 'YES';
281 END IF;
282
283 if (g_print_debug) then
284 fa_debug_pkg.add('FARX_RP.Preview_Reclass',
285 'Starting Preview',
286 '');
287 end if;
288 /* Fix for BUG# 1302611 where rbs_name was being intepreted as a literal
289 rather than using the value in the variable
290 */
291 /* Bug 8402286 removing LARGE ROLLBACK SEGMENT
292 -- Set large rollback segment.
293 fnd_profile.get('FA_LARGE_ROLLBACK_SEGMENT', rbs_name);
294 IF (rbs_name is not null) THEN
295 sql_stmt := 'Set Transaction Use Rollback Segment '|| rbs_name;
296 execute immediate sql_stmt;
297 END IF;
298 */
299
300 -- Initialize global variables.
301 -- (These are session specific variables, and thus values need to
302 -- be re-initialized.)
303 a_tbl.delete;
304 a_index := 0;
305 g_asset_count := 0;
306 --g_total_assets := 0;
307
308 -- Get concurrent request id for the mass reclass preview request.
309 -- h_request_id is used when request_id is inserted into the interface
310 -- table, fa_mass_reclass_itf.
311 -- Need to fetch request id from fnd_global package instead of fa_mass_reclass
312 -- table, since fa_mass_reclass table stores the latest request id for
313 -- the SRS Preview report requests(run after this module) or
314 -- Run requests only.
315 h_request_id := fnd_global.conc_request_id;
316
317 -- Fetch mass reclass record information.
318 OPEN mass_reclass;
319 FETCH mass_reclass INTO mr_rec;
320 CLOSE mass_reclass;
321
322 -- Concurrent request id fetched from fa_mass_reclass table is in no use
323 -- in the preview module.
324 -- Assign h_request_id to the global mass reclass record field so that
325 -- it can be used in other procedures.
326 mr_rec.conc_request_id := h_request_id;
327
328 /*=========================================================================
329 Delete rows previously inserted into the interface table with the same
330 request id, if there is any.
331 =========================================================================*/
332 DELETE FROM fa_mass_reclass_itf
333 WHERE request_id = h_request_id;
334 COMMIT;
335
336 /*=========================================================================
337 Check to make sure current status is 'PREVIEW'
341 CLOSE get_status;
338 =========================================================================*/
339 OPEN get_status;
340 FETCH get_status INTO h_preview_status_d, h_current_status_d;
342
343 IF (h_preview_status_d <> h_current_status_d) THEN
344 -- Re-using message for mass reclass program.
345 FA_SRVR_MSG.Add_Message(
346 CALLING_FN => 'FARX_RP.Preview_Reclass',
347 NAME => 'FA_MASSRCL_WRONG_STATUS',
348 TOKEN1 => 'CURRENT',
349 VALUE1 => h_current_status_d,
350 TOKEN2 => 'RUNNING',
351 VALUE2 => h_preview_status_d);
352 -- Preview will complete with error status.
353 RAISE mrcl_failure;
354 END IF;
355
356 /*=========================================================================
357 Check if reclass transaction date for the mass reclass record from
358 mass reclass form is in the current corporate book period.
359 (No prior period reclass is allowed.)
360 =========================================================================*/
361 IF NOT FA_MASS_RECLASS_PKG.Check_Trans_Date(
362 X_Corp_Book => mr_rec.book_type_code,
363 X_Trans_Date => mr_rec.trans_date_entered) THEN
364 RAISE mrcl_failure;
365 END IF;
366
367 /*=========================================================================
368 Validate assets and insert preview records into the interface table.
369 =========================================================================*/
370 IF (mr_rec.redefault_flag = 'YES') THEN
371 -- Depreciation rules will be redefaulted.
372 -- Reset g_deprn_count before loading the cache table.
373 FA_LOAD_TBL_PKG.g_deprn_count := 0;
374 -- Load depreciation rules table for the corporate book and all the
375 -- associated tax books for the new category.
376 -- Simulates caching effect.
377 FA_LOAD_TBL_PKG.Load_Deprn_Rules_Tbl(
378 p_corp_book => mr_rec.book_type_code,
379 p_category_id => mr_rec.to_category_id,
380 x_return_status => h_status,
381 p_log_level_rec => g_log_level_rec);
382 IF NOT h_status THEN
383 RAISE mrcl_failure;
384 END IF;
385 -- Load the conversion table to store converted values for certain
386 -- fields of the depreciation rules records in the depreciation rules
387 -- table.
388 FA_MASS_REC_UTILS_PKG.Load_Conversion_Table(p_log_level_rec => g_log_level_rec);
389 END IF;
390
391 /* Get category flex structure. */
392 OPEN get_cat_flex_struct;
393 FETCH get_cat_flex_struct INTO h_cat_flex_struct;
394 CLOSE get_cat_flex_struct;
395
396 /* Get the new category code from the new category id. */
397 FA_RX_SHARED_PKG.Concat_Category(
398 struct_id => h_cat_flex_struct,
399 ccid => mr_rec.to_category_id,
400 concat_string => h_new_concat_cat,
401 segarray => h_cat_segs);
402
403 /* Loop all the qualified assets, and insert all the validated assets
404 into the interface table, fa_mass_reclass_itf. */
405 OPEN mass_reclass_assets;
406
407 LOOP
408 a_index := a_index + 1;
409 FETCH mass_reclass_assets
410 INTO a_tbl(a_index).asset_id, a_tbl(a_index).asset_number,
411 a_tbl(a_index).description, a_tbl(a_index).category_id;
412 EXIT WHEN mass_reclass_assets%NOTFOUND;
413
414 IF (a_tbl(a_index).asset_id <> h_last_asset OR
415 h_last_asset IS NULL) THEN
416 -- This is the first time the asset entered this loop.
417
418 -- Save the asset id for the next loop.
419 h_last_asset := a_tbl(a_index).asset_id;
420
421 -- Check if the asset meets the additional user criteria.
422 IF FA_MASS_RECLASS_PKG.Check_Criteria(
423 X_Asset_Id => a_tbl(a_index).asset_id,
424 X_Fully_Rsvd_Flag => mr_rec.fully_rsvd_flag) THEN
425
426 if (g_print_debug) then
427 fa_debug_pkg.add('FARX_RP.Preview_Reclass',
428 'Preview - after check_criteria',
429 a_tbl(a_index).asset_id );
430 end if;
431 IF (a_tbl(a_index).category_id = mr_rec.to_category_id) THEN
432 -- Reclass and redefault are not processed on the asset, if
433 -- the new category is the same as the old category.
434 -- Retrieve the old asset information into the pl/sql table,
435 -- a_tbl for all the books the asset belongs to.
436 -- (At every 200 assets, the records stored in a_tbl, will be inserted
437 -- into the interface table.)
438 Store_Results(X_Get_New_Rules => 'NO',
439 X_Cat_Flex_Struct => h_cat_flex_struct);
440 -- Increment the number of assets stored in a_tbl.
441 g_asset_count := g_asset_count + 1;
442 ELSE
443 /* Validate asset for reclass with Reclass Validation engine. */
444 IF FA_REC_PVT_PKG1.Validate_Reclass_Basic(
445 p_asset_id => a_tbl(a_index).asset_id,
446 p_old_category_id => a_tbl(a_index).category_id,
447 p_new_category_id => mr_rec.to_category_id,
448 p_mr_req_id => h_request_id,
449 x_old_cat_type => h_dummy,
450 p_log_level_rec => g_log_level_rec) THEN
451 -- Now, we allow redefault to be performed per book.
452 -- Validate redefault per each book and store the
453 -- results in the asset table.
454 Store_Results(X_Get_New_Rules => 'YES',
455 X_Cat_Flex_Struct => h_cat_flex_struct);
456 -- Increment the number of assets stored in a_tbl.
457 g_asset_count := g_asset_count + 1;
461 'Preview - validate_reclass_basic skipped asset',
458 ELSE
459 if (g_print_debug) then
460 fa_debug_pkg.add('FARX_RP.Preview_Reclass',
462 a_tbl(a_index).asset_id );
463 end if;
464 -- Basic reclass validation failed. Reclass fails.
465 a_index := a_index - 1;
466 END IF; /* IF FA_REC_PVT_PKG1.Validate_Reclass_Basic */
467 END IF; /* IF (a_tbl(a_index).category_id = */
468 ELSE
469 if (g_print_debug) then
470 fa_debug_pkg.add('FARX_RP.Preview_Reclass',
471 'Preview - check_criteria skipped asset ',
472 a_tbl(a_index).asset_id );
473 end if;
474 -- Invalid asset record.
475 a_index := a_index - 1;
476 END IF; /* IF FA_MASS_RECLASS_PKG.Check_Criteria */
477 ELSE /* a_tbl(a_index).asset_id = h_last_asset */
478 -- This asset has already been validated. Decrementing index count
479 -- to avoid records with duplicate asset id's in a_tbl.
480 a_index := a_index - 1;
481 END IF; /* IF (a_tbl(a_index).asset_id <> */
482
483 /* Insert asset records into the interface table, FA_MASS_RECLASS_ITF,
484 at every 200 assets. */
485 -- If g_asset_count(number of valid assets) = 200, insert all the 200
486 -- asset records in a_tbl(1..a_index) into the interface table,
487 -- re-initialize the pl/sql table, a_tbl, and reset g_asset_count
488 -- and a_index to 0. Commit changes at every 200 assets as well.
489 IF (g_asset_count = h_commit_level) THEN
490 FOR i IN 1 .. a_index LOOP
491 if (g_print_debug) then
492 fa_debug_pkg.add('FARX_RP.Preview_Reclass',
493 'Preview - inserting asset into itf-table',
494 a_tbl(a_index).asset_id );
495 end if;
496 FA_MASS_REC_UTILS_PKG.Insert_Itf(
497 X_Report_Type => 'PREVIEW',
498 X_Request_Id => h_request_id,
499 X_Mass_Reclass_Id => X_Mass_Reclass_Id,
500 X_Asset_Rec => a_tbl(i),
501 X_New_Category => h_new_concat_cat,
502 X_Last_Update_Date => mr_rec.last_update_date,
503 X_Last_Updated_By => mr_rec.last_updated_by,
504 X_Created_By => mr_rec.created_by,
505 X_Creation_Date => mr_rec.creation_date,
506 X_Last_Update_Login => mr_rec.last_update_login,
507 p_log_level_rec => g_log_level_rec
508 );
509 END LOOP;
510 a_tbl.delete;
511 --g_total_assets := g_total_assets + g_asset_count;
512 g_asset_count := 0;
513 a_index := 0;
514 COMMIT WORK;
515 END IF;
516
517 END LOOP;
518
519 CLOSE mass_reclass_assets;
520
521 /* Insert the remaining valid asset records into the interface table. */
522 -- Up to a_index - 1, to account for the extra increment taken for a_index
523 -- when no more rows were found in the cursor loop.
524 FOR i IN 1 .. (a_index - 1) LOOP
525 FA_MASS_REC_UTILS_PKG.Insert_Itf(
526 X_Report_Type => 'PREVIEW',
527 X_Request_Id => h_request_id,
528 X_Mass_Reclass_Id => X_Mass_Reclass_Id,
529 X_Asset_Rec => a_tbl(i),
530 X_New_Category => h_new_concat_cat,
531 X_Last_Update_Date => mr_rec.last_update_date,
532 X_Last_Updated_By => mr_rec.last_updated_by,
533 X_Created_By => mr_rec.created_by,
534 X_Creation_Date => mr_rec.creation_date,
535 X_Last_Update_Login => mr_rec.last_update_login,
536 p_log_level_rec => g_log_level_rec
537 );
538 END LOOP;
539 a_tbl.delete;
540 --g_total_assets := g_total_assets + g_asset_count;
541 g_asset_count := 0;
542 a_index := 0;
543 COMMIT WORK;
544
545 IF (mr_rec.redefault_flag = 'YES') THEN
546 -- Reset g_deprn_count after completing mass reclass transaction.
547 FA_LOAD_TBL_PKG.g_deprn_count := 0;
548 END IF;
549
550 /*=========================================================================
551 Fetch the preview records from the interface table and print them on
552 the SRS output screen for the preview report.
553 =========================================================================*/
554 /* Commenting out, since this will be taken care of by SRS report(FASRCPVW.rdf.)
555 FA_MASS_REC_UTILS_PKG.Print_RX_Report(
556 X_Report_Type => 'PREVIEW',
557 X_Mass_Reclass_Rec => mr_rec,
558 X_Num_Assets => g_total_assets);
559 */
560
561 /*=========================================================================
562 Update the status of the mass reclass to 'PREVIEWED'
563 (This step is now handled in SRS report(FASRCPVW.rdf), which is fired
564 after the RX report request.)
565 =========================================================================*/
566 /*
567 UPDATE fa_mass_reclass
568 SET status = 'PREVIEWED'
569 WHERE mass_reclass_id = X_Mass_Reclass_Id
570 AND status = 'PREVIEW';
571 COMMIT WORK;
572 */
573 errbuf := ''; -- No error.
574 retcode := 0; -- Completed normally.
575
576 EXCEPTION
577 WHEN mrcl_failure THEN
578 retcode := 2; -- Completed with error.
579
580 -- Reset global variable values.
581 FA_LOAD_TBL_PKG.g_deprn_count := 0;
582 a_tbl.delete;
583 a_index := 0;
584 g_asset_count := 0;
585 --g_total_assets := 0;
586 /* A fatal error has occurred. Update status to 'FAILED_PRE'. */
587 ROLLBACK WORK;
588 UPDATE fa_mass_reclass
589 SET status = 'FAILED_PRE'
590 WHERE mass_reclass_id = X_Mass_Reclass_Id;
591 /* Delete rows inserted into the interface table. */
592 DELETE FROM fa_mass_reclass_itf
596 /* Retrieve message log and write result to log and output. */
593 WHERE request_id = h_request_id;
594 /* Commit changes. */
595 COMMIT WORK;
597 IF (X_RX_Flag = 'YES') THEN
598 FND_MSG_PUB.Count_And_Get(
599 p_count => h_msg_count,
600 p_data => h_msg_data);
601 FA_SRVR_MSG.Write_Msg_Log(
602 msg_count => h_msg_count,
603 msg_data => h_msg_data);
604 IF (h_debug_flag = 'YES') THEN
605 FA_DEBUG_PKG.Write_Debug_Log;
606 END IF;
607 END IF;
608 WHEN OTHERS THEN
609 retcode := 2; -- Completed with error.
610 IF SQLCODE <> 0 THEN
611 FA_SRVR_MSG.Add_SQL_Error(
612 CALLING_FN => 'FARX_RP.Preview_Reclass');
613 END IF;
614
615 -- Reset global variable values.
616 FA_LOAD_TBL_PKG.g_deprn_count := 0;
617 a_tbl.delete;
618 a_index := 0;
619 g_asset_count := 0;
620 --g_total_assets := 0;
621 /* A fatal error has occurred. Update status to 'FAILED_PRE'. */
622 ROLLBACK WORK;
623 UPDATE fa_mass_reclass
624 SET status = 'FAILED_PRE'
625 WHERE mass_reclass_id = X_Mass_Reclass_Id;
626 /* Delete rows inserted into the interface table. */
627 DELETE FROM fa_mass_reclass_itf
628 WHERE request_id = h_request_id;
629 /* Commit changes. */
630 COMMIT WORK;
631 /* Retrieve message log and write result to log and output. */
632 IF (X_RX_Flag = 'YES') THEN
633 FND_MSG_PUB.Count_And_Get(
634 p_count => h_msg_count,
635 p_data => h_msg_data);
636 FA_SRVR_MSG.Write_Msg_Log(
637 msg_count => h_msg_count,
638 msg_data => h_msg_data);
639 IF (h_debug_flag = 'YES') THEN
640 FA_DEBUG_PKG.Write_Debug_Log;
641 END IF;
642 END IF;
643 END Preview_Reclass;
644
645
646 /*============================================================================+
647 | PROCEDURE Store_Results |
648 +=============================================================================*/
649
650 PROCEDURE Store_Results(
651 X_Get_New_Rules IN VARCHAR2 := 'NO',
652 X_Cat_Flex_Struct IN NUMBER := NULL
653 ) IS
654
655 h_book_type_code VARCHAR2(30) := NULL;
656 h_cat_flex_struct NUMBER := X_Cat_Flex_Struct;
657 h_concat_cat VARCHAR2(220);
658 h_cat_segs FA_RX_SHARED_PKG.Seg_Array;
659 h_dpis DATE;
660 h_depreciate_flag VARCHAR2(3);
661 pos NUMBER;
662 h_dummy_bool1 BOOLEAN;
663 h_dummy_bool2 BOOLEAN;
664 h_dummy_rules1 FA_LOAD_TBL_PKG.asset_deprn_info;
665 h_dummy_rules2 FA_LOAD_TBL_PKG.asset_deprn_info;
666 h_dummy_date DATE;
667 h_dummy_char1 VARCHAR2(10);
668 h_dummy_char2 VARCHAR2(4);
669 store_failure EXCEPTION;
670
671 -- cursor to get all the corporate and tax books the asset belongs to.
672 -- books are ordered in alphabetical order in preview report.
673 CURSOR book_cr IS
674 SELECT bk.book_type_code
675 FROM fa_book_controls bc, fa_books bk
676 WHERE bk.asset_id = a_tbl(a_index).asset_id
677 AND bk.date_ineffective IS NULL
678 AND bk.book_type_code = bc.book_type_code
679 AND bc.book_class IN ('CORPORATE', 'TAX')
680 AND nvl(bc.date_ineffective, sysdate+1) > sysdate
681 ORDER BY bk.book_type_code;
682 /*
683 SELECT TH.book_type_code
684 FROM FA_BOOK_CONTROLS BC,
685 FA_TRANSACTION_HEADERS TH
686 WHERE TH.transaction_type_code||'' IN ('ADDITION','CIP ADDITION')
687 AND TH.asset_id = a_tbl(a_index).asset_id
688 AND BC.book_type_code = TH.book_type_code
689 AND nvl(BC.date_ineffective, sysdate + 1) > sysdate
690 GROUP BY TH.book_type_code
691 ORDER BY MIN(TH.date_effective);
692 */
693
694 -- cursor to get the category flex structure.
695 CURSOR get_cat_flex_struct IS
696 SELECT category_flex_structure
697 FROM fa_system_controls;
698
699 -- cursor to get date placed in service and depreciate flag.
700 CURSOR get_dpis_deprn_flag IS
701 SELECT date_placed_in_service, depreciate_flag
702 FROM FA_BOOKS
703 WHERE asset_id = a_tbl(a_index).asset_id
704 AND book_type_code = h_book_type_code
705 AND date_ineffective IS NULL;
706
707 -- cursor to get the old(current) depreciation rules.
708 CURSOR get_old_rules IS
709 SELECT prorate_convention_code, ceiling_name, deprn_method_code,
710 life_in_months, basic_rate, adjusted_rate,
711 bonus_rule, production_capacity, unit_of_measure,
712 depreciate_flag, allowed_deprn_limit, allowed_deprn_limit_amount,
713 percent_salvage_value
714 FROM FA_BOOKS
715 WHERE asset_id = a_tbl(a_index).asset_id
716 AND book_type_code = h_book_type_code
717 AND date_ineffective IS NULL;
718
719 BEGIN
720 -- Get the category flexfield structure, if value not provided.
721 IF X_Cat_Flex_Struct IS NULL THEN
722 OPEN get_cat_flex_struct;
723 FETCH get_cat_flex_struct INTO h_cat_flex_struct;
724 CLOSE get_cat_flex_struct;
725 END IF;
726
727 -- Get the category in concatenated string for the asset's current
728 -- category.
729 FA_RX_SHARED_PKG.Concat_Category(
730 struct_id => h_cat_flex_struct,
734
731 ccid => a_tbl(a_index).category_id,
732 concat_string => h_concat_cat,
733 segarray => h_cat_segs);
735 IF (X_Get_New_Rules = 'YES') THEN
736 OPEN book_cr;
737 LOOP
738 FETCH book_cr INTO h_book_type_code;
739 EXIT WHEN book_cr%notfound;
740
741 /* For each book, store a preview record for an asset with
742 new depreciation rules, if redefault is allowed and if user
743 chooses to redefault the depreciation rules. Otherwise,
744 there is no change in depreciation rules for the book. */
745 -- asset_id, asset_number, category_id fields are already assigned.
746 a_tbl(a_index).book_type_code := h_book_type_code;
747 a_tbl(a_index).category := h_concat_cat;
748
749 IF (mr_rec.redefault_flag = 'YES') THEN
750 -- User chooses to redefault. Validate redefault.
751 IF FA_REC_PVT_PKG2.Validate_Redefault(
752 p_asset_id => a_tbl(a_index).asset_id,
753 p_new_category_id => mr_rec.to_category_id,
754 p_book_type_code => h_book_type_code,
755 p_amortize_flag => mr_rec.amortize_flag,
756 p_mr_req_id => mr_rec.conc_request_id,
757 x_rule_change_exists => h_dummy_bool1,
758 x_old_rules => h_dummy_rules1,
759 x_new_rules => h_dummy_rules2,
760 x_use_rules => h_dummy_bool2,
761 x_prorate_date => h_dummy_date,
762 x_rate_source_rule => h_dummy_char1,
763 x_deprn_basis_rule => h_dummy_char2,
764 p_log_level_rec => g_log_level_rec) AND
765 Check_Trans_Date_Book(
766 X_Asset_Id => a_tbl(a_index).asset_id,
767 X_Book_Type_Code => h_book_type_code,
768 X_Trx_Type => 'ADJUSTMENT')
769
770 /* Now also check the transaction_date_entered for the asset
771 in each book in which we try to perform redefault.
772 Make sure no other transaction follows.
773 Transaction_date_entered values are validated in reclass/redefault
774 engines, as the values required for the validation are all
775 calculated in the transaction engine. Here, we separate this
776 logic out nocopy for redefault.
777 FA_MASS_RECLASS_PKG.Check_Trans_Date(called in Preview_Reclass())
778 performs an equivalent validation for transaction_date_entered
779 for the reclass part. */
780 THEN
781 -- Validation for redefault succeeded in this book.
782 -- Store new rules for this book.
783 -- Get the date placed in service of the asset to fetch
784 -- the new depreciation rules.
785 -- Also get depreciate flag, since we will not redefault depreciate
786 -- flag through mass reclass any more.
787 OPEN get_dpis_deprn_flag;
788 FETCH get_dpis_deprn_flag INTO h_dpis, h_depreciate_flag;
789 CLOSE get_dpis_deprn_flag;
790
791 -- Get new depreciation rules.
792
793 -- First, get the index of the new depreciation rules record
794 -- from the depreciation table.
795 FA_LOAD_TBL_PKG.Find_Position_Deprn_Rules(
796 p_book_type_code => h_book_type_code,
797 p_date_placed_in_service => h_dpis,
798 x_pos => pos,
799 p_log_level_rec => g_log_level_rec);
800 IF pos IS NULL THEN
801 FA_SRVR_MSG.Add_Message(
802 CALLING_FN => 'FARX_RP.Store_Results',
803 NAME => 'FA_REC_NO_CAT_DEFAULTS');
804 raise store_failure;
805 END IF;
806
807 a_tbl(a_index).convention
808 := FA_LOAD_TBL_PKG.deprn_table(pos).prorate_conv_code;
809 a_tbl(a_index).ceiling
810 := FA_LOAD_TBL_PKG.deprn_table(pos).ceiling_name;
811 a_tbl(a_index).method
812 := FA_LOAD_TBL_PKG.deprn_table(pos).deprn_method;
813 a_tbl(a_index).life_in_months
814 := FA_LOAD_TBL_PKG.deprn_table(pos).life_in_months;
815 a_tbl(a_index).basic_rate
816 := FA_LOAD_TBL_PKG.deprn_table(pos).basic_rate;
817 a_tbl(a_index).adjusted_rate
818 := FA_LOAD_TBL_PKG.deprn_table(pos).adjusted_rate;
819 a_tbl(a_index).bonus_rule
820 := FA_LOAD_TBL_PKG.deprn_table(pos).bonus_rule;
821 a_tbl(a_index).capacity
822 := FA_LOAD_TBL_PKG.deprn_table(pos).production_capacity;
823 a_tbl(a_index).unit_of_measure
824 := FA_LOAD_TBL_PKG.deprn_table(pos).unit_of_measure;
825 -- We will not redefault depreciate flag through mass reclass.
826 a_tbl(a_index).depreciate_flag := h_depreciate_flag;
827 a_tbl(a_index).allowed_deprn_limit
828 := FA_LOAD_TBL_PKG.deprn_table(pos).allow_deprn_limit;
829 a_tbl(a_index).deprn_limit_amt
830 := FA_LOAD_TBL_PKG.deprn_table(pos).deprn_limit_amount;
831 a_tbl(a_index).percent_salvage_val
832 := FA_LOAD_TBL_PKG.deprn_table(pos).percent_salvage_value;
833
834 -- Convert values using conversion_table, which caches
835 -- converted values of certain fields for the new rules.
836 -- (The corresponding converted record is stored in the
837 -- same index position in conv_tbl as that of deprn_table.)
838 a_tbl(a_index).life := FA_MASS_REC_UTILS_PKG.conv_tbl(pos).life;
839 a_tbl(a_index).basic_rate_pct
840 := FA_MASS_REC_UTILS_PKG.conv_tbl(pos).basic_rate_pct;
841 a_tbl(a_index).adjusted_rate_pct
842 := FA_MASS_REC_UTILS_PKG.conv_tbl(pos).adjusted_rate_pct;
846 := FA_MASS_REC_UTILS_PKG.conv_tbl(pos).salvage_val_pct;
843 a_tbl(a_index).deprn_limit_pct
844 := FA_MASS_REC_UTILS_PKG.conv_tbl(pos).deprn_limit_pct;
845 a_tbl(a_index).salvage_val_pct
847 ELSE /* IF FA_REC_PVT_PKG2.Validate_Redefault */
848 -- Validation for redefault failed in this book.
849 -- Store old results for this book.
850 -- Get the old(current) depreciation rules.
851 OPEN get_old_rules;
852 FETCH get_old_rules
853 INTO a_tbl(a_index).convention, a_tbl(a_index).ceiling,
854 a_tbl(a_index).method,
855 a_tbl(a_index).life_in_months, a_tbl(a_index).basic_rate,
856 a_tbl(a_index).adjusted_rate,
857 a_tbl(a_index).bonus_rule, a_tbl(a_index).capacity,
858 a_tbl(a_index).unit_of_measure,
859 a_tbl(a_index).depreciate_flag,
860 a_tbl(a_index).allowed_deprn_limit,
861 a_tbl(a_index).deprn_limit_amt,
862 a_tbl(a_index).percent_salvage_val;
863 CLOSE get_old_rules;
864
865 -- Convert formats for certain fields.
866 FA_MASS_REC_UTILS_PKG.Convert_Formats(
867 X_Life_In_Months => a_tbl(a_index).life_in_months,
868 X_Basic_Rate => a_tbl(a_index).basic_rate,
869 X_Adjusted_Rate => a_tbl(a_index).adjusted_rate,
870 X_Allowed_Deprn_Limit => a_tbl(a_index).allowed_deprn_limit,
871 X_Percent_Salvage_Val => a_tbl(a_index).percent_salvage_val,
875 X_Deprn_Limit_Pct => a_tbl(a_index).deprn_limit_pct,
872 X_Life => a_tbl(a_index).life,
873 X_Basic_Rate_Pct => a_tbl(a_index).basic_rate_pct,
874 X_Adjusted_Rate_Pct => a_tbl(a_index).adjusted_rate_pct,
876 X_Salvage_Val_Pct => a_tbl(a_index).salvage_val_pct,
877 p_log_level_rec => g_log_level_rec
878 );
879 END IF; /* IF FA_REC_PVT_PKG2.Validate_Redefault */
880 ELSE /* IF (mr_rec.redefault_flag = 'YES') */
881 -- User chooses not to redefault.
882 -- Store old results.
883 -- Get the old(current) depreciation rules.
884 OPEN get_old_rules;
885 FETCH get_old_rules
886 INTO a_tbl(a_index).convention, a_tbl(a_index).ceiling,
887 a_tbl(a_index).method,
888 a_tbl(a_index).life_in_months, a_tbl(a_index).basic_rate,
889 a_tbl(a_index).adjusted_rate,
890 a_tbl(a_index).bonus_rule, a_tbl(a_index).capacity,
891 a_tbl(a_index).unit_of_measure,
892 a_tbl(a_index).depreciate_flag,
893 a_tbl(a_index).allowed_deprn_limit,
894 a_tbl(a_index).deprn_limit_amt,
895 a_tbl(a_index).percent_salvage_val;
896 CLOSE get_old_rules;
897
898 -- Convert formats for certain fields.
899 FA_MASS_REC_UTILS_PKG.Convert_Formats(
900 X_Life_In_Months => a_tbl(a_index).life_in_months,
901 X_Basic_Rate => a_tbl(a_index).basic_rate,
902 X_Adjusted_Rate => a_tbl(a_index).adjusted_rate,
903 X_Allowed_Deprn_Limit => a_tbl(a_index).allowed_deprn_limit,
904 X_Percent_Salvage_Val => a_tbl(a_index).percent_salvage_val,
905 X_Life => a_tbl(a_index).life,
906 X_Basic_Rate_Pct => a_tbl(a_index).basic_rate_pct,
907 X_Adjusted_Rate_Pct => a_tbl(a_index).adjusted_rate_pct,
908 X_Deprn_Limit_Pct => a_tbl(a_index).deprn_limit_pct,
909 X_Salvage_Val_Pct => a_tbl(a_index).salvage_val_pct,
910 p_log_level_rec => g_log_level_rec
911 );
912 END IF; /* IF (mr_rec.redefault_flag = 'YES') */
913
914 -- The following values are used in review reports only.
915 a_tbl(a_index).cost_acct_ccid := NULL;
916 a_tbl(a_index).cost_acct := NULL;
917 a_tbl(a_index).deprn_rsv_acct_ccid := NULL;
918 a_tbl(a_index).deprn_rsv_acct := NULL;
919
920 -- Propagate asset_id, asset_number, category_id to the next
921 -- book record for the asset.
922 a_tbl(a_index + 1).asset_id := a_tbl(a_index).asset_id;
923 a_tbl(a_index + 1).asset_number := a_tbl(a_index).asset_number;
924 a_tbl(a_index + 1).description := a_tbl(a_index).description;
925 a_tbl(a_index + 1).category_id := a_tbl(a_index).category_id;
926
927 -- Increment the index position for the next book to be stored.
928 a_index := a_index + 1;
929
930 END LOOP;
931
932 -- Decrement a_index by 1 to cancel the extra index movement made
933 -- at the last book entry.
934 a_index := a_index - 1;
935
936 CLOSE book_cr;
937 ELSE /* X_Get_New_Rules = 'NO' */
938 OPEN book_cr;
939 LOOP
940 FETCH book_cr INTO h_book_type_code;
941 EXIT WHEN book_cr%notfound;
942
943 /* For each book, store a preview record for an asset in the
944 asset table. Depreciation rules remain unchanged for
945 the asset. */
946 -- asset_id, asset_number, category_id fields are already assigned.
947 a_tbl(a_index).book_type_code := h_book_type_code;
948 a_tbl(a_index).category := h_concat_cat;
949
950 -- Get the old(current) depreciation rules.
951 OPEN get_old_rules;
952 FETCH get_old_rules
953 INTO a_tbl(a_index).convention, a_tbl(a_index).ceiling,
954 a_tbl(a_index).method,
955 a_tbl(a_index).life_in_months, a_tbl(a_index).basic_rate,
956 a_tbl(a_index).adjusted_rate,
957 a_tbl(a_index).bonus_rule, a_tbl(a_index).capacity,
958 a_tbl(a_index).unit_of_measure,
959 a_tbl(a_index).depreciate_flag, a_tbl(a_index).allowed_deprn_limit,
960 a_tbl(a_index).deprn_limit_amt,
961 a_tbl(a_index).percent_salvage_val;
962 CLOSE get_old_rules;
963
964 -- The following values are used in review reports only.
965 a_tbl(a_index).cost_acct_ccid := NULL;
966 a_tbl(a_index).cost_acct := NULL;
967 a_tbl(a_index).deprn_rsv_acct_ccid := NULL;
968 a_tbl(a_index).deprn_rsv_acct := NULL;
969
970 -- Convert formats for certain fields.
971 FA_MASS_REC_UTILS_PKG.Convert_Formats(
972 X_Life_In_Months => a_tbl(a_index).life_in_months,
973 X_Basic_Rate => a_tbl(a_index).basic_rate,
974 X_Adjusted_Rate => a_tbl(a_index).adjusted_rate,
975 X_Allowed_Deprn_Limit => a_tbl(a_index).allowed_deprn_limit,
976 X_Percent_Salvage_Val => a_tbl(a_index).percent_salvage_val,
977 X_Life => a_tbl(a_index).life,
978 X_Basic_Rate_Pct => a_tbl(a_index).basic_rate_pct,
979 X_Adjusted_Rate_Pct => a_tbl(a_index).adjusted_rate_pct,
980 X_Deprn_Limit_Pct => a_tbl(a_index).deprn_limit_pct,
981 X_Salvage_Val_Pct => a_tbl(a_index).salvage_val_pct,
982 p_log_level_rec => g_log_level_rec
983 );
984
985 -- Propagate asset_id, asset_number, category_id to the next
986 -- book record for the asset.
987 a_tbl(a_index + 1).asset_id := a_tbl(a_index).asset_id;
988 a_tbl(a_index + 1).asset_number := a_tbl(a_index).asset_number;
989 a_tbl(a_index + 1).description := a_tbl(a_index).description;
990 a_tbl(a_index + 1).category_id := a_tbl(a_index).category_id;
991
992 -- Increment the index position for the next book to be stored.
993 a_index := a_index + 1;
994
995 END LOOP;
996
997 -- Decrement a_index by 1 to cancel the extra index movement made
998 -- at the last book entry.
999 a_index := a_index - 1;
1000
1001 CLOSE book_cr;
1002 END IF; /* IF (X_Get_New_Rules = 'YES') */
1003
1004 EXCEPTION
1005 WHEN OTHERS THEN
1006 FA_SRVR_MSG.Add_SQL_Error(
1007 CALLING_FN => 'FARX_RP.Store_Results');
1008 raise;
1009 END Store_Results;
1010
1011
1012 /*============================================================================+
1013 | FUNCTIONE Check_Trans_Date_Book |
1014 +=============================================================================*/
1015
1016 FUNCTION Check_Trans_Date_Book(
1017 X_Asset_Id IN NUMBER,
1018 X_Book_Type_Code IN VARCHAR2,
1019 X_Trx_Type IN VARCHAR2
1020 ) RETURN BOOLEAN IS
1021 h_current_period_flag VARCHAR2(3) := 'N';
1022 h_trans_date DATE;
1023 h_dpis DATE;
1024 -- Cursor to check whether the asset was added in the current open period.
1025 CURSOR added_this_period IS
1026 SELECT 'Y'
1027 FROM FA_DEPRN_PERIODS dp,
1028 FA_TRANSACTION_HEADERS th
1029 WHERE th.book_type_code = X_Book_Type_Code
1030 AND th.asset_id = X_Asset_Id
1031 AND th.transaction_type_code||'' in ('ADDITION', 'CIP ADDITION')
1032 AND dp.book_type_code = X_Book_Type_Code
1033 AND dp.period_open_date <= th.date_effective
1034 AND nvl(dp.period_close_date, sysdate) > th.date_effective
1035 AND dp.period_close_date IS NULL
1036 AND th.transaction_date_entered >= dp.calendar_period_open_date;
1037 -- Cursor to get transaction date entered.
1038 CURSOR get_trx_date_entered IS
1039 SELECT greatest(dp.calendar_period_open_date,
1040 least(trunc(sysdate), dp.calendar_period_close_date))
1041 FROM FA_DEPRN_PERIODS dp
1042 WHERE dp.book_type_code = X_Book_Type_Code
1043 AND dp.period_close_date is null;
1044 -- Cursor to get date placed in service.
1045 CURSOR get_dpis IS
1046 SELECT date_placed_in_service
1047 FROM FA_BOOKS bk
1048 WHERE bk.asset_id = X_Asset_Id
1049 AND bk.book_type_code = X_Book_Type_Code
1050 AND date_ineffective IS NULL;
1051 BEGIN
1052 /* Get transaction_date_entered. */
1053 /* Adjustment calculates transaction_date_entered slightly differently from
1054 other transaction engines. If the asset is added in the current period,
1055 transaction_date_entered defaults to date placed in service. */
1056 IF (X_Trx_Type = 'ADJUSTMENT') THEN
1057 -- Check if the asset is added in the current period or not.
1058 OPEN added_this_period;
1059 FETCH added_this_period INTO h_current_period_flag;
1060 CLOSE added_this_period;
1061
1062 -- Get transaction_date_entered.
1063 IF (h_current_period_flag = 'Y') THEN
1064 -- Added in the current period.
1065 OPEN get_dpis;
1066 FETCH get_dpis INTO h_dpis;
1067 CLOSE get_dpis;
1068 h_trans_date := h_dpis;
1069 ELSE
1070 OPEN get_trx_date_entered;
1071 FETCH get_trx_date_entered INTO h_trans_date;
1072 CLOSE get_trx_date_entered;
1073 END IF;
1074 ELSE /* IF (X_Trx_Type = 'ADJUSTMENT') */
1075 OPEN get_trx_date_entered;
1076 FETCH get_trx_date_entered INTO h_trans_date;
1077 CLOSE get_trx_date_entered;
1078 END IF;
1079
1080 -- Check if any other transaction exists between the transaction date
1081 -- entered and the current date.
1082 IF NOT FA_REC_PVT_PKG1.Check_Trans_Date(
1083 p_asset_id => X_Asset_Id,
1084 p_book_type_code => X_Book_Type_Code,
1085 p_trans_date => h_trans_date,
1086 p_log_level_rec => g_log_level_rec)
1087 THEN
1088 FA_SRVR_MSG.Add_Message(
1089 CALLING_FN => 'FARX_RP.Check_Trans_Date_Book');
1090 RETURN (FALSE);
1091 END IF;
1092
1093 RETURN (TRUE);
1094
1095 EXCEPTION
1096 WHEN OTHERS THEN
1097 FA_SRVR_MSG.Add_SQL_Error(
1098 CALLING_FN => 'FARX_RP.Check_Trans_Date_Book');
1099 RETURN (FALSE);
1100 END Check_Trans_Date_Book;
1101
1102
1103 END FARX_RP;
[Home] [Help]