[Home] [Help]
PACKAGE BODY: APPS.FARX_MCP
Source
1 PACKAGE BODY FARX_MCP AS
2 /* $Header: FARXMCPB.pls 120.3.12010000.1 2008/07/28 13:13:55 appldev ship $ */
3
4 -- Mass change record from fa_mass_changes table.
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,
66 mc.from_convention,
67 mc.to_convention,
68 mc.from_method_code,
69 mc.to_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, -- Bug 6964615 start
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 -- Bug 6964615 start
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, -- Bug 6964615 start
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 -- Bug 6964615 end
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
193 nvl(bk.group_asset_id, -99) = mch.from_group_asset_id))
194 AND bk.group_asset_id = gad.asset_id(+)
195 -- Bug 6964615
196 and nvl(bk.salvage_type, 'XX') =
197 nvl(mch.from_salvage_type,
198 nvl(bk.salvage_type, 'XX'))
199 and nvl(bk.salvage_value, -1) =
200 nvl(mch.from_salvage_value,
201 nvl(bk.salvage_value, -1))
202 and nvl(bk.percent_salvage_value, -1) =
203 nvl(mch.from_percent_salvage_value/100,
204 nvl(bk.percent_salvage_value, -1))
205 and nvl(bk.deprn_limit_type, 'XX') =
206 nvl(mch.from_deprn_limit_type,
207 nvl(bk.deprn_limit_type, 'XX'))
208 and nvl(bk.allowed_deprn_limit_amount, -1) =
209 nvl(mch.from_deprn_limit_amount,
210 nvl(bk.allowed_deprn_limit_amount, -1))
211 and nvl(bk.allowed_deprn_limit, -1) =
212 nvl(mch.from_deprn_limit/100,
213 nvl(bk.allowed_deprn_limit, -1))
214 ORDER BY ad.asset_number;
215
216 h_request_id NUMBER;
217 h_msg_count NUMBER;
218 h_msg_data VARCHAR2(2000) := NULL;
219 h_preview_status_d VARCHAR2(80);
220 h_current_status_d VARCHAR2(80);
221 h_status BOOLEAN := FALSE;
222
223 h_cat_flex_struct NUMBER;
224 h_concat_cat VARCHAR2(220); -- category in concatenated string.
225 h_cat_segs FA_RX_SHARED_PKG.Seg_Array;
226 h_debug_flag VARCHAR2(3) := 'NO';
227
228 -- exception raised from this module and child modules.
229 mchg_failure EXCEPTION;
230 h_dummy VARCHAR2(30);
231
232 -- Commit results per every 200 assets.
233 h_commit_level NUMBER := 200;
234
235 l_to_rsr varchar2(15);
236
237 BEGIN
238
239 -- Initialize message stacks.
240 FA_SRVR_MSG.Init_Server_Message;
241 FA_DEBUG_PKG.Initialize;
242 FA_DEBUG_PKG.SET_DEBUG_FLAG;
243
244
245 -- Initialize global variables.
246 -- (These are session specific variables, and thus values need to
247 -- be re-initialized.)
248 a_tbl.delete;
249 a_index := 0;
250 g_asset_count := 0;
251
252 -- Get concurrent request id for the mass change preview request.
253 -- h_request_id is used when request_id is inserted into the interface
254 -- table, fa_mass_change_itf.
255 -- Need to fetch request id from fnd_global package instead of fa_mass_change
256 -- table, since fa_mass_change table stores the latest request id for
257 -- the SRS Preview report requests(run after this module) or
258 -- Run requests only.
259 h_request_id := fnd_global.conc_request_id;
260
261 -- Fetch mass change record information.
262 OPEN mass_change;
263 FETCH mass_change INTO
264 mc_rec.mass_change_id,
265 mc_rec.book_type_code,
266 mc_rec.transaction_date_entered,
267 mc_rec.concurrent_request_id,
268 mc_rec.status,
269 mc_rec.asset_type,
270 mc_rec.category_id,
271 mc_rec.from_asset_number,
272 mc_rec.to_asset_number,
273 mc_rec.from_date_placed_in_service,
274 mc_rec.to_date_placed_in_service,
275 mc_rec.from_convention,
276 mc_rec.to_convention,
277 mc_rec.from_method_code,
278 mc_rec.to_method_code,
279 mc_rec.from_life_in_months,
280 mc_rec.to_life_in_months,
281 mc_rec.from_bonus_rule,
282 mc_rec.to_bonus_rule,
283 mc_rec.date_effective,
284 mc_rec.from_basic_rate,
285 mc_rec.to_basic_rate,
286 mc_rec.from_adjusted_rate,
287 mc_rec.to_adjusted_rate,
288 mc_rec.from_production_capacity,
289 mc_rec.to_production_capacity,
290 mc_rec.from_uom,
291 mc_rec.to_uom,
292 mc_rec.from_group_association,
293 mc_rec.to_group_association,
294 mc_rec.from_group_asset_id,
295 mc_rec.to_group_asset_id,
296 mc_rec.from_group_asset_number,
297 mc_rec.to_group_asset_number,
298 mc_rec.change_fully_rsvd_assets,
299 mc_rec.amortize_flag,
300 mc_rec.created_by,
301 mc_rec.creation_date,
302 mc_rec.last_updated_by,
303 mc_rec.last_update_login,
304 mc_rec.last_update_date,
305 mc_rec.from_salvage_type,
306 mc_rec.to_salvage_type,
307 mc_rec.from_percent_salvage_value,
308 mc_rec.to_percent_salvage_value,
309 mc_rec.from_salvage_value,
310 mc_rec.to_salvage_value,
311 mc_rec.from_deprn_limit_type,
312 mc_rec.to_deprn_limit_type,
313 mc_rec.from_deprn_limit,
314 mc_rec.to_deprn_limit,
315 mc_rec.from_deprn_limit_amount,
316 mc_rec.to_deprn_limit_amount;
317 CLOSE mass_change;
318
319
320 if not (fa_cache_pkg.fazcbc(X_book => mc_rec.book_type_code)) then
321 raise mchg_failure;
322 end if;
323
324 g_print_debug := fa_cache_pkg.fa_print_debug;
325
326 -- Set debug flag.
327 IF (g_print_debug) THEN
328 h_debug_flag := 'YES';
329 END IF;
330
331 if (g_print_debug) then
332 fa_debug_pkg.add('FARX_MCP.Preview_Change',
333 'Starting Preview',
334 '');
335 end if;
336
337 -- Concurrent request id fetched from fa_mass_changes table is in no use
338 -- in the preview module.
339 -- Assign h_request_id to the global mass change record field so that
340 -- it can be used in other procedures.
341
342 mc_rec.concurrent_request_id := h_request_id;
343
344 /*=========================================================================
345 Delete rows previously inserted into the interface table with the same
346 request id, if there is any.
347 =========================================================================*/
348
349 if (g_print_debug) then
350 fa_debug_pkg.add('FARX_MCP.Preview_Change',
351 'before deleting rows from itf table',
352 '');
353 end if;
354
355 DELETE FROM fa_mass_changes_itf
356 WHERE request_id = h_request_id;
357 COMMIT;
358
359
360 /*=========================================================================
361 Check to make sure current status is 'PREVIEW'
362 =========================================================================*/
366 INTO h_preview_status_d, h_current_status_d;
363
364 /* OPEN get_status;
365 FETCH get_status
367 CLOSE get_status;
368
369 IF (h_preview_status_d <> h_current_status_d) THEN
370 -- Re-using message for mass change program.
371 FA_SRVR_MSG.Add_Message(
372 CALLING_FN => 'FARX_MCP.Preview_Change',
373 NAME => 'FA_MASSRCL_WRONG_STATUS',
374 TOKEN1 => 'CURRENT',
375 VALUE1 => h_current_status_d,
376 TOKEN2 => 'RUNNING',
377 VALUE2 => h_preview_status_d);
378 -- Preview will complete with error status.
379 RAISE mchg_failure;
380 END IF;
381 */
382
383
384 /*=========================================================================
385 Validate assets and insert preview records into the interface table.
386 =========================================================================*/
387 -- Get category flex structure.
388 OPEN get_cat_flex_struct;
389 FETCH get_cat_flex_struct
390 INTO h_cat_flex_struct;
391 CLOSE get_cat_flex_struct;
392
393 -- get the rate source rule for the to method
394 if (mc_rec.to_method_code is not null) then
395 if not fa_cache_pkg.fazccmt
396 (X_method => mc_rec.to_method_code,
397 X_life => mc_rec.to_life_in_months
398 ) then
399 raise mchg_failure;
400 end if;
401
402 l_to_rsr := fa_cache_pkg.fazccmt_record.rate_source_rule;
403
404 end if;
405
406
407 -- Loop all the qualified assets, and insert all the validated assets
408 -- into the interface table, fa_mass_change_itf.
409 OPEN mass_change_assets;
410
411 LOOP
412
413 a_index := a_index + 1;
414
415 FETCH mass_change_assets
416 INTO a_tbl(a_index).asset_id,
417 a_tbl(a_index).asset_number,
418 a_tbl(a_index).description,
419 a_tbl(a_index).asset_type,
420 a_tbl(a_index).category_id,
421 a_tbl(a_index).from_convention,
422 a_tbl(a_index).from_method,
423 a_tbl(a_index).from_life_in_months,
424 a_tbl(a_index).from_bonus_rule,
425 a_tbl(a_index).from_basic_rate,
426 a_tbl(a_index).from_adjusted_rate,
427 a_tbl(a_index).from_capacity,
428 a_tbl(a_index).from_unit_of_measure,
429 a_tbl(a_index).book_type_code,
430 a_tbl(a_index).from_group_asset_number,
431 a_tbl(a_index).from_salvage_type, -- Bug 6964615 start
432 a_tbl(a_index).from_percent_salvage_value,
433 a_tbl(a_index).from_salvage_value,
434 a_tbl(a_index).from_deprn_limit_type,
435 a_tbl(a_index).from_deprn_limit,
436 a_tbl(a_index).from_deprn_limit_amount; -- Bug 6964615 end
437 EXIT WHEN mass_change_assets%NOTFOUND;
438
439 if (g_print_debug) then
440 fa_debug_pkg.add('after fecth',
441 'asset_id',
442 a_tbl(a_index).asset_id);
443 end if;
444
445 if (g_print_debug) then
446 fa_debug_pkg.add('calling',
447 'store results',
448 a_index);
449 end if;
450
451 Store_Results(X_mc_rec => mc_rec,
452 X_To_RSR => l_to_rsr,
453 X_Cat_Flex_Struct => h_cat_flex_struct);
454
455
456 -- Insert asset records into the interface table, FA_MASS_RECLASS_ITF,
457 -- at every 200 assets.
458 -- If g_asset_count(number of valid assets) = 200, insert all the 200
459 -- asset records in a_tbl(1..a_index) into the interface table,
460 -- re-initialize the pl/sql table, a_tbl, and reset g_asset_count
461 -- and a_index to 0. Commit changes at every 200 assets as well.
462 IF (g_asset_count = h_commit_level) THEN
463 FOR i IN 1 .. a_index LOOP
464 if (g_print_debug) then
465 fa_debug_pkg.add('FARX_RP.Preview_Reclass',
466 'Preview - inserting asset into itf-table at 200 loop',
467 a_tbl(a_index).asset_id );
468 end if;
469
470 FA_MASS_CHG_UTILS_PKG.Insert_Itf(
471 X_Report_Type => 'PREVIEW',
472 X_Request_Id => h_request_id,
473 X_Mass_Change_Id => X_Mass_Change_Id,
474 X_Asset_Rec => a_tbl(i),
475 X_Last_Update_Date => mc_rec.last_update_date,
476 X_Last_Updated_By => mc_rec.last_updated_by,
477 X_Created_By => mc_rec.created_by,
478 X_Creation_Date => mc_rec.creation_date,
479 X_Last_Update_Login => mc_rec.last_update_login
480 );
481 END LOOP;
482
483 a_tbl.delete;
484 g_asset_count := 0;
485 a_index := 0;
486 COMMIT WORK;
487 END IF;
488
489 END LOOP;
490
491 if (g_print_debug) then
492 fa_debug_pkg.add('FARX_MCP.Preview_Change',
496
493 'after loop',
494 '');
495 end if;
497 CLOSE mass_change_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
503 if (g_print_debug) then
504 fa_debug_pkg.add('FARX_MCP.Preview_Change',
505 'after closing cursor',
506 '');
507 end if;
508
509
510 FOR i IN 1 .. (a_index - 1) LOOP
511
512 if (g_print_debug) then
513 fa_debug_pkg.add('FARX_MCP.Preview_Change',
514 'asset inserted',
515 a_tbl(i).asset_id);
516 fa_debug_pkg.add('FARX_MCP.Preview_Change',
517 'book inserted',
518 a_tbl(i).book_type_code);
519 end if;
520
521
522
523 FA_MASS_CHG_UTILS_PKG.Insert_Itf(
524 X_Report_Type => 'PREVIEW',
525 X_Request_Id => h_request_id,
526 X_Mass_Change_Id => X_Mass_Change_Id,
527 X_Asset_Rec => a_tbl(i),
528 X_Last_Update_Date => mc_rec.last_update_date,
529 X_Last_Updated_By => mc_rec.last_updated_by,
530 X_Created_By => mc_rec.created_by,
531 X_Creation_Date => mc_rec.creation_date,
532 X_Last_Update_Login => mc_rec.last_update_login
533 );
534 END LOOP;
535
536 a_tbl.delete;
537 g_asset_count := 0;
538 a_index := 0;
539 COMMIT WORK;
540
541 /*=========================================================================
542 Fetch the preview records from the interface table and print them on
543 the SRS output screen for the preview report.
544 =========================================================================*/
545
546 -- Commenting out, since this will be taken care of by SRS report(FASRCPVW.rdf.)
547
548
549 /*=========================================================================
550 Update the status of the mass change to 'PREVIEWED'
551 (This step is now handled in SRS report(FASRCPVW.rdf), which is fired
552 after the RX report request.)
553 =========================================================================*/
554
555 /*
556 UPDATE fa_mass_changes
557 SET status = 'PREVIEWED'
558 WHERE mass_change_id = X_Mass_Reclass_Id
559 AND status = 'PREVIEW';
560 COMMIT WORK;
561 */
562
563 -- Commented for bugfix 4672237
564 -- if (g_print_debug) then
565 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
566 -- end if;
567
568 FND_MSG_PUB.Count_And_Get(
569 p_count => h_msg_count,
570 p_data => h_msg_data);
571 FA_SRVR_MSG.Write_Msg_Log(
572 msg_count => h_msg_count,
573 msg_data => h_msg_data);
574 IF (h_debug_flag = 'YES') THEN
575 FA_DEBUG_PKG.Write_Debug_Log;
576 END IF;
577
578 errbuf := ''; -- No error.
579 retcode := 0; -- Completed normally.
580
581 EXCEPTION
582 WHEN mchg_failure THEN
583 retcode := 2; -- Completed with error.
584
585 a_tbl.delete;
586 a_index := 0;
587 g_asset_count := 0;
588 --g_total_assets := 0;
589
590 /* A fatal error has occurred. Update status to 'FAILED_PRE'. */
591 ROLLBACK WORK;
592 UPDATE fa_mass_changes
593 SET status = 'FAILED_PRE'
594 WHERE mass_change_id = X_Mass_Change_Id;
595
596 /* Delete rows inserted into the interface table. */
597 DELETE FROM fa_mass_changes_itf
598 WHERE request_id = h_request_id;
599
600 /* Commit changes. */
601 COMMIT WORK;
602
603
604 -- Commented for bugfix 4672237
605 -- if (g_print_debug) then
606 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
607 -- end if;
608
609 /* Retrieve message log and write result to log and output. */
610 IF (X_RX_Flag = 'YES') THEN
611 FND_MSG_PUB.Count_And_Get(
612 p_count => h_msg_count,
613 p_data => h_msg_data);
614 FA_SRVR_MSG.Write_Msg_Log(
615 msg_count => h_msg_count,
616 msg_data => h_msg_data);
617 IF (h_debug_flag = 'YES') THEN
618 FA_DEBUG_PKG.Write_Debug_Log;
619 END IF;
620 END IF;
621 WHEN OTHERS THEN
622 retcode := 2; -- Completed with error.
623 IF SQLCODE <> 0 THEN
624 FA_SRVR_MSG.Add_SQL_Error(
625 CALLING_FN => 'FARX_RP.Preview_Reclass');
626 END IF;
627
628 -- Reset global variable values.
629 a_tbl.delete;
630 a_index := 0;
631 g_asset_count := 0;
635 ROLLBACK WORK;
632 --g_total_assets := 0;
633
634 /* A fatal error has occurred. Update status to 'FAILED_PRE'. */
636 UPDATE fa_mass_changes
637 SET status = 'FAILED_PRE'
638 WHERE mass_change_id = X_Mass_Change_Id;
639
640 /* Delete rows inserted into the interface table. */
641 DELETE FROM fa_mass_changes_itf
642 WHERE request_id = h_request_id;
643
644 /* Commit changes. */
645 COMMIT WORK;
646
647
648 -- Commented for bugfix 4672237
649 -- if (g_print_debug) then
650 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
651 -- end if;
652
653 /* Retrieve message log and write result to log and output. */
654 IF (X_RX_Flag = 'YES') THEN
655 FND_MSG_PUB.Count_And_Get(
656 p_count => h_msg_count,
657 p_data => h_msg_data);
658 FA_SRVR_MSG.Write_Msg_Log(
659 msg_count => h_msg_count,
660 msg_data => h_msg_data);
661 IF (h_debug_flag = 'YES') THEN
662 FA_DEBUG_PKG.Write_Debug_Log;
663 END IF;
664 END IF;
665
666 END Preview_Change;
667
668
669 /*============================================================================+
670 | PROCEDURE Store_Results |
671 +=============================================================================*/
672
673 PROCEDURE Store_Results(
674 X_mc_rec IN FA_MASS_CHG_UTILS_PKG.mass_change_rec_type,
675 X_To_RSR IN VARCHAR2,
676 X_Cat_Flex_Struct IN NUMBER := NULL
677 ) IS
678
679 h_book_type_code VARCHAR2(15) := NULL;
680 h_cat_flex_struct NUMBER := X_Cat_Flex_Struct;
681 h_concat_cat VARCHAR2(220);
682 h_cat_segs FA_RX_SHARED_PKG.Seg_Array;
683 h_from_RSR VARCHAR2(30);
684
685 store_failure EXCEPTION;
686
687 -- cursor to get the category flex structure.
688 CURSOR get_cat_flex_struct IS
689 SELECT category_flex_structure
690 FROM fa_system_controls;
691
692 -- cursor to get the old(current) depreciation rules.
693 CURSOR get_old_info IS
694 SELECT prorate_convention_code,
695 deprn_method_code,
696 life_in_months,
697 basic_rate,
698 adjusted_rate,
699 bonus_rule,
700 production_capacity,
701 unit_of_measure,
702 ad.asset_number
703 FROM FA_BOOKS bk,
704 FA_ADDITIONS_B ad
705 WHERE bk.asset_id = a_tbl(a_index).asset_id
706 AND bk.book_type_code = h_book_type_code
707 AND bk.date_ineffective IS NULL
708 AND bk.group_asset_id = ad.asset_id(+);
709
710 BEGIN
711 -- Get the category flexfield structure, if value not provided.
712 IF X_Cat_Flex_Struct IS NULL THEN
713 OPEN get_cat_flex_struct;
714 FETCH get_cat_flex_struct INTO h_cat_flex_struct;
715 CLOSE get_cat_flex_struct;
716 END IF;
717
718 -- Get the category in concatenated string for the asset's current
719 -- category.
720 FA_RX_SHARED_PKG.Concat_Category(
721 struct_id => h_cat_flex_struct,
722 ccid => a_tbl(a_index).category_id,
723 concat_string => h_concat_cat,
724 segarray => h_cat_segs);
725
726
727 -- For each book, store a preview record for an asset with
728 -- new depreciation rules, if redefault is allowed and if user
729 -- chooses to redefault the depreciation rules. Otherwise,
730 -- there is no change in depreciation rules for the book. */
731
732 -- asset_id, asset_number, category_id fields are already assigned.
733
734 -- a_tbl(a_index).book_type_code := h_book_type_code; -- done above
735 h_book_type_code := a_tbl(a_index).book_type_code;
736 a_tbl(a_index).category := h_concat_cat;
737
738
739 -- load the current rules first
740 OPEN get_old_info;
741 FETCH get_old_info
742 INTO a_tbl(a_index).from_convention,
743 a_tbl(a_index).from_method,
744 a_tbl(a_index).from_life_in_months,
745 a_tbl(a_index).from_basic_rate,
746 a_tbl(a_index).from_adjusted_rate,
747 a_tbl(a_index).from_bonus_rule,
748 a_tbl(a_index).from_capacity,
749 a_tbl(a_index).from_unit_of_measure,
750 a_tbl(a_index).from_group_asset_number;
751 CLOSE get_old_info;
752
753
754 -- Set new depreciation rules.
755 a_tbl(a_index).to_convention := nvl(X_mc_rec.to_convention, a_tbl(a_index).from_convention);
756 a_tbl(a_index).to_method := nvl(X_mc_rec.to_method_code, a_tbl(a_index).from_method);
757 a_tbl(a_index).to_bonus_rule := nvl(X_mc_rec.to_bonus_rule, a_tbl(a_index).from_bonus_rule);
758
759 if (g_print_debug) then
760 fa_debug_pkg.add('store',
761 'entering group logic, to group assoc',
762 X_mc_rec.to_group_association);
763 fa_debug_pkg.add('store',
764 'entering group logic, from group num',
768 'entering group logic, to group number',
765 X_mc_rec.from_group_asset_number);
766
767 fa_debug_pkg.add('store',
769 X_mc_rec.to_group_asset_number);
770
771 end if;
772
773 if (X_mc_rec.to_group_association is null) then
774 a_tbl(a_index).to_group_asset_number := a_tbl(a_index).from_group_asset_number;
775 elsif (X_mc_rec.to_group_association = 'MEMBER') then
776 a_tbl(a_index).to_group_asset_number := X_mc_rec.to_group_asset_number;
777 else
778 a_tbl(a_index).to_group_asset_number := null;
779 end if;
780
781 if (nvl(X_mc_rec.from_method_code, 'X') <> nvl(X_mc_rec.to_method_code, 'X')) then
782
783 if (X_mc_rec.from_method_code is not null) then
784 if not fa_cache_pkg.fazccmt
785 (X_method => mc_rec.from_method_code,
786 X_life => mc_rec.from_life_in_months
787 ) then
788 raise store_failure;
789 end if;
790 end if;
791
792
793 if (nvl(h_from_rsr, 'X') <> nvl(X_to_rsr, 'X')) then
794 if (X_to_rsr = 'FLAT') then
795 a_tbl(a_index).to_life_in_months := null;
796 a_tbl(a_index).to_capacity := null;
797 a_tbl(a_index).to_unit_of_measure := null;
798 a_tbl(a_index).to_basic_rate := nvl(X_mc_rec.to_basic_rate, a_tbl(a_index).from_basic_rate);
799 a_tbl(a_index).to_adjusted_rate := nvl(X_mc_rec.to_adjusted_rate, a_tbl(a_index).from_adjusted_rate);
800 elsif (X_to_rsr = 'PRODUCTION') then
801 a_tbl(a_index).to_life_in_months := null;
802 a_tbl(a_index).to_basic_rate := null;
803 a_tbl(a_index).to_adjusted_rate := null;
804 a_tbl(a_index).to_capacity := nvl(X_mc_rec.to_production_capacity, a_tbl(a_index).from_capacity);
805 a_tbl(a_index).to_unit_of_measure := nvl(X_mc_rec.to_uom, a_tbl(a_index).from_unit_of_measure);
806 else
807 a_tbl(a_index).to_life_in_months := nvl(X_mc_rec.to_life_in_months, a_tbl(a_index).from_life_in_months);
808 a_tbl(a_index).to_basic_rate := null;
809 a_tbl(a_index).to_adjusted_rate := null;
810 a_tbl(a_index).to_capacity := null;
811 a_tbl(a_index).to_unit_of_measure := null;
812 end if;
813
814 else
815 a_tbl(a_index).to_life_in_months := nvl(X_mc_rec.to_life_in_months, a_tbl(a_index).from_life_in_months);
816 a_tbl(a_index).to_basic_rate := nvl(X_mc_rec.to_basic_rate, a_tbl(a_index).from_basic_rate);
817 a_tbl(a_index).to_adjusted_rate := nvl(X_mc_rec.to_adjusted_rate, a_tbl(a_index).from_adjusted_rate);
818 a_tbl(a_index).to_capacity := nvl(X_mc_rec.to_production_capacity, a_tbl(a_index).from_capacity);
819 a_tbl(a_index).to_unit_of_measure := nvl(X_mc_rec.to_uom, a_tbl(a_index).from_unit_of_measure);
820 end if;
821
822 else
823 a_tbl(a_index).to_life_in_months := nvl(X_mc_rec.to_life_in_months, a_tbl(a_index).from_life_in_months);
824 a_tbl(a_index).to_basic_rate := nvl(X_mc_rec.to_basic_rate, a_tbl(a_index).from_basic_rate);
825 a_tbl(a_index).to_adjusted_rate := nvl(X_mc_rec.to_adjusted_rate, a_tbl(a_index).from_adjusted_rate);
826 a_tbl(a_index).to_capacity := nvl(X_mc_rec.to_production_capacity, a_tbl(a_index).from_capacity);
827 a_tbl(a_index).to_unit_of_measure := nvl(X_mc_rec.to_uom, a_tbl(a_index).from_unit_of_measure);
828 end if;
829
830 -- Convert formats for certain fields.
831
832 -- life...
833 -- Need to get the substring from the second position, since
834 -- to_char conversion with the format, always attaches extra space
835 -- at the beginning of the string.
836
837 IF a_tbl(a_index).From_Life_In_Months IS NOT NULL THEN
838 a_tbl(a_index).From_Life := lpad(to_char(trunc(a_tbl(a_index).From_Life_In_Months/12)), 3)||'.'||
839 substr(to_char(mod(a_tbl(a_index).From_Life_In_Months, 12), '00'), 2, 2);
840 ELSE
841 a_tbl(a_index).From_Life := NULL;
842 END IF;
843
844 IF a_tbl(a_index).To_Life_In_Months IS NOT NULL THEN
845 a_tbl(a_index).To_Life := lpad(to_char(trunc(a_tbl(a_index).To_Life_In_Months/12)), 3)||'.'||
846 substr(to_char(mod(a_tbl(a_index).To_Life_In_Months, 12), '00'), 2, 2);
847 ELSE
848 a_tbl(a_index).To_Life := NULL;
849 END IF;
850
851 -- rates...
852 -- May use the following format in report output:
853 -- substr(to_char(round(a_tbl(a_index).From_Basic_Rate*100, 2), '999.99'), 2, 6) or
854 -- lpad(to_char(round(a_tbl(a_index).From_Basic_Rate*100, 2)), 6)
855
856 IF a_tbl(a_index).From_Basic_Rate IS NOT NULL THEN
857 a_tbl(a_index).From_Basic_Rate_Pct := round(a_tbl(a_index).From_Basic_Rate*100, 2);
858 ELSE
859 a_tbl(a_index).From_Basic_Rate_Pct := NULL;
860 END IF;
861
862 IF a_tbl(a_index).To_Basic_Rate IS NOT NULL THEN
863 a_tbl(a_index).To_Basic_Rate_Pct := round(a_tbl(a_index).To_Basic_Rate*100, 2);
864 ELSE
865 a_tbl(a_index).To_Basic_Rate_Pct := NULL;
866 END IF;
867
868 IF a_tbl(a_index).From_Adjusted_Rate IS NOT NULL THEN
869 a_tbl(a_index).From_Adjusted_Rate_Pct := round(a_tbl(a_index).From_Adjusted_Rate*100, 2);
870 ELSE
871 a_tbl(a_index).From_Adjusted_Rate_Pct := NULL;
872 END IF;
873
874 IF a_tbl(a_index).To_Adjusted_Rate IS NOT NULL THEN
875 a_tbl(a_index).To_Adjusted_Rate_Pct := round(a_tbl(a_index).To_Adjusted_Rate*100, 2);
876 ELSE
877 a_tbl(a_index).To_Adjusted_Rate_Pct := NULL;
878 END IF;
879
880 -- The following values are used in review reports only.
881 -- a_tbl(a_index).cost_acct_ccid := NULL;
882 -- a_tbl(a_index).cost_acct := NULL;
883 -- a_tbl(a_index).deprn_rsv_acct_ccid := NULL;
884 -- a_tbl(a_index).deprn_rsv_acct := NULL;
885
886 -- Propagate asset_id, asset_number, category_id to the next
887 -- book record for the asset.
888 a_tbl(a_index + 1).asset_id := a_tbl(a_index).asset_id;
889 a_tbl(a_index + 1).asset_number := a_tbl(a_index).asset_number;
890 a_tbl(a_index + 1).description := a_tbl(a_index).description;
891 a_tbl(a_index + 1).category_id := a_tbl(a_index).category_id;
892
893 EXCEPTION
894 WHEN OTHERS THEN
895 FA_SRVR_MSG.Add_SQL_Error(
896 CALLING_FN => 'FARX_MCP.Store_Results');
897 raise;
898
899 END Store_Results;
900
901
902
903 END FARX_MCP;