[Home] [Help]
PACKAGE BODY: APPS.FARX_MCP
Source
4 -- Mass change record from fa_mass_changes table.
1 PACKAGE BODY FARX_MCP AS
2 /* $Header: FARXMCPB.pls 120.10 2010/06/14 15:49:18 klakshmi ship $ */
3
5 mc_rec FA_MASS_CHG_UTILS_PKG.mass_change_rec_type;
6
7 -- Table of asset records.
8 a_tbl FA_MASS_CHG_UTILS_PKG.asset_tbl_type;
9
10 -- Index into the asset table, a_tbl.
11 a_index NUMBER := 0;
12
13 -- Number of assets(disregaring book_type_code) stored in a_tbl.
14 -- Reset at every 200 assets.
15 g_asset_count NUMBER := 0;
16
17 -- Total number of assets to be printed in report.
18 --g_total_assets NUMBER := 0;
19
20 /* a_index <> g_asset_count if asset belongs to more than one book. */
21
22 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
23
24
25
26
27 /*============================================================================+
28 | PROCEDURE Preview_Change |
29 +=============================================================================*/
30
31 PROCEDURE Preview_Change(
32 X_Mass_Change_Id IN NUMBER,
33 X_RX_Flag IN VARCHAR2 := 'NO',
34 retcode OUT NOCOPY NUMBER,
35 errbuf OUT NOCOPY VARCHAR2) IS
36
37 -- cursor to fetch the current and preview status
38 CURSOR get_status IS
39 SELECT lu_prev.meaning,
40 lu_curr.meaning
41 FROM fa_lookups lu_prev,
42 fa_lookups lu_curr
43 WHERE lu_prev.lookup_type = 'MASS_TRX_STATUS' AND
44 lu_prev.lookup_code = 'PREVIEW'
45 AND lu_curr.lookup_type = 'MASS_TRX_STATUS' AND
46 lu_curr.lookup_code = mc_rec.status;
47
48 -- cursor to get category flex structure.
49 CURSOR get_cat_flex_struct IS
50 SELECT category_flex_structure
51 FROM fa_system_controls;
52
53 -- cursor to fetch mass change record from fa_mass_change
54 CURSOR mass_change IS
55 SELECT mc.mass_change_id,
56 mc.book_type_code,
57 mc.transaction_date_entered,
58 mc.concurrent_request_id,
59 mc.status,
60 mc.asset_type,
61 mc.category_id,
62 mc.from_asset_number,
63 mc.to_asset_number,
64 mc.from_date_placed_in_service,
65 mc.to_date_placed_in_service,
69 mc.to_method_code,
66 mc.from_convention,
67 mc.to_convention,
68 mc.from_method_code,
70 mc.from_life_in_months,
71 mc.to_life_in_months,
72 mc.from_bonus_rule,
73 mc.to_bonus_rule,
74 mc.date_effective,
75 mc.from_basic_rate,
76 mc.to_basic_rate,
77 mc.from_adjusted_rate,
78 mc.to_adjusted_rate,
79 mc.from_production_capacity,
80 mc.to_production_capacity,
81 mc.from_uom,
82 mc.to_uom,
83 mc.from_group_association,
84 mc.to_group_association,
85 mc.from_group_asset_id,
86 mc.to_group_asset_id,
87 gad1.asset_number,
88 gad2.asset_number,
89 mc.change_fully_rsvd_assets,
90 mc.amortize_flag,
91 mc.created_by,
92 mc.creation_date,
93 mc.last_updated_by,
94 mc.last_update_login,
95 mc.last_update_date,
96 mc.from_salvage_type,
97 mc.to_salvage_type,
98 mc.from_percent_salvage_value,
99 mc.to_percent_salvage_value,
100 mc.from_salvage_value,
101 mc.to_salvage_value,
102 mc.from_deprn_limit_type,
103 mc.to_deprn_limit_type,
104 mc.from_deprn_limit,
105 mc.to_deprn_limit,
106 mc.from_deprn_limit_amount,
107 mc.to_deprn_limit_amount
108 FROM fa_mass_changes mc,
109 fa_additions_b gad1,
110 fa_additions_b gad2
111 WHERE mass_change_id = X_Mass_Change_Id
112 AND mc.from_group_asset_id = gad1.asset_id(+)
113 AND mc.to_group_asset_id = gad2.asset_id(+);
114
115 -- assets that meet the user's selection criteria.
116 -- some assets selected by this cursor are discarded in the validation engine.
117 CURSOR mass_change_assets IS
118 SELECT ad.asset_id,
119 ad.asset_number,
120 ad.description,
121 ad.asset_type,
122 ad.asset_category_id,
123 bk.prorate_convention_code,
124 bk.deprn_method_code,
125 bk.life_in_months,
126 bk.bonus_rule,
127 bk.basic_rate,
128 bk.adjusted_rate,
129 bk.production_capacity,
130 bk.unit_of_measure,
131 bk.book_type_code,
132 gad.asset_number,
133 bk.salvage_type,
134 bk.percent_salvage_value,
135 bk.salvage_value,
136 bk.deprn_limit_type,
137 bk.allowed_deprn_limit,
138 bk.allowed_deprn_limit_amount
139 FROM fa_books bk,
140 fa_additions ad,
141 fa_additions_b gad,
142 fa_mass_changes mch
143 WHERE mch.mass_change_id = mc_rec.mass_change_id
144 AND ad.asset_type = nvl(mch.asset_type, ad.asset_type)
145 AND ad.asset_type <> 'CIP'
146 AND ad.asset_number >= nvl(mch.from_asset_number, ad.asset_number)
147 AND ad.asset_number <= nvl(mch.to_asset_number, ad.asset_number)
148 AND ad.asset_category_id = nvl(mch.category_id, ad.asset_category_id)
149 AND bk.book_type_code = mch.book_type_code
150 AND bk.asset_id = ad.asset_id
151 AND NVL(bk.Disabled_flag, 'N') = 'N' --HH
152 AND bk.date_ineffective IS NULL -- pick the most recent row.
153 AND bk.period_counter_fully_retired IS NULL
154 and nvl(bk.period_counter_fully_reserved,99) =
155 nvl(bk.period_counter_life_complete,99)
156 and nvl(bk.period_counter_fully_reserved, -1) =
157 decode(mch.change_fully_rsvd_assets, 'YES',
158 nvl(bk.period_counter_fully_reserved, -1), -1)
159 and bk.date_placed_in_service >=
160 nvl(mch.from_date_placed_in_service,
161 bk.date_placed_in_service)
162 and bk.date_placed_in_service <=
163 nvl(mch.to_date_placed_in_service,
164 bk.date_placed_in_service)
165 and bk.deprn_method_code =
166 nvl(mch.from_method_code,
167 bk.deprn_method_code)
168 and nvl(bk.life_in_months, -1) =
169 nvl(mch.from_life_in_months,
170 nvl(bk.life_in_months, -1))
171 and nvl(bk.basic_rate, -1) =
172 nvl(mch.from_basic_rate,
173 nvl(bk.basic_rate, -1))
174 and nvl(bk.adjusted_rate, -1) =
175 nvl(mch.from_adjusted_rate,
176 nvl(bk.adjusted_rate, -1))
177 and nvl(bk.production_capacity, -1) =
178 nvl(mch.from_production_capacity,
179 nvl(bk.production_capacity, -1))
180 and nvl(bk.unit_of_measure, -1) =
181 nvl(mch.from_uom,
182 nvl(bk.unit_of_measure, -1))
183 and bk.prorate_convention_code =
184 nvl(mch.from_convention,
185 bk.prorate_convention_code)
186 and nvl(bk.bonus_rule, -1) =
187 nvl(mch.from_bonus_rule,
188 nvl(bk.bonus_rule,-1))
189 and ((mch.from_group_association is null) or
190 (mch.from_group_association = 'STANDALONE' and
191 bk.group_asset_id is null) or
192 (mch.from_group_association = 'MEMBER' and
196 nvl(mch.from_salvage_type,
193 nvl(bk.group_asset_id, -99) = mch.from_group_asset_id))
194 AND bk.group_asset_id = gad.asset_id(+)
195 and nvl(bk.salvage_type, 'XX') =
197 nvl(bk.salvage_type, 'XX'))
198 and nvl(bk.salvage_value, -1) =
199 nvl(mch.from_salvage_value,
200 nvl(bk.salvage_value, -1))
201 and nvl(bk.percent_salvage_value, -1) =
202 nvl(mch.from_percent_salvage_value/100,
203 nvl(bk.percent_salvage_value, -1))
204 and nvl(bk.deprn_limit_type, 'XX') =
205 nvl(mch.from_deprn_limit_type,
206 nvl(bk.deprn_limit_type, 'XX'))
207 and nvl(bk.allowed_deprn_limit_amount, -1) =
208 nvl(mch.from_deprn_limit_amount,
209 nvl(bk.allowed_deprn_limit_amount, -1))
210 and nvl(bk.allowed_deprn_limit, -1) =
211 nvl(mch.from_deprn_limit/100,
212 nvl(bk.allowed_deprn_limit, -1))
213 MINUS
214 SELECT ad.asset_id,
215 ad.asset_number,
216 ad.description,
217 ad.asset_type,
218 ad.asset_category_id,
219 bk.prorate_convention_code,
220 bk.deprn_method_code,
221 bk.life_in_months,
222 bk.bonus_rule,
223 bk.basic_rate,
224 bk.adjusted_rate,
225 bk.production_capacity,
226 bk.unit_of_measure,
227 bk.book_type_code,
228 gad.asset_number,
229 bk.salvage_type,
230 bk.percent_salvage_value,
231 bk.salvage_value,
232 bk.deprn_limit_type,
233 bk.allowed_deprn_limit,
234 bk.allowed_deprn_limit_amount
235 FROM fa_books bk,
236 fa_additions ad,
237 fa_additions_b gad,
238 fa_mass_changes mch
239 WHERE mch.mass_change_id = mc_rec.mass_change_id
240 AND ad.asset_type = nvl(mch.asset_type, ad.asset_type)
241 AND ad.asset_type <> 'CIP'
242 AND ad.asset_number >= nvl(mch.from_asset_number, ad.asset_number)
243 AND ad.asset_number <= nvl(mch.to_asset_number, ad.asset_number)
244 AND ad.asset_category_id = nvl(mch.category_id, ad.asset_category_id)
245 AND bk.book_type_code = mch.book_type_code
246 AND bk.asset_id = ad.asset_id
247 AND NVL(bk.Disabled_flag, 'N') = 'N' --HH
248 AND bk.date_ineffective IS NULL -- pick the most recent row.
249 AND bk.period_counter_fully_retired IS NULL
250 and nvl(bk.period_counter_fully_reserved,99) =
251 nvl(bk.period_counter_life_complete,99)
252 and nvl(bk.period_counter_fully_reserved, -1) =
253 decode(mch.change_fully_rsvd_assets, 'YES',
254 nvl(bk.period_counter_fully_reserved, -1), -1)
255 and bk.date_placed_in_service >=
256 nvl(mch.from_date_placed_in_service,
257 bk.date_placed_in_service)
258 and bk.date_placed_in_service <=
259 nvl(mch.to_date_placed_in_service,
260 bk.date_placed_in_service)
261 and bk.deprn_method_code =
262 nvl(mch.to_method_code,
263 bk.deprn_method_code)
264 and nvl(bk.life_in_months, -1) =
265 nvl(mch.to_life_in_months,
266 nvl(bk.life_in_months, -1))
267 and nvl(bk.basic_rate, -1) =
268 nvl(mch.to_basic_rate,
269 nvl(bk.basic_rate, -1))
270 and nvl(bk.adjusted_rate, -1) =
271 nvl(mch.to_adjusted_rate,
272 nvl(bk.adjusted_rate, -1))
273 and nvl(bk.production_capacity, -1) =
274 nvl(mch.to_production_capacity,
275 nvl(bk.production_capacity, -1))
276 and nvl(bk.unit_of_measure, -1) =
277 nvl(mch.to_uom,
278 nvl(bk.unit_of_measure, -1))
279 and bk.prorate_convention_code =
280 nvl(mch.to_convention,
281 bk.prorate_convention_code)
282 and nvl(bk.bonus_rule, -1) =
283 nvl(mch.to_bonus_rule,
284 nvl(bk.bonus_rule,-1))
285 and nvl (mch.to_group_association,'XXXX') = nvl (mch.from_group_association,'XXXX')
286 and nvl (mch.to_group_asset_id,-99) = nvl (mch.from_group_asset_id,-99)
287 and bk.group_asset_id = gad.asset_id(+)
288 and nvl(bk.salvage_type, 'XX') =
289 nvl(mch.to_salvage_type,
290 nvl(bk.salvage_type, 'XX'))
291 and nvl(bk.salvage_value, -1) =
292 nvl(mch.to_salvage_value,
293 nvl(bk.salvage_value, -1))
294 and nvl(bk.percent_salvage_value, -1) =
295 nvl(mch.to_percent_salvage_value/100,
296 nvl(bk.percent_salvage_value, -1))
297 and nvl(bk.deprn_limit_type, 'XX') =
298 nvl(mch.to_deprn_limit_type,
299 nvl(bk.deprn_limit_type, 'XX'))
300 and nvl(bk.allowed_deprn_limit_amount, -1) =
301 nvl(mch.to_deprn_limit_amount,
302 nvl(bk.allowed_deprn_limit_amount, -1))
303 and nvl(bk.allowed_deprn_limit, -1) =
304 nvl(mch.to_deprn_limit/100,
305 nvl(bk.allowed_deprn_limit, -1))
309 h_msg_count NUMBER;
306 ORDER BY 2;
307
308 h_request_id NUMBER;
310 h_msg_data VARCHAR2(2000) := NULL;
311 h_preview_status_d VARCHAR2(80);
312 h_current_status_d VARCHAR2(80);
313 h_status BOOLEAN := FALSE;
314
315 h_cat_flex_struct NUMBER;
316 h_concat_cat VARCHAR2(220); -- category in concatenated string.
317 h_cat_segs FA_RX_SHARED_PKG.Seg_Array;
318 h_debug_flag VARCHAR2(3) := 'NO';
319
320 -- exception raised from this module and child modules.
321 mchg_failure EXCEPTION;
322 h_dummy VARCHAR2(30);
323
324 -- Commit results per every 200 assets.
325 h_commit_level NUMBER := 200;
326
327 /* do not need these variables as per bug 8402286
328 need to remove large rollback segment
329 rbs_name VARCHAR2(30);
330 sql_stmt VARCHAR2(101);
331 */
332 l_to_rsr varchar2(15);
333
334 BEGIN
335
336 -- Initialize message stacks.
337 FA_SRVR_MSG.Init_Server_Message;
338 FA_DEBUG_PKG.Initialize;
339 FA_DEBUG_PKG.SET_DEBUG_FLAG;
340
341 /* Bug 8402286 removing LARGE ROLLBACK SEGMENT
342 -- Set large rollback segment.
343 fnd_profile.get('FA_LARGE_ROLLBACK_SEGMENT', rbs_name);
344 IF (rbs_name is not null) THEN
345 sql_stmt := 'Set Transaction Use Rollback Segment '|| rbs_name;
346 execute immediate sql_stmt;
347 END IF;
348 */
349
350
351 -- Initialize global variables.
352 -- (These are session specific variables, and thus values need to
353 -- be re-initialized.)
354 a_tbl.delete;
355 a_index := 0;
356 g_asset_count := 0;
357
358 -- Get concurrent request id for the mass change preview request.
359 -- h_request_id is used when request_id is inserted into the interface
360 -- table, fa_mass_change_itf.
361 -- Need to fetch request id from fnd_global package instead of fa_mass_change
362 -- table, since fa_mass_change table stores the latest request id for
363 -- the SRS Preview report requests(run after this module) or
364 -- Run requests only.
365 h_request_id := fnd_global.conc_request_id;
366
367 -- Fetch mass change record information.
368 OPEN mass_change;
369 FETCH mass_change INTO
370 mc_rec.mass_change_id,
371 mc_rec.book_type_code,
372 mc_rec.transaction_date_entered,
373 mc_rec.concurrent_request_id,
374 mc_rec.status,
375 mc_rec.asset_type,
376 mc_rec.category_id,
377 mc_rec.from_asset_number,
378 mc_rec.to_asset_number,
379 mc_rec.from_date_placed_in_service,
380 mc_rec.to_date_placed_in_service,
381 mc_rec.from_convention,
382 mc_rec.to_convention,
383 mc_rec.from_method_code,
384 mc_rec.to_method_code,
385 mc_rec.from_life_in_months,
386 mc_rec.to_life_in_months,
387 mc_rec.from_bonus_rule,
388 mc_rec.to_bonus_rule,
389 mc_rec.date_effective,
390 mc_rec.from_basic_rate,
391 mc_rec.to_basic_rate,
392 mc_rec.from_adjusted_rate,
393 mc_rec.to_adjusted_rate,
394 mc_rec.from_production_capacity,
395 mc_rec.to_production_capacity,
396 mc_rec.from_uom,
397 mc_rec.to_uom,
398 mc_rec.from_group_association,
399 mc_rec.to_group_association,
400 mc_rec.from_group_asset_id,
401 mc_rec.to_group_asset_id,
402 mc_rec.from_group_asset_number,
403 mc_rec.to_group_asset_number,
404 mc_rec.change_fully_rsvd_assets,
405 mc_rec.amortize_flag,
406 mc_rec.created_by,
407 mc_rec.creation_date,
408 mc_rec.last_updated_by,
409 mc_rec.last_update_login,
410 mc_rec.last_update_date,
411 mc_rec.from_salvage_type,
412 mc_rec.to_salvage_type,
413 mc_rec.from_percent_salvage_value,
414 mc_rec.to_percent_salvage_value,
415 mc_rec.from_salvage_value,
416 mc_rec.to_salvage_value,
417 mc_rec.from_deprn_limit_type,
418 mc_rec.to_deprn_limit_type,
419 mc_rec.from_deprn_limit,
420 mc_rec.to_deprn_limit,
421 mc_rec.from_deprn_limit_amount,
422 mc_rec.to_deprn_limit_amount;
423 CLOSE mass_change;
424
425
426 if not (fa_cache_pkg.fazcbc(X_book => mc_rec.book_type_code)) then
427 raise mchg_failure;
428 end if;
429
430 g_print_debug := fa_cache_pkg.fa_print_debug;
431
432 -- Set debug flag.
433 IF (g_print_debug) THEN
434 h_debug_flag := 'YES';
435 END IF;
436
437 if (g_print_debug) then
438 fa_debug_pkg.add('FARX_MCP.Preview_Change',
439 'Starting Preview',
440 '');
441 end if;
442
443 -- Concurrent request id fetched from fa_mass_changes table is in no use
444 -- in the preview module.
445 -- Assign h_request_id to the global mass change record field so that
446 -- it can be used in other procedures.
447
448 mc_rec.concurrent_request_id := h_request_id;
449
450 /*=========================================================================
451 Delete rows previously inserted into the interface table with the same
455 if (g_print_debug) then
452 request id, if there is any.
453 =========================================================================*/
454
456 fa_debug_pkg.add('FARX_MCP.Preview_Change',
457 'before deleting rows from itf table',
458 '');
459 end if;
460
461 DELETE FROM fa_mass_changes_itf
462 WHERE request_id = h_request_id;
463 COMMIT;
464
465
466 /*=========================================================================
467 Check to make sure current status is 'PREVIEW'
468 =========================================================================*/
469
470 /* OPEN get_status;
471 FETCH get_status
472 INTO h_preview_status_d, h_current_status_d;
473 CLOSE get_status;
474
475 IF (h_preview_status_d <> h_current_status_d) THEN
476 -- Re-using message for mass change program.
477 FA_SRVR_MSG.Add_Message(
478 CALLING_FN => 'FARX_MCP.Preview_Change',
479 NAME => 'FA_MASSRCL_WRONG_STATUS',
480 TOKEN1 => 'CURRENT',
481 VALUE1 => h_current_status_d,
482 TOKEN2 => 'RUNNING',
483 VALUE2 => h_preview_status_d);
484 -- Preview will complete with error status.
485 RAISE mchg_failure;
486 END IF;
487 */
488
489
490 /*=========================================================================
491 Validate assets and insert preview records into the interface table.
492 =========================================================================*/
493 -- Get category flex structure.
494 OPEN get_cat_flex_struct;
495 FETCH get_cat_flex_struct
496 INTO h_cat_flex_struct;
497 CLOSE get_cat_flex_struct;
498
499 -- get the rate source rule for the to method
500 if (mc_rec.to_method_code is not null) then
501 if not fa_cache_pkg.fazccmt
502 (X_method => mc_rec.to_method_code,
503 X_life => mc_rec.to_life_in_months
504 ) then
505 raise mchg_failure;
506 end if;
507
508 l_to_rsr := fa_cache_pkg.fazccmt_record.rate_source_rule;
509
510 end if;
511
512
513 -- Loop all the qualified assets, and insert all the validated assets
514 -- into the interface table, fa_mass_change_itf.
515 OPEN mass_change_assets;
516
517 LOOP
518
519 a_index := a_index + 1;
520
521 FETCH mass_change_assets
522 INTO a_tbl(a_index).asset_id,
523 a_tbl(a_index).asset_number,
524 a_tbl(a_index).description,
525 a_tbl(a_index).asset_type,
526 a_tbl(a_index).category_id,
527 a_tbl(a_index).from_convention,
528 a_tbl(a_index).from_method,
529 a_tbl(a_index).from_life_in_months,
530 a_tbl(a_index).from_bonus_rule,
531 a_tbl(a_index).from_basic_rate,
532 a_tbl(a_index).from_adjusted_rate,
533 a_tbl(a_index).from_capacity,
534 a_tbl(a_index).from_unit_of_measure,
535 a_tbl(a_index).book_type_code,
536 a_tbl(a_index).from_group_asset_number,
537 a_tbl(a_index).from_salvage_type,
538 a_tbl(a_index).from_percent_salvage_value,
539 a_tbl(a_index).from_salvage_value,
540 a_tbl(a_index).from_deprn_limit_type,
541 a_tbl(a_index).from_deprn_limit,
542 a_tbl(a_index).from_deprn_limit_amount;
543 EXIT WHEN mass_change_assets%NOTFOUND;
544
545 if (g_print_debug) then
546 fa_debug_pkg.add('after fecth',
547 'asset_id',
548 a_tbl(a_index).asset_id);
549 end if;
550
551 if (g_print_debug) then
552 fa_debug_pkg.add('calling',
553 'store results',
554 a_index);
555 end if;
556
557 Store_Results(X_mc_rec => mc_rec,
558 X_To_RSR => l_to_rsr,
559 X_Cat_Flex_Struct => h_cat_flex_struct);
560
561
562 -- Insert asset records into the interface table, FA_MASS_RECLASS_ITF,
563 -- at every 200 assets.
564 -- If g_asset_count(number of valid assets) = 200, insert all the 200
565 -- asset records in a_tbl(1..a_index) into the interface table,
566 -- re-initialize the pl/sql table, a_tbl, and reset g_asset_count
567 -- and a_index to 0. Commit changes at every 200 assets as well.
568 IF (g_asset_count = h_commit_level) THEN
569 FOR i IN 1 .. a_index LOOP
573 a_tbl(a_index).asset_id );
570 if (g_print_debug) then
571 fa_debug_pkg.add('FARX_RP.Preview_Reclass',
572 'Preview - inserting asset into itf-table at 200 loop',
574 end if;
575
576 FA_MASS_CHG_UTILS_PKG.Insert_Itf(
577 X_Report_Type => 'PREVIEW',
578 X_Request_Id => h_request_id,
579 X_Mass_Change_Id => X_Mass_Change_Id,
580 X_Asset_Rec => a_tbl(i),
581 X_Last_Update_Date => mc_rec.last_update_date,
582 X_Last_Updated_By => mc_rec.last_updated_by,
583 X_Created_By => mc_rec.created_by,
584 X_Creation_Date => mc_rec.creation_date,
585 X_Last_Update_Login => mc_rec.last_update_login,
586 p_log_level_rec => null
587 );
588 END LOOP;
589
590 a_tbl.delete;
591 g_asset_count := 0;
592 a_index := 0;
593 COMMIT WORK;
594 END IF;
595
596 END LOOP;
597
598 if (g_print_debug) then
599 fa_debug_pkg.add('FARX_MCP.Preview_Change',
600 'after loop',
601 '');
602 end if;
603
604 CLOSE mass_change_assets;
605
606 -- Insert the remaining valid asset records into the interface table.
607 -- Up to a_index - 1, to account for the extra increment taken for a_index
608 -- when no more rows were found in the cursor loop.
609
610 if (g_print_debug) then
611 fa_debug_pkg.add('FARX_MCP.Preview_Change',
612 'after closing cursor',
613 '');
614 end if;
615
616
617 FOR i IN 1 .. (a_index - 1) LOOP
618
619 if (g_print_debug) then
620 fa_debug_pkg.add('FARX_MCP.Preview_Change',
621 'asset inserted',
622 a_tbl(i).asset_id);
623 fa_debug_pkg.add('FARX_MCP.Preview_Change',
624 'book inserted',
625 a_tbl(i).book_type_code);
626 end if;
627
628
629
630 FA_MASS_CHG_UTILS_PKG.Insert_Itf(
631 X_Report_Type => 'PREVIEW',
632 X_Request_Id => h_request_id,
633 X_Mass_Change_Id => X_Mass_Change_Id,
634 X_Asset_Rec => a_tbl(i),
635 X_Last_Update_Date => mc_rec.last_update_date,
636 X_Last_Updated_By => mc_rec.last_updated_by,
637 X_Created_By => mc_rec.created_by,
638 X_Creation_Date => mc_rec.creation_date,
639 X_Last_Update_Login => mc_rec.last_update_login,
640 p_log_level_rec => null
641 );
642 END LOOP;
643
644 a_tbl.delete;
645 g_asset_count := 0;
646 a_index := 0;
647 COMMIT WORK;
648
649 /*=========================================================================
650 Fetch the preview records from the interface table and print them on
651 the SRS output screen for the preview report.
652 =========================================================================*/
653
654 -- Commenting out, since this will be taken care of by SRS report(FASRCPVW.rdf.)
655
656
657 /*=========================================================================
658 Update the status of the mass change to 'PREVIEWED'
659 (This step is now handled in SRS report(FASRCPVW.rdf), which is fired
660 after the RX report request.)
661 =========================================================================*/
662
663 /*
664 UPDATE fa_mass_changes
665 SET status = 'PREVIEWED'
666 WHERE mass_change_id = X_Mass_Reclass_Id
667 AND status = 'PREVIEW';
668 COMMIT WORK;
669 */
670
671 /* Bug 8402286 : BP:4672237
672 if (g_print_debug) then
673 fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
674 end if;
675 */
676 FND_MSG_PUB.Count_And_Get(
677 p_count => h_msg_count,
678 p_data => h_msg_data);
679 FA_SRVR_MSG.Write_Msg_Log(
680 msg_count => h_msg_count,
681 msg_data => h_msg_data);
682 IF (h_debug_flag = 'YES') THEN
683 FA_DEBUG_PKG.Write_Debug_Log;
684 END IF;
685
686 errbuf := ''; -- No error.
687 retcode := 0; -- Completed normally.
688
689 EXCEPTION
690 WHEN mchg_failure THEN
691 retcode := 2; -- Completed with error.
692
693 a_tbl.delete;
694 a_index := 0;
695 g_asset_count := 0;
696 --g_total_assets := 0;
697
698 /* A fatal error has occurred. Update status to 'FAILED_PRE'. */
699 ROLLBACK WORK;
700 UPDATE fa_mass_changes
701 SET status = 'FAILED_PRE'
702 WHERE mass_change_id = X_Mass_Change_Id;
703
704 /* Delete rows inserted into the interface table. */
705 DELETE FROM fa_mass_changes_itf
706 WHERE request_id = h_request_id;
707
708 /* Commit changes. */
709 COMMIT WORK;
710 /* Bug 8402286 : BP:4672237
711 if (g_print_debug) then
712 fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
713 end if;
714 */
715 /* Retrieve message log and write result to log and output. */
719 p_data => h_msg_data);
716 IF (X_RX_Flag = 'YES') THEN
717 FND_MSG_PUB.Count_And_Get(
718 p_count => h_msg_count,
720 FA_SRVR_MSG.Write_Msg_Log(
721 msg_count => h_msg_count,
722 msg_data => h_msg_data);
723 IF (h_debug_flag = 'YES') THEN
724 FA_DEBUG_PKG.Write_Debug_Log;
725 END IF;
726 END IF;
727 WHEN OTHERS THEN
728 retcode := 2; -- Completed with error.
729 IF SQLCODE <> 0 THEN
730 FA_SRVR_MSG.Add_SQL_Error(
731 CALLING_FN => 'FARX_RP.Preview_Reclass');
732 END IF;
733
734 -- Reset global variable values.
735 a_tbl.delete;
736 a_index := 0;
737 g_asset_count := 0;
738 --g_total_assets := 0;
739
740 /* A fatal error has occurred. Update status to 'FAILED_PRE'. */
741 ROLLBACK WORK;
742 UPDATE fa_mass_changes
743 SET status = 'FAILED_PRE'
744 WHERE mass_change_id = X_Mass_Change_Id;
745
746 /* Delete rows inserted into the interface table. */
747 DELETE FROM fa_mass_changes_itf
748 WHERE request_id = h_request_id;
749
750 /* Commit changes. */
751 COMMIT WORK;
752 /* Bug 8402286 : BP:4672237
753 if (g_print_debug) then
754 fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
755 end if;
756 */
757 /* Retrieve message log and write result to log and output. */
758 IF (X_RX_Flag = 'YES') THEN
759 FND_MSG_PUB.Count_And_Get(
760 p_count => h_msg_count,
761 p_data => h_msg_data);
762 FA_SRVR_MSG.Write_Msg_Log(
763 msg_count => h_msg_count,
764 msg_data => h_msg_data);
765 IF (h_debug_flag = 'YES') THEN
766 FA_DEBUG_PKG.Write_Debug_Log;
767 END IF;
768 END IF;
769
770 END Preview_Change;
771
772
773 /*============================================================================+
774 | PROCEDURE Store_Results |
775 +=============================================================================*/
776
777 PROCEDURE Store_Results(
778 X_mc_rec IN FA_MASS_CHG_UTILS_PKG.mass_change_rec_type,
779 X_To_RSR IN VARCHAR2,
780 X_Cat_Flex_Struct IN NUMBER := NULL
781 ) IS
782
783 h_book_type_code VARCHAR2(30) := NULL;
784 h_cat_flex_struct NUMBER := X_Cat_Flex_Struct;
785 h_concat_cat VARCHAR2(220);
786 h_cat_segs FA_RX_SHARED_PKG.Seg_Array;
787 h_from_RSR VARCHAR2(30);
788
789 store_failure EXCEPTION;
790
791 -- cursor to get the category flex structure.
792 CURSOR get_cat_flex_struct IS
793 SELECT category_flex_structure
794 FROM fa_system_controls;
795
796 -- cursor to get the old(current) depreciation rules.
797 CURSOR get_old_info IS
798 SELECT prorate_convention_code,
799 deprn_method_code,
800 life_in_months,
801 basic_rate,
802 adjusted_rate,
803 bonus_rule,
804 production_capacity,
805 unit_of_measure,
806 ad.asset_number
807 FROM FA_BOOKS bk,
808 FA_ADDITIONS_B ad
809 WHERE bk.asset_id = a_tbl(a_index).asset_id
810 AND bk.book_type_code = h_book_type_code
811 AND bk.date_ineffective IS NULL
812 AND bk.group_asset_id = ad.asset_id(+);
813
814 BEGIN
815 -- Get the category flexfield structure, if value not provided.
816 IF X_Cat_Flex_Struct IS NULL THEN
817 OPEN get_cat_flex_struct;
818 FETCH get_cat_flex_struct INTO h_cat_flex_struct;
819 CLOSE get_cat_flex_struct;
820 END IF;
821
822 -- Get the category in concatenated string for the asset's current
823 -- category.
824 FA_RX_SHARED_PKG.Concat_Category(
825 struct_id => h_cat_flex_struct,
826 ccid => a_tbl(a_index).category_id,
827 concat_string => h_concat_cat,
828 segarray => h_cat_segs);
829
830
831 -- For each book, store a preview record for an asset with
832 -- new depreciation rules, if redefault is allowed and if user
833 -- chooses to redefault the depreciation rules. Otherwise,
834 -- there is no change in depreciation rules for the book. */
835
836 -- asset_id, asset_number, category_id fields are already assigned.
837
838 -- a_tbl(a_index).book_type_code := h_book_type_code; -- done above
839 h_book_type_code := a_tbl(a_index).book_type_code;
840 a_tbl(a_index).category := h_concat_cat;
841
842
843 -- load the current rules first
844 OPEN get_old_info;
845 FETCH get_old_info
846 INTO a_tbl(a_index).from_convention,
847 a_tbl(a_index).from_method,
848 a_tbl(a_index).from_life_in_months,
849 a_tbl(a_index).from_basic_rate,
850 a_tbl(a_index).from_adjusted_rate,
851 a_tbl(a_index).from_bonus_rule,
852 a_tbl(a_index).from_capacity,
853 a_tbl(a_index).from_unit_of_measure,
854 a_tbl(a_index).from_group_asset_number;
855 CLOSE get_old_info;
856
857
858 -- Set new depreciation rules.
859 a_tbl(a_index).to_convention := nvl(X_mc_rec.to_convention, a_tbl(a_index).from_convention);
863 if (g_print_debug) then
860 a_tbl(a_index).to_method := nvl(X_mc_rec.to_method_code, a_tbl(a_index).from_method);
861 a_tbl(a_index).to_bonus_rule := nvl(X_mc_rec.to_bonus_rule, a_tbl(a_index).from_bonus_rule);
862
864 fa_debug_pkg.add('store',
865 'entering group logic, to group assoc',
866 X_mc_rec.to_group_association);
867 fa_debug_pkg.add('store',
868 'entering group logic, from group num',
869 X_mc_rec.from_group_asset_number);
870
871 fa_debug_pkg.add('store',
872 'entering group logic, to group number',
873 X_mc_rec.to_group_asset_number);
874
875 end if;
876
877 if (X_mc_rec.to_group_association is null) then
878 a_tbl(a_index).to_group_asset_number := a_tbl(a_index).from_group_asset_number;
879 elsif (X_mc_rec.to_group_association = 'MEMBER') then
880 a_tbl(a_index).to_group_asset_number := X_mc_rec.to_group_asset_number;
881 else
882 a_tbl(a_index).to_group_asset_number := null;
883 end if;
884
885 if (nvl(X_mc_rec.from_method_code, 'X') <> nvl(X_mc_rec.to_method_code, 'X')) then
886
887 if (X_mc_rec.from_method_code is not null) then
888 if not fa_cache_pkg.fazccmt
889 (X_method => mc_rec.from_method_code,
890 X_life => mc_rec.from_life_in_months
891 ) then
892 raise store_failure;
893 end if;
894 end if;
895
896
897 if (nvl(h_from_rsr, 'X') <> nvl(X_to_rsr, 'X')) then
898 if (X_to_rsr = 'FLAT') then
899 a_tbl(a_index).to_life_in_months := null;
900 a_tbl(a_index).to_capacity := null;
901 a_tbl(a_index).to_unit_of_measure := null;
902 a_tbl(a_index).to_basic_rate := nvl(X_mc_rec.to_basic_rate, a_tbl(a_index).from_basic_rate);
903 a_tbl(a_index).to_adjusted_rate := nvl(X_mc_rec.to_adjusted_rate, a_tbl(a_index).from_adjusted_rate);
904 elsif (X_to_rsr = 'PRODUCTION') then
905 a_tbl(a_index).to_life_in_months := null;
906 a_tbl(a_index).to_basic_rate := null;
907 a_tbl(a_index).to_adjusted_rate := null;
908 a_tbl(a_index).to_capacity := nvl(X_mc_rec.to_production_capacity, a_tbl(a_index).from_capacity);
909 a_tbl(a_index).to_unit_of_measure := nvl(X_mc_rec.to_uom, a_tbl(a_index).from_unit_of_measure);
910 else
911 a_tbl(a_index).to_life_in_months := nvl(X_mc_rec.to_life_in_months, a_tbl(a_index).from_life_in_months);
912 a_tbl(a_index).to_basic_rate := null;
913 a_tbl(a_index).to_adjusted_rate := null;
914 a_tbl(a_index).to_capacity := null;
915 a_tbl(a_index).to_unit_of_measure := null;
916 end if;
917
918 else
919 a_tbl(a_index).to_life_in_months := nvl(X_mc_rec.to_life_in_months, a_tbl(a_index).from_life_in_months);
920 a_tbl(a_index).to_basic_rate := nvl(X_mc_rec.to_basic_rate, a_tbl(a_index).from_basic_rate);
921 a_tbl(a_index).to_adjusted_rate := nvl(X_mc_rec.to_adjusted_rate, a_tbl(a_index).from_adjusted_rate);
922 a_tbl(a_index).to_capacity := nvl(X_mc_rec.to_production_capacity, a_tbl(a_index).from_capacity);
923 a_tbl(a_index).to_unit_of_measure := nvl(X_mc_rec.to_uom, a_tbl(a_index).from_unit_of_measure);
924 end if;
925
926 else
927 a_tbl(a_index).to_life_in_months := nvl(X_mc_rec.to_life_in_months, a_tbl(a_index).from_life_in_months);
928 a_tbl(a_index).to_basic_rate := nvl(X_mc_rec.to_basic_rate, a_tbl(a_index).from_basic_rate);
929 a_tbl(a_index).to_adjusted_rate := nvl(X_mc_rec.to_adjusted_rate, a_tbl(a_index).from_adjusted_rate);
930 a_tbl(a_index).to_capacity := nvl(X_mc_rec.to_production_capacity, a_tbl(a_index).from_capacity);
931 a_tbl(a_index).to_unit_of_measure := nvl(X_mc_rec.to_uom, a_tbl(a_index).from_unit_of_measure);
932 end if;
933
934 -- Convert formats for certain fields.
935
936 -- life...
937 -- Need to get the substring from the second position, since
938 -- to_char conversion with the format, always attaches extra space
939 -- at the beginning of the string.
940
941 IF a_tbl(a_index).From_Life_In_Months IS NOT NULL THEN
942 a_tbl(a_index).From_Life := lpad(to_char(trunc(a_tbl(a_index).From_Life_In_Months/12)), 3)||'.'||
943 substr(to_char(mod(a_tbl(a_index).From_Life_In_Months, 12), '00'), 2, 2);
944 ELSE
945 a_tbl(a_index).From_Life := NULL;
946 END IF;
947
948 IF a_tbl(a_index).To_Life_In_Months IS NOT NULL THEN
949 a_tbl(a_index).To_Life := lpad(to_char(trunc(a_tbl(a_index).To_Life_In_Months/12)), 3)||'.'||
950 substr(to_char(mod(a_tbl(a_index).To_Life_In_Months, 12), '00'), 2, 2);
951 ELSE
952 a_tbl(a_index).To_Life := NULL;
953 END IF;
954
955 -- rates...
956 -- May use the following format in report output:
957 -- substr(to_char(round(a_tbl(a_index).From_Basic_Rate*100, 2), '999.99'), 2, 6) or
958 -- lpad(to_char(round(a_tbl(a_index).From_Basic_Rate*100, 2)), 6)
959
960 IF a_tbl(a_index).From_Basic_Rate IS NOT NULL THEN
961 a_tbl(a_index).From_Basic_Rate_Pct := round(a_tbl(a_index).From_Basic_Rate*100, 2);
962 ELSE
963 a_tbl(a_index).From_Basic_Rate_Pct := NULL;
964 END IF;
965
966 IF a_tbl(a_index).To_Basic_Rate IS NOT NULL THEN
967 a_tbl(a_index).To_Basic_Rate_Pct := round(a_tbl(a_index).To_Basic_Rate*100, 2);
968 ELSE
969 a_tbl(a_index).To_Basic_Rate_Pct := NULL;
970 END IF;
971
972 IF a_tbl(a_index).From_Adjusted_Rate IS NOT NULL THEN
973 a_tbl(a_index).From_Adjusted_Rate_Pct := round(a_tbl(a_index).From_Adjusted_Rate*100, 2);
974 ELSE
975 a_tbl(a_index).From_Adjusted_Rate_Pct := NULL;
976 END IF;
977
978 IF a_tbl(a_index).To_Adjusted_Rate IS NOT NULL THEN
979 a_tbl(a_index).To_Adjusted_Rate_Pct := round(a_tbl(a_index).To_Adjusted_Rate*100, 2);
980 ELSE
981 a_tbl(a_index).To_Adjusted_Rate_Pct := NULL;
982 END IF;
983
984 -- The following values are used in review reports only.
985 -- a_tbl(a_index).cost_acct_ccid := NULL;
986 -- a_tbl(a_index).cost_acct := NULL;
987 -- a_tbl(a_index).deprn_rsv_acct_ccid := NULL;
988 -- a_tbl(a_index).deprn_rsv_acct := NULL;
989
990 -- Propagate asset_id, asset_number, category_id to the next
991 -- book record for the asset.
992 a_tbl(a_index + 1).asset_id := a_tbl(a_index).asset_id;
993 a_tbl(a_index + 1).asset_number := a_tbl(a_index).asset_number;
994 a_tbl(a_index + 1).description := a_tbl(a_index).description;
995 a_tbl(a_index + 1).category_id := a_tbl(a_index).category_id;
996
997 EXCEPTION
998 WHEN OTHERS THEN
999 FA_SRVR_MSG.Add_SQL_Error(
1000 CALLING_FN => 'FARX_MCP.Store_Results');
1001 raise;
1002
1003 END Store_Results;
1004
1005
1006
1007 END FARX_MCP;