[Home] [Help]
PACKAGE BODY: APPS.FA_RX_GROUP
Source
1 PACKAGE BODY FA_RX_GROUP AS
2 /* $Header: farxgab.pls 120.20 2010/03/31 09:31:51 gigupta ship $ */
3
4 -- global variables
5 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
6
7
8 PROCEDURE get_group_asset_info (
9 p_book_type_code IN VARCHAR2,
10 p_sob_id IN VARCHAR2 default NULL, -- MRC: Set of books id
11 p_start_fiscal_year IN VARCHAR2,
12 p_end_fiscal_year IN VARCHAR2,
13 p_major_category_low IN VARCHAR2,
14 p_major_category_high IN VARCHAR2,
15 p_minor_category_low IN VARCHAR2,
16 p_minor_category_high IN VARCHAR2,
17 p_category_segment_name IN VARCHAR2,
18 p_category_segment_low IN VARCHAR2,
19 p_category_segment_high IN VARCHAR2,
20 p_asset_number_low IN VARCHAR2,
21 p_asset_number_high IN VARCHAR2,
22 p_drill_down IN VARCHAR2,
23 p_request_id IN NUMBER,
24 p_user_id IN NUMBER,
25 x_retcode OUT NOCOPY NUMBER,
26 x_errbuf OUT NOCOPY VARCHAR2)
27 IS
28 l_info_rec info_rec_type;
29
30 l_application_id fa_system_controls.fa_application_id%TYPE;
31 l_category_flex_structure fa_system_controls.category_flex_structure%TYPE;
32
33 l_param_where_stmt VARCHAR2(1000);
34 l_group_sql_stmt VARCHAR2(5000);
35 l_sql_stmt VARCHAR2(5000);
36
37 l_group_adjustment_amount NUMBER;
38 l_second_half_add_rec_cost NUMBER;
39 l_second_half_grp_adjustment NUMBER;
40 l_second_half_mem_adjustment NUMBER;
41 l_group_reclass_in NUMBER;
42 l_group_reclass_cost_in_out NUMBER; /*Bug# 9375920 */
43 l_group_reclass_rsv_in_out NUMBER; /*Bug# 9375920 */
44 l_group_reclass_out NUMBER;
45 l_all_reduced_deprn_amount NUMBER;
46 l_non_cip_num NUMBER;
47
48 l_message VARCHAR2(30);
49
50 -- MRC
51 h_sob_id NUMBER;
52 h_mrcsobtype VARCHAR2(1);
53 -- End MRC
54
55 TYPE group_csrtype IS REF CURSOR;
56 l_group_csr group_csrtype;
57 l_group_rec group_rec_type;
58 l_member_rec group_rec_type;
59
60 TYPE amount_csrtype IS REF CURSOR;
61 l_amount_csr amount_csrtype;
62
63 main_err EXCEPTION;
64
65 CURSOR ret_adj_cur IS
66 SELECT NVL(SUM(NVL(fad.adjustment_amount,0)),0) adj_amt,
67 fad.adjustment_type adj_type
68 FROM fa_adjustments fad,
69 fa_book_controls bc,
70 fa_fiscal_year fy,
71 fa_transaction_headers thg
72 WHERE fad.asset_id = l_group_rec.asset_id
73 AND fad.book_type_code = p_book_type_code
74 AND fad.source_type_code = 'RETIREMENT'
75 AND fad.adjustment_type IN ('PROCEEDS CLR','REMOVALCOST CLR')
76 AND fad.transaction_header_id = thg.transaction_header_id
77 AND thg.member_transaction_header_id IS NULL
78 AND thg.book_type_code = bc.book_type_code
79 AND thg.transaction_date_entered BETWEEN fy.start_date and fy.end_date
80 AND fy.fiscal_year = l_info_rec.fiscal_year
81 AND fy.fiscal_year_name = bc.fiscal_year_name
82 GROUP BY fad.adjustment_type;
83
84 -- MRC
85 CURSOR mc_ret_adj_cur IS
86 SELECT NVL(SUM(NVL(fad.adjustment_amount,0)),0) adj_amt,
87 fad.adjustment_type adj_type
88 FROM fa_mc_adjustments fad,
89 fa_book_controls bc,
90 fa_fiscal_year fy,
91 fa_transaction_headers thg
92 WHERE fad.asset_id = l_group_rec.asset_id
93 AND fad.book_type_code = p_book_type_code
94 AND fad.source_type_code = 'RETIREMENT'
95 AND fad.adjustment_type IN ('PROCEEDS CLR','REMOVALCOST CLR')
96 AND fad.transaction_header_id = thg.transaction_header_id
97 AND thg.member_transaction_header_id IS NULL
98 AND thg.book_type_code = bc.book_type_code
99 AND thg.transaction_date_entered BETWEEN fy.start_date and fy.end_date
100 AND fy.fiscal_year = l_info_rec.fiscal_year
101 AND fy.fiscal_year_name = bc.fiscal_year_name
102 AND fad.set_of_books_id = l_info_rec.set_of_books_id
103 GROUP BY fad.adjustment_type;
104 -- End MRC
105
106 l_log_level_rec FA_API_TYPES.log_level_rec_type;
107
108 BEGIN
109 IF g_print_debug THEN
110 fa_rx_util_pkg.debug('get_group_asset_info: '
111 || 'farx_ga.get_group_asset_info()+');
112 fa_rx_util_pkg.debug('get_group_asset_info: '
113 || 'book: ' || p_book_type_code);
114 fa_rx_util_pkg.debug('get_group_asset_info: '
115 || 'fiscal year from: ' || p_start_fiscal_year);
116 fa_rx_util_pkg.debug('get_group_asset_info: '
117 || 'fiscal year to: ' || p_end_fiscal_year);
118 fa_rx_util_pkg.debug('get_group_asset_info: '
119 || 'user_id: ' || p_user_id);
120 fa_rx_util_pkg.debug('get_group_asset_info: '
121 || 'request_id: ' || p_request_id);
122 END IF;
123 l_message := 'get_group_asset_info start';
124
125
126 ----------------------------------------------
127 -- Initialization
128 ----------------------------------------------
129 l_info_rec.book_type_code := p_book_type_code;
130 l_info_rec.request_id := p_request_id;
131 l_info_rec.user_id := p_user_id;
132
133 h_sob_id := to_number(p_sob_id); -- MRC
134
135 -- MRC
136 if h_sob_id is not null then
137 begin
138 select 'P'
139 into H_MRCSOBTYPE
140 from fa_book_controls
141 where book_type_code = p_book_type_code
142 and set_of_books_id = h_sob_id;
143 exception
144 when no_data_found then
145 H_MRCSOBTYPE := 'R';
146 end;
147 else
148 H_MRCSOBTYPE := 'P';
149 end if;
150 -- End MRC
151
152 -- Get organization name, functional currency and flex structure
153 SELECT sc.fa_application_id,
154 sc.category_flex_structure,
155 sob.name,
156 sob.currency_code,
157 decode(H_MRCSOBTYPE, 'P', bc.set_of_books_id, h_sob_id), -- MRC
158 bc.deprn_calendar
159 INTO l_application_id,
160 l_category_flex_structure,
161 l_info_rec.organization_name,
162 l_info_rec.functional_currency_code,
163 l_info_rec.set_of_books_id,
164 l_info_rec.deprn_calendar
165 FROM fa_system_controls sc,
166 fa_book_controls bc,
167 gl_sets_of_books sob,
168 fnd_currencies cur
169 WHERE bc.book_type_code = p_book_type_code
170 AND sob.set_of_books_id = decode(H_MRCSOBTYPE, 'P', bc.set_of_books_id, h_sob_id) -- MRC
171 AND sob.currency_code = cur.currency_code;
172
173 IF g_print_debug THEN
174 fa_rx_util_pkg.debug('get_group_asset_info: '
175 || 'set_of_books_id: ' || l_info_rec.set_of_books_id);
176 fa_rx_util_pkg.debug('get_group_asset_info: '
177 || 'deprn_calendar: ' || l_info_rec.deprn_calendar);
178 END IF;
179 l_message := 'initialization end';
180
181
182 -- Create select and where clauses for categories paramters
183 get_category_sql(l_application_id,
184 l_category_flex_structure,
185 'BASED_CATEGORY',
186 p_major_category_low,
187 p_major_category_high,
188 l_info_rec.major_cat_select_stmt,
189 l_param_where_stmt);
190
191 get_category_sql(l_application_id,
192 l_category_flex_structure,
193 'MINOR_CATEGORY',
194 p_minor_category_low,
195 p_minor_category_high,
196 l_info_rec.minor_cat_select_stmt,
197 l_sql_stmt);
198 l_param_where_stmt := l_param_where_stmt || l_sql_stmt;
199
200 get_category_sql(l_application_id,
201 l_category_flex_structure,
202 p_category_segment_name,
203 p_category_segment_low,
204 p_category_segment_high,
205 l_info_rec.other_cat_select_stmt,
206 l_sql_stmt);
207 l_param_where_stmt := l_param_where_stmt || l_sql_stmt;
208
209
210 -- Add group asset number where clause
211 IF p_asset_number_low = p_asset_number_high THEN
212 l_param_where_stmt := l_param_where_stmt || ' AND ad.asset_number = '''
213 || p_asset_number_low || '''';
214
215 ELSIF p_asset_number_low IS NOT NULL
216 AND p_asset_number_high IS NOT NULL THEN
217 l_param_where_stmt := l_param_where_stmt || ' AND ad.asset_number
218 BETWEEN ''' ||
219 p_asset_number_low || '''' || ' AND ''' ||
220 p_asset_number_high || '''';
221
222 ELSIF p_asset_number_low IS NOT NULL THEN
223 l_param_where_stmt := l_param_where_stmt || ' AND ad.asset_number >= '''
224 || p_asset_number_low || '''';
225
226 ELSIF p_asset_number_high IS NOT NULL THEN
227 l_param_where_stmt := l_param_where_stmt || ' AND ad.asset_number <= '''
228 || p_asset_number_high || '''';
229 END IF;
230
231 IF g_print_debug THEN
232 fa_rx_util_pkg.debug('get_group_asset_info: '
233 || 'l_param_where_stmt:' || l_param_where_stmt);
234 END IF;
235 l_message := 'category sql end';
236
237
238 IF NOT fa_cache_pkg.fazcbc(p_book_type_code) THEN
239 raise main_err;
240 END IF;
241
242
243 -----------------------------------------------------
244 -- Main logic
245 -- Fiscal Year Loop -> Group Loop -> Member Loop
246 -----------------------------------------------------
247
248 l_info_rec.fiscal_year := p_start_fiscal_year;
249
250 LOOP
251 EXIT WHEN l_info_rec.fiscal_year > p_end_fiscal_year;
252
253
254 -- Get first and last depreciated period counter of the fiscal year
255 -- Bug #2846317 - can report open period if it's depreciated
256 SELECT MIN(period_counter),
257 MAX(period_counter)
258 INTO l_info_rec.min_period_counter,
259 l_info_rec.max_period_counter
260 FROM fa_deprn_periods
261 WHERE book_type_code = p_book_type_code
262 AND fiscal_year = l_info_rec.fiscal_year
263 AND NVL(deprn_run, 'N') = 'Y';
264
265 -- Exit if no period is depreciated in fiscal year
266 EXIT WHEN l_info_rec.max_period_counter IS NULL;
267
268 l_message := 'fiscal year loop (1)';
269
270
271 --------------------------------------------------------------
272 -- <Group Query Loop>
273 -- Query group assets matching to the report parameter.
274 --------------------------------------------------------------
275
276 -- Create main query for group
277 l_group_sql_stmt :=
278 'SELECT
279 ad.asset_number,
280 ad.description,
281 ad.asset_type, '
282 || l_info_rec.major_cat_select_stmt || ','
283 || l_info_rec.minor_cat_select_stmt || ','
284 || l_info_rec.other_cat_select_stmt || ',
285 bk.date_placed_in_service,
286 bk.deprn_method_code,
287 br.rule_name,
288 bk.tracking_method,
289 bk.adjusted_rate,
290 NULL,
291 NVL(bk.cost, 0) + NVL(bk.cip_cost, 0),
292 NVL(bk.salvage_value, 0),
293 NVL(bk.adjusted_recoverable_cost, 0),
294 NVL(prev.cost, 0) + NVL(prev.cip_cost, 0) - NVL(prev.deprn_reserve, 0),
295 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
296 0,
297 NVL(bk.terminal_gain_loss_amount, 0),
298 NULL, NULL,
299 NVL(ds.adjusted_cost, 0),
300 NULL, NULL,
301 NVL(ds.ytd_deprn, 0),
302 NVL(ds.deprn_reserve, 0),
303 NULL, NULL,
304 ad.asset_id,
305 NULL,
306 DECODE(bk.life_in_months, NULL, NULL,
307 TO_CHAR(FLOOR(bk.life_in_months / 12)) || ''.'' ||
308 TO_CHAR(MOD(bk.life_in_months, 12))),
309 met.deprn_basis_rule,
310 met.exclude_salvage_value_flag,
311 NVL(bk.reduction_rate, 0),
312 bk.depreciation_option,
313 bk.recognize_gain_loss,
314 bk.exclude_proceeds_from_basis,
315 NULL, NULL,
316 ds.period_counter ';
317
318 -- Add from clause
319 get_from_sql_stmt(l_info_rec, NULL, h_mrcsobtype, l_sql_stmt); -- MRC
320 l_group_sql_stmt := l_group_sql_stmt || l_sql_stmt;
321
322 -- Add where clause
323 get_where_sql_stmt(l_info_rec, NULL, h_mrcsobtype, l_sql_stmt); -- MRC
324 l_group_sql_stmt := l_group_sql_stmt || l_sql_stmt || l_param_where_stmt;
325
326 IF g_print_debug THEN
327 fa_rx_util_pkg.debug('get_group_asset_info: '
328 || 'l_group_sql_stmt:' || l_group_sql_stmt);
329 END IF;
330 l_message := 'fiscal year loop (2)';
331
332
333
334 -- Group query loop start
335 OPEN l_group_csr FOR l_group_sql_stmt;
336 LOOP
337 FETCH l_group_csr INTO l_group_rec;
338 EXIT WHEN l_group_csr%NOTFOUND;
339
340 l_message := 'group loop (1)';
341
342 IF g_print_debug THEN
343 fa_rx_util_pkg.debug('get_group_asset_info: '
344 || 'group_asset: ' || l_group_rec.asset_number);
345 END IF;
346
347 ----------------------------------------------------------
348 -- Query add/adj/retirement amount in the FY
349 ----------------------------------------------------------
350
351 -- Query fa_retirements
352 -- (retiring member asset is allowed only in current period)
353 if(H_MRCSOBTYPE <> 'R') then -- MRC
354 SELECT NVL(SUM(ret.proceeds_of_sale), 0),
355 NVL(SUM(ret.cost_of_removal), 0),
356 NVL(SUM(ret.nbv_retired), 0),
357 NVL(SUM(ret.cost_retired), 0),
358 NVL(SUM(ret.reserve_retired), 0),
359 NVL(SUM(ret.recapture_amount), 0)
360 INTO l_group_rec.proceeds_of_sale,
361 l_group_rec.cost_of_removal,
362 l_group_rec.net_proceeds,
363 l_group_rec.cost_retired,
364 l_group_rec.reserve_retired,
365 l_group_rec.recapture_amount
366 FROM fa_retirements ret,
367 fa_book_controls bc,
368 fa_fiscal_year fy,
369 fa_transaction_headers thg,
370 fa_transaction_headers thm
371 WHERE bc.book_type_code = p_book_type_code
372 AND fy.fiscal_year = l_info_rec.fiscal_year
373 AND fy.fiscal_year_name = bc.fiscal_year_name
374 AND thm.book_type_code = bc.book_type_code
375 AND thm.transaction_date_entered
376 BETWEEN fy.start_date and fy.end_date
377 AND thg.book_type_code = bc.book_type_code
378 AND thg.asset_id = l_group_rec.asset_id
379 AND thg.member_transaction_header_id = thm.transaction_header_id
380 AND ret.transaction_header_id_in = thm.transaction_header_id
381 AND ret.status <> 'DELETED';
382 -- MRC
383 else
384 SELECT NVL(SUM(ret.proceeds_of_sale), 0),
385 NVL(SUM(ret.cost_of_removal), 0),
386 NVL(SUM(ret.nbv_retired), 0),
387 NVL(SUM(ret.cost_retired), 0),
388 NVL(SUM(ret.reserve_retired), 0),
389 NVL(SUM(ret.recapture_amount), 0)
390 INTO l_group_rec.proceeds_of_sale,
391 l_group_rec.cost_of_removal,
392 l_group_rec.net_proceeds,
393 l_group_rec.cost_retired,
394 l_group_rec.reserve_retired,
395 l_group_rec.recapture_amount
396 FROM fa_mc_retirements ret,
397 fa_book_controls bc,
398 fa_fiscal_year fy,
399 fa_transaction_headers thg,
400 fa_transaction_headers thm
401 WHERE bc.book_type_code = p_book_type_code
402 AND fy.fiscal_year = l_info_rec.fiscal_year
403 AND fy.fiscal_year_name = bc.fiscal_year_name
404 AND thm.book_type_code = bc.book_type_code
405 AND thm.transaction_date_entered
406 BETWEEN fy.start_date and fy.end_date
407 AND thg.book_type_code = bc.book_type_code
408 AND thg.asset_id = l_group_rec.asset_id
409 AND thg.member_transaction_header_id = thm.transaction_header_id
410 AND ret.transaction_header_id_in = thm.transaction_header_id
411 AND ret.status <> 'DELETED'
412 AND ret.set_of_books_id = l_info_rec.set_of_books_id;
413 end if;
414 -- End MRC
415
416 if(H_MRCSOBTYPE <> 'R') then -- MRC
417 FOR rec_ret_adj IN ret_adj_cur
418 LOOP
419 IF rec_ret_adj.adj_type = 'PROCEEDS CLR' THEN
420 l_group_rec.proceeds_of_sale := l_group_rec.proceeds_of_sale + rec_ret_adj.adj_amt;
421 ELSE
422 l_group_rec.cost_of_removal := l_group_rec.cost_of_removal + rec_ret_adj.adj_amt;
423 END IF;
424
425 END LOOP;
426
427 -- MRC
428 else
429 FOR mc_rec_ret_adj IN mc_ret_adj_cur
430 LOOP
431 IF mc_rec_ret_adj.adj_type = 'PROCEEDS CLR' THEN
432 l_group_rec.proceeds_of_sale := l_group_rec.proceeds_of_sale + mc_rec_ret_adj.adj_amt;
433 ELSE
434 l_group_rec.cost_of_removal := l_group_rec.cost_of_removal + mc_rec_ret_adj.adj_amt;
435 END IF;
436
437 END LOOP;
438 end if;
439 -- End MRC
440
441
442 l_message := 'group loop (2)';
443
444 -- Get group level adjustments amount
445 -- Query transactions that occurred during the fiscal year,
446 -- includeing back dated transactions.
447 -- But exclude transanctions that happened during the
448 -- non-depreciated period, even if it's back dated one.
449
450 if(H_MRCSOBTYPE <> 'R') then -- MRC
451 SELECT NVL(SUM(DECODE(adj.debit_credit_flag,
452 'DR', adj.adjustment_amount,
453 'CR', -adj.adjustment_amount, 0)), 0),
454 NVL(SUM(DECODE(GREATEST(thg.transaction_date_entered,
455 fy.mid_year_date),
456 thg.transaction_date_entered,
457 DECODE(adj.debit_credit_flag,
458 'DR', adj.adjustment_amount,
459 'CR', -adj.adjustment_amount, 0),
460 0)), 0)
461 INTO l_group_adjustment_amount,
462 l_second_half_grp_adjustment
463 FROM fa_adjustments adj,
464 fa_book_controls bc,
465 fa_fiscal_year fy,
466 fa_transaction_headers thg
467 WHERE thg.asset_id = l_group_rec.asset_id
468 AND thg.book_type_code = p_book_type_code
469 AND thg.member_transaction_header_id IS NULL
470 AND thg.transaction_header_id = adj.transaction_header_id
471 AND adj.period_counter_created
472 BETWEEN l_info_rec.min_period_counter
473 and l_info_rec.max_period_counter
474 AND adj.adjustment_type = 'COST'
475 AND fy.fiscal_year = l_info_rec.fiscal_year
476 AND fy.fiscal_year_name = bc.fiscal_year_name
477 AND bc.book_type_code = p_book_type_code;
478 -- MRC
479 else
480 SELECT NVL(SUM(DECODE(adj.debit_credit_flag,
481 'DR', adj.adjustment_amount,
482 'CR', -adj.adjustment_amount, 0)), 0),
483 NVL(SUM(DECODE(GREATEST(thg.transaction_date_entered,
484 fy.mid_year_date),
485 thg.transaction_date_entered,
486 DECODE(adj.debit_credit_flag,
487 'DR', adj.adjustment_amount,
488 'CR', -adj.adjustment_amount, 0),
489 0)), 0)
490 INTO l_group_adjustment_amount,
491 l_second_half_grp_adjustment
492 FROM fa_mc_adjustments adj,
493 fa_book_controls bc,
494 fa_fiscal_year fy,
495 fa_transaction_headers thg
496 WHERE thg.asset_id = l_group_rec.asset_id
497 AND thg.book_type_code = p_book_type_code
498 AND thg.member_transaction_header_id IS NULL
499 AND thg.transaction_header_id = adj.transaction_header_id
500 AND adj.period_counter_created
501 BETWEEN l_info_rec.min_period_counter
502 and l_info_rec.max_period_counter
503 AND adj.adjustment_type = 'COST'
504 AND fy.fiscal_year = l_info_rec.fiscal_year
505 AND fy.fiscal_year_name = bc.fiscal_year_name
506 AND bc.book_type_code = p_book_type_code
507 AND adj.set_of_books_id = l_info_rec.set_of_books_id;
508 end if;
509 -- End MRC
510
511 l_message := 'group loop (3)';
512
513
514 -- Get member level addition, adjustment and retirement amount
515 get_trx_amount_sql(l_group_rec, l_info_rec, NULL, h_mrcsobtype, l_sql_stmt); -- MRC
516
517 OPEN l_amount_csr FOR l_sql_stmt;
518 FETCH l_amount_csr
519 INTO l_second_half_add_rec_cost,
520 l_second_half_mem_adjustment,
521 l_group_rec.second_half_addition,
522 l_group_rec.addition_amount,
523 l_group_rec.adjustment_amount;
524 CLOSE l_amount_csr;
525
526 l_message := 'group loop (4)';
527
528
529 -- Get group reclass amount
530 if(H_MRCSOBTYPE <> 'R') then -- MRC
531 /*Bug#9375920 Modified the cursor to fetch change in cost because of member reclass in/out */
532 SELECT NVL(SUM(bk.cost - bk_old.cost),0)
533 INTO l_group_reclass_cost_in_out
534 FROM fa_books bk,
535 fa_books bk_old,
536 fa_transaction_headers fth,
537 fa_book_controls bc,
538 fa_deprn_periods dp
539 WHERE bk.transaction_header_id_in = fth.transaction_header_id
540 AND bk_old.transaction_header_id_out = fth.transaction_header_id
541 AND fth.ASSET_ID = l_group_rec.asset_id
542 AND fth.TRANSACTION_KEY = 'GC'
543 AND bc.book_type_code = p_book_type_code
544 AND fth.book_type_code = p_book_type_code
545 AND bk.book_type_code = p_book_type_code
546 AND bk_old.book_type_code = p_book_type_code
547 AND bk.asset_id = fth.ASSET_ID
548 AND bk_old.asset_id = fth.ASSET_ID
549 AND dp.fiscal_year = l_info_rec.fiscal_year
550 AND dp.book_type_code = p_book_type_code
551 AND fth.date_effective BETWEEN dp.period_open_date
552 AND nvl(dp.period_close_date,sysdate);
553 /*Bug#9375920- Added to fetch change in reserve because of memebr reclass in/out */
554 SELECT NVL(SUM(DECODE(adj.adjustment_type || '-' || adj.debit_credit_flag,
555 'RESERVE-CR', adj.adjustment_amount,
556 'RESERVE-DR', -adj.adjustment_amount,0)), 0) reserve
557 INTO l_group_reclass_rsv_in_out
558 FROM fa_adjustments adj,
559 fa_transaction_headers thg
560 WHERE adj.asset_id = l_group_rec.asset_id
561 AND adj.book_type_code = p_book_type_code
562 AND thg.transaction_header_id = adj.transaction_header_id
563 AND thg.asset_id = l_group_rec.asset_id
564 AND thg.book_type_code = p_book_type_code
565 AND adj.period_counter_created BETWEEN l_info_rec.min_period_counter
566 AND l_info_rec.max_period_counter
567 AND adj.source_type_code = 'ADJUSTMENT'
568 AND thg.transaction_key = 'GC';
569 -- MRC
570 else
571 SELECT SUM(bk.cost - bk_old.cost)
572 INTO l_group_reclass_cost_in_out
573 FROM fa_mc_books bk,
574 fa_mc_books bk_old,
575 fa_transaction_headers fth,
576 fa_mc_book_controls bc,
577 fa_book_controls bc1,
578 fa_mc_deprn_periods dp
579 WHERE bk.transaction_header_id_in = fth.transaction_header_id
580 AND bk_old.transaction_header_id_out = fth.transaction_header_id
581 AND fth.ASSET_ID = l_group_rec.asset_id
582 AND fth.TRANSACTION_KEY = 'GC'
583 AND bc1.book_type_code = p_book_type_code
584 AND bc.book_type_code = bc1.book_type_code
585 AND fth.book_type_code = p_book_type_code
586 AND bk.book_type_code = p_book_type_code
587 AND bk_old.book_type_code = p_book_type_code
588 AND bk.asset_id = fth.ASSET_ID
589 AND bk_old.asset_id = fth.ASSET_ID
590 AND bk.set_of_books_id = l_info_rec.set_of_books_id
591 AND bk_old.set_of_books_id = l_info_rec.set_of_books_id
592 AND bc.set_of_books_id = l_info_rec.set_of_books_id
593 AND dp.fiscal_year = l_info_rec.fiscal_year
594 AND dp.book_type_code = p_book_type_code
595 AND dp.set_of_books_id = l_info_rec.set_of_books_id
596 AND fth.date_effective BETWEEN dp.period_open_date
597 AND nvl(dp.period_close_date,sysdate);
598
599 SELECT NVL(SUM(DECODE(adj.adjustment_type || '-' || adj.debit_credit_flag,
600 'RESERVE-CR', adj.adjustment_amount,
601 'RESERVE-DR', -adj.adjustment_amount,0)), 0) reserve
602 INTO l_group_reclass_rsv_in_out
603 FROM fa_mc_adjustments adj,
604 fa_transaction_headers thg
605 WHERE adj.asset_id = l_group_rec.asset_id
606 AND adj.book_type_code = p_book_type_code
607 AND thg.transaction_header_id = adj.transaction_header_id
608 AND thg.asset_id = l_group_rec.asset_id
609 AND thg.book_type_code = p_book_type_code
610 AND adj.period_counter_created BETWEEN l_info_rec.min_period_counter
611 AND l_info_rec.max_period_counter
612 AND adj.source_type_code = 'ADJUSTMENT'
613 AND adj.set_of_books_id = l_info_rec.set_of_books_id
614 AND thg.transaction_key = 'GC';
615 end if;
616 -- End MRC
617
618 l_message := 'group loop (4-2)';
619
620
621 ----------------------------------------------------------
622 -- Calculate and set each column
623 ----------------------------------------------------------
624 -- Convert life_year_month to number
625 l_group_rec.life_year_month :=
626 fnd_number.canonical_to_number(l_group_rec.life_year_month_string);
627
628 -- Addition during first/second half of the fiscal year
629 IF NVL(l_group_rec.rule_name, ' ') <> FA_RXGA_HALF_YEAR_RULE THEN
630 l_group_rec.first_half_addition := NULL;
631 l_group_rec.second_half_addition := NULL;
632 ELSE
633 l_group_rec.first_half_addition :=
634 l_group_rec.addition_amount - l_group_rec.second_half_addition;
635 END IF;
636
637 -- Adjustment amount
638 -- = group level COST trx + member level COST/CIP COST trx
639 -- - (member addition + member retirement) + group reclass
640 l_group_rec.adjustment_amount :=
641 l_group_adjustment_amount + l_group_rec.adjustment_amount
642 - (l_group_rec.addition_amount - l_group_rec.cost_retired)
643 + l_group_reclass_cost_in_out; /* Bug#9375920 */
644
645 -- Net proceeds
646 IF NVL(l_group_rec.exclude_proceeds_from_basis, 'N') = 'Y' THEN
647 -- Set net proceeds = 0 for class 10.1
648 l_group_rec.net_proceeds := 0;
649 END IF;
650
651
652 -- NBV before depreciation
653 l_group_rec.nbv_before_deprn
654 := l_group_rec.beginning_nbv + l_group_rec.addition_amount
655 + l_group_rec.adjustment_amount - l_group_rec.net_proceeds
656 - l_group_reclass_rsv_in_out; /* Bug#9375920 */
657
658
659 -- Depreciable basis adjustment / Reduced NBV
660 -- (only applicable for 50% rule)
661 IF NOT (NVL(l_group_rec.rule_name, ' ')
662 IN (FA_RXGA_POSITIVE_REDUCTION, FA_RXGA_HALF_YEAR_RULE)) THEN
663 l_group_rec.deprn_basis_adjustment := NULL;
664 l_group_rec.reduced_nbv := NULL;
665
666 ELSIF l_group_rec.max_period_counter < l_info_rec.min_period_counter THEN
667
668 -- Set zero if there was no depreciation during the fiscal year.
669 l_group_rec.deprn_basis_adjustment := 0;
670 l_group_rec.reduced_nbv := 0;
671
672 ELSE
673 -- for class 90 (CIP group)
674 -- If all the member assets are CIP, adjusted_cost is alyways 0.
675 IF l_group_rec.reduced_nbv = 0
676 AND NVL(l_group_rec.rule_name, ' ') = FA_RXGA_POSITIVE_REDUCTION THEN
677
678 SELECT COUNT(*)
679 INTO l_non_cip_num
680 FROM fa_books bk, fa_additions ad
681 WHERE bk.book_type_code = p_book_type_code
682 AND bk.group_asset_id = l_group_rec.asset_id
683 AND ad.asset_type <> 'CIP'
684 AND bk.asset_id = ad.asset_id;
685
686 IF NVL(l_non_cip_num, 0) = 0 THEN
687 l_group_rec.reduced_nbv := l_group_rec.nbv_before_deprn;
688 END IF;
689 END IF;
690
691 -- Reduced NBV (adjusted cost has already been set)
692 IF l_group_rec.deprn_basis_rule = fa_std_types.FAD_DBR_NBV
693 AND NVL(l_group_rec.exclude_salvage_value_flag, 'NO') = 'YES' THEN
694 l_group_rec.reduced_nbv :=
695 l_group_rec.reduced_nbv + l_group_rec.salvage_value;
696 END IF;
697
698 -- Depreciable basis adjustment
699 l_group_rec.deprn_basis_adjustment :=
700 l_group_rec.nbv_before_deprn - l_group_rec.reduced_nbv;
701 END IF;
702
703
704 -- Reduced/Regular/Annual depreciation amount
705 IF NVL(l_group_rec.rule_name, ' ') <> FA_RXGA_HALF_YEAR_RULE THEN
706
707 -- Set NULL if depreciable basis rule is not
708 -- Year End Balance with Half Year Rule.
709 l_group_rec.regular_deprn_amount := NULL;
710 l_group_rec.reduced_deprn_amount := NULL;
711
712 ELSIF l_group_rec.max_period_counter < l_info_rec.min_period_counter THEN
713 -- Set zero if there was no depreciation during the fiscal year.
714 l_group_rec.reduced_deprn_amount := 0;
715 l_group_rec.regular_deprn_amount := 0;
716 l_group_rec.annual_deprn_amount := 0;
717
718 ELSE
719 -- calculate reduced deprn amount, which assumed reduction rate
720 -- was applied to the entire NBV before deprn
721 l_all_reduced_deprn_amount :=
722 l_group_rec.nbv_before_deprn * (1 - l_group_rec.reduction_rate)
723 * l_group_rec.adjusted_rate;
724 IF NOT fa_utils_pkg.faxtru(
725 X_num => l_all_reduced_deprn_amount,
726 X_book_type_code => p_book_type_code,
727 X_set_of_books_id => l_info_rec.set_of_books_id,
728 p_log_level_rec => l_log_level_rec
729 ) THEN
730 raise main_err;
731 END IF;
732
733 IF g_print_debug THEN
734 fa_rx_util_pkg.debug('l_all_reduced_deprn_amount: '
735 || l_all_reduced_deprn_amount);
736 fa_rx_util_pkg.debug('annual_deprn_amount: '
737 || l_group_rec.annual_deprn_amount);
738 END IF;
739
740 IF l_group_rec.annual_deprn_amount = l_all_reduced_deprn_amount THEN
741 l_group_rec.reduced_deprn_amount := l_group_rec.annual_deprn_amount;
742 ELSE
743 l_group_rec.reduced_deprn_amount := (l_second_half_add_rec_cost
744 + l_second_half_grp_adjustment + l_second_half_mem_adjustment)
745 * l_group_rec.adjusted_rate * (1 - l_group_rec.reduction_rate);
746 IF NOT fa_utils_pkg.faxtru(
747 X_num => l_group_rec.reduced_deprn_amount,
748 X_book_type_code => p_book_type_code,
749 X_set_of_books_id => l_info_rec.set_of_books_id,
750 p_log_level_rec => l_log_level_rec
751 ) THEN
752 raise main_err;
753 END IF;
754 END IF;
755
756 -- Regular depreciation amount
757 l_group_rec.regular_deprn_amount :=
758 l_group_rec.annual_deprn_amount - l_group_rec.reduced_deprn_amount;
759 END IF;
760
761 -- Ending NBV
762 -- Bug #2873705
763 l_group_rec.ending_nbv := l_group_rec.cost
764 - l_group_rec.deprn_reserve
765 + l_group_rec.terminal_gain_loss_amount;
766
767 -- Terminal Loss
768 -- Bug #2876230 - set terminal loss only
769 IF l_group_rec.terminal_gain_loss_amount < 0 THEN
770 l_group_rec.terminal_gain_loss_amount
771 := l_group_rec.terminal_gain_loss_amount * -1;
772 ELSE
773 l_group_rec.terminal_gain_loss_amount := 0;
774 END IF;
775
776 l_message := 'group loop (5)';
777
778
779 ----------------------------------------------------------
780 -- Insert only group / Query member assets
781 ----------------------------------------------------------
782 IF NVL(p_drill_down, 'N') <> 'Y' THEN
783
784 -- Insert only group info into interface table
785 insert_data(l_info_rec, l_group_rec, l_member_rec);
786
787 l_message := 'group loop (6)';
788
789 ELSE
790 -- Query member assets that belong to the group
791 l_info_rec.member_query_mode := 'EXISTS';
792 query_member_assets(l_info_rec, l_group_rec, h_mrcsobtype); -- MRC
793
794 -- Query member assets that no longer belong to the group
795 l_info_rec.member_query_mode := 'NOT EXISTS';
796 query_member_assets(l_info_rec, l_group_rec, h_mrcsobtype); -- MRC
797
798 END IF; -- drill down y/n
799
800 END LOOP; -- group query loop
801 CLOSE l_group_csr;
802
803
804 l_info_rec.fiscal_year := l_info_rec.fiscal_year + 1;
805 END LOOP; -- fiscal year loop
806
807 IF g_print_debug THEN
808 fa_rx_util_pkg.debug('get_group_asset_info: '
809 || 'farx_ga.get_group_asset_info()-');
810 END IF;
811
812 EXCEPTION
813 WHEN OTHERS THEN
814 IF g_print_debug THEN
815 fa_rx_util_pkg.log(sqlcode);
816 fa_rx_util_pkg.log(sqlerrm);
817 fa_rx_util_pkg.log(l_message);
818 END IF;
819
820 IF sqlcode <> 0 THEN
821 fa_rx_conc_mesg_pkg.log(sqlerrm);
822 END IF;
823
824 IF l_group_csr%ISOPEN THEN
825 CLOSE l_group_csr;
826 END IF;
827 IF l_amount_csr%ISOPEN THEN
828 CLOSE l_amount_csr;
829 END IF;
830
831 x_retcode := 2;
832 IF g_print_debug THEN
833 fa_rx_util_pkg.debug('get_group_asset_info: '
834 || 'farx_ga.get_group_asset_info(EXCEPTION)-');
835 END IF;
836
837 END get_group_asset_info;
838
839
840 -------------------------------------------------------------------
841 --
842 -- Function: get_category_sql
843 -- This function returns select clause and where clause for each
844 -- category.
845 --
846 -------------------------------------------------------------------
847 PROCEDURE get_category_sql (
848 p_application_id IN NUMBER,
849 p_category_flex_structure IN NUMBER,
850 p_qualifier IN VARCHAR2,
851 p_category_low IN VARCHAR2,
852 p_category_high IN VARCHAR2,
853 x_select_stmt OUT NOCOPY VARCHAR2,
854 x_where_stmt OUT NOCOPY VARCHAR2)
855 IS
856 BEGIN
857 IF g_print_debug THEN
858 fa_rx_util_pkg.debug('get_category_sql: '
859 || 'p_application_id: ' || p_application_id);
860 fa_rx_util_pkg.debug('get_category_sql: ' || 'p_category_flex_structure: '
861 || p_category_flex_structure);
862 fa_rx_util_pkg.debug('get_category_sql: '
863 || 'p_qualifier: ' || p_qualifier);
864 fa_rx_util_pkg.debug('get_category_sql: '
865 || 'p_category_low: ' || p_category_low);
866 fa_rx_util_pkg.debug('get_category_sql: '
867 || 'p_category_high: ' || p_category_high);
868 END IF;
869
870
871 -- Create select clause for category
872 IF p_qualifier IS NULL THEN
873 x_select_stmt := 'null';
874 ELSE
875 BEGIN
876 x_select_stmt :=
877 fa_rx_flex_pkg.flex_sql(p_application_id, 'CAT#',
878 p_category_flex_structure, 'cat',
879 'SELECT', p_qualifier);
880 EXCEPTION
881 WHEN OTHERS THEN
882 x_select_stmt := 'null';
883 END;
884 END IF;
885
886
887 -- Create where clause
888 IF p_category_low = p_category_high THEN
889 x_where_stmt := ' AND ' ||
890 fa_rx_flex_pkg.flex_sql(
891 p_application_id,
892 'CAT#',
893 p_category_flex_structure,
894 'cat',
895 'WHERE',
896 p_qualifier,
897 '=',
898 p_category_low);
899
900 ELSIF p_category_low IS NOT NULL AND p_category_high IS NOT NULL THEN
901 x_where_stmt := ' AND ' ||
902 fa_rx_flex_pkg.flex_sql(
903 p_application_id,
904 'CAT#',
905 p_category_flex_structure,
906 'cat',
907 'WHERE',
908 p_qualifier,
909 'BETWEEN',
910 p_category_low,
911 p_category_high);
912
913 ELSIF p_category_low IS NOT NULL THEN
914 x_where_stmt := ' AND ' ||
915 fa_rx_flex_pkg.flex_sql(
916 p_application_id,
917 'CAT#',
918 p_category_flex_structure,
919 'cat',
920 'WHERE',
921 p_qualifier,
922 '>=',
923 p_category_low);
924
925 ELSIF p_category_high IS NOT NULL THEN
926 x_where_stmt := ' AND ' ||
927 fa_rx_flex_pkg.flex_sql(
928 p_application_id,
929 'CAT#',
930 p_category_flex_structure,
931 'cat',
932 'WHERE',
933 p_qualifier,
934 '<=',
935 p_category_high);
936 END IF;
937
938 EXCEPTION
939 WHEN OTHERS THEN
940 IF g_print_debug THEN
941 fa_rx_util_pkg.debug('get_category_sql: '
942 || 'farx_ga.get_category_sql(EXCEPTION)-');
943 END IF;
944 raise;
945 END get_category_sql;
946
947
948 -------------------------------------------------------------------
949 --
950 -- Function: get_from_sql_stmt
951 -- This function returns from clause for group query and member
952 -- query.
953 --
954 -------------------------------------------------------------------
955 PROCEDURE get_from_sql_stmt (
956 p_info_rec IN info_rec_type,
957 p_group_asset_id IN NUMBER,
958 p_mrcsobtype IN VARCHAR2 default NULL, -- MRC: SOB Type
959 x_sql_stmt OUT NOCOPY VARCHAR2)
960 IS
961 BEGIN
962
963 -- Subquery is to get fa_books and fa_deprn_summary,
964 -- which are used to calculate beginning NBV.
965 -- Subquery doesn't necessarily get the previous year info.
966 -- If there was no depreciation calculation in the previous year,
967 -- subquery gets the max period counter prior to the fiscal year.
968 if(P_MRCSOBTYPE <> 'R') then -- MRC
969 x_sql_stmt := ' FROM
970 fa_additions ad,
971 fa_books bk,
972 fa_categories_b cat,
973 fa_deprn_basis_rules br,
974 fa_methods met,
975 fa_deprn_periods dp,
976 fa_deprn_summary ds,
977 ( SELECT bk_pre.asset_id,
978 bk_pre.group_asset_id,
979 bk_pre.cost,
980 bk_pre.cip_cost,
981 ds_pre.deprn_reserve
982 FROM fa_books bk_pre,
983 fa_deprn_summary ds_pre,
984 fa_deprn_periods dp_pre,
985 fa_additions ad_pre
986 WHERE bk_pre.book_type_code = ''' || p_info_rec.book_type_code || '''
987 AND dp_pre.book_type_code = bk_pre.book_type_code
988 AND dp_pre.period_counter + 1 = ' || p_info_rec.min_period_counter || '
989 AND dp_pre.period_close_date BETWEEN bk_pre.date_effective
990 AND NVL(bk_pre.date_ineffective, dp_pre.period_close_date)
991 AND ds_pre.book_type_code = bk_pre.book_type_code
992 AND ds_pre.asset_id = bk_pre.asset_id
993 AND ds_pre.period_counter = (
994 SELECT MAX(ds3.period_counter)
995 FROM fa_deprn_summary ds3
996 WHERE ds_pre.book_type_code = ds3.book_type_code
997 AND ds_pre.asset_id = ds3.asset_id
998 AND ds3.period_counter < ' || p_info_rec.min_period_counter || '
999 )
1000 AND ad_pre.asset_id = bk_pre.asset_id ';
1001 -- MRC
1002 else
1003 x_sql_stmt := ' FROM
1004 fa_additions ad,
1005 fa_mc_books bk,
1006 fa_categories_b cat,
1007 fa_deprn_basis_rules br,
1008 fa_methods met,
1009 fa_mc_deprn_periods dp,
1010 fa_mc_deprn_summary ds,
1011 ( SELECT bk_pre.asset_id,
1012 bk_pre.group_asset_id,
1013 bk_pre.cost,
1014 bk_pre.cip_cost,
1015 ds_pre.deprn_reserve
1016 FROM fa_mc_books bk_pre,
1017 fa_mc_deprn_summary ds_pre,
1018 fa_mc_deprn_periods dp_pre,
1019 fa_additions ad_pre
1020 WHERE bk_pre.book_type_code = ''' || p_info_rec.book_type_code || '''
1021 AND dp_pre.book_type_code = bk_pre.book_type_code
1022 AND dp_pre.period_counter + 1 = ' || p_info_rec.min_period_counter || '
1023 AND dp_pre.period_close_date BETWEEN bk_pre.date_effective
1024 AND NVL(bk_pre.date_ineffective, dp_pre.period_close_date)
1025 AND ds_pre.book_type_code = bk_pre.book_type_code
1026 AND ds_pre.asset_id = bk_pre.asset_id
1027 AND ds_pre.period_counter = (
1028 SELECT MAX(ds3.period_counter)
1029 FROM fa_mc_deprn_summary ds3
1030 WHERE ds_pre.book_type_code = ds3.book_type_code
1031 AND ds_pre.asset_id = ds3.asset_id
1032 AND ds3.set_of_books_id = ' || p_info_rec.set_of_books_id || '
1033 AND ds3.period_counter < ' || p_info_rec.min_period_counter || '
1034 )
1035 AND ad_pre.asset_id = bk_pre.asset_id
1036 AND bk_pre.set_of_books_id = ' || p_info_rec.set_of_books_id || '
1037 AND ds_pre.set_of_books_id = ' || p_info_rec.set_of_books_id || '
1038 AND dp_pre.set_of_books_id = ' || p_info_rec.set_of_books_id ;
1039 end if;
1040 -- End MRC
1041
1042
1043 -- slightly different depends on whether it is for group or member
1044 IF p_group_asset_id IS NULL THEN
1045 x_sql_stmt := x_sql_stmt ||
1046 'AND ad_pre.asset_type = ''GROUP'') prev ';
1047 ELSE
1048 x_sql_stmt := x_sql_stmt ||
1049 'AND bk_pre.group_asset_id = ' || p_group_asset_id || ') prev ';
1050 END IF;
1051
1052 EXCEPTION
1053 WHEN OTHERS THEN
1054 IF g_print_debug THEN
1055 fa_rx_util_pkg.debug('get_from_sql_stmt: '
1056 || 'farx_ga.get_from_sql_stmt(EXCEPTION)-');
1057 END IF;
1058 raise;
1059 END get_from_sql_stmt;
1060
1061
1062 -------------------------------------------------------------------
1063 --
1064 -- Function: get_where_sql_stmt
1065 -- This function returns where clause for group query and member
1066 -- query.
1067 --
1068 -------------------------------------------------------------------
1069 PROCEDURE get_where_sql_stmt (
1070 p_info_rec IN info_rec_type,
1071 p_group_asset_id IN NUMBER,
1072 p_mrcsobtype IN VARCHAR2 default NULL, -- MRC: SOB Type
1073 x_sql_stmt OUT NOCOPY VARCHAR2)
1074 IS
1075 BEGIN
1076
1077 -- This where clause ges fa_books, which corresponds to
1078 -- the last depreciated period in the fiscal year.
1079
1080 -- This also gets fa_deprn_summary no matter what.
1081 -- There is at least deprn_source_code = 'BOOKS' row for every asset.
1082 -- fa_deprn_summary.period_counter will be the max period
1083 -- including the current fiscal year.
1084
1085 if(P_MRCSOBTYPE <> 'R') then -- MRC
1086 x_sql_stmt :=
1087 'WHERE bk.book_type_code = ''' || p_info_rec.book_type_code || '''
1088 AND bk.asset_id = ad.asset_id
1089 AND ad.asset_category_id = cat.category_id
1090 AND met.deprn_basis_rule_id = br.deprn_basis_rule_id (+)
1091 AND bk.deprn_method_code = met.method_code
1092 AND NVL(bk.life_in_months, 0) = NVL(met.life_in_months, 0)
1093 AND dp.book_type_code = bk.book_type_code
1094 AND dp.period_counter = ' || p_info_rec.max_period_counter || '
1095 AND ((dp.period_close_date IS NULL
1096 AND bk.date_ineffective IS NULL)
1097 OR (dp.period_close_date BETWEEN bk.date_effective
1098 AND NVL(bk.date_ineffective, dp.period_close_date)))
1099 AND ds.book_type_code = bk.book_type_code
1100 AND ds.asset_id = bk.asset_id
1101 AND ds.period_counter = (
1102 SELECT MAX(ds2.period_counter)
1103 FROM fa_deprn_summary ds2
1104 WHERE ds2.book_type_code = ds.book_type_code
1105 AND ds2.asset_id = ds.asset_id
1106 AND ds2.period_counter <= ' || p_info_rec.max_period_counter || ' )
1107 AND bk.asset_id = prev.asset_id (+)';
1108
1109 -- MRC
1110 else
1111 x_sql_stmt :=
1112 'WHERE bk.book_type_code = ''' || p_info_rec.book_type_code || '''
1113 AND bk.asset_id = ad.asset_id
1114 AND ad.asset_category_id = cat.category_id
1115 AND met.deprn_basis_rule_id = br.deprn_basis_rule_id (+)
1116 AND bk.deprn_method_code = met.method_code
1117 AND NVL(bk.life_in_months, 0) = NVL(met.life_in_months, 0)
1118 AND dp.book_type_code = bk.book_type_code
1119 AND dp.period_counter = ' || p_info_rec.max_period_counter || '
1120 AND ((dp.period_close_date IS NULL
1121 AND bk.date_ineffective IS NULL)
1122 OR (dp.period_close_date BETWEEN bk.date_effective
1123 AND NVL(bk.date_ineffective, dp.period_close_date)))
1124 AND ds.book_type_code = bk.book_type_code
1125 AND ds.asset_id = bk.asset_id
1126 AND ds.period_counter = (
1127 SELECT MAX(ds2.period_counter)
1128 FROM fa_mc_deprn_summary ds2
1129 WHERE ds2.book_type_code = ds.book_type_code
1130 AND ds2.asset_id = ds.asset_id
1131 AND ds2.set_of_books_id = ' || p_info_rec.set_of_books_id || '
1132 AND ds2.period_counter <= ' || p_info_rec.max_period_counter || ' )
1133 AND bk.asset_id = prev.asset_id (+)
1134 AND bk.set_of_books_id = ' || p_info_rec.set_of_books_id || '
1135 AND dp.set_of_books_id = ' || p_info_rec.set_of_books_id || '
1136 AND ds.set_of_books_id = ' || p_info_rec.set_of_books_id ;
1137 end if;
1138 -- End MRC
1139
1140
1141 -- slightly different depends on whether it is for group or member
1142 IF p_group_asset_id IS NULL THEN
1143 x_sql_stmt := x_sql_stmt ||
1144 ' AND ad.asset_type = ''GROUP''';
1145 ELSIF p_info_rec.member_query_mode = 'EXISTS' THEN
1146 x_sql_stmt := x_sql_stmt ||
1147 ' AND bk.group_asset_id = ' || p_group_asset_id ||
1148 ' AND bk.asset_id = amt.asset_id (+)
1149 AND bk.asset_id = ret.asset_id (+) ';
1150 ELSE
1151 x_sql_stmt := x_sql_stmt ||
1152 ' AND prev.group_asset_id = ' || p_group_asset_id ||
1153 ' AND NVL(bk.group_asset_id, -1) <> ' || p_group_asset_id ||
1154 ' AND bk.asset_id = amt.asset_id (+)
1155 AND bk.asset_id = ret.asset_id (+) ';
1156 END IF;
1157
1158 EXCEPTION
1159 WHEN OTHERS THEN
1160 IF g_print_debug THEN
1161 fa_rx_util_pkg.debug('get_where_sql_stmt: '
1162 || 'farx_ga.get_where_sql_stmt(EXCEPTION)-');
1163 END IF;
1164 raise;
1165 END get_where_sql_stmt;
1166
1167
1168 -------------------------------------------------------------------
1169 --
1170 -- Function: get_trx_amount_sql
1171 -- This function returns sql statement for addition, adjustment
1172 -- and retirement query.
1173 --
1174 -------------------------------------------------------------------
1175 PROCEDURE get_trx_amount_sql (
1176 p_group_rec IN group_rec_type,
1177 p_info_rec IN info_rec_type,
1178 p_group_asset_id IN NUMBER,
1179 p_mrcsobtype IN VARCHAR2 default NULL, -- MRC: SOB Type
1180 x_sql_stmt OUT NOCOPY VARCHAR2)
1181 IS
1182 BEGIN
1183
1184 -- If it's group, need to query the following amount
1185 -- to calculate reduced/regular depreciation amount.
1186 -- 1. recoverable cost of member asset added during the second
1187 -- half of the fiscal year
1188 -- 2. adjustment amount occurred during the second half of the
1189 -- fiscal year
1190
1191 IF p_group_asset_id IS NULL THEN
1192 x_sql_stmt := '
1193 SELECT NVL(SUM(DECODE(
1194 GREATEST(thm.transaction_date_entered, fy.mid_year_date),
1195 thm.transaction_date_entered,
1196 DECODE(adj.source_type_code || ''-'' || adj.adjustment_type
1197 || ''-'' || adj.debit_credit_flag,
1198 ''ADDITION-COST-DR'', ';
1199
1200 IF p_group_rec.deprn_basis_rule = fa_std_types.FAD_DBR_NBV
1201 AND NVL(p_group_rec.exclude_salvage_value_flag, 'NO') = 'YES' THEN
1202 x_sql_stmt := x_sql_stmt || '
1203 bkm.recoverable_cost + bkm.salvage_value,
1204 0), 0)), 0) second_half_add_rec_cost, ';
1205 ELSE
1206 x_sql_stmt := x_sql_stmt || '
1207 bkm.recoverable_cost,
1208 0), 0)), 0) second_half_add_rec_cost, ';
1209 END IF;
1210
1211 x_sql_stmt := x_sql_stmt || '
1212 NVL(SUM(DECODE(GREATEST(thm.transaction_date_entered, fy.mid_year_date),
1213 thm.transaction_date_entered, DECODE(adj.adjustment_type,
1214 ''COST'',
1215 DECODE(adj.source_type_code,
1216 ''ADDITION'', 0,
1217 ''RETIREMENT'', 0,
1218 DECODE(adj.debit_credit_flag,
1219 ''DR'', adj.adjustment_amount,
1220 ''CR'', -adj.adjustment_amount,
1221 0)),
1222 ''CIP COST'',
1223 DECODE(adj.source_type_code,
1224 ''ADDITION'', 0,
1225 ''RETIREMENT'', 0,
1226 DECODE(adj.debit_credit_flag,
1227 ''DR'', adj.adjustment_amount,
1228 ''CR'', -adj.adjustment_amount,
1229 0)), 0), 0)), 0)
1230 second_half_mem_adjustment, ';
1231
1232 ELSE
1233 x_sql_stmt := '(SELECT adj.asset_id, ';
1234 END IF;
1235
1236
1237 -- Query transactions that occurred during the fiscal year.
1238 -- (includeing back dated transactions)
1239 -- but exclude transanctions that happened during the open period
1240 -- (even if it's back dated one.)
1241 -- Note: cip adjustment is treated as addition (class 90)
1242
1243 if(P_MRCSOBTYPE <> 'R') then -- MRC
1244 x_sql_stmt := x_sql_stmt || '
1245 NVL(SUM(DECODE(GREATEST(thm.transaction_date_entered, fy.mid_year_date),
1246 thm.transaction_date_entered,
1247 DECODE(adj.source_type_code || ''-'' || adj.adjustment_type || ''-''
1248 || adj.debit_credit_flag,
1249 ''ADDITION-COST-DR'', adj.adjustment_amount,
1250 ''ADDITION-COST-CR'', -adj.adjustment_amount,
1251 ''CIP ADDITION-COST-DR'', adj.adjustment_amount,
1252 ''CIP ADDITION-COST-CR'', -adj.adjustment_amount,
1253 ''CIP ADJUSTMENT-COST-DR'', adj.adjustment_amount,
1254 0), 0)), 0)
1255 second_half_addition,
1256 NVL(SUM(DECODE(adj.source_type_code || ''-'' || adj.adjustment_type
1257 || ''-'' || adj.debit_credit_flag,
1258 ''ADDITION-COST-DR'', adj.adjustment_amount,
1259 ''ADDITION-COST-CR'', -adj.adjustment_amount,
1260 ''CIP ADDITION-CIP COST-DR'', adj.adjustment_amount,
1261 ''CIP ADDITION-CIP COST-CR'', -adj.adjustment_amount,
1262 ''CIP ADJUSTMENT-CIP COST-DR'', adj.adjustment_amount,
1263 0)), 0)
1264 addition_amount,
1265 NVL(SUM(DECODE(adj.adjustment_type || ''-'' || adj.debit_credit_flag,
1266 ''COST-DR'', adj.adjustment_amount,
1267 ''COST-CR'', -adj.adjustment_amount,
1268 ''CIP COST-DR'', adj.adjustment_amount,
1269 ''CIP COST-CR'', -adj.adjustment_amount, 0)), 0)
1270 adjustment_amount
1271 FROM fa_adjustments adj,
1272 fa_book_controls bc,
1273 fa_fiscal_year fy,
1274 fa_transaction_headers thm,
1275 fa_books bkm
1276 WHERE bkm.group_asset_id = ' || p_group_rec.asset_id || '
1277 AND adj.asset_id = bkm.asset_id
1278 AND bkm.transaction_header_id_in = thm.transaction_header_id
1279 AND adj.book_type_code = ''' || p_info_rec.book_type_code || '''
1280 AND adj.period_counter_created
1281 BETWEEN ' || p_info_rec.min_period_counter ||
1282 ' and ' || p_info_rec.max_period_counter || '
1283 AND adj.transaction_header_id = thm.transaction_header_id
1284 AND fy.fiscal_year = ' || p_info_rec.fiscal_year || '
1285 AND fy.fiscal_year_name = bc.fiscal_year_name
1286 AND bc.book_type_code = adj.book_type_code ';
1287 -- MRC
1288 else
1289 x_sql_stmt := x_sql_stmt || '
1290 NVL(SUM(DECODE(GREATEST(thm.transaction_date_entered, fy.mid_year_date),
1291 thm.transaction_date_entered,
1292 DECODE(adj.source_type_code || ''-'' || adj.adjustment_type || ''-''
1293 || adj.debit_credit_flag,
1294 ''ADDITION-COST-DR'', adj.adjustment_amount,
1295 ''ADDITION-COST-CR'', -adj.adjustment_amount,
1296 ''CIP ADDITION-COST-DR'', adj.adjustment_amount,
1297 ''CIP ADDITION-COST-CR'', -adj.adjustment_amount,
1298 ''CIP ADJUSTMENT-COST-DR'', adj.adjustment_amount,
1299 0), 0)), 0)
1300 second_half_addition,
1301 NVL(SUM(DECODE(adj.source_type_code || ''-'' || adj.adjustment_type
1302 || ''-'' || adj.debit_credit_flag,
1303 ''ADDITION-COST-DR'', adj.adjustment_amount,
1304 ''ADDITION-COST-CR'', -adj.adjustment_amount,
1305 ''CIP ADDITION-CIP COST-DR'', adj.adjustment_amount,
1306 ''CIP ADDITION-CIP COST-CR'', -adj.adjustment_amount,
1307 ''CIP ADJUSTMENT-CIP COST-DR'', adj.adjustment_amount,
1308 0)), 0)
1309 addition_amount,
1310 NVL(SUM(DECODE(adj.adjustment_type || ''-'' || adj.debit_credit_flag,
1311 ''COST-DR'', adj.adjustment_amount,
1312 ''COST-CR'', -adj.adjustment_amount,
1313 ''CIP COST-DR'', adj.adjustment_amount,
1314 ''CIP COST-CR'', -adj.adjustment_amount, 0)), 0)
1315 adjustment_amount
1316 FROM fa_mc_adjustments adj,
1317 fa_mc_book_controls bc,
1318 fa_book_controls bc1,
1319 fa_fiscal_year fy,
1320 fa_transaction_headers thm,
1321 fa_mc_books bkm
1322 WHERE bkm.group_asset_id = ' || p_group_rec.asset_id || '
1323 AND adj.asset_id = bkm.asset_id
1324 AND bkm.transaction_header_id_in = thm.transaction_header_id
1325 AND adj.book_type_code = ''' || p_info_rec.book_type_code || '''
1326 AND adj.period_counter_created
1327 BETWEEN ' || p_info_rec.min_period_counter ||
1328 ' and ' || p_info_rec.max_period_counter || '
1329 AND adj.transaction_header_id = thm.transaction_header_id
1330 AND fy.fiscal_year = ' || p_info_rec.fiscal_year || '
1331 AND fy.fiscal_year_name = bc1.fiscal_year_name
1332 AND bc.book_type_code = bc1.book_type_code
1333 AND bc.book_type_code = adj.book_type_code
1334 AND adj.set_of_books_id = ' || p_info_rec.set_of_books_id || '
1335 AND bc.set_of_books_id = ' || p_info_rec.set_of_books_id || '
1336 AND bkm.set_of_books_id = ' || p_info_rec.set_of_books_id ;
1337 end if;
1338 -- End MRC
1339
1340 IF p_group_asset_id IS NOT NULL THEN
1341 x_sql_stmt := x_sql_stmt || 'GROUP BY adj.asset_id) amt ';
1342 END IF;
1343
1344 EXCEPTION
1345 WHEN OTHERS THEN
1346 IF g_print_debug THEN
1347 fa_rx_util_pkg.debug('get_trx_amount_sql: '
1348 || 'farx_ga.get_trx_amount_sql(EXCEPTION)-');
1349 END IF;
1350 raise;
1351 END get_trx_amount_sql;
1352
1353
1354
1355 -------------------------------------------------------------------
1356 --
1357 -- Function: get_retirement_sql
1358 -- This function returns sql statement for retirement amount
1359 -- for member assets.
1360 --
1361 -------------------------------------------------------------------
1362 PROCEDURE get_retirement_sql (
1363 p_info_rec IN info_rec_type,
1364 p_group_asset_id IN NUMBER,
1365 p_mrcsobtype IN VARCHAR2 default NULL, -- MRC: SOB Type
1366 x_sql_stmt OUT NOCOPY VARCHAR2)
1367 IS
1368 BEGIN
1369 if(P_MRCSOBTYPE <> 'R') then -- MRC
1370 x_sql_stmt := '(SELECT ret.asset_id,
1371 NVL(SUM(ret.proceeds_of_sale), 0) proceeds_of_sale,
1372 NVL(SUM(ret.cost_of_removal), 0) cost_of_removal,
1373 NVL(SUM(ret.cost_retired), 0) cost_retired,
1374 NVL(SUM(ret.reserve_retired), 0) reserve_retired
1375 FROM fa_retirements ret,
1376 fa_book_controls bc,
1377 fa_fiscal_year fy,
1378 fa_transaction_headers thm,
1379 fa_books bkm
1380 WHERE bkm.group_asset_id = ' || p_group_asset_id || '
1381 AND bc.book_type_code = ''' || p_info_rec.book_type_code || '''
1382 AND fy.fiscal_year = ' || p_info_rec.fiscal_year || '
1383 AND fy.fiscal_year_name = bc.fiscal_year_name
1384 AND thm.book_type_code = bc.book_type_code
1385 AND bkm.book_type_code = bc.book_type_code
1386 AND thm.transaction_date_entered
1387 BETWEEN fy.start_date and fy.end_date
1388 AND ret.asset_id = thm.asset_id
1389 AND bkm.asset_id = thm.asset_id
1390 AND bkm.transaction_header_id_in = thm.transaction_header_id
1391 AND ret.transaction_header_id_in = thm.transaction_header_id
1392 AND ret.status <> ''DELETED''
1393 GROUP BY ret.asset_id) ret ';
1394
1395 -- MRC
1396 else
1397 x_sql_stmt := '(SELECT ret.asset_id,
1398 NVL(SUM(ret.proceeds_of_sale), 0) proceeds_of_sale,
1399 NVL(SUM(ret.cost_of_removal), 0) cost_of_removal,
1400 NVL(SUM(ret.cost_retired), 0) cost_retired,
1401 NVL(SUM(ret.reserve_retired), 0) reserve_retired
1402 FROM fa_mc_retirements ret,
1403 fa_mc_book_controls bc,
1404 fa_fiscal_year fy,
1405 fa_transaction_headers thm,
1406 fa_mc_books bkm
1407 WHERE bkm.group_asset_id = ' || p_group_asset_id || '
1408 AND bc.book_type_code = ''' || p_info_rec.book_type_code || '''
1409 AND fy.fiscal_year = ' || p_info_rec.fiscal_year || '
1410 AND fy.fiscal_year_name = bc.fiscal_year_name
1411 AND thm.book_type_code = bc.book_type_code
1412 AND bkm.book_type_code = bc.book_type_code
1413 AND thm.transaction_date_entered
1414 BETWEEN fy.start_date and fy.end_date
1415 AND ret.asset_id = thm.asset_id
1416 AND bkm.asset_id = thm.asset_id
1417 AND bkm.transaction_header_id_in = thm.transaction_header_id
1418 AND ret.transaction_header_id_in = thm.transaction_header_id
1419 AND ret.status <> ''DELETED''
1420 AND ret.set_of_books_id = ' || p_info_rec.set_of_books_id || '
1421 AND bc.set_of_books_id = ' || p_info_rec.set_of_books_id || '
1422 AND bkm.set_of_books_id = ' || p_info_rec.set_of_books_id || '
1423 GROUP BY ret.asset_id) ret ';
1424 end if;
1425 -- End MRC
1426
1427 EXCEPTION
1428 WHEN OTHERS THEN
1429 IF g_print_debug THEN
1430 fa_rx_util_pkg.debug('get_retirement_sql: '
1431 || 'farx_ga.get_retirement_sql(EXCEPTION)-');
1432 END IF;
1433 raise;
1434 END get_retirement_sql;
1435
1436
1437 -------------------------------------------------------------------
1438 --
1439 -- Function: insert_data
1440 -- This function inserts data into fa_group_rep_itf.
1441 --
1442 -------------------------------------------------------------------
1443 PROCEDURE insert_data (
1444 p_info_rec IN info_rec_type,
1445 p_group_rec IN group_rec_type,
1446 p_member_rec IN group_rec_type)
1447 IS
1448 BEGIN
1449 INSERT INTO fa_group_rep_itf (
1450 request_id, created_by, creation_date,
1451 last_updated_by, last_update_date, last_update_login,
1452 organization_name, functional_currency_code,
1453 set_of_books_id, book_type_code, deprn_calendar, fiscal_year,
1454 grp_asset_number, grp_description, grp_asset_type,
1455 grp_major_category, grp_minor_category, grp_other_category,
1456 grp_date_placed_in_service, grp_deprn_method_code,
1457 grp_rule_name, grp_tracking_method,
1458 grp_adjusted_rate, grp_life_year_month,
1459 grp_cost, grp_salvage_value,
1460 grp_adjusted_recoverable_cost, grp_beginning_nbv,
1461 grp_first_half_addition, grp_second_half_addition,
1462 grp_addition_amount, grp_adjustment_amount,
1463 grp_net_proceeds, grp_proceeds_of_sale, grp_cost_of_removal,
1464 grp_cost_retired, grp_reserve_retired,
1465 grp_recapture_amount, grp_terminal_gain_loss_amount,
1466 grp_nbv_before_deprn, grp_deprn_basis_adjustment,
1467 grp_reduced_nbv,
1468 grp_regular_deprn_amount, grp_reduced_deprn_amount,
1469 grp_annual_deprn_amount, grp_deprn_reserve, grp_ending_nbv,
1470 mem_asset_number, mem_description, mem_asset_type,
1471 mem_major_category, mem_minor_category, mem_other_category,
1472 mem_date_placed_in_service, mem_deprn_method_code,
1473 mem_rule_name, mem_adjusted_rate, mem_life_year_month,
1474 mem_cost, mem_salvage_value,
1475 mem_adjusted_recoverable_cost, mem_beginning_nbv,
1476 mem_first_half_addition, mem_second_half_addition,
1477 mem_addition_amount, mem_adjustment_amount,
1478 mem_net_proceeds, mem_proceeds_of_sale, mem_cost_of_removal,
1479 mem_cost_retired, mem_reserve_retired,
1480 mem_nbv_before_deprn, mem_deprn_basis_adjustment,
1481 mem_reduced_nbv,
1482 mem_annual_deprn_amount, mem_deprn_reserve, mem_ending_nbv,
1483 mem_status
1484 ) VALUES (
1485 p_info_rec.request_id, p_info_rec.user_id, sysdate,
1486 p_info_rec.user_id, sysdate, p_info_rec.user_id,
1487 p_info_rec.organization_name, p_info_rec.functional_currency_code,
1488 p_info_rec.set_of_books_id, p_info_rec.book_type_code,
1489 p_info_rec.deprn_calendar, p_info_rec.fiscal_year,
1490 p_group_rec.asset_number,
1491 p_group_rec.description,
1492 p_group_rec.asset_type,
1493 p_group_rec.major_category,
1494 p_group_rec.minor_category,
1495 p_group_rec.other_category,
1496 p_group_rec.date_placed_in_service,
1497 p_group_rec.deprn_method_code,
1498 p_group_rec.rule_name,
1499 p_group_rec.tracking_method,
1500 p_group_rec.adjusted_rate,
1501 p_group_rec.life_year_month,
1502 p_group_rec.cost,
1503 p_group_rec.salvage_value,
1504 p_group_rec.adjusted_recoverable_cost,
1505 p_group_rec.beginning_nbv,
1506 p_group_rec.first_half_addition,
1507 p_group_rec.second_half_addition,
1508 p_group_rec.addition_amount,
1509 p_group_rec.adjustment_amount,
1510 p_group_rec.net_proceeds,
1511 p_group_rec.proceeds_of_sale,
1512 p_group_rec.cost_of_removal,
1513 p_group_rec.cost_retired,
1514 p_group_rec.reserve_retired,
1515 p_group_rec.recapture_amount,
1516 p_group_rec.terminal_gain_loss_amount,
1517 p_group_rec.nbv_before_deprn,
1518 p_group_rec.deprn_basis_adjustment,
1519 p_group_rec.reduced_nbv,
1520 p_group_rec.regular_deprn_amount,
1521 p_group_rec.reduced_deprn_amount,
1522 p_group_rec.annual_deprn_amount,
1523 p_group_rec.deprn_reserve,
1524 p_group_rec.ending_nbv,
1525 p_member_rec.asset_number,
1526 p_member_rec.description,
1527 p_member_rec.asset_type,
1528 p_member_rec.major_category,
1529 p_member_rec.minor_category,
1530 p_member_rec.other_category,
1531 p_member_rec.date_placed_in_service,
1532 p_member_rec.deprn_method_code,
1533 p_member_rec.rule_name,
1534 p_member_rec.adjusted_rate,
1535 p_member_rec.life_year_month,
1536 p_member_rec.cost,
1537 p_member_rec.salvage_value,
1538 p_member_rec.adjusted_recoverable_cost,
1539 p_member_rec.beginning_nbv,
1540 p_member_rec.first_half_addition,
1541 p_member_rec.second_half_addition,
1542 p_member_rec.addition_amount,
1543 p_member_rec.adjustment_amount,
1544 p_member_rec.net_proceeds,
1545 p_member_rec.proceeds_of_sale,
1546 p_member_rec.cost_of_removal,
1547 p_member_rec.cost_retired,
1548 p_member_rec.reserve_retired,
1549 p_member_rec.nbv_before_deprn,
1550 p_member_rec.deprn_basis_adjustment,
1551 p_member_rec.reduced_nbv,
1552 p_member_rec.annual_deprn_amount,
1553 p_member_rec.deprn_reserve,
1554 p_member_rec.ending_nbv,
1555 p_member_rec.status);
1556
1557 EXCEPTION
1558 WHEN OTHERS THEN
1559 IF g_print_debug THEN
1560 fa_rx_util_pkg.debug('insert_data: '
1561 || 'farx_ga.insert_data(EXCEPTION)-');
1562 END IF;
1563 raise;
1564 END insert_data;
1565
1566
1567 -------------------------------------------------------------------
1568 --
1569 -- Function: query_member_assets
1570 -- This function queries member assets.
1571 --
1572 -------------------------------------------------------------------
1573 PROCEDURE query_member_assets (
1574 p_info_rec IN info_rec_type,
1575 p_group_rec IN group_rec_type,
1576 p_mrcsobtype IN VARCHAR2 default NULL) -- MRC: SOB Type
1577 IS
1578 l_message VARCHAR2(30);
1579 l_member_sql_stmt VARCHAR2(10000);
1580 l_sql_stmt VARCHAR2(5000);
1581
1582 l_group_reclass_in NUMBER;
1583 l_group_reclass_out NUMBER;
1584
1585 TYPE group_csrtype IS REF CURSOR;
1586 l_member_csr group_csrtype;
1587 l_member_rec group_rec_type;
1588 BEGIN
1589 ---------------------------------------------------
1590 -- <Member query loop>
1591 -- Query member assets belong to the group
1592 ---------------------------------------------------
1593 l_message := 'member loop (1)';
1594
1595 -- Create query for member asset
1596 -- Don't use asset_type to identify cost or cip_cost because
1597 -- you cannot get right amount once the asset is capitalized
1598
1599 l_member_sql_stmt :=
1600 'SELECT
1601 ad.asset_number,
1602 ad.description,
1603 ad.asset_type, '
1604 || p_info_rec.major_cat_select_stmt || ','
1605 || p_info_rec.minor_cat_select_stmt || ','
1606 || p_info_rec.other_cat_select_stmt || ',
1607 bk.date_placed_in_service,
1608 bk.deprn_method_code,
1609 br.rule_name,
1610 NULL,
1611 bk.adjusted_rate,
1612 NULL,
1613 NVL(bk.cost, 0) + NVL(bk.cip_cost, 0),
1614 NVL(bk.salvage_value, 0),
1615 NVL(bk.adjusted_recoverable_cost, 0),
1616 NVL(prev.cost, 0) + NVL(prev.cip_cost, 0) - NVL(prev.deprn_reserve, 0),
1617 NULL,
1618 NVL(amt.second_half_addition, 0),
1619 NVL(amt.addition_amount, 0),
1620 NVL(amt.adjustment_amount, 0),
1621 NULL,
1622 NVL(ret.proceeds_of_sale, 0),
1623 NVL(ret.cost_of_removal, 0),
1624 NVL(ret.cost_retired, 0),
1625 NVL(ret.reserve_retired, 0),
1626 NULL, NULL, NULL, NULL,
1627 NVL(ds.adjusted_cost, 0),
1628 NULL, NULL,
1629 NVL(ds.ytd_deprn, 0),
1630 NVL(ds.deprn_reserve, 0),
1631 NULL, NULL,
1632 ad.asset_id,
1633 prev.group_asset_id,
1634 DECODE(bk.life_in_months, NULL, NULL,
1635 TO_CHAR(FLOOR(bk.life_in_months / 12)) || ''.'' ||
1636 TO_CHAR(MOD(bk.life_in_months, 12))),
1637 met.deprn_basis_rule,
1638 met.exclude_salvage_value_flag,
1639 NULL, NULL, NULL, NULL,
1640 bk.period_counter_fully_retired,
1641 bk.period_counter_fully_reserved,
1642 ds.period_counter ';
1643
1644 l_message := 'member loop (2)';
1645
1646 -- Add from clause
1647 get_from_sql_stmt(p_info_rec, p_group_rec.asset_id, p_mrcsobtype, l_sql_stmt); -- MRC
1648 l_member_sql_stmt := l_member_sql_stmt || l_sql_stmt || ', ';
1649
1650 get_trx_amount_sql(p_group_rec, p_info_rec,
1651 p_group_rec.asset_id,
1652 p_mrcsobtype, -- MRC
1653 l_sql_stmt);
1654 l_member_sql_stmt := l_member_sql_stmt || l_sql_stmt || ', ';
1655
1656 get_retirement_sql(p_info_rec, p_group_rec.asset_id, p_mrcsobtype, l_sql_stmt); -- MRC
1657 l_member_sql_stmt := l_member_sql_stmt || l_sql_stmt;
1658
1659 -- Add where clause
1660 get_where_sql_stmt(p_info_rec, p_group_rec.asset_id, p_mrcsobtype, l_sql_stmt); -- MRC
1661 l_member_sql_stmt := l_member_sql_stmt || l_sql_stmt;
1662
1663 -- Exclude assets which became fully retired before this FY
1664 -- (adjusted cost > 0 is for Canada's class 13)
1665 IF NVL(p_group_rec.recognize_gain_loss, 'NO') = 'YES' THEN
1666 l_member_sql_stmt := l_member_sql_stmt
1667 || ' AND NVL(bk.period_counter_fully_retired,'
1668 || p_info_rec.min_period_counter
1669 || ') >= ' || p_info_rec.min_period_counter
1670 || ' AND bk.adjusted_cost > 0 ';
1671 END IF;
1672
1673 l_message := 'member loop (3)';
1674
1675
1676 -- Member query loop start
1677 OPEN l_member_csr FOR l_member_sql_stmt;
1678 LOOP
1679 FETCH l_member_csr INTO l_member_rec;
1680 EXIT WHEN l_member_csr%NOTFOUND;
1681
1682 l_message := 'member loop (4)';
1683
1684 ---------------------------------------------------
1685 -- query group reclass amounts
1686 ---------------------------------------------------
1687 l_group_reclass_in := 0;
1688 l_group_reclass_out := 0;
1689
1690 -- Asset that no longer belongs to the group
1691 IF p_info_rec.member_query_mode = 'NOT EXISTS' THEN
1692 l_message := 'member loop (4-1)';
1693
1694 BEGIN
1695 if(P_MRCSOBTYPE <> 'R') then -- MRC
1696 SELECT NVL(bkm.cost, 0) - NVL(adj.adjustment_amount, 0)
1697 INTO l_group_reclass_out
1698 FROM fa_adjustments adj,
1699 fa_transaction_headers thg,
1700 fa_books bkm
1701 WHERE adj.asset_id = p_group_rec.asset_id
1702 AND adj.book_type_code = p_info_rec.book_type_code
1703 AND thg.transaction_header_id = adj.transaction_header_id
1704 AND adj.period_counter_created
1705 BETWEEN p_info_rec.min_period_counter
1706 and p_info_rec.max_period_counter
1707 AND adj.source_type_code = 'ADJUSTMENT'
1708 AND adj.adjustment_type = 'RESERVE'
1709 AND thg.member_transaction_header_id = bkm.transaction_header_id_in
1710 AND NVL(bkm.group_asset_id, -1) <> p_group_rec.asset_id
1711 AND bkm.asset_id = l_member_rec.asset_id;
1712 -- MRC
1713 else
1714 SELECT NVL(bkm.cost, 0) - NVL(adj.adjustment_amount, 0)
1715 INTO l_group_reclass_out
1716 FROM fa_mc_adjustments adj,
1717 fa_transaction_headers thg,
1718 fa_mc_books bkm
1719 WHERE adj.asset_id = p_group_rec.asset_id
1720 AND adj.book_type_code = p_info_rec.book_type_code
1721 AND thg.transaction_header_id = adj.transaction_header_id
1722 AND adj.period_counter_created
1723 BETWEEN p_info_rec.min_period_counter
1724 and p_info_rec.max_period_counter
1725 AND adj.source_type_code = 'ADJUSTMENT'
1726 AND adj.adjustment_type = 'RESERVE'
1727 AND thg.member_transaction_header_id = bkm.transaction_header_id_in
1728 AND NVL(bkm.group_asset_id, -1) <> p_group_rec.asset_id
1729 AND bkm.asset_id = l_member_rec.asset_id
1730 AND adj.set_of_books_id = p_info_rec.set_of_books_id
1731 AND bkm.set_of_books_id = p_info_rec.set_of_books_id;
1732 end if;
1733 -- End MRC
1734
1735 EXCEPTION
1736 WHEN OTHERS THEN
1737 null;
1738 END;
1739 l_message := 'member loop (4-2)';
1740
1741 l_member_rec.cost := 0;
1742 l_member_rec.salvage_value := 0;
1743 l_member_rec.adjusted_recoverable_cost := 0;
1744 l_member_rec.reduced_nbv := 0;
1745 l_member_rec.annual_deprn_amount := 0;
1746 l_member_rec.deprn_reserve := 0;
1747
1748 -- standalone/other group -> this group
1749 ELSIF l_member_rec.addition_amount = 0
1750 AND NVL(l_member_rec.pre_group_asset_id, -1) <> p_group_rec.asset_id THEN
1751 l_message := 'member loop (4-3)';
1752
1753 BEGIN
1754 if(P_MRCSOBTYPE <> 'R') then -- MRC
1755 SELECT NVL(bkm.cost, 0) - NVL(adj.adjustment_amount, 0)
1756 INTO l_group_reclass_in
1757 FROM fa_adjustments adj,
1758 fa_transaction_headers thg,
1759 fa_books bkm
1760 WHERE adj.asset_id = p_group_rec.asset_id
1761 AND adj.book_type_code = p_info_rec.book_type_code
1762 AND thg.transaction_header_id = adj.transaction_header_id
1763 AND adj.period_counter_created
1764 BETWEEN p_info_rec.min_period_counter
1765 and p_info_rec.max_period_counter
1766 AND adj.source_type_code = 'ADJUSTMENT'
1767 AND adj.adjustment_type = 'RESERVE'
1768 AND thg.member_transaction_header_id = bkm.transaction_header_id_in
1769 AND NVL(bkm.group_asset_id, -1) = p_group_rec.asset_id;
1770 -- MRC
1771 else
1772 SELECT NVL(bkm.cost, 0) - NVL(adj.adjustment_amount, 0)
1773 INTO l_group_reclass_in
1774 FROM fa_mc_adjustments adj,
1775 fa_transaction_headers thg,
1776 fa_mc_books bkm
1777 WHERE adj.asset_id = p_group_rec.asset_id
1778 AND adj.book_type_code = p_info_rec.book_type_code
1779 AND thg.transaction_header_id = adj.transaction_header_id
1780 AND adj.period_counter_created
1781 BETWEEN p_info_rec.min_period_counter
1782 and p_info_rec.max_period_counter
1783 AND adj.source_type_code = 'ADJUSTMENT'
1784 AND adj.adjustment_type = 'RESERVE'
1785 AND thg.member_transaction_header_id = bkm.transaction_header_id_in
1786 AND NVL(bkm.group_asset_id, -1) = p_group_rec.asset_id
1787 AND adj.set_of_books_id = p_info_rec.set_of_books_id
1788 AND bkm.set_of_books_id = p_info_rec.set_of_books_id;
1789 end if;
1790 -- End MRC
1791
1792 EXCEPTION
1793 WHEN OTHERS THEN
1794 null;
1795 END;
1796 l_message := 'member loop (4-4)';
1797 END IF;
1798
1799
1800 ---------------------------------------------------
1801 -- Calculate and set each column
1802 ---------------------------------------------------
1803 -- Convert life_year_month to number
1804 l_member_rec.life_year_month :=
1805 fnd_number.canonical_to_number(l_member_rec.life_year_month_string);
1806
1807 -- Addition during first/second half of the fiscal year
1808 -- Note: checking group's depreciable basis rule.
1809 IF NVL(p_group_rec.rule_name, ' ') <> FA_RXGA_HALF_YEAR_RULE THEN
1810 l_member_rec.first_half_addition := NULL;
1811 l_member_rec.second_half_addition := NULL;
1812 ELSE
1813 l_member_rec.first_half_addition :=
1814 l_member_rec.addition_amount - l_member_rec.second_half_addition;
1815 END IF;
1816
1817 -- Adjustment amount
1818 -- = member level COST/CIP COST transactions
1819 -- - (member additions + member retirement)
1820 -- + group reclass amounts
1821 l_member_rec.adjustment_amount := l_member_rec.adjustment_amount
1822 - (l_member_rec.addition_amount - l_member_rec.cost_retired)
1823 + (l_group_reclass_in - l_group_reclass_out);
1824
1825 -- Net proceeds
1826 IF NVL(p_group_rec.exclude_proceeds_from_basis, 'N') = 'Y' THEN
1827 -- Set proceeds = 0 for class 10.1
1828 l_member_rec.net_proceeds := 0;
1829 ELSE
1830 l_member_rec.net_proceeds :=
1831 l_member_rec.proceeds_of_sale - l_member_rec.cost_of_removal;
1832 END IF;
1833
1834
1835 IF p_group_rec.tracking_method IS NULL then
1836
1837 -- Set NULL to depreciation amount related columns
1838 -- when member tracking is off
1839 l_member_rec.beginning_nbv := NULL;
1840 l_member_rec.nbv_before_deprn := NULL;
1841 l_member_rec.deprn_basis_adjustment := NULL;
1842 l_member_rec.reduced_nbv := NULL;
1843 l_member_rec.annual_deprn_amount := NULL;
1844 l_member_rec.deprn_reserve := NULL;
1845 l_member_rec.ending_nbv := NULL;
1846 l_member_rec.deprn_method_code := NULL;
1847 l_member_rec.rule_name := NULL;
1848 l_member_rec.adjusted_rate := NULL;
1849 l_member_rec.life_year_month := NULL;
1850
1851 ELSE
1852 -- Set group's method if tracking is not calculated by member method
1853 IF NOT (NVL(p_group_rec.tracking_method, '') = 'CALCULATE'
1854 AND NVL(p_group_rec.depreciation_option, '') = 'MEMBER') THEN
1855 l_member_rec.deprn_method_code := p_group_rec.deprn_method_code;
1856 l_member_rec.rule_name := p_group_rec.rule_name;
1857 l_member_rec.adjusted_rate := p_group_rec.adjusted_rate;
1858 l_member_rec.life_year_month := p_group_rec.life_year_month;
1859 l_member_rec.deprn_basis_rule := p_group_rec.deprn_basis_rule;
1860 l_member_rec.exclude_salvage_value_flag := p_group_rec.exclude_salvage_value_flag;
1861 END IF;
1862
1863 -- Group reclass is treated like an addition to the group
1864 IF NVL(l_member_rec.pre_group_asset_id, 0) <> p_group_rec.asset_id THEN
1865 l_member_rec.beginning_nbv := 0;
1866 END IF;
1867
1868 -- NBV before depreciation
1869 l_member_rec.nbv_before_deprn
1870 := l_member_rec.beginning_nbv + l_member_rec.addition_amount
1871 + l_member_rec.adjustment_amount - l_member_rec.net_proceeds;
1872
1873 -- Annual depreciation amount
1874 -- (Set zero if there was no depreciation during the fiscal year)
1875 IF l_member_rec.max_period_counter < p_info_rec.min_period_counter THEN
1876 l_member_rec.annual_deprn_amount := 0;
1877 END IF;
1878
1879
1880 -- Reduced NBV / Deprn basis adjustment
1881 -- (only applicable for 50% rule)
1882 IF NOT (NVL(p_group_rec.rule_name, ' ')
1883 IN (FA_RXGA_POSITIVE_REDUCTION, FA_RXGA_HALF_YEAR_RULE)) THEN
1884 l_member_rec.deprn_basis_adjustment := NULL;
1885 l_member_rec.reduced_nbv := NULL;
1886
1887 ELSIF l_member_rec.max_period_counter < p_info_rec.min_period_counter THEN
1888 -- Set zero if there was no depreciation during the fiscal year
1889 l_member_rec.deprn_basis_adjustment := 0;
1890 l_member_rec.reduced_nbv := 0;
1891
1892 ELSE
1893 -- Reduced NBV (adjusted cost has already been set)
1894 IF l_member_rec.deprn_basis_rule = fa_std_types.FAD_DBR_NBV
1895 AND NVL(l_member_rec.exclude_salvage_value_flag, 'NO') = 'YES' THEN
1896 l_member_rec.reduced_nbv :=
1897 l_member_rec.reduced_nbv + l_member_rec.salvage_value;
1898 END IF;
1899
1900 -- Depreciable basis adjustment
1901 l_member_rec.deprn_basis_adjustment :=
1902 l_member_rec.nbv_before_deprn - l_member_rec.reduced_nbv;
1903 END IF;
1904
1905 -- Endign NBV
1906 l_member_rec.ending_nbv := l_member_rec.cost - l_member_rec.deprn_reserve;
1907
1908 END IF;
1909
1910 -- status
1911 -- bug #2846290
1912 IF NVL(l_member_rec.period_counter_fully_retired,
1913 p_info_rec.max_period_counter + 1) <= p_info_rec.max_period_counter THEN
1914 l_member_rec.status := 'FULLY RETIRED';
1915 ELSIF NVL(l_member_rec.period_counter_fully_reserved,
1916 p_info_rec.max_period_counter + 1) <= p_info_rec.max_period_counter THEN
1917 l_member_rec.status := 'FULLY RESERVED';
1918 END IF;
1919
1920 l_message := 'member loop (5)';
1921
1922
1923 -- Insert into interface table
1924 insert_data(p_info_rec, p_group_rec, l_member_rec);
1925
1926 l_message := 'member loop (6)';
1927
1928 END LOOP; -- member query loop
1929 CLOSE l_member_csr;
1930
1931 EXCEPTION
1932 WHEN OTHERS THEN
1933 IF g_print_debug THEN
1934 fa_rx_util_pkg.log(sqlcode);
1935 fa_rx_util_pkg.log(sqlerrm);
1936 fa_rx_util_pkg.log(l_message);
1937 fa_rx_util_pkg.debug('query_member_assets: '
1938 || 'farx_ga.query_member_assets(EXCEPTION)-');
1939 END IF;
1940
1941 IF sqlcode <> 0 THEN
1942 fa_rx_conc_mesg_pkg.log(sqlerrm);
1943 END IF;
1944
1945 fnd_message.set_name('OFA', l_message);
1946 IF l_message = 'FA_SHARED_INSERT_FAIL' THEN
1947 fnd_message.set_token('TABLE', 'FA_GROUP_REP_ITF');
1948 END IF;
1949 fa_rx_conc_mesg_pkg.log(fnd_message.get);
1950
1951 IF l_member_csr%ISOPEN THEN
1952 CLOSE l_member_csr;
1953 END IF;
1954
1955 raise;
1956 END query_member_assets;
1957
1958
1959 END FA_RX_GROUP;