1 PACKAGE BODY FA_MASS_RECLASS_PKG AS
2 /* $Header: FAXMRCLB.pls 120.13 2005/11/11 08:04:56 tdewanga ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6 -- Mass reclass record from fa_mass_reclass table.
7 mr_rec FA_MASS_REC_UTILS_PKG.mass_reclass_rec;
8
9 /*====================================================================================+
10 | PROCEDURE Do_Mass_Reclass |
11 +=====================================================================================*/
12
13
14 PROCEDURE Do_Mass_Reclass(
15 p_mass_reclass_id IN NUMBER,
16 p_parent_request_id IN NUMBER,
17 p_total_requests IN NUMBER,
18 p_request_number IN NUMBER,
19 px_max_asset_id IN OUT NOCOPY NUMBER,
20 x_processed_count OUT NOCOPY NUMBER,
21 x_success_count OUT NOCOPY number,
22 x_failure_count OUT NOCOPY number,
23 x_return_status OUT NOCOPY number) IS
24
25 -- cursor to fetch mass reclass record from fa_mass_reclass
26 CURSOR mass_reclass IS
27 SELECT mr.mass_reclass_id,
28 mr.book_type_code,
29 mr.transaction_date_entered,
30 mr.concurrent_request_id,
31 mr.status,
32 mr.asset_type,
33 mr.location_id,
34 mr.employee_id,
35 mr.asset_key_id,
36 mr.from_cost,
37 mr.to_cost,
38 mr.from_asset_number,
39 mr.to_asset_number,
40 mr.from_date_placed_in_service,
41 mr.to_date_placed_in_service,
42 mr.from_category_id,
43 mr.to_category_id,
44 mr.segment1_low, mr.segment2_low, mr.segment3_low, mr.segment4_low,
45 mr.segment5_low, mr.segment6_low, mr.segment7_low, mr.segment8_low,
46 mr.segment9_low, mr.segment10_low, mr.segment11_low, mr.segment12_low,
47 mr.segment13_low, mr.segment14_low, mr.segment15_low, mr.segment16_low,
48 mr.segment17_low, mr.segment18_low, mr.segment19_low, mr.segment20_low,
49 mr.segment21_low, mr.segment22_low, mr.segment23_low, mr.segment24_low,
50 mr.segment25_low, mr.segment26_low, mr.segment27_low, mr.segment28_low,
51 mr.segment29_low, mr.segment30_low,
52 mr.segment1_high, mr.segment2_high, mr.segment3_high, mr.segment4_high,
53 mr.segment5_high, mr.segment6_high, mr.segment7_high, mr.segment8_high,
54 mr.segment9_high, mr.segment10_high, mr.segment11_high, mr.segment12_high,
55 mr.segment13_high, mr.segment14_high, mr.segment15_high, mr.segment16_high,
56 mr.segment17_high, mr.segment18_high, mr.segment19_high, mr.segment20_high,
57 mr.segment21_high, mr.segment22_high, mr.segment23_high, mr.segment24_high,
58 mr.segment25_high, mr.segment26_high, mr.segment27_high, mr.segment28_high,
59 mr.segment29_high, mr.segment30_high,
60 mr.include_fully_rsvd_flag,
61 mr.copy_cat_desc_flag,
62 mr.inherit_deprn_rules_flag,
63 mr.amortize_flag,
64 mr.created_by,
65 mr.creation_date,
66 mr.last_updated_by,
67 mr.last_update_login,
68 mr.last_update_date
69 FROM fa_mass_reclass mr
70 WHERE mass_reclass_id = p_mass_reclass_Id;
71
72 -- assets that meet the user's selection criteria.
73 -- some assets selected by this cursor are discarded in the validation engine.
74 CURSOR mass_reclass_assets IS
75 SELECT ad.asset_id,
76 ad.asset_number,
77 ad.asset_category_id,
78 dh.assigned_to
79 FROM gl_code_combinations gc,
80 fa_distribution_history dh,
81 fa_book_controls bc,
82 fa_books bk,
83 fa_additions_b ad
84 WHERE ad.asset_type = nvl(mr_rec.asset_type, ad.asset_type)
85 AND ad.asset_number >= nvl(mr_rec.from_asset_number, ad.asset_number)
86 AND ad.asset_number <= nvl(mr_rec.to_asset_number, ad.asset_number)
87 AND nvl(ad.asset_key_ccid, -9999) = nvl(mr_rec.asset_key_id,
88 nvl(ad.asset_key_ccid, -9999))
89 AND ad.asset_category_id = nvl(mr_rec.from_category_id, ad.asset_category_id)
90 AND bk.book_type_code = mr_rec.book_type_code
91 AND bk.book_type_code = bc.book_type_code
92 -- corp book should be currently effective.
93 AND nvl(bc.date_ineffective, sysdate+1) > sysdate
94 AND bk.asset_id = ad.asset_id
95 AND nvl(bk.disabled_flag, 'N') = 'N' --HH
96 AND bk.date_ineffective IS NULL -- pick the most recent row.
97 -- dpis, exp acct, employee, location, cost range: selection criteria
98 -- for corporate book only.
99 AND bk.date_placed_in_service >= nvl(mr_rec.from_dpis,
100 bk.date_placed_in_service)
101 AND bk.date_placed_in_service <= nvl(mr_rec.to_dpis,
102 bk.date_placed_in_service)
103 AND bk.cost >= nvl(mr_rec.from_cost, bk.cost)
104 AND bk.cost <= nvl(mr_rec.to_cost, bk.cost)
105 AND dh.asset_id = ad.asset_id
106 AND nvl(dh.assigned_to, -9999) = nvl(mr_rec.employee_id, nvl(dh.assigned_to, -9999))
107 AND dh.location_id = nvl(mr_rec.location_id, dh.location_id)
108 AND dh.date_ineffective IS NULL -- pick only the active distributions.
109 AND dh.code_combination_id = gc.code_combination_id
110 -- more check is done on retired asset in reclass validation engine.
111 -- more check is done on reserved asset in Check_Criteria function.
112 AND bk.period_counter_fully_retired IS NULL
113 -- cannot avoid the use of OR, since gc.segment1 can be null.
114 -- cannot use nvl(gc.segment1, 'NULL') for comparison, since
115 -- the value 'NULL' may fall between the range accidentally.
116 -- may break the OR to UNION later.
117 -- rule-based optimizer transforms OR to UNION ALL automatically
118 -- when it sees it being more efficient. since the columns
119 -- in OR are not indexed, transforming to UNION ALL has
120 -- no gain in performance and using OR is unavoidable here
121 -- for the correctness of the program.
122 AND ((gc.segment1 between nvl(mr_rec.segment1_low, gc.segment1)
123 and nvl(mr_rec.segment1_high, gc.segment1)) OR
124 (mr_rec.segment1_low IS NULL and mr_rec.segment1_high IS NULL))
125 AND ((gc.segment2 between nvl(mr_rec.segment2_low, gc.segment2)
126 and nvl(mr_rec.segment2_high, gc.segment2)) OR
127 (mr_rec.segment2_low IS NULL and mr_rec.segment2_high IS NULL))
128 AND ((gc.segment3 between nvl(mr_rec.segment3_low, gc.segment3)
129 and nvl(mr_rec.segment3_high, gc.segment3)) OR
130 (mr_rec.segment3_low IS NULL and mr_rec.segment3_high IS NULL))
131 AND ((gc.segment4 between nvl(mr_rec.segment4_low, gc.segment4)
132 and nvl(mr_rec.segment4_high, gc.segment4)) OR
133 (mr_rec.segment4_low IS NULL and mr_rec.segment4_high IS NULL))
134 AND ((gc.segment5 between nvl(mr_rec.segment5_low, gc.segment5)
135 and nvl(mr_rec.segment5_high, gc.segment5)) OR
136 (mr_rec.segment5_low IS NULL and mr_rec.segment5_high IS NULL))
137 AND ((gc.segment6 between nvl(mr_rec.segment6_low, gc.segment6)
138 and nvl(mr_rec.segment6_high, gc.segment6)) OR
139 (mr_rec.segment6_low IS NULL and mr_rec.segment6_high IS NULL))
140 AND ((gc.segment7 between nvl(mr_rec.segment7_low, gc.segment7)
141 and nvl(mr_rec.segment7_high, gc.segment7)) OR
142 (mr_rec.segment7_low IS NULL and mr_rec.segment7_high IS NULL))
143 AND ((gc.segment8 between nvl(mr_rec.segment8_low, gc.segment8)
144 and nvl(mr_rec.segment8_high, gc.segment8)) OR
145 (mr_rec.segment8_low IS NULL and mr_rec.segment8_high IS NULL))
146 AND ((gc.segment9 between nvl(mr_rec.segment9_low, gc.segment9)
147 and nvl(mr_rec.segment9_high, gc.segment9)) OR
148 (mr_rec.segment9_low IS NULL and mr_rec.segment9_high IS NULL))
149 AND ((gc.segment10 between nvl(mr_rec.segment10_low, gc.segment10)
150 and nvl(mr_rec.segment10_high, gc.segment10)) OR
151 (mr_rec.segment10_low IS NULL and mr_rec.segment10_high IS NULL))
152 AND ((gc.segment11 between nvl(mr_rec.segment11_low, gc.segment11)
153 and nvl(mr_rec.segment11_high, gc.segment11)) OR
154 (mr_rec.segment11_low IS NULL and mr_rec.segment11_high IS NULL))
155 AND ((gc.segment12 between nvl(mr_rec.segment12_low, gc.segment12)
156 and nvl(mr_rec.segment12_high, gc.segment12)) OR
157 (mr_rec.segment12_low IS NULL and mr_rec.segment12_high IS NULL))
158 AND ((gc.segment13 between nvl(mr_rec.segment13_low, gc.segment13)
159 and nvl(mr_rec.segment13_high, gc.segment13)) OR
160 (mr_rec.segment13_low IS NULL and mr_rec.segment13_high IS NULL))
161 AND ((gc.segment14 between nvl(mr_rec.segment14_low, gc.segment14)
162 and nvl(mr_rec.segment14_high, gc.segment14)) OR
163 (mr_rec.segment14_low IS NULL and mr_rec.segment14_high IS NULL))
164 AND ((gc.segment15 between nvl(mr_rec.segment15_low, gc.segment15)
165 and nvl(mr_rec.segment15_high, gc.segment15)) OR
166 (mr_rec.segment15_low IS NULL and mr_rec.segment15_high IS NULL))
167 AND ((gc.segment16 between nvl(mr_rec.segment16_low, gc.segment16)
168 and nvl(mr_rec.segment16_high, gc.segment16)) OR
169 (mr_rec.segment16_low IS NULL and mr_rec.segment16_high IS NULL))
170 AND ((gc.segment17 between nvl(mr_rec.segment17_low, gc.segment17)
171 and nvl(mr_rec.segment17_high, gc.segment17)) OR
172 (mr_rec.segment17_low IS NULL and mr_rec.segment17_high IS NULL))
173 AND ((gc.segment18 between nvl(mr_rec.segment18_low, gc.segment18)
174 and nvl(mr_rec.segment18_high, gc.segment18)) OR
175 (mr_rec.segment18_low IS NULL and mr_rec.segment18_high IS NULL))
176 AND ((gc.segment19 between nvl(mr_rec.segment19_low, gc.segment19)
177 and nvl(mr_rec.segment19_high, gc.segment19)) OR
178 (mr_rec.segment19_low IS NULL and mr_rec.segment19_high IS NULL))
179 AND ((gc.segment20 between nvl(mr_rec.segment20_low, gc.segment20)
180 and nvl(mr_rec.segment20_high, gc.segment20)) OR
181 (mr_rec.segment20_low IS NULL and mr_rec.segment20_high IS NULL))
182 AND ((gc.segment21 between nvl(mr_rec.segment21_low, gc.segment21)
183 and nvl(mr_rec.segment21_high, gc.segment21)) OR
184 (mr_rec.segment21_low IS NULL and mr_rec.segment21_high IS NULL))
185 AND ((gc.segment22 between nvl(mr_rec.segment22_low, gc.segment22)
186 and nvl(mr_rec.segment22_high, gc.segment22)) OR
187 (mr_rec.segment22_low IS NULL and mr_rec.segment22_high IS NULL))
188 AND ((gc.segment23 between nvl(mr_rec.segment23_low, gc.segment23)
189 and nvl(mr_rec.segment23_high, gc.segment23)) OR
190 (mr_rec.segment23_low IS NULL and mr_rec.segment23_high IS NULL))
191 AND ((gc.segment24 between nvl(mr_rec.segment24_low, gc.segment24)
192 and nvl(mr_rec.segment24_high, gc.segment24)) OR
193 (mr_rec.segment24_low IS NULL and mr_rec.segment24_high IS NULL))
194 AND ((gc.segment25 between nvl(mr_rec.segment25_low, gc.segment25)
195 and nvl(mr_rec.segment25_high, gc.segment25)) OR
196 (mr_rec.segment25_low IS NULL and mr_rec.segment25_high IS NULL))
197 AND ((gc.segment26 between nvl(mr_rec.segment26_low, gc.segment26)
198 and nvl(mr_rec.segment26_high, gc.segment26)) OR
199 (mr_rec.segment26_low IS NULL and mr_rec.segment26_high IS NULL))
200 AND ((gc.segment27 between nvl(mr_rec.segment27_low, gc.segment27)
201 and nvl(mr_rec.segment27_high, gc.segment27)) OR
202 (mr_rec.segment27_low IS NULL and mr_rec.segment27_high IS NULL))
203 AND ((gc.segment28 between nvl(mr_rec.segment28_low, gc.segment28)
204 and nvl(mr_rec.segment28_high, gc.segment28)) OR
205 (mr_rec.segment28_low IS NULL and mr_rec.segment28_high IS NULL))
206 AND ((gc.segment29 between nvl(mr_rec.segment29_low, gc.segment29)
207 and nvl(mr_rec.segment29_high, gc.segment29)) OR
208 (mr_rec.segment29_low IS NULL and mr_rec.segment29_high IS NULL))
209 AND ((gc.segment30 between nvl(mr_rec.segment30_low, gc.segment30)
210 and nvl(mr_rec.segment30_high, gc.segment30)) OR
211 (mr_rec.segment30_low IS NULL and mr_rec.segment30_high IS NULL))
212 AND ad.asset_id > px_max_asset_id
213 AND MOD(ad.asset_id, p_total_requests) = (p_request_number - 1)
214 ORDER BY ad.asset_id;
215
216 -- local variables
217 TYPE v30_tbl IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
218 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
219
220 l_asset_number v30_tbl;
221 l_asset_id num_tbl;
222 l_asset_category_id num_tbl;
223 l_assigned_to num_tbl;
224
225 l_msg_count NUMBER := 0;
226 l_msg_data VARCHAR2(2000) := NULL;
227 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
228 l_rowcount NUMBER;
229 l_warn_status BOOLEAN := FALSE;
230
231 -- to keep track of the last asset id that entered the mass_reclass_assets
232 -- cursor loop. we need this to avoid DISTINCT in the SELECT statement
233 -- for mass_reclass_assets cursor. asset may be selected multiple times
234 -- if it is multi-distributed and if more than one distribution lines
235 -- meet the reclass selection criteria(if at least one distribution line
236 -- meets user criteria, the asset is selected for reclass.)
237 l_last_asset NUMBER(15) := NULL;
238 l_status BOOLEAN := FALSE;
239 l_dummy_num NUMBER;
240 l_cat_flex_struct NUMBER;
241 l_concat_cat VARCHAR2(220);
242 l_cat_segs fa_rx_shared_pkg.Seg_Array;
243
244 -- counter to keep track of the number of assets that entered the
245 -- mass_reclass_assets cursor loop and passed Check_Criteria.
246 -- this counter is reset to zero, at every 200 assets.
247 l_counter NUMBER := 0;
248
249 -- used for bulk fetch
250 l_batch_size NUMBER;
251 l_loop_count NUMBER;
252
253 -- used for api call
254 l_trans_rec FA_API_TYPES.trans_rec_type;
255 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
256 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
257 l_recl_opt_rec FA_API_TYPES.reclass_options_rec_type;
258
259 l_calling_fn VARCHAR2(50) := 'FA_MASS_RECLASS_PKG.DO_RECLASS';
260 l_string varchar2(250);
261
262 mrcl_failure EXCEPTION; -- mass reclass failure
263 done_exc EXCEPTION;
264
265 BEGIN
266
267 if (not g_log_level_rec.initialized) then
268 if (NOT fa_util_pub.get_log_level_rec (
269 x_log_level_rec => g_log_level_rec
270 )) then
271 raise mrcl_failure;
272 end if;
273 end if;
274
275 px_max_asset_id := nvl(px_max_asset_id, 0);
276 x_processed_count := 0;
277 x_success_count := 0;
278 x_failure_count := 0;
279
280 if (px_max_asset_id = 0) then
281
282 FND_FILE.put(FND_FILE.output,'');
283 FND_FILE.new_line(FND_FILE.output,1);
284
285 -- dump out the headings
286 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_COLUMN');
287 l_string := fnd_message.get;
288
289 FND_FILE.put(FND_FILE.output,l_string);
290 FND_FILE.new_line(FND_FILE.output,1);
291
292 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_LINE');
293 l_string := fnd_message.get;
294
295 FND_FILE.put(FND_FILE.output,l_string);
296 FND_FILE.new_line(FND_FILE.output,1);
297
298 end if;
299
300 -- Fetch mass reclass record information.
301 OPEN mass_reclass;
302 FETCH mass_reclass INTO mr_rec;
303 CLOSE mass_reclass;
304
305 if not (fa_cache_pkg.fazcbc(X_book => mr_rec.book_type_code,
306 p_log_level_rec => g_log_level_rec)) then
307 raise mrcl_failure;
308 end if;
309
310 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
311
312 l_recl_opt_rec.copy_cat_desc_flag := mr_rec.copy_cat_desc_flag;
313 l_recl_opt_rec.redefault_flag := mr_rec.redefault_flag;
314 l_asset_cat_rec.category_id := mr_rec.to_category_id;
315 l_asset_hdr_rec.book_type_code := mr_rec.book_type_code;
316
317 /*===========================================================================
318 Check if reclass transaction date for the mass reclass record from
319 mass reclass form is in the current corporate book period.
320 (No prior period reclass is allowed.)
321 ===========================================================================*/
322
323 if px_max_asset_id = 0 then
324 IF NOT Check_Trans_Date(
325 X_Corp_Book => mr_rec.book_type_code,
326 X_Trans_Date => mr_rec.trans_date_entered,
327 p_log_level_rec => g_log_level_rec) THEN
328 RAISE mrcl_failure;
329 END IF;
330 end if;
331
332 /*===========================================================================
333 Perform mass reclass.
334 ===========================================================================*/
335 IF (mr_rec.redefault_flag = 'YES') THEN
336 -- Depreciation rules will be redefaulted.
337 -- Reset g_deprn_count before initiating mass reclass transaction.
338 FA_LOAD_TBL_PKG.g_deprn_count := 0;
339
340 -- Load depreciation rules table for the corporate book and all the
341 -- associated tax books for the new category.
342 -- Simulates caching effect.
343 FA_LOAD_TBL_PKG.Load_Deprn_Rules_Tbl(
344 p_corp_book => mr_rec.book_type_code,
345 p_category_id => mr_rec.to_category_id,
346 x_return_status => l_status,
347 p_log_level_rec => g_log_level_rec);
348 IF NOT l_status THEN
349 RAISE mrcl_failure;
350 END IF;
351 END IF;
352
353 /* Get the new category code from the new category id. */
354 if not fa_cache_pkg.fazsys then
355 RAISE mrcl_failure;
356 end if;
357
358 l_cat_flex_struct := fa_cache_pkg.fazsys_record.category_flex_structure;
359
360 FA_RX_SHARED_PKG.Concat_Category(
361 struct_id => l_cat_flex_struct,
362 ccid => mr_rec.to_category_id,
363 concat_string => l_concat_cat,
364 segarray => l_cat_segs);
365
366
367 /* Loop all the qualified assets, and perform mass reclass. */
368 OPEN mass_reclass_assets;
369 FETCH mass_reclass_assets BULK COLLECT INTO
370 l_asset_id,
371 l_asset_number,
372 l_asset_category_id,
373 l_assigned_to
374 LIMIT l_batch_size;
375 close mass_reclass_assets;
376
377 x_processed_count := l_asset_id.count;
378
379 if (l_asset_id.count = 0) then
380 raise done_exc;
381 end if;
382
383 for l_loop_count in 1..l_asset_id.count loop
384
385 -- clear the debug stack for each asset
386 FA_DEBUG_PKG.Initialize;
387 -- reset the message level to prevent bogus errors
388 FA_SRVR_MSG.Set_Message_Level(message_level => 10);
389
390 if NOT l_warn_status then
391 if not FA_ASSET_VAL_PVT.validate_assigned_to (
392 p_transaction_type_code => 'RECLASS',
393 p_assigned_to => l_assigned_to(l_loop_count),
394 p_date => mr_rec.trans_date_entered,
395 p_calling_fn => l_calling_fn,
396 p_log_level_rec => g_log_level_rec) then
397 l_warn_status := TRUE; -- set to warning when invalid employee encountered
398 end if;
399 end if;
400
401 IF (l_asset_id(l_loop_count) <> l_last_asset OR l_last_asset IS NULL) THEN
402 -- Skip the reclass, if the asset has already entered this loop before.
403 -- Using l_last_asset to keep track of the last asset that entered the
404 -- cursor loop instead of using DISTINCT in the SELECT statement for
405 -- mass_reclass_assets cursor.
406
407 -- Save the asset id for the next loop.
408 l_last_asset := l_asset_id(l_loop_count);
409
410 IF Check_Criteria(
411 X_Asset_Id => l_asset_id(l_loop_count),
412 X_Fully_Rsvd_Flag => mr_rec.fully_rsvd_flag,
413 p_log_level_rec => g_log_level_rec) THEN
414 -- Perform reclass only on the assets that meet all the user
415 -- selection criteria.
416
417 fa_srvr_msg.add_message(
418 calling_fn => NULL,
419 name => 'FA_SHARED_ASSET_NUMBER',
420 token1 => 'NUMBER',
421 value1 => l_asset_number(l_loop_count),
422 p_log_level_rec => g_log_level_rec);
423
424 IF (l_asset_category_id(l_loop_count) = mr_rec.to_category_id) THEN
425 -- Reclass and redefault are not processed on the asset, if
426 -- the new category is the same as the old category.
427 -- This asset is printed on the log with a message, but is not
428 -- counted as a Processed asset.
429 -- (The log prints number of assets processed, number of success,
430 -- number of failures.)
431
432 -- List asset number and the message.
433 -- use the write_message util
434
435 write_message(l_asset_number(l_loop_count),
436 'FA_REC_NOT_PROCESSED');
437
438 /*
439 fnd_message.set_name('OFA', 'FA_SHARED_ASSET_NUMBER');
440 fnd_message.set_token('NUMBER', l_asset_number(l_loop_count), FALSE);
441 fnd_msg_pub.add;
442 FA_SRVR_MSG.Add_Message(
443 CALLING_FN => l_calling_fn,
444 NAME => 'FA_REC_NOT_PROCESSED',
445 TOKEN1 => 'ASSET',
446 VALUE1 => l_asset_number(l_loop_count),
447 p_log_level_rec => g_log_level_rec);
448 */
449 -- Increment the counter.
450 -- Increment only the counter for messaging. This asset is
451 -- not considered a Processed asset.
452 l_counter := l_counter + 1;
453
454 ELSE
455 -- validation ok, null out then load the structs and process the adjustment
456 l_trans_rec.transaction_header_id := NULL;
457 l_trans_rec.who_info.last_update_date := sysdate;
458 l_trans_rec.transaction_date_entered := mr_rec.trans_date_entered;
459 l_trans_rec.mass_reference_id := p_parent_request_id;
460 l_trans_rec.calling_interface := 'FAMRCL';
461 l_trans_rec.mass_transaction_id := p_mass_reclass_id;
462
463 l_asset_hdr_rec.asset_id := l_asset_id(l_loop_count);
464 l_asset_hdr_rec.period_of_addition := null;
465
466 if (mr_rec.amortize_flag = 'YES') then
467 l_trans_rec.transaction_subtype := 'AMORTIZED';
468 else
469 l_trans_rec.transaction_subtype := 'EXPENSED';
470 end if;
471
472
473 /* Call the new Reclass Public API for each asset. */
474
475 FA_RECLASS_PUB.do_reclass (
476 -- std parameters
477 p_api_version => 1.0,
478 p_init_msg_list => FND_API.G_FALSE,
479 p_commit => FND_API.G_FALSE,
480 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
481 p_calling_fn => l_calling_fn,
482 x_return_status => l_return_status,
483 x_msg_count => l_msg_count,
484 x_msg_data => l_msg_data,
485 -- api parameters
486 px_trans_rec => l_trans_rec,
487 px_asset_hdr_rec => l_asset_hdr_rec,
488 px_asset_cat_rec_new => l_asset_cat_rec,
489 p_recl_opt_rec => l_recl_opt_rec);
490
491 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
492 FND_CONCURRENT.AF_COMMIT;
493 l_counter := l_counter + 1;
494 x_success_count := x_success_count + 1;
495
496 write_message(l_asset_number(l_loop_count),
497 'FA_MCP_RECLASS_SUCCESS',
498 p_log_level_rec => g_log_level_rec);
499
500 ELSE
501 /* 'W'(warning status) or error status. */
502 -- Partial failure(failure in redefault only) is counted as failure.
503 l_counter := l_counter + 1;
504 x_failure_count := x_failure_count + 1;
505 write_message(l_asset_number(l_loop_count),
506 NULL,
507 p_log_level_rec => g_log_level_rec);
508
509 END IF;
510
511 -- Commented for bugfix 4672237
512 -- if (g_log_level_rec.statement_level) then
513 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
514 -- end if;
515
516 END IF; /* IF (l_category_id = mr_rec.to_category_id) */
517 END IF; /* IF Check_Criteria */
518 END IF; /* IF (l_asset_id <> l_last_asset OR l_last_asset IS NULL) */
519 END LOOP;
520
521 IF (mr_rec.redefault_flag = 'YES') THEN
522 -- Reset g_deprn_count after completing mass reclass transaction.
523 FA_LOAD_TBL_PKG.g_deprn_count := 0;
524 END IF;
525
526 FND_CONCURRENT.AF_COMMIT;
527
528 px_max_asset_id := l_asset_id(l_asset_id.count);
529 if (l_warn_status) then
530 x_return_status := 1; -- return warning
531 else
532 x_return_status := 0; -- success
533 end if;
534
535 EXCEPTION
536 WHEN done_exc then
537 if (l_warn_status) then
538 x_return_status := 1;
539 else
540 x_return_status := 0;
541 end if;
542
543 WHEN mrcl_failure THEN
544 fa_srvr_msg.add_message(calling_fn => l_calling_fn
545 ,p_log_level_rec => g_log_level_rec);
546 FND_CONCURRENT.AF_ROLLBACK;
547 FA_LOAD_TBL_PKG.g_deprn_count := 0;
548 x_return_status := 2;
549
550 WHEN OTHERS THEN
551 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
552 ,p_log_level_rec => g_log_level_rec);
553 FND_CONCURRENT.AF_ROLLBACK;
554 FA_LOAD_TBL_PKG.g_deprn_count := 0;
555 x_return_status := 2;
556
557 END Do_Mass_Reclass;
558
559
560 /*====================================================================================+
561 | FUNCTION Check_Trans_Date |
562 +=====================================================================================*/
563
564 FUNCTION Check_Trans_Date(
565 X_Corp_Book IN VARCHAR2,
566 X_Trans_Date IN DATE,
567 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
568 l_cp_open_date DATE;
569 l_cp_close_date DATE;
570 -- cursor to get the calendar period open date of the current corpote
571 -- book period.
572 CURSOR get_cp_open_close_date IS
573 SELECT calendar_period_open_date,
574 calendar_period_close_date
575 FROM fa_deprn_periods
576 WHERE book_type_code = X_Corp_Book
577 AND period_close_date IS NULL;
578
579 BEGIN
580 -- Check if the transaction date is in the current corporate book period.
581 OPEN get_cp_open_close_date;
582 FETCH get_cp_open_close_date
583 INTO l_cp_open_date,
584 l_cp_close_date;
585 CLOSE get_cp_open_close_date;
586
587 IF (X_Trans_Date < l_cp_open_date OR
588 X_Trans_date > l_cp_close_date) THEN
589 FA_SRVR_MSG.Add_Message(
590 CALLING_FN => 'FA_MASS_RECLASS_PKG.Check_Trans_Date',
591 NAME => 'FA_REC_INVALID_TRANS_DATE',
592 p_log_level_rec => p_log_level_rec);
593 RETURN (FALSE);
594 END IF;
595
596 RETURN (TRUE);
597
598 EXCEPTION
599 WHEN OTHERS THEN
600 FA_SRVR_MSG.Add_SQL_Error(
601 CALLING_FN => 'FA_MASS_RECLASS_PKG.Check_Trans_Date'
602 ,p_log_level_rec => p_log_level_rec);
603 RETURN (FALSE);
604 END Check_Trans_Date;
605
606
607 /*====================================================================================+
608 | FUNCTION Check_Criteria |
609 +=====================================================================================*/
610
611 FUNCTION Check_Criteria(
612 X_Asset_Id IN NUMBER,
613 X_Fully_Rsvd_Flag IN VARCHAR2,
614 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
615
616 l_book VARCHAR2(15);
617 check_flag VARCHAR2(15);
618
619 -- cursor to get all the corporate and tax books the asset belongs to.
620 CURSOR book_cr IS
621 SELECT bk.book_type_code
622 FROM fa_book_controls bc, fa_books bk
623 WHERE bk.asset_id = X_Asset_Id
624 AND bk.date_ineffective IS NULL
625 AND bk.book_type_code = bc.book_type_code
626 AND bc.book_class IN ('CORPORATE', 'TAX')
627 AND nvl(bc.date_ineffective, sysdate+1) > sysdate;
628
629 CURSOR check_not_rsvd IS
630 SELECT 'NOT RESERVED'
631 FROM fa_books bk
632 WHERE bk.asset_id = X_Asset_Id
633 AND bk.book_type_code = l_book
634 AND bk.date_ineffective IS NULL
635 AND bk.period_counter_fully_reserved IS NULL;
636
637 CURSOR check_rsvd IS
638 SELECT 'RESERVED'
639 FROM fa_books bk
640 WHERE bk.asset_id = X_Asset_Id
641 AND bk.book_type_code = l_book
642 AND bk.date_ineffective IS NULL
643 AND bk.period_counter_fully_reserved IS NOT NULL;
644
645 BEGIN
646 -- Check to make sure fully reserved asset selection criteria is met in
647 -- all the books the asset belongs to.
648 IF (X_Fully_Rsvd_Flag IS NOT NULL) THEN
649 -- if x_fully_rsvd_flag is null, then we don't care whether the asset is
650 -- reserved or not.
651 OPEN book_cr;
652 LOOP
653 FETCH book_cr INTO l_book;
654 EXIT WHEN book_cr%NOTFOUND;
655
656 IF (X_Fully_Rsvd_Flag = 'YES') THEN
657 OPEN check_not_rsvd;
658 FETCH check_not_rsvd INTO check_flag;
659 IF (check_not_rsvd%FOUND) THEN
660 CLOSE check_not_rsvd;
661 RETURN (FALSE);
662 END IF;
663 CLOSE check_not_rsvd;
664 ELSIF (X_Fully_Rsvd_Flag = 'NO') THEN
665 OPEN check_rsvd;
666 FETCH check_rsvd INTO check_flag;
667 IF (check_rsvd%FOUND) THEN
668 CLOSE check_rsvd;
669 RETURN (FALSE);
670 END IF;
671 CLOSE check_rsvd;
672 END IF;
673
674 END LOOP;
675
676 CLOSE book_cr;
677
678 END IF;
679
680 RETURN (TRUE);
681
682 EXCEPTION
683 WHEN OTHERS THEN
684 FA_SRVR_MSG.Add_SQL_Error(
685 CALLING_FN => 'FA_MASS_RECLASS_PKG.Check_Criteria'
686 ,p_log_level_rec => p_log_level_rec);
687 RETURN (FALSE);
688
689 END Check_Criteria;
690
691 -----------------------------------------------------------------------------
692
693 PROCEDURE write_message
694 (p_asset_number in varchar2,
695 p_message in varchar2,
696 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
697
698 l_message varchar2(30);
699 l_mesg varchar2(100);
700 l_string varchar2(512);
701 l_calling_fn varchar2(40); -- conditionally populated below
702
703 BEGIN
704
705 -- first dump the message to the output file
706 -- set/translate/retrieve the mesg from fnd
707
708 l_message := nvl(p_message, 'FA_MASSRCL_FAIL_TRX');
709
710 if (l_message <> 'FA_MCP_RECLASS_SUCCESS' and
711 l_message <> 'FA_REC_NOT_PROCESSED') then
712 l_calling_fn := 'fa_mass_reclass_pkg.do_reclass';
713 end if;
714
715 fnd_message.set_name('OFA', l_message);
716 l_mesg := substrb(fnd_message.get, 1, 100);
717
718 l_string := rpad(p_asset_number, 15) || ' ' || l_mesg;
719
720 FND_FILE.put(FND_FILE.output,l_string);
721 FND_FILE.new_line(FND_FILE.output,1);
722
723 -- now process the messages for the log file
724 fa_srvr_msg.add_message
725 (calling_fn => l_calling_fn,
726 name => l_message
727 ,p_log_level_rec => p_log_level_rec);
728
729 EXCEPTION
730 when others then
731 raise;
732
733 END write_message;
734
735 END FA_MASS_RECLASS_PKG;
[Home] [Help]