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