[Home] [Help]
PACKAGE BODY: APPS.FARX_MCR
Source
1 PACKAGE BODY FARX_MCR AS
2 /* $Header: FARXMCRB.pls 120.3.12010000.1 2008/07/28 13:13:57 appldev ship $ */
3
4
5 -- Mass change record from fa_mass_changes table.
6 mc_rec FA_MASS_CHG_UTILS_PKG.mass_change_rec_type;
7
8 -- Table of asset records.
9 -- (Stores book_type_code as well, and thus one asset will appear multiple
10 -- times if the asset belongs to multiple books.)
11 a_tbl FA_MASS_CHG_UTILS_PKG.asset_tbl_type;
12
13 -- Index into the asset table, a_tbl.
14 a_index NUMBER := 0;
15
16 -- Number of assets(disregaring book_type_code) stored in a_tbl.
17 -- Reset at every 200 assets.
18 g_asset_count 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 | PROCEDURE Review_Change |
27 +=====================================================================================*/
28
29 PROCEDURE Review_Change(
30 X_Mass_Change_Id IN NUMBER,
31 X_RX_Flag IN VARCHAR2 := 'NO',
32 retcode OUT NOCOPY NUMBER,
33 errbuf OUT NOCOPY VARCHAR2) IS
34
35 -- cursor to fetch the current and review status
36 CURSOR get_status IS
37 SELECT lu_rev.meaning,
38 lu_curr.meaning
39 FROM fa_lookups lu_rev,
40 fa_lookups lu_curr
41 WHERE lu_rev.lookup_type = 'MASS_TRX_STATUS' AND
42 lu_rev.lookup_code = 'COMPLETED'
43 AND lu_curr.lookup_type = 'MASS_TRX_STATUS' AND
44 lu_curr.lookup_code = mc_rec.status;
45
46 -- cursor to get category flexfield structure.
47 CURSOR get_cat_flex_struct IS
48 SELECT category_flex_structure
49 FROM fa_system_controls;
50
51 -- cursor to fetch mass change record from fa_mass_change
52 CURSOR mass_change IS
53 SELECT mc.mass_change_id,
54 mc.book_type_code,
55 mc.transaction_date_entered,
56 mc.concurrent_request_id,
57 mc.status,
58 mc.asset_type,
59 mc.category_id,
60 mc.from_asset_number,
61 mc.to_asset_number,
62 mc.from_date_placed_in_service,
63 mc.to_date_placed_in_service,
64 mc.from_convention,
65 mc.to_convention,
66 mc.from_method_code,
67 mc.to_method_code,
68 mc.from_life_in_months,
69 mc.to_life_in_months,
70 mc.from_bonus_rule,
71 mc.to_bonus_rule,
72 mc.date_effective,
73 mc.from_basic_rate,
74 mc.to_basic_rate,
75 mc.from_adjusted_rate,
76 mc.to_adjusted_rate,
77 mc.from_production_capacity,
78 mc.to_production_capacity,
79 mc.from_uom,
80 mc.to_uom,
81 mc.from_group_association,
82 mc.to_group_association,
83 mc.from_group_asset_id,
84 mc.to_group_asset_id,
85 gad1.asset_number,
86 gad2.asset_number,
87 mc.change_fully_rsvd_assets,
88 mc.amortize_flag,
89 mc.created_by,
90 mc.creation_date,
91 mc.last_updated_by,
92 mc.last_update_login,
93 mc.last_update_date,
94 mc.from_salvage_type, -- Bug 6964615 start
95 mc.to_salvage_type,
96 mc.from_percent_salvage_value,
97 mc.to_percent_salvage_value,
98 mc.from_salvage_value,
99 mc.to_salvage_value,
100 mc.from_deprn_limit_type,
101 mc.to_deprn_limit_type,
102 mc.from_deprn_limit,
103 mc.to_deprn_limit,
104 mc.from_deprn_limit_amount,
105 mc.to_deprn_limit_amount -- Bug 6964615 end
106 FROM fa_mass_changes mc,
107 fa_additions_b gad1,
108 fa_additions_b gad2
109 WHERE mass_change_id = X_Mass_Change_Id
110 AND mc.from_group_asset_id = gad1.asset_id(+)
111 AND mc.to_group_asset_id = gad2.asset_id(+);
112
113 -- asset-book records that were changed by mass change
114
115 CURSOR mass_change_assets IS
116 SELECT ad.asset_id,
117 ad.asset_number,
118 ad.description,
119 ad.asset_type,
120 bk1.book_type_code,
121 ad.asset_category_id,
122 NULL,
123 bk1.prorate_convention_code,
124 bk2.prorate_convention_code,
125 bk1.deprn_method_code,
126 bk2.deprn_method_code,
127 bk1.life_in_months,
128 bk2.life_in_months,
129 NULL,
130 NULL,
131 bk1.basic_rate,
132 bk2.basic_rate,
133 NULL,
134 NULL,
135 bk1.adjusted_rate,
136 bk2.adjusted_rate,
137 NULL,
138 NULL,
139 bk1.bonus_rule,
140 bk2.bonus_rule,
141 bk1.production_capacity,
142 bk2.production_capacity,
143 bk1.unit_of_measure,
144 bk2.unit_of_measure,
145 gad1.asset_number,
146 gad2.asset_number,
147 bk1.salvage_type, -- Bug 6964615 start
148 bk2.salvage_type,
149 bk1.percent_salvage_value,
150 bk2.percent_salvage_value,
151 bk1.salvage_value,
152 bk2.salvage_value,
153 bk1.deprn_limit_type,
154 bk2.deprn_limit_type,
155 bk1.allowed_deprn_limit,
156 bk2.allowed_deprn_limit,
157 bk1.allowed_deprn_limit_amount,
158 bk2.allowed_deprn_limit_amount -- Bug 6964615 end
159 FROM fa_books bk1,
160 fa_books bk2,
161 fa_additions ad,
162 fa_additions_b gad1,
163 fa_additions_b gad2,
164 fa_transaction_headers th
165 WHERE th.mass_transaction_id = mc_rec.mass_change_id
166 AND th.member_transaction_header_id is null -- exclude the spawned adjustments on groups
167 AND ad.asset_id = th.asset_id
168 AND bk1.asset_id = th.asset_id
169 AND bk2.asset_id = th.asset_id
170 AND bk1.book_type_code = th.book_type_code
171 AND bk2.book_type_code = th.book_type_code
172 AND bk1.transaction_header_id_out = th.transaction_header_id
173 AND bk2.transaction_header_id_in = th.transaction_header_id
174 AND bk1.group_asset_id = gad1.asset_id(+)
175 AND bk2.group_asset_id = gad2.asset_id(+)
176 ORDER BY ad.asset_number;
177
178 -- to store th.transaction_header_id in the cursor above.
179 h_mch_thid NUMBER(15);
180
181 h_request_id NUMBER;
182 h_msg_count NUMBER;
183 h_msg_data VARCHAR2(2000) := NULL;
184 h_review_status_d VARCHAR2(10);
185 h_current_status_d VARCHAR2(10);
186 h_cat_flex_struct NUMBER;
187 h_cat_segs FA_RX_SHARED_PKG.Seg_Array;
188 h_category_id NUMBER(15) := NULL;
189 h_concat_cat VARCHAR2(220);
190 h_debug_flag VARCHAR2(3) := 'NO';
191
192 -- to keep track of the last asset id that entered the mass_change_assets
193 -- cursor loop.
194 h_last_asset NUMBER(15) := NULL;
195
196 -- indicates whether the book information was found. used only when
197 -- redefault option was set to YES.
198 h_bk_info_found BOOLEAN;
199
200 -- exception raised from this module and child modules.
201 mchg_failure EXCEPTION;
202
203 -- Commit results per every 200 assets.
204 h_commit_level NUMBER := 200;
205
206 BEGIN
207
208 -- Initialize message stacks.
209 FA_SRVR_MSG.Init_Server_Message;
210 FA_DEBUG_PKG.Initialize;
211
212
213 -- Initialize global variables.
214 -- (These are session specific variables, and thus values need to
215 -- be re-initialized.)
216 a_tbl.delete;
217 a_index := 0;
218 g_asset_count := 0;
219
220 -- Get concurrent request id for the mass change review request.
221 -- h_request_id is used when request_id is inserted into the interface
222 -- table, fa_mass_changes_itf.
223 -- Need to fetch request id from fnd_global package instead of fa_mass_changes
224 -- table, since fa_mass_changes table stores the latest request id for
225 -- the Preview or Run requests only.
226 h_request_id := fnd_global.conc_request_id;
227
228 -- Fetch mass changes record information.
229 OPEN mass_change;
230 -- Bug 6964615
231 FETCH mass_change INTO
232 mc_rec.mass_change_id,
233 mc_rec.book_type_code,
234 mc_rec.transaction_date_entered,
235 mc_rec.concurrent_request_id,
236 mc_rec.status,
237 mc_rec.asset_type,
238 mc_rec.category_id,
239 mc_rec.from_asset_number,
240 mc_rec.to_asset_number,
241 mc_rec.from_date_placed_in_service,
242 mc_rec.to_date_placed_in_service,
243 mc_rec.from_convention,
244 mc_rec.to_convention,
245 mc_rec.from_method_code,
246 mc_rec.to_method_code,
247 mc_rec.from_life_in_months,
248 mc_rec.to_life_in_months,
249 mc_rec.from_bonus_rule,
250 mc_rec.to_bonus_rule,
251 mc_rec.date_effective,
252 mc_rec.from_basic_rate,
253 mc_rec.to_basic_rate,
254 mc_rec.from_adjusted_rate,
255 mc_rec.to_adjusted_rate,
256 mc_rec.from_production_capacity,
257 mc_rec.to_production_capacity,
258 mc_rec.from_uom,
259 mc_rec.to_uom,
260 mc_rec.from_group_association,
261 mc_rec.to_group_association,
262 mc_rec.from_group_asset_id,
263 mc_rec.to_group_asset_id,
264 mc_rec.from_group_asset_number,
265 mc_rec.to_group_asset_number,
266 mc_rec.change_fully_rsvd_assets,
267 mc_rec.amortize_flag,
268 mc_rec.created_by,
269 mc_rec.creation_date,
270 mc_rec.last_updated_by,
271 mc_rec.last_update_login,
272 mc_rec.last_update_date,
273 mc_rec.from_salvage_type,
274 mc_rec.to_salvage_type,
275 mc_rec.from_percent_salvage_value,
276 mc_rec.to_percent_salvage_value,
277 mc_rec.from_salvage_value,
278 mc_rec.to_salvage_value,
279 mc_rec.from_deprn_limit_type,
280 mc_rec.to_deprn_limit_type,
281 mc_rec.from_deprn_limit,
282 mc_rec.to_deprn_limit,
283 mc_rec.from_deprn_limit_amount,
284 mc_rec.to_deprn_limit_amount;
285 CLOSE mass_change;
286
287 if not(fa_cache_pkg.fazcbc(X_book => mc_rec.book_type_code)) then
288 raise mchg_failure;
289 end if;
290
291 g_print_debug := fa_cache_pkg.fa_print_debug;
292
293 -- Set debug flag.
294 IF (g_print_debug) THEN
295 h_debug_flag := 'YES';
296 END IF;
297
298 /*===========================================================================
299 Delete rows previously inserted into the interface table with the same
300 request id, if there is any.
301 ===========================================================================*/
302 DELETE FROM fa_mass_changes_itf
303 WHERE request_id = h_request_id;
304 COMMIT;
305
306 /*===========================================================================
307 Check to make sure current status is 'COMPLETED'
308 ===========================================================================*/
309 OPEN get_status;
310 FETCH get_status INTO h_review_status_d, h_current_status_d;
311 CLOSE get_status;
312
313 if g_print_debug then
314 fa_debug_pkg.add('FARX_CR.Review_Change',
315 'After fetching status',
316 '');
317 end if;
318
319 IF (h_review_status_d <> h_current_status_d) THEN
320 -- Re-using message for mass changes program.
321 FA_SRVR_MSG.Add_Message(
322 CALLING_FN => 'FARX_RR.Review_Change',
323 NAME => 'FA_MASSRCL_WRONG_STATUS',
324 TOKEN1 => 'CURRENT',
325 VALUE1 => h_current_status_d,
326 TOKEN2 => 'RUNNING',
327 VALUE2 => h_review_status_d);
328 -- Review will complete with error status.
329 RAISE mchg_failure;
330 END IF;
331
332 /*===========================================================================
333 Insert review records into the interface table.
334 ===========================================================================*/
335
336 if g_print_debug then
337 fa_debug_pkg.add('FARX_CR.Review_Change',
338 'getting cat structure',
342
339 '');
340 end if;
341
343 /* Get category flex structure. */
344 OPEN get_cat_flex_struct;
345 FETCH get_cat_flex_struct INTO h_cat_flex_struct;
346 CLOSE get_cat_flex_struct;
347
348 OPEN mass_change_assets;
349
350 LOOP
351
352 if g_print_debug then
353 fa_debug_pkg.add('FARX_CR.Review_Change',
354 'in loop',
355 '');
356 end if;
357
358 -- Fetch the asset-book pair into the pl/sql table.
359 a_index := a_index + 1;
360
361 -- Bug 6964615
362 FETCH mass_change_assets INTO
363 a_tbl(a_index).asset_id,
364 a_tbl(a_index).asset_number,
365 a_tbl(a_index).description,
366 a_tbl(a_index).asset_type,
367 a_tbl(a_index).book_type_code,
368 a_tbl(a_index).category_id,
369 a_tbl(a_index).category,
370 a_tbl(a_index).from_convention,
371 a_tbl(a_index).to_convention,
372 a_tbl(a_index).from_method,
373 a_tbl(a_index).to_method,
374 a_tbl(a_index).from_life_in_months,
375 a_tbl(a_index).to_life_in_months,
376 a_tbl(a_index).from_life,
377 a_tbl(a_index).to_life,
378 a_tbl(a_index).from_basic_rate,
379 a_tbl(a_index).to_basic_rate,
380 a_tbl(a_index).from_basic_rate_pct,
381 a_tbl(a_index).to_basic_rate_pct,
382 a_tbl(a_index).from_adjusted_rate,
383 a_tbl(a_index).to_adjusted_rate,
384 a_tbl(a_index).from_adjusted_rate_pct,
385 a_tbl(a_index).to_adjusted_rate_pct,
386 a_tbl(a_index).from_bonus_rule,
387 a_tbl(a_index).to_bonus_rule,
388 a_tbl(a_index).from_capacity,
389 a_tbl(a_index).to_capacity,
390 a_tbl(a_index).from_unit_of_measure,
391 a_tbl(a_index).to_unit_of_measure,
392 a_tbl(a_index).from_group_asset_number,
393 a_tbl(a_index).to_group_asset_number,
394 a_tbl(a_index).from_salvage_type,
395 a_tbl(a_index).to_salvage_type,
396 a_tbl(a_index).from_percent_salvage_value,
397 a_tbl(a_index).to_percent_salvage_value,
398 a_tbl(a_index).from_salvage_value,
399 a_tbl(a_index).to_salvage_value,
400 a_tbl(a_index).from_deprn_limit_type,
401 a_tbl(a_index).to_deprn_limit_type,
402 a_tbl(a_index).from_deprn_limit,
403 a_tbl(a_index).to_deprn_limit,
404 a_tbl(a_index).from_deprn_limit_amount,
405 a_tbl(a_index).to_deprn_limit_amount;
406 EXIT WHEN mass_change_assets%NOTFOUND;
407
408 -- Get category in concatenated string format.
409 FA_RX_SHARED_PKG.Concat_Category(
410 struct_id => h_cat_flex_struct,
411 ccid => a_tbl(a_index).category_id,
412 concat_string => a_tbl(a_index).category,
413 segarray => h_cat_segs);
414 h_concat_cat := a_tbl(a_index).category;
415
416 -- Convert formats for certain fields.
417
418 -- life...
419 -- Need to get the substring from the second position, since
420 -- to_char conversion with the format, always attaches extra space
421 -- at the beginning of the string.
422
423 IF a_tbl(a_index).From_Life_In_Months IS NOT NULL THEN
424 a_tbl(a_index).From_Life := lpad(to_char(trunc(a_tbl(a_index).From_Life_In_Months/12)), 3)||'.'||
425 substr(to_char(mod(a_tbl(a_index).From_Life_In_Months, 12), '00'), 2, 2);
426 ELSE
427 a_tbl(a_index).From_Life := NULL;
428 END IF;
429
430 IF a_tbl(a_index).To_Life_In_Months IS NOT NULL THEN
431 a_tbl(a_index).To_Life := lpad(to_char(trunc(a_tbl(a_index).To_Life_In_Months/12)), 3)||'.'||
432 substr(to_char(mod(a_tbl(a_index).To_Life_In_Months, 12), '00'), 2, 2);
433 ELSE
434 a_tbl(a_index).To_Life := NULL;
435 END IF;
436
437 -- rates...
438 -- May use the following format in report output:
439 -- substr(to_char(round(a_tbl(a_index).From_Basic_Rate*100, 2), '999.99'), 2, 6) or
440 -- lpad(to_char(round(a_tbl(a_index).From_Basic_Rate*100, 2)), 6)
441
442 IF a_tbl(a_index).From_Basic_Rate IS NOT NULL THEN
443 a_tbl(a_index).From_Basic_Rate_Pct := round(a_tbl(a_index).From_Basic_Rate*100, 2);
444 ELSE
445 a_tbl(a_index).From_Basic_Rate_Pct := NULL;
446 END IF;
447
448 IF a_tbl(a_index).To_Basic_Rate IS NOT NULL THEN
449 a_tbl(a_index).To_Basic_Rate_Pct := round(a_tbl(a_index).To_Basic_Rate*100, 2);
450 ELSE
451 a_tbl(a_index).To_Basic_Rate_Pct := NULL;
452 END IF;
453
454 IF a_tbl(a_index).From_Adjusted_Rate IS NOT NULL THEN
455 a_tbl(a_index).From_Adjusted_Rate_Pct := round(a_tbl(a_index).From_Adjusted_Rate*100, 2);
456 ELSE
457 a_tbl(a_index).From_Adjusted_Rate_Pct := NULL;
458 END IF;
459
460 IF a_tbl(a_index).To_Adjusted_Rate IS NOT NULL THEN
461 a_tbl(a_index).To_Adjusted_Rate_Pct := round(a_tbl(a_index).To_Adjusted_Rate*100, 2);
462 ELSE
466
463 a_tbl(a_index).To_Adjusted_Rate_Pct := NULL;
464 END IF;
465
467
468
469 -- Update last asset processed and the asset count.
470 IF (a_tbl(a_index).asset_id <> h_last_asset OR
471 h_last_asset IS NULL) THEN
472 h_last_asset := a_tbl(a_index).asset_id;
473 g_asset_count := g_asset_count + 1;
474 END IF;
475
476 /* Insert asset records into the interface table, FA_MASS_CHANGES_ITF,
477 at every 200 assets and re-initialize the counter and the asset table. */
478 -- If the 200th asset belongs to more than one book, only the information
479 -- for the first book of this asset will be inserted into the table.
480 -- The rest will be taken care of in the next insertion.
481
482 IF (g_asset_count = h_commit_level) THEN
483 FOR i IN 1 .. a_index LOOP
484 FA_MASS_CHG_UTILS_PKG.Insert_Itf(
485 X_Report_Type => 'REVIEW',
486 X_Request_Id => h_request_id,
487 X_Mass_Change_Id => X_Mass_Change_Id,
488 X_Asset_Rec => a_tbl(i),
489 X_Last_Update_Date => mc_rec.last_update_date,
490 X_Last_Updated_By => mc_rec.last_updated_by,
491 X_Created_By => mc_rec.created_by,
492 X_Creation_Date => mc_rec.creation_date,
493 X_Last_Update_Login => mc_rec.last_update_login
494 );
495 END LOOP;
496
497 a_tbl.delete;
498 g_asset_count := 0;
499 a_index := 0;
500 -- Also re-initialize h_last_asset so that g_asset_count
501 -- is incremented to 1 at the next loop entry as in the former
502 -- insertion.
503 h_last_asset := NULL;
504 COMMIT WORK;
505
506 END IF;
507
508 END LOOP;
509
510 CLOSE mass_change_assets;
511
512 /* Insert the remaining asset records into the interface table. */
513 -- Up to a_index - 1, to account for the extra increment taken for a_index
514 -- when no more rows were found in the cursor loop.
515 FOR i IN 1 .. (a_index - 1) LOOP
516 FA_MASS_CHG_UTILS_PKG.Insert_Itf(
517 X_Report_Type => 'REVIEW',
518 X_Request_Id => h_request_id,
519 X_Mass_Change_Id => X_Mass_Change_Id,
520 X_Asset_Rec => a_tbl(i),
521 X_Last_Update_Date => mc_rec.last_update_date,
522 X_Last_Updated_By => mc_rec.last_updated_by,
523 X_Created_By => mc_rec.created_by,
524 X_Creation_Date => mc_rec.creation_date,
525 X_Last_Update_Login => mc_rec.last_update_login
526 );
527
528 END LOOP;
529
530 a_tbl.delete;
531 g_asset_count := 0;
532 a_index := 0;
533
534 fa_rx_conc_mesg_pkg.log('');
535 fnd_message.set_name('OFA', 'FA_MASSRCL_CHG_RVW');
536 h_msg_data := fnd_message.get;
537 fa_rx_conc_mesg_pkg.log(h_msg_data);
538 fa_rx_conc_mesg_pkg.log('');
539 COMMIT WORK;
540
541
542 FND_MSG_PUB.Count_And_Get(
543 p_count => h_msg_count,
544 p_data => h_msg_data);
545 FA_SRVR_MSG.Write_Msg_Log(
546 msg_count => h_msg_count,
547 msg_data => h_msg_data);
548 IF (h_debug_flag = 'YES') THEN
549 FA_DEBUG_PKG.Write_Debug_Log;
550 END IF;
551
552 errbuf := ''; -- No error.
553 retcode := 0; -- Completed normally.
554
555 EXCEPTION
556 WHEN mchg_failure THEN
557 retcode := 2; -- Completed with error.
558
559 -- Reset global variable values.
560 a_tbl.delete;
561 a_index := 0;
562 g_asset_count := 0;
563 /* A fatal error has occurred. Rollback transaction. */
564 ROLLBACK WORK;
565 /* Delete rows inserted into the interface table. */
566 DELETE FROM fa_mass_changes_itf
567 WHERE request_id = h_request_id;
568 /* Commit changes. */
569 COMMIT WORK;
570 /* Retrieve message log and write result to log and output. */
571
572 IF (X_RX_Flag = 'YES') THEN
573 FND_MSG_PUB.Count_And_Get(
574 p_count => h_msg_count,
575 p_data => h_msg_data);
576 FA_SRVR_MSG.Write_Msg_Log(
577 msg_count => h_msg_count,
578 msg_data => h_msg_data);
579 IF (h_debug_flag = 'YES') THEN
580 FA_DEBUG_PKG.Write_Debug_Log;
581 END IF;
582 END IF;
583 WHEN OTHERS THEN
584 retcode := 2; -- Completed with error.
585 IF SQLCODE <> 0 THEN
586 FA_SRVR_MSG.Add_SQL_Error(CALLING_FN => 'FARX_RP.Review_Change');
587 END IF;
588
589 -- Reset global variable values.
590 a_tbl.delete;
591 a_index := 0;
592 g_asset_count := 0;
593 --g_total_assets := 0;
594 /* A fatal error has occurred. Rollback transaction. */
595 ROLLBACK WORK;
596 /* Delete rows inserted into the interface table. */
597 DELETE FROM fa_mass_changes_itf
598 WHERE request_id = h_request_id;
599 /* Commit changes. */
600 COMMIT WORK;
601 /* Retrieve message log and write result to log and output. */
602 IF (X_RX_Flag = 'YES') THEN
603 FND_MSG_PUB.Count_And_Get(
604 p_count => h_msg_count,
605 p_data => h_msg_data);
606 FA_SRVR_MSG.Write_Msg_Log(
607 msg_count => h_msg_count,
608 msg_data => h_msg_data);
609 IF (h_debug_flag = 'YES') THEN
610 FA_DEBUG_PKG.Write_Debug_Log;
611 END IF;
612 END IF;
616 END FARX_MCR;
613
614 END Review_Change;
615