[Home] [Help]
PACKAGE BODY: APPS.FA_QUERY_BALANCES_PKG
Source
1 PACKAGE BODY FA_QUERY_BALANCES_PKG as
2 /* $Header: faxqbalb.pls 120.15 2011/03/08 10:36:18 gigupta ship $*/
3
4
5 -- Call this procedure from a Forms 4.5 client, or any PLSQL below
6 -- version 2.0. This just loads the dpr_row struct to pass to
7 -- QUERY_BALANCES_INT, and unpacks the results.
8
9 -- Can call in several "RUN_MODEs":
10 -- STANDARD: Query detail/summary in given period from CORPORATE book.
11 -- ADJUSTED: Query detail/summary in given period from TAX book.
12 -- For both STANDARD/ADJUSTED: Returns period-closing balances if period
13 -- is closed. If current period, returns last period's closing balances
14 -- plus any adjustments from this period.
15 -- DEPRN: Query everything except deprn balances... return these as
16 -- zero so as not to interfere with deprn program which calls this.
17 -- Also query PRIOR period's deprn row to get balances for
18 -- depreciation program.
19 -- TRANSACTION: Query detail/summary balances of asset in given book
20 -- (either CORP or TAX) right after the given transaction is applied.
21 -- Don't need to indicate which period or book class. Also, trx_id need
22 -- not be for this asset; it need only exist in the system.
23
24
25 -- BUG# 1823498 MRC changes
26 -- transfers and reclasses will be calling this directly
27 -- for each reporting book as well as primary so each function will
28 -- determine whether to select from the corp or mc tables based on the
29 -- value of the GL SOB profile
30 -- -- bridgway 06/20/01
31
32
33 PROCEDURE QUERY_BALANCES
34 (X_ASSET_ID NUMBER,
35 X_BOOK VARCHAR2,
36 X_PERIOD_CTR NUMBER DEFAULT 0,
37 X_DIST_ID NUMBER DEFAULT 0,
38 X_RUN_MODE VARCHAR2 DEFAULT 'STANDARD',
39 X_COST OUT NOCOPY NUMBER,
40 X_DEPRN_RSV OUT NOCOPY NUMBER,
41 X_REVAL_RSV OUT NOCOPY NUMBER,
42 X_YTD_DEPRN OUT NOCOPY NUMBER,
43 X_YTD_REVAL_EXP OUT NOCOPY NUMBER,
44 X_REVAL_DEPRN_EXP OUT NOCOPY NUMBER,
45 X_DEPRN_EXP OUT NOCOPY NUMBER,
46 X_REVAL_AMO OUT NOCOPY NUMBER,
47 X_PROD OUT NOCOPY NUMBER,
48 X_YTD_PROD OUT NOCOPY NUMBER,
49 X_LTD_PROD OUT NOCOPY NUMBER,
50 X_ADJ_COST OUT NOCOPY NUMBER,
51 X_REVAL_AMO_BASIS OUT NOCOPY NUMBER,
52 X_BONUS_RATE OUT NOCOPY NUMBER,
53 X_DEPRN_SOURCE_CODE OUT NOCOPY VARCHAR2,
54 X_ADJUSTED_FLAG OUT NOCOPY BOOLEAN,
55 X_TRANSACTION_HEADER_ID IN NUMBER DEFAULT -1,
56 X_BONUS_DEPRN_RSV OUT NOCOPY NUMBER,
57 X_BONUS_YTD_DEPRN OUT NOCOPY NUMBER,
58 X_BONUS_DEPRN_AMOUNT OUT NOCOPY NUMBER,
59 X_IMPAIRMENT_RSV OUT NOCOPY NUMBER,
60 X_YTD_IMPAIRMENT OUT NOCOPY NUMBER,
61 X_IMPAIRMENT_AMOUNT OUT NOCOPY NUMBER,
62 X_CAPITAL_ADJUSTMENT OUT NOCOPY NUMBER, -- Bug 6666666
63 X_GENERAL_FUND OUT NOCOPY NUMBER, -- Bug 6666666
64 X_MRC_SOB_TYPE_CODE IN VARCHAR2,
65 X_SET_OF_BOOKS_ID IN NUMBER,
66 p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
67
68 -- Use the Standard Type define in faxstds.pls
69 H_DPR_ROW FA_STD_TYPES.FA_DEPRN_ROW_STRUCT;
70 H_SUCCESS BOOLEAN;
71
72 cursor c_get_group_info is
73 select group_asset_id, member_rollup_flag
74 from fa_books
75 where asset_id = X_ASSET_ID
76 and book_type_code = X_BOOK
77 and transaction_header_id_out is null;
78
79 l_group_asset_id number;
80 l_member_rollup_flag varchar2(1);
81
82 error_found exception;
83
84 BEGIN
85
86 -- Load dpr_row structure with input parameters.
87 H_DPR_ROW.ASSET_ID := X_ASSET_ID;
88 H_DPR_ROW.BOOK := X_BOOK;
89 H_DPR_ROW.PERIOD_CTR := X_PERIOD_CTR;
90 H_DPR_ROW.DIST_ID := X_DIST_ID;
91
92 -- clear/load the book controls caches in case it's
93 -- either stale or not populated, since this routine
94 -- is only called on an asset by asset basis from the
95 -- form, performance is not a huge concern.
96
97 if (nvl(fa_cache_pkg.fazcbc_record.book_type_code, '-NULL') <> X_book) then
98 if not fa_cache_pkg.fazcbc(X_BOOK => X_BOOK
99 ,p_log_level_rec => p_log_level_rec) then
100 raise error_found;
101 end if;
102 end if;
103
104 h_dpr_row.mrc_sob_type_code := x_mrc_sob_type_code;
105 h_dpr_row.set_of_books_id := x_set_of_books_id;
106
107 OPEN c_get_group_info;
108 FETCH c_get_group_info INTO l_group_asset_id, l_member_rollup_flag;
109 CLOSE c_get_group_info;
110
111 if (l_group_asset_id is null and
112 l_member_rollup_flag = 'Y') then
113 h_dpr_row.asset_type := 'GROUP';
114 h_dpr_row.member_rollup_flag := l_member_rollup_flag;
115 end if;
116
117 -- Call internal function.
118
119 QUERY_BALANCES_INT (
120 X_DPR_ROW => H_DPR_ROW,
121 X_RUN_MODE => X_RUN_MODE,
122 X_DEBUG => FALSE,
123 X_SUCCESS => H_SUCCESS,
124 X_CALLING_FN => 'QUERY_BALANCES',
125 X_TRANSACTION_HEADER_ID => X_TRANSACTION_HEADER_ID,
126 p_log_level_rec => p_log_level_rec);
127
128 -- Unpack output parameters from dpr_row structure.
129
130 X_COST := H_DPR_ROW.COST;
131 X_DEPRN_RSV := H_DPR_ROW.DEPRN_RSV;
132 X_REVAL_RSV := H_DPR_ROW.REVAL_RSV;
133 X_YTD_DEPRN := H_DPR_ROW.YTD_DEPRN;
134 X_REVAL_DEPRN_EXP := H_DPR_ROW.REVAL_DEPRN_EXP;
135 X_DEPRN_EXP := H_DPR_ROW.DEPRN_EXP;
136 X_YTD_REVAL_EXP := H_DPR_ROW.YTD_REVAL_DEPRN_EXP;
137 X_REVAL_AMO := H_DPR_ROW.REVAL_AMO;
138 X_PROD := H_DPR_ROW.PROD;
139 X_YTD_PROD := H_DPR_ROW.YTD_PROD;
140 X_LTD_PROD := H_DPR_ROW.LTD_PROD;
141 X_ADJ_COST := H_DPR_ROW.ADJ_COST;
142 X_REVAL_AMO_BASIS := H_DPR_ROW.REVAL_AMO_BASIS;
143 X_BONUS_RATE := H_DPR_ROW.BONUS_RATE;
144 X_DEPRN_SOURCE_CODE := H_DPR_ROW.DEPRN_SOURCE_CODE;
145 X_ADJUSTED_FLAG := H_DPR_ROW.ADJUSTED_FLAG;
146 X_BONUS_DEPRN_RSV := H_DPR_ROW.BONUS_DEPRN_RSV;
147 X_BONUS_YTD_DEPRN := H_DPR_ROW.BONUS_YTD_DEPRN;
148 X_BONUS_DEPRN_AMOUNT := H_DPR_ROW.BONUS_DEPRN_AMOUNT;
149 X_IMPAIRMENT_RSV := H_DPR_ROW.IMPAIRMENT_RSV;
150 X_YTD_IMPAIRMENT := H_DPR_ROW.YTD_IMPAIRMENT;
151 X_IMPAIRMENT_AMOUNT := H_DPR_ROW.IMPAIRMENT_AMOUNT;
152 X_CAPITAL_ADJUSTMENT := H_DPR_ROW.CAPITAL_ADJUSTMENT; -- Bug 6666666
153 X_GENERAL_FUND := H_DPR_ROW.GENERAL_FUND; -- Bug 6666666
154
155
156 EXCEPTION
157 when error_found then
158 fa_srvr_msg.add_sql_error(
159 calling_fn => 'QUERY_BALANCES', p_log_level_rec => p_log_level_rec);
160 fa_srvr_msg.add_message(
161 calling_fn => 'QUERY_BALANCES',
162 name => 'FA_WHATIF_ASSET_QUERY_BAL',
163 token1 => 'ASSET_ID',
164 value1 => to_char(X_asset_id),
165 p_log_level_rec => p_log_level_rec);
166 fa_standard_pkg.raise_error(
167 CALLED_FN => 'QUERY_BALANCES',
168 CALLING_FN => 'CLIENT', p_log_level_rec => p_log_level_rec);
169 when others then
170 if (p_log_level_rec.statement_level) then
171 FA_DEBUG_PKG.ADD(
172 fname => 'FA_QUERY_BALANCES_PKG.QUERY_BALANCES',
173 element => 'ASSET_ID',
174 value => H_DPR_ROW.ASSET_ID, p_log_level_rec => p_log_level_rec);
175 FA_DEBUG_PKG.ADD(
176 fname => 'FA_QUERY_BALANCES_PKG.QUERY_BALANCES',
177 element => 'BOOK',
178 value => H_DPR_ROW.BOOK, p_log_level_rec => p_log_level_rec);
179 FA_DEBUG_PKG.ADD(
180 fname => 'FA_QUERY_BALANCES_PKG.QUERY_BALANCES',
181 element => 'COST',
182 value => H_DPR_ROW.COST, p_log_level_rec => p_log_level_rec);
183 end if;
184 fa_srvr_msg.add_sql_error(
185 calling_fn => 'QUERY_BALANCES', p_log_level_rec => p_log_level_rec);
186 fa_srvr_msg.add_message(
187 calling_fn => 'QUERY_BALANCES',
188 name => 'FA_WHATIF_ASSET_QUERY_BAL',
189 token1 => 'ASSET_ID',
190 value1 => to_char(X_asset_id),
191 p_log_level_rec => p_log_level_rec);
192 fa_standard_pkg.raise_error(
193 CALLED_FN => 'QUERY_BALANCES',
194 CALLING_FN => 'CLIENT', p_log_level_rec => p_log_level_rec);
195
196 END QUERY_BALANCES;
197
198 -----------------------------------------------------------------------------------
199
200
201 -- Adds the current period's adjustments (ADJ_DRS) to the
202 -- financial info in the most recent depreciation row (DEST_DRS).
203 -- S.B. called right after get_adjustments.
204 -- This should go away in Rel11, where adjustments will update
205 -- deprn rows.
206
207
208 PROCEDURE ADD_ADJ_TO_DEPRN
209 (X_ADJ_DRS IN OUT NOCOPY FA_STD_TYPES.FA_DEPRN_ROW_STRUCT,
210 X_DEST_DRS IN OUT NOCOPY FA_STD_TYPES.FA_DEPRN_ROW_STRUCT,
211 X_SUCCESS OUT NOCOPY BOOLEAN,
212 X_CALLING_FN VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
213
214 h_deprn_expense_adjs NUMBER;
215 h_curr_period_counter NUMBER;
216 h_dummy NUMBER;
217
218
219 BEGIN
220
221
222 X_success := FALSE;
223
224 -- Get current period counter and book class.
225 -- This change is done by Sujit Dalai when YTD revaluation was implemented .
226 -- Fix for 947800. Added h_dummy to avoid ORA-1403.
227
228 if (X_ADJ_DRS.BOOK is not null) then
229 h_curr_period_counter := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
230 h_dummy := 1;
231 end if;
232
233 -- Get adjustment amount from fa_adjustment when adjustment_type is 'EXPENSE'
234 -- and source_type_code is 'REVALUATION'. This change is done by Sujit Dalai.
235 -- Fix for 947800. Added h_dummy to avoid ORA-1403.
236
237 if (h_curr_period_counter is not null) then
238 /* Bug 6348506 Split the query into UNION ALL for improving performance */
239
240 /* Bug 6915685: Fix for perf fix 6348506 caused ora-1422.
241 Thats because each SQL within UNION ALL had a group-by function
242 that would always return 1 row each. Therefore, UNION ALL will
243 cause 2 rows to be returned, cause ORA-1422.
244 To fix this, added IF condition. This should definitely take care of
245 both performance issue (6348506) and ora-1422 issue (6915685) */
246
247 if (x_adj_drs.mrc_sob_type_code = 'R') then
248 if (x_adj_drs.dist_id = 0) then -- bugfix 6915685: added IF clause
249 SELECT NVL(SUM(DECODE(ADJ.DEBIT_CREDIT_FLAG,
250 'DR', ADJ.ADJUSTMENT_AMOUNT,
251 'CR', -1*ADJ.ADJUSTMENT_AMOUNT)), 0), 1
252 INTO h_deprn_expense_adjs, h_dummy
253 FROM FA_MC_ADJUSTMENTS ADJ
254 WHERE ADJ.ASSET_ID = x_adj_drs.asset_id
255 AND ADJ.BOOK_TYPE_CODE = x_adj_drs.book
256 AND ADJ.PERIOD_COUNTER_CREATED = h_curr_period_counter
257 AND ADJ.SOURCE_TYPE_CODE = 'REVALUATION'
258 AND ADJ.ADJUSTMENT_TYPE = 'EXPENSE'
259 AND ADJ.SET_OF_BOOKS_ID = x_adj_drs.set_of_books_id;
260 else
261 SELECT NVL(SUM(DECODE(ADJ.DEBIT_CREDIT_FLAG,
262 'DR', ADJ.ADJUSTMENT_AMOUNT,
263 'CR', -1*ADJ.ADJUSTMENT_AMOUNT)), 0), 1
264 INTO h_deprn_expense_adjs, h_dummy
265 FROM FA_MC_ADJUSTMENTS ADJ
266 WHERE ADJ.ASSET_ID = x_adj_drs.asset_id
267 AND ADJ.BOOK_TYPE_CODE = x_adj_drs.book
268 AND ADJ.PERIOD_COUNTER_CREATED = h_curr_period_counter
269 AND ADJ.SOURCE_TYPE_CODE = 'REVALUATION'
270 AND ADJ.ADJUSTMENT_TYPE = 'EXPENSE'
271 AND ADJ.DISTRIBUTION_ID = x_adj_drs.dist_id
272 AND ADJ.SET_OF_BOOKS_ID = x_adj_drs.set_of_books_id;
273 end if;
274 else
275 if (x_adj_drs.dist_id = 0) then -- bugfix 6915685: added IF clause
276 SELECT NVL(SUM(DECODE(ADJ.DEBIT_CREDIT_FLAG,
277 'DR', ADJ.ADJUSTMENT_AMOUNT,
278 'CR', -1*ADJ.ADJUSTMENT_AMOUNT)), 0), 1
279 INTO h_deprn_expense_adjs, h_dummy
280 FROM FA_ADJUSTMENTS ADJ
281 WHERE ADJ.ASSET_ID = x_adj_drs.asset_id
282 AND ADJ.BOOK_TYPE_CODE = x_adj_drs.book
283 AND ADJ.PERIOD_COUNTER_CREATED = h_curr_period_counter
284 AND ADJ.SOURCE_TYPE_CODE = 'REVALUATION'
285 AND ADJ.ADJUSTMENT_TYPE = 'EXPENSE';
286 else
287 SELECT NVL(SUM(DECODE(ADJ.DEBIT_CREDIT_FLAG,
288 'DR', ADJ.ADJUSTMENT_AMOUNT,
289 'CR', -1*ADJ.ADJUSTMENT_AMOUNT)), 0), 1
290 INTO h_deprn_expense_adjs, h_dummy
291 FROM FA_ADJUSTMENTS ADJ
292 WHERE ADJ.ASSET_ID = x_adj_drs.asset_id
293 AND ADJ.BOOK_TYPE_CODE = x_adj_drs.book
294 AND ADJ.PERIOD_COUNTER_CREATED = h_curr_period_counter
295 AND ADJ.SOURCE_TYPE_CODE = 'REVALUATION'
296 AND ADJ.ADJUSTMENT_TYPE = 'EXPENSE'
297 AND ADJ.DISTRIBUTION_ID = x_adj_drs.dist_id;
298 end if;
299 end if;
300
301 end if ;
302
303 -- Add expense adjustment to reserve.
304 if (X_adj_drs.deprn_exp <> 0) then
305 X_dest_drs.deprn_exp := X_dest_drs.deprn_exp + X_adj_drs.deprn_exp;
306 X_dest_drs.ytd_deprn := X_dest_drs.ytd_deprn + X_adj_drs.deprn_exp;
307 X_dest_drs.deprn_rsv := (X_dest_drs.deprn_rsv + X_adj_drs.deprn_exp
308 - h_deprn_expense_adjs) ;
309 end if;
310
311 -- Add bonus expense adjustment to bonus reserve.
312 if (X_adj_drs.bonus_deprn_amount <> 0) then
313 X_dest_drs.bonus_deprn_amount := X_dest_drs.bonus_deprn_amount + X_adj_drs.bonus_deprn_amount;
314 X_dest_drs.bonus_ytd_deprn := X_dest_drs.bonus_ytd_deprn + X_adj_drs.bonus_deprn_amount;
315 X_dest_drs.bonus_deprn_rsv := (X_dest_drs.bonus_deprn_rsv + X_adj_drs.bonus_deprn_amount);
316 -- bonus: not handling revaluation expense as done for regular deprn. - h_deprn_expense_adjs
317 end if;
318
319 -- Add impairment expense adjustment to impairment reserve.
320 if (X_adj_drs.impairment_amount <> 0) then
321 X_dest_drs.impairment_amount := X_dest_drs.impairment_amount +
322 X_adj_drs.impairment_amount;
323 X_dest_drs.ytd_impairment := X_dest_drs.ytd_impairment +
324 X_adj_drs.impairment_amount;
325 /* This statement is commented for bug 7460979. This causes wrong Deprn Reserve values.
326 X_dest_drs.impairment_rsv := X_dest_drs.impairment_rsv +
327 X_adj_drs.impairment_amount;*/
328 end if;
329
330 -- Bug 6666666 : SORP Complaince
331 if (X_adj_drs.capital_adjustment <> 0) then
332 X_dest_drs.capital_adjustment := X_dest_drs.capital_adjustment + X_adj_drs.capital_adjustment;
333 end if;
334
335 if (X_adj_drs.general_fund <> 0) then
336 X_dest_drs.general_fund := X_dest_drs.general_fund + X_adj_drs.general_fund;
337 end if;
338
339 -- Add reval deprn expense adjustment to existing reval deprn
340 -- expense balance.
341 if (X_adj_drs.reval_deprn_exp <> 0) then
342 X_dest_drs.reval_deprn_exp := X_dest_drs.reval_deprn_exp + X_adj_drs.reval_deprn_exp;
343 end if;
344
345 -- Add reval reserve and amortization.
346 if (X_adj_drs.reval_amo <> 0) then
347 X_dest_drs.reval_amo := X_dest_drs.reval_amo + X_adj_drs.reval_amo;
348 X_dest_drs.reval_rsv := X_dest_drs.reval_rsv - X_adj_drs.reval_amo;
349 end if;
350
351 -- Add production adjustments to production balances.
352 if (X_adj_drs.prod <> 0) then
353 X_dest_drs.prod := X_dest_drs.prod + X_adj_drs.prod;
354 X_dest_drs.ytd_prod := X_dest_drs.ytd_prod + X_adj_drs.prod;
355 X_dest_drs.ltd_prod := X_dest_drs.ltd_prod + X_adj_drs.prod;
356 end if;
357
358 -- Deprn reserve adjustments
359 if (X_adj_drs.deprn_rsv <> 0) then
360 X_dest_drs.deprn_rsv := X_dest_drs.deprn_rsv - X_adj_drs.deprn_rsv;
361 end if;
362
363 -- Bonus Deprn reserve adjustments
364 if (X_adj_drs.bonus_deprn_rsv <> 0) then
365 X_dest_drs.bonus_deprn_rsv := X_dest_drs.bonus_deprn_rsv - X_adj_drs.bonus_deprn_rsv;
366 end if;
367
368 -- Impairment reserve adjustments
369 if (X_adj_drs.impairment_rsv <> 0) then
370 X_dest_drs.impairment_rsv := X_dest_drs.impairment_rsv - X_adj_drs.impairment_rsv;
371 end if;
372
373 -- Reval reserve adjustments
374 if (X_adj_drs.reval_rsv <> 0) then
375 X_dest_drs.reval_rsv := X_dest_drs.reval_rsv - X_adj_drs.reval_rsv;
376 end if;
377
378 -- Cost adjustments
379 if (X_adj_drs.cost <> 0) then
380 X_dest_drs.cost := X_dest_drs.cost + X_adj_drs.cost;
381 end if;
382
383 -- Addition-cost-to-clear adjustments
384 if (X_adj_drs.add_cost_to_clear <> 0) then
385 X_dest_drs.add_cost_to_clear := X_dest_drs.add_cost_to_clear +
386 X_adj_drs.add_cost_to_clear;
387 end if;
388
389 if (X_adj_drs.reval_loss_balance <> 0) then
390 X_dest_drs.reval_loss_balance := X_dest_drs.reval_loss_balance + X_adj_drs.reval_loss_balance;
391 end if;
392
393 X_success := TRUE;
394
395 EXCEPTION
396 when others then
397 fa_srvr_msg.add_sql_error(calling_fn => 'ADD_ADJ_TO_DEPRN', p_log_level_rec => p_log_level_rec);
398 fa_standard_pkg.raise_error(
399 CALLED_FN => 'ADD_ADJ_TO_DEPRN',
400 CALLING_FN => X_CALLING_FN, p_log_level_rec => p_log_level_rec);
401
402 END ADD_ADJ_TO_DEPRN;
403
404
405 --------------------------------------------------------------------------------
406
407
408 PROCEDURE QUERY_BALANCES_INT(
409 X_DPR_ROW IN OUT NOCOPY FA_STD_TYPES.FA_DEPRN_ROW_STRUCT,
410 X_RUN_MODE VARCHAR2,
411 X_DEBUG BOOLEAN,
412 X_SUCCESS OUT NOCOPY BOOLEAN,
413 X_CALLING_FN VARCHAR2,
414 X_TRANSACTION_HEADER_ID IN NUMBER DEFAULT -1, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
415
416
417 h_found_per_ctr number;
418 h_is_acc_null boolean;
419 h_dpr_adjs fa_std_types.fa_deprn_row_struct;
420 h_success boolean;
421 h_count number;
422 h_mrc_sob_type_code varchar2(1);
423
424 h_mesg_name varchar2(30);
425 bad_mode exception;
426 bad_trx_id exception;
427 sob_cache_error exception;
428
429 BEGIN
430
431 h_mesg_name := 'FA_WHATIF_ASSET_QUERY_BAL';
432
433 X_success := FALSE;
434 h_is_acc_null := FALSE;
435
436 if (X_RUN_MODE not in ('STANDARD','ADJUSTED','DEPRN',
437 'TRANSACTION','INVALID')) then
438 raise bad_mode;
439 end if;
440
441
442 -- If running in TRANSACTION mode, then
443 -- set period_ctr to whichever period the transaction occurred.
444
445 if (X_run_mode = 'TRANSACTION') then
446
447 h_mesg_name := 'FA_POST_SQL_PC';
448
449 select dp.period_counter-1,
450 count(*)
451 into X_dpr_row.period_ctr,
452 h_count
453 from fa_deprn_periods dp,
454 fa_transaction_headers th
455 where dp.book_type_code = X_dpr_row.book
456 and th.date_effective between
457 dp.period_open_date and
458 nvl(dp.period_close_date,to_date('31-12-4712','DD-MM-YYYY'))
459 and th.transaction_header_id = X_transaction_header_id
460 group by dp.period_counter-1;
461
462 h_mesg_name := 'FA_WHATIF_ASSET_QUERY_BAL';
463
464 end if;
465
466 if h_count=0 then raise bad_trx_id; end if;
467
468 -- If dist_id is given, then query that distribution only...
469 -- Query from fa_deprn_detail.
470 -- If dist_id is 0, then query at summary level: fa_deprn_summary.
471
472 if (X_dpr_row.dist_id <> 0) then
473
474 QUERY_DEPRN_DETAIL
475 (X_DPR_ROW => X_dpr_row,
476 X_FOUND_PER_CTR => h_found_per_ctr,
477 X_IS_ACC_NULL => h_is_acc_null,
478 X_RUN_MODE => X_run_mode,
479 X_SUCCESS => h_success,
480 X_CALLING_FN =>'QUERY_BALANCES_INT',
481 p_log_level_rec => p_log_level_rec);
482
483 -- If querying current period, or if period indicated does not have
484 -- deprn rows, or if querying from TAX book, then unaccounted adjustments
485 -- may exist... We need to add these to information from deprn rows.
486
487 if (h_found_per_ctr = 0 OR
488 h_found_per_ctr <> X_dpr_row.period_ctr OR
489 X_run_mode in ('ADJUSTED','TRANSACTION')) then
490
491 h_dpr_adjs.asset_id := X_dpr_row.asset_id;
492 h_dpr_adjs.book := X_dpr_row.book;
493 h_dpr_adjs.mrc_sob_type_code := X_dpr_row.mrc_sob_type_code;
494 h_dpr_adjs.set_of_books_id := X_dpr_row.set_of_books_id;
495
496 if (X_run_mode = 'TRANSACTION') then
497 h_dpr_adjs.period_ctr := X_dpr_row.period_ctr + 1;
498 else
499 h_dpr_adjs.period_ctr := X_dpr_row.period_ctr;
500 end if;
501
502 h_dpr_adjs.dist_id := X_dpr_row.dist_id;
503
504 -- Get adjustments info
505 GET_ADJUSTMENTS_INFO
506 (X_ADJ_ROW => h_dpr_adjs,
507 X_FOUND_PER_CTR => h_found_per_ctr,
508 X_RUN_MODE => X_run_mode,
509 X_TRANSACTION_HEADER_ID => X_transaction_header_id,
510 X_SUCCESS => h_success,
511 X_CALLING_FN => 'QUERY_BALANCES_INT',
512 p_log_level_rec => p_log_level_rec);
513
514 end if;
515
516 elsif (X_dpr_row.dist_id = 0) then
517
518 h_mrc_sob_type_code := X_dpr_row.mrc_sob_type_code;
519
520 -- Query at summary level
521 QUERY_DEPRN_SUMMARY
522 (X_DPR_ROW => X_dpr_row,
523 X_FOUND_PER_CTR => h_found_per_ctr,
524 X_RUN_MODE => X_run_mode,
525 X_SUCCESS => h_success,
526 X_CALLING_FN => 'QUERY_BALANCES_INT',
527 p_log_level_rec => p_log_level_rec);
528
529 -- If given period has no deprn row, or if querying TAX book,
530 -- then need to check for adjustments and add to info from deprn rows.
531
532 if (X_dpr_row.period_ctr <> h_found_per_ctr OR
533 X_run_mode in ('ADJUSTED','TRANSACTION')) then
534
535 h_dpr_adjs.asset_id := X_dpr_row.asset_id;
536 h_dpr_adjs.book := X_dpr_row.book;
537 h_dpr_adjs.mrc_sob_type_code := X_dpr_row.mrc_sob_type_code;
538 h_dpr_adjs.set_of_books_id := X_dpr_row.set_of_books_id;
539
540 if (X_run_mode = 'TRANSACTION') then
541 h_dpr_adjs.period_ctr := X_dpr_row.period_ctr + 1;
542 else
543 h_dpr_adjs.period_ctr := X_dpr_row.period_ctr;
544 end if;
545 h_dpr_adjs.dist_id := X_dpr_row.dist_id;
546
547 h_dpr_adjs.asset_type := X_dpr_row.asset_type;
548 h_dpr_adjs.member_rollup_flag := X_dpr_row.member_rollup_flag;
549
550 GET_ADJUSTMENTS_INFO
551 (X_ADJ_ROW => h_dpr_adjs,
552 X_FOUND_PER_CTR => h_found_per_ctr,
553 X_RUN_MODE => X_run_mode,
554 X_TRANSACTION_HEADER_ID => X_transaction_header_id,
555 X_SUCCESS => h_success,
556 X_CALLING_FN => 'QUERY_BALANCES_INT',
557 p_log_level_rec => p_log_level_rec);
558
559 end if;
560 end if;
561
562 -- now add the detail/summary structure values to the adjustments
563 add_adj_to_deprn
564 (X_ADJ_DRS => h_dpr_adjs,
565 X_DEST_DRS => X_dpr_row,
566 X_SUCCESS => h_success,
567 X_CALLING_FN =>'QUERY_BALANCES_INT',
568 p_log_level_rec => p_log_level_rec);
569
570
571 -- Indicate whether cost has been cleared or not.
572 if (h_is_acc_null) then
573 X_dpr_row.add_cost_to_clear := 0;
574 end if;
575
576 -- Give period of the info returned (useful if we asked for
577 -- current period.
578 X_dpr_row.period_ctr := h_found_per_ctr;
579
580 -- Indicate if any adjustments were found that had to be added to
581 -- info from deprn rows.
582 X_dpr_row.adjusted_flag := h_dpr_adjs.adjusted_flag;
583
584 X_success := TRUE;
585
586 EXCEPTION
587 when bad_mode then
588 fa_standard_pkg.raise_error(
589 CALLED_FN =>'QUERY_BALANCES_INT',
590 CALLING_FN => X_CALLING_FN,
591 NAME => 'FA_QADD_INVALID_MODE', p_log_level_rec => p_log_level_rec);
592
593 when bad_trx_id then
594 fa_standard_pkg.raise_error(
595 CALLED_FN =>'QUERY_BALANCES_INT',
596 CALLING_FN => X_CALLING_FN,
597 NAME => 'FA_QADD_INVALID_TRXID', p_log_level_rec => p_log_level_rec);
598
599 when sob_cache_error then
600 fa_srvr_msg.add_sql_error
601 (calling_fn => 'fa_query_balances_pkg.query_balances_int', p_log_level_rec => p_log_level_rec);
602 fa_standard_pkg.raise_error(
603 CALLED_FN =>'QUERY_BALANCES_INT',
604 CALLING_FN => X_CALLING_FN,
605 NAME => 'FA_QADD_INVALID_TRXID', p_log_level_rec => p_log_level_rec);
606
607 when others then
608 if (p_log_level_rec.statement_level) then
609 FA_DEBUG_PKG.ADD (
610 fname => 'FA_QUERY_BALANCES_PKG.QUERY_BALANCES_INT',
611 element => 'DIST_ID',
612 value => X_DPR_ROW.DIST_ID, p_log_level_rec => p_log_level_rec);
613 FA_DEBUG_PKG.ADD (
614 fname => 'FA_QUERY_BALANCES_PKG.QUERY_BALANCES_INT',
615 element => 'PERIOD_CTR',
616 value => X_DPR_ROW.PERIOD_CTR, p_log_level_rec => p_log_level_rec);
617 FA_DEBUG_PKG.ADD (
618 fname => 'FA_QUERY_BALANCES_PKG.QUERY_BALANCES_INT',
619 element => 'RUN_MODE',
620 value => X_RUN_MODE, p_log_level_rec => p_log_level_rec);
621 end if;
622 fa_srvr_msg.add_sql_error(calling_fn => 'QUERY_BALANCES_INT', p_log_level_rec => p_log_level_rec);
623 if h_mesg_name = 'FA_WHATIF_ASSET_QUERY_BAL' then
624 fa_srvr_msg.add_message
625 (calling_fn => 'QUERY_BALANCES_INT',
626 name => h_mesg_name,
627 token1 => 'ASSET_ID',
628 value1 => to_char(X_dpr_row.asset_id),
629 p_log_level_rec => p_log_level_rec);
630 else
631 fa_srvr_msg.add_message
632 (calling_fn => 'QUERY_BALANCES_INT',
633 name => h_mesg_name, p_log_level_rec => p_log_level_rec);
634 end if;
635 fa_standard_pkg.raise_error(
636 CALLED_FN =>'QUERY_BALANCES_INT',
637 CALLING_FN => X_CALLING_FN,
638 NAME => h_mesg_name, p_log_level_rec => p_log_level_rec);
639
640 END QUERY_BALANCES_INT;
641
642 -----------------------------------------------------------------------------
643
644 -- This procedure gets info related to the current period:
645 -- period counter, fiscal year, and number of periods in fiscal year
646
647 PROCEDURE GET_PERIOD_INFO (
648 X_BOOK VARCHAR2,
649 X_CUR_PER_CTR IN OUT NOCOPY NUMBER,
650 X_CUR_FY IN OUT NOCOPY NUMBER,
651 X_NUM_PERS_FY IN OUT NOCOPY NUMBER,
652 X_SUCCESS OUT NOCOPY BOOLEAN,
653 X_CALLING_FN VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
654
655 error_found exception;
656
657 BEGIN
658
659 X_SUCCESS := FALSE;
660
661 -- select period counter, number of periods
662 -- per fiscal year, and the current fiscal year
663
664 X_CUR_PER_CTR := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
665 X_CUR_FY := fa_cache_pkg.fazcbc_record.current_fiscal_year;
666
667 if not fa_cache_pkg.fazcct
668 (X_calendar => fa_cache_pkg.fazcbc_record.deprn_calendar, p_log_level_rec => p_log_level_rec) then
669 raise error_found;
670 end if;
671
672 X_NUM_PERS_FY := fa_cache_pkg.fazcct_record.number_per_fiscal_year;
673 X_SUCCESS := TRUE;
674
675 EXCEPTION
676 when error_found then
677 fa_srvr_msg.add_sql_error(
678 calling_fn => 'FA_QUERY_BALANCES_PKG.GET_PERIOD_INFO', p_log_level_rec => p_log_level_rec);
679 fa_srvr_msg.add_message(
680 calling_fn => 'FA_QUERY_BALANCES_PKG.GET_PERIOD_INFO',
681 name => 'FA_EXP_GET_CUR_PERIOD_INFO', p_log_level_rec => p_log_level_rec);
682
683 FA_STANDARD_PKG.RAISE_ERROR (
684 CALLED_FN => 'fa_query_balances_pkg.get_period_info',
685 CALLING_FN => X_CALLING_FN, p_log_level_rec => p_log_level_rec);
686 when others then
687 if (p_log_level_rec.statement_level) then
688 FA_DEBUG_PKG.ADD (
689 fname =>'FA_QUERY_BALANCES_PKG.GET_PERIOD_INFO',
690 element =>'CUR_PER_CTR',
691 value =>X_CUR_PER_CTR, p_log_level_rec => p_log_level_rec);
692 FA_DEBUG_PKG.ADD (
693 fname =>'FA_QUERY_BALANCES_PKG.GET_PERIOD_INFO',
694 element => 'NUM_PERS_FY',
695 value => X_NUM_PERS_FY, p_log_level_rec => p_log_level_rec);
696 end if;
697
698 fa_srvr_msg.add_sql_error(
699 calling_fn => 'FA_QUERY_BALANCES_PKG.GET_PERIOD_INFO', p_log_level_rec => p_log_level_rec);
700 fa_srvr_msg.add_message(
701 calling_fn => 'FA_QUERY_BALANCES_PKG.GET_PERIOD_INFO',
702 name => 'FA_EXP_GET_CUR_PERIOD_INFO', p_log_level_rec => p_log_level_rec);
703
704 FA_STANDARD_PKG.RAISE_ERROR (
705 CALLED_FN => 'fa_query_balances_pkg.get_period_info',
706 CALLING_FN => X_CALLING_FN, p_log_level_rec => p_log_level_rec);
707
708 END GET_PERIOD_INFO;
709
710 -------------------------------------------------------------------------------------
711
712 -- Use this procedure to query summary-level information
713 -- from fa_deprn_summary in given period (or current period if 0)
714
715
716 PROCEDURE QUERY_DEPRN_SUMMARY (
717 X_DPR_ROW IN OUT NOCOPY FA_STD_TYPES.FA_DEPRN_ROW_STRUCT,
718 X_FOUND_PER_CTR IN OUT NOCOPY NUMBER,
719 X_RUN_MODE VARCHAR2,
720 X_SUCCESS OUT NOCOPY BOOLEAN,
721 X_CALLING_FN VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
722
723 h_cur_per_ctr number;
724 h_num_pers_fy number;
725 h_cur_fy number;
726 h_fy number;
727 h_period_counter number;
728 h_is_acc_null_num number;
729 h_proc_success boolean;
730
731 h_mesg_name varchar2(30);
732
733
734 -- Main select statement. Get summary-level info for most recent period
735 -- at or before given period.
736 -- If RUN_MODE = 'DEPRN', return 0 for deprn_amount, reval_deprn_expense,
737 -- and reval_amortization. Also decrement period counter tto get last
738 -- period's info (used in depreciation program).
739
740 CURSOR GET_MC_DS IS
741 SELECT 0,
742 DEPRN_RESERVE,
743 decode (X_RUN_MODE,
744 'DEPRN', 0,
745 DECODE(PERIOD_COUNTER,
746 h_period_counter, DEPRN_AMOUNT,
747 0)),
748 NVL(REVAL_RESERVE, 0),
749 DECODE (FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
750 h_fy, YTD_DEPRN,
751 0),
752 DECODE (FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
753 h_fy, NVL(YTD_REVAL_DEPRN_EXPENSE, 0),
754 0),
755 decode (X_RUN_MODE,
756 'DEPRN', 0,
757 DECODE(PERIOD_COUNTER,
758 h_period_counter, NVL(REVAL_DEPRN_EXPENSE, 0),
759 0)),
760 decode (X_RUN_MODE,
761 'DEPRN', 0,
762 DECODE(PERIOD_COUNTER,
763 h_period_counter, NVL(REVAL_AMORTIZATION, 0),
764 0)),
765 NVL(BONUS_DEPRN_RESERVE,0),
766 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
767 h_fy, BONUS_YTD_DEPRN,
768 0),
769 decode (X_RUN_MODE,
770 'DEPRN', 0,
771 DECODE(PERIOD_COUNTER,
772 h_period_counter, BONUS_DEPRN_AMOUNT,
773 0)),
774 NVL(impairment_reserve,0),
775 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
776 h_fy, YTD_IMPAIRMENT,
777 0),
778 decode (X_RUN_MODE,
779 'DEPRN', 0,
780 DECODE(PERIOD_COUNTER,
781 h_period_counter, IMPAIRMENT_AMOUNT,
782 0)),
783 PERIOD_COUNTER,
784 NVL(PRODUCTION, 0),
785 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
786 h_fy, NVL(YTD_PRODUCTION, 0),
787 0),
788 NVL(LTD_PRODUCTION, 0),
789 ADJUSTED_COST,
790 NVL(REVAL_AMORTIZATION_BASIS, 0),
791 NVL(BONUS_RATE, 0),
792 DEPRN_SOURCE_CODE,
793 0,
794 NVL(PRIOR_FY_EXPENSE, 0),
795 NVL(PRIOR_FY_BONUS_EXPENSE, 0),
796 NVL(CAPITAL_ADJUSTMENT,0), --Bug 6666666
797 NVL(GENERAL_FUND,0), --Bug 6666666
798 NVL(REVAL_LOSS_BALANCE,0)
799 FROM FA_MC_DEPRN_SUMMARY DS
800 WHERE DS.ASSET_ID = X_dpr_row.asset_id
801 AND DS.BOOK_TYPE_CODE = X_dpr_row.book
802 AND DS.PERIOD_COUNTER <= h_period_counter
803 AND DS.SET_OF_BOOKS_ID = X_dpr_row.set_of_books_id
804 ORDER BY PERIOD_COUNTER DESC;
805
806
807 CURSOR GET_DS IS
808 SELECT 0,
809 DEPRN_RESERVE,
810 decode (X_RUN_MODE,
811 'DEPRN', 0,
812 DECODE(PERIOD_COUNTER,
813 h_period_counter, DEPRN_AMOUNT,
814 0)),
815 NVL(REVAL_RESERVE, 0),
816 /* Bug# 6353715 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
817 h_fy, YTD_DEPRN,
818 0),*/
819 DECODE(FLOOR(
820 DECODE(deprn_source_code
821 ,'BOOKS',PERIOD_COUNTER
822 , (PERIOD_COUNTER - 1))/ h_num_pers_fy),
823 h_fy, YTD_DEPRN,
824 0),
825 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
826 h_fy, NVL(YTD_REVAL_DEPRN_EXPENSE, 0),
827 0),
828 decode (X_RUN_MODE,
829 'DEPRN', 0,
830 DECODE(PERIOD_COUNTER,
831 h_period_counter, NVL(REVAL_DEPRN_EXPENSE, 0),
832 0)),
833 decode (X_RUN_MODE,
834 'DEPRN', 0,
835 DECODE(PERIOD_COUNTER,
836 h_period_counter, NVL(REVAL_AMORTIZATION, 0),
837 0)),
838 NVL(BONUS_DEPRN_RESERVE,0),
839 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
840 h_fy, BONUS_YTD_DEPRN,
841 0),
842 decode (X_RUN_MODE,
843 'DEPRN', 0,
844 DECODE(PERIOD_COUNTER,
845 h_period_counter, BONUS_DEPRN_AMOUNT,
846 0)),
847 NVL(impairment_reserve,0),
848 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
849 h_fy, YTD_IMPAIRMENT,
850 0),
851 decode (X_RUN_MODE,
852 'DEPRN', 0,
853 DECODE(PERIOD_COUNTER,
854 h_period_counter, IMPAIRMENT_AMOUNT,
855 0)),
856 PERIOD_COUNTER,
857 NVL(PRODUCTION, 0),
858 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
859 h_fy, NVL(YTD_PRODUCTION, 0),
860 0),
861 NVL(LTD_PRODUCTION, 0),
862 ADJUSTED_COST,
863 NVL(REVAL_AMORTIZATION_BASIS, 0),
864 NVL(BONUS_RATE, 0),
865 DEPRN_SOURCE_CODE,
866 0,
867 NVL(PRIOR_FY_EXPENSE, 0),
868 NVL(PRIOR_FY_BONUS_EXPENSE, 0),
869 NVL(CAPITAL_ADJUSTMENT,0), --Bug 6666666
870 NVL(GENERAL_FUND,0), --Bug 6666666
871 NVL(REVAL_LOSS_BALANCE,0)
872 FROM FA_DEPRN_SUMMARY DS
873 WHERE DS.ASSET_ID = X_dpr_row.asset_id
874 AND DS.BOOK_TYPE_CODE = X_dpr_row.book
875 AND DS.PERIOD_COUNTER <= h_period_counter
876 ORDER BY PERIOD_COUNTER DESC;
877
878
879 -- for Sumup Assets
880 CURSOR GET_MC_DM IS
881 SELECT 0,
882 sum(DEPRN_RESERVE),
883 sum(decode (X_RUN_MODE,
884 'DEPRN', 0,
885 DECODE(PERIOD_COUNTER,
886 h_period_counter, DEPRN_AMOUNT,
887 0))),
888 sum(NVL(REVAL_RESERVE, 0)),
889 sum(DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
890 h_fy, YTD_DEPRN,
891 0)),
892 sum(DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
893 h_fy, NVL(YTD_REVAL_DEPRN_EXPENSE, 0),
894 0)),
895 sum(decode (X_RUN_MODE,
896 'DEPRN', 0,
897 DECODE(PERIOD_COUNTER,
898 h_period_counter, NVL(REVAL_DEPRN_EXPENSE, 0),
899 0))),
900 sum(decode (X_RUN_MODE,
901 'DEPRN', 0,
902 DECODE(PERIOD_COUNTER,
903 h_period_counter, NVL(REVAL_AMORTIZATION, 0),
904 0))),
905 sum(NVL(BONUS_DEPRN_RESERVE,0)),
906 sum(DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
907 h_fy, BONUS_YTD_DEPRN,
908 0)),
909 sum(decode (X_RUN_MODE,
910 'DEPRN', 0,
911 DECODE(PERIOD_COUNTER,
912 h_period_counter, BONUS_DEPRN_AMOUNT,
913 0))),
914 sum(NVL(impairment_reserve,0)), -- bug 5951733 (added sum function)
915 sum(DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy), -- bug 5951733 (added sum function)
916 h_fy, YTD_IMPAIRMENT,
917 0)),
918 sum(decode (X_RUN_MODE, -- bug 5951733 (added sum function)
919 'DEPRN', 0,
920 DECODE(PERIOD_COUNTER,
921 h_period_counter, IMPAIRMENT_AMOUNT,
922 0))),
923 max(PERIOD_COUNTER), -- Bug#6350172
924 sum(NVL(PRODUCTION, 0)),
925 sum(DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
926 h_fy, NVL(YTD_PRODUCTION, 0),
927 0)),
928 sum(NVL(LTD_PRODUCTION, 0)),
929 sum(ADJUSTED_COST),
930 sum(NVL(REVAL_AMORTIZATION_BASIS, 0)),
931 sum(NVL(BONUS_RATE, 0)),
932 'DEPRN' DEPRN_SOURCE_CODE,
933 0,
934 sum(NVL(PRIOR_FY_EXPENSE, 0)),
935 sum(NVL(PRIOR_FY_BONUS_EXPENSE, 0)),
936 sum(NVL(CAPITAL_ADJUSTMENT,0)), --Bug 6666666
937 sum(NVL(GENERAL_FUND,0)) --Bug 6666666
938 FROM FA_MC_DEPRN_SUMMARY DS
939 WHERE DS.ASSET_ID in (select bk.asset_id
940 from fa_books bk
941 where bk.book_type_code = x_dpr_row.book
942 and bk.transaction_header_id_out is null
943 and bk.group_asset_id = x_dpr_row.asset_id)
944 AND DS.BOOK_TYPE_CODE = X_dpr_row.book
945 AND DS.SET_OF_BOOKS_ID = X_dpr_row.set_of_books_id
946 -- Bug#6350172: Modified query to fetch correct reserve values
947 AND DS.PERIOD_COUNTER = ( select max(period_counter) from fa_mc_deprn_summary
948 where asset_id = ds.asset_id
949 and book_type_code = X_dpr_row.book
950 and set_of_books_id = X_dpr_row.set_of_books_id);
951
952 -- for Sumup Assets
953 CURSOR GET_DM IS
954 SELECT 0,
955 sum(DEPRN_RESERVE),
956 sum(decode (X_RUN_MODE,
957 'DEPRN', 0,
958 DECODE(PERIOD_COUNTER,
959 h_period_counter, DEPRN_AMOUNT,
960 0))),
961 sum(NVL(REVAL_RESERVE, 0)),
962 sum(DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
963 h_fy, YTD_DEPRN,
964 0)),
965 sum(DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
966 h_fy, NVL(YTD_REVAL_DEPRN_EXPENSE, 0),
967 0)),
968 sum(decode (X_RUN_MODE,
969 'DEPRN', 0,
970 DECODE(PERIOD_COUNTER,
971 h_period_counter, NVL(REVAL_DEPRN_EXPENSE, 0),
972 0))),
973 sum(decode (X_RUN_MODE,
974 'DEPRN', 0,
975 DECODE(PERIOD_COUNTER,
976 h_period_counter, NVL(REVAL_AMORTIZATION, 0),
977 0))),
978 sum(NVL(BONUS_DEPRN_RESERVE,0)),
979 sum(DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
980 h_fy, BONUS_YTD_DEPRN,
981 0)),
982 sum(decode (X_RUN_MODE,
983 'DEPRN', 0,
984 DECODE(PERIOD_COUNTER,
985 h_period_counter, BONUS_DEPRN_AMOUNT,
986 0))),
987 sum(NVL(impairment_reserve,0)), -- bug 5951733 (added sum function)
988 sum(DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy), -- bug 5951733 (added sum function)
989 h_fy, YTD_IMPAIRMENT,
990 0)),
991 sum(decode (X_RUN_MODE, -- bug 5951733 (added sum function)
992 'DEPRN', 0,
993 DECODE(PERIOD_COUNTER,
994 h_period_counter, IMPAIRMENT_AMOUNT,
995 0))),
996 max(PERIOD_COUNTER), -- Bug#6350172
997 sum(NVL(PRODUCTION, 0)),
998 sum(DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
999 h_fy, NVL(YTD_PRODUCTION, 0),
1000 0)),
1001 sum(NVL(LTD_PRODUCTION, 0)),
1002 sum(ADJUSTED_COST),
1003 sum(NVL(REVAL_AMORTIZATION_BASIS, 0)),
1004 sum(NVL(BONUS_RATE, 0)),
1005 'DEPRN' DEPRN_SOURCE_CODE,
1006 0,
1007 sum(NVL(PRIOR_FY_EXPENSE, 0)),
1008 sum(NVL(PRIOR_FY_BONUS_EXPENSE, 0)),
1009 sum(NVL(CAPITAL_ADJUSTMENT,0)), --Bug 6666666
1010 sum(NVL(GENERAL_FUND,0)) --Bug 6666666
1011 FROM FA_DEPRN_SUMMARY DS
1012 WHERE DS.ASSET_ID in (select bk.asset_id
1013 from fa_books bk
1014 where bk.book_type_code = x_dpr_row.book
1015 and bk.transaction_header_id_out is null
1016 and bk.group_asset_id = x_dpr_row.asset_id)
1017 AND DS.BOOK_TYPE_CODE = X_dpr_row.book
1018 -- Bug#6350172: Modified query to fetch correct reserve values
1019 AND DS.PERIOD_COUNTER = ( select max(period_counter) from fa_deprn_summary
1020 where asset_id = ds.asset_id and book_type_code = X_dpr_row.book );
1021
1022
1023 BEGIN
1024
1025 X_success := FALSE;
1026
1027 -- Get the current period's counter, fiscal year, number of periods
1028 -- in fiscal year.
1029
1030 h_mesg_name := 'FA_EXP_GET_CUR_PERIOD_INFO';
1031
1032 get_period_info(
1033 X_BOOK => X_DPR_ROW.BOOK,
1034 X_CUR_PER_CTR => h_cur_per_ctr,
1035 X_CUR_FY => h_cur_fy,
1036 X_NUM_PERS_FY => h_num_pers_fy,
1037 X_SUCCESS => h_proc_success,
1038 X_CALLING_FN => 'QUERY_DEPRN_DETAIL',
1039 p_log_level_rec => p_log_level_rec);
1040
1041
1042 -- Determine current period_counter given RUN_MODE and
1043 -- period_ctr given in X_DPR_ROW
1044
1045 -- If running in DEPRN mode,
1046 -- decrement period counter to get LAST period's
1047 -- info.
1048
1049 if (X_RUN_MODE in ('DEPRN')) then
1050 h_period_counter := h_cur_per_ctr - 1;
1051 h_fy := h_cur_fy;
1052
1053 -- If period counter not given, set to current period.
1054 elsif (X_dpr_row.period_ctr = 0) then
1055 h_period_counter := h_cur_per_ctr;
1056 h_fy := h_cur_fy;
1057
1058 -- If period counter given AND not DEPRN mode, then
1059 -- need to reselect fiscal year.
1060 else
1061 h_period_counter := X_dpr_row.period_ctr;
1062 h_mesg_name := 'FA_PURGE_GET_FISCAL_YEAR';
1063 --bugfix 3666915 starts
1064 begin
1065 select fiscal_year
1066 into h_fy
1067 from fa_deprn_periods
1068 where book_type_code = X_dpr_row.book
1069 and period_counter = h_period_counter;
1070 Exception
1071 when no_data_found then
1072 null;
1073 end;
1074 --bugfix 3666915 ends
1075 end if;
1076
1077
1078 -- Retrieve row that matches current period counter.
1079 -- If such a row doesn't exist, then get row that
1080 -- matches most recent period counter.
1081
1082 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
1083
1084 if (X_dpr_row.asset_type = 'GROUP' and
1085 X_dpr_row.member_rollup_flag = 'Y') then
1086 -- for Sumup Assets
1087 if (x_dpr_row.mrc_sob_type_code = 'R') then
1088 OPEN GET_MC_DM;
1089 else
1090 OPEN GET_DM;
1091 end if;
1092 else -- non sumup assets
1093 if (x_dpr_row.mrc_sob_type_code = 'R') then
1094 OPEN GET_MC_DS;
1095 else
1096 OPEN GET_DS;
1097 end if;
1098 end if;
1099
1100
1101 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
1102
1103 if (X_dpr_row.asset_type = 'GROUP' and
1104 X_dpr_row.member_rollup_flag = 'Y') then
1105 -- for Sumup Assets
1106 if (x_dpr_row.mrc_sob_type_code = 'R') then
1107 FETCH GET_MC_DM INTO
1108 X_dpr_row.cost,
1109 X_dpr_row.deprn_rsv,
1110 X_dpr_row.deprn_exp,
1111 X_dpr_row.reval_rsv,
1112 X_dpr_row.ytd_deprn,
1113 X_dpr_row.ytd_reval_deprn_exp,
1114 X_dpr_row.reval_deprn_exp,
1115 X_dpr_row.reval_amo,
1116 X_dpr_row.bonus_deprn_rsv,
1117 X_dpr_row.bonus_ytd_deprn,
1118 X_dpr_row.bonus_deprn_amount,
1119 X_dpr_row.impairment_rsv,
1120 X_dpr_row.ytd_impairment,
1121 X_dpr_row.impairment_amount,
1122 X_found_per_ctr,
1123 X_dpr_row.prod,
1124 X_dpr_row.ytd_prod,
1125 X_dpr_row.ltd_prod,
1126 X_dpr_row.adj_cost,
1127 X_dpr_row.reval_amo_basis,
1128 X_dpr_row.bonus_rate,
1129 X_dpr_row.deprn_source_code,
1130 X_dpr_row.add_cost_to_clear,
1131 X_dpr_row.prior_fy_exp,
1132 X_dpr_row.prior_fy_bonus_exp,
1133 X_dpr_row.capital_adjustment, -- Bug 6666666
1134 X_dpr_row.general_fund; -- Bug 6666666
1135
1136 if (GET_MC_DM%NOTFOUND) then
1137
1138 X_dpr_row.cost := 0;
1139 X_dpr_row.deprn_rsv := 0;
1140 X_dpr_row.deprn_exp := 0;
1141 X_dpr_row.reval_rsv := 0;
1142 X_dpr_row.ytd_deprn := 0;
1143 X_dpr_row.ytd_reval_deprn_exp := 0;
1144 X_dpr_row.reval_deprn_exp := 0;
1145 X_dpr_row.reval_amo := 0;
1146 X_dpr_row.bonus_deprn_rsv := 0;
1147 X_dpr_row.bonus_ytd_deprn := 0;
1148 X_dpr_row.bonus_deprn_amount := 0;
1149 X_dpr_row.impairment_rsv := 0;
1150 X_dpr_row.ytd_impairment := 0;
1151 X_dpr_row.impairment_amount := 0;
1152 X_dpr_row.add_cost_to_clear := 0;
1153 X_found_per_ctr := 0;
1154 X_dpr_row.prod := 0;
1155 X_dpr_row.ytd_prod := 0;
1156 X_dpr_row.ltd_prod := 0;
1157 X_dpr_row.adj_cost := 0;
1158 X_dpr_row.reval_amo_basis := 0;
1159 X_dpr_row.bonus_rate := 0;
1160 X_dpr_row.deprn_source_code := '';
1161 X_dpr_row.prior_fy_exp := 0;
1162 X_dpr_row.prior_fy_bonus_exp := 0;
1163 X_dpr_row.capital_adjustment := 0; -- Bug 6666666
1164 X_dpr_row.general_fund := 0; -- Bug 6666666
1165
1166 end if;
1167
1168 else
1169 FETCH GET_DM INTO
1170 X_dpr_row.cost,
1171 X_dpr_row.deprn_rsv,
1172 X_dpr_row.deprn_exp,
1173 X_dpr_row.reval_rsv,
1174 X_dpr_row.ytd_deprn,
1175 X_dpr_row.ytd_reval_deprn_exp,
1176 X_dpr_row.reval_deprn_exp,
1177 X_dpr_row.reval_amo,
1178 X_dpr_row.bonus_deprn_rsv,
1179 X_dpr_row.bonus_ytd_deprn,
1180 X_dpr_row.bonus_deprn_amount,
1181 X_dpr_row.impairment_rsv,
1182 X_dpr_row.ytd_impairment,
1183 X_dpr_row.impairment_amount,
1184 X_found_per_ctr,
1185 X_dpr_row.prod,
1186 X_dpr_row.ytd_prod,
1187 X_dpr_row.ltd_prod,
1188 X_dpr_row.adj_cost,
1189 X_dpr_row.reval_amo_basis,
1190 X_dpr_row.bonus_rate,
1191 X_dpr_row.deprn_source_code,
1192 X_dpr_row.add_cost_to_clear,
1193 X_dpr_row.prior_fy_exp,
1194 X_dpr_row.prior_fy_bonus_exp,
1195 X_dpr_row.capital_adjustment, -- Bug 6666666
1196 X_dpr_row.general_fund; -- Bug 6666666
1197
1198
1199 -- If no fa_deprn_summary row exists, then return all zeroes.
1200
1201 if (GET_DM%NOTFOUND) then
1202
1203 X_dpr_row.cost := 0;
1204 X_dpr_row.deprn_rsv := 0;
1205 X_dpr_row.deprn_exp := 0;
1206 X_dpr_row.reval_rsv := 0;
1207 X_dpr_row.ytd_deprn := 0;
1208 X_dpr_row.ytd_reval_deprn_exp := 0;
1209 X_dpr_row.reval_deprn_exp := 0;
1210 X_dpr_row.reval_amo := 0;
1211 X_dpr_row.bonus_deprn_rsv := 0;
1212 X_dpr_row.bonus_ytd_deprn := 0;
1213 X_dpr_row.bonus_deprn_amount := 0;
1214 X_dpr_row.impairment_rsv := 0;
1215 X_dpr_row.ytd_impairment := 0;
1216 X_dpr_row.impairment_amount := 0;
1217 X_dpr_row.add_cost_to_clear := 0;
1218 X_found_per_ctr := 0;
1219 X_dpr_row.prod := 0;
1220 X_dpr_row.ytd_prod := 0;
1221 X_dpr_row.ltd_prod := 0;
1222 X_dpr_row.adj_cost := 0;
1223 X_dpr_row.reval_amo_basis := 0;
1224 X_dpr_row.bonus_rate := 0;
1225 X_dpr_row.deprn_source_code := '';
1226 X_dpr_row.prior_fy_exp := 0;
1227 X_dpr_row.prior_fy_bonus_exp := 0;
1228 X_dpr_row.capital_adjustment := 0; -- Bug 6666666
1229 X_dpr_row.general_fund := 0; -- Bug 6666666
1230 end if;
1231
1232 end if;
1233
1234 else -- Non Sumup assets
1235 if (x_dpr_row.mrc_sob_type_code = 'R') then
1236 FETCH GET_MC_DS INTO
1237 X_dpr_row.cost,
1238 X_dpr_row.deprn_rsv,
1239 X_dpr_row.deprn_exp,
1240 X_dpr_row.reval_rsv,
1241 X_dpr_row.ytd_deprn,
1242 X_dpr_row.ytd_reval_deprn_exp,
1243 X_dpr_row.reval_deprn_exp,
1244 X_dpr_row.reval_amo,
1245 X_dpr_row.bonus_deprn_rsv,
1246 X_dpr_row.bonus_ytd_deprn,
1247 X_dpr_row.bonus_deprn_amount,
1248 X_dpr_row.impairment_rsv,
1249 X_dpr_row.ytd_impairment,
1250 X_dpr_row.impairment_amount,
1251 X_found_per_ctr,
1252 X_dpr_row.prod,
1253 X_dpr_row.ytd_prod,
1254 X_dpr_row.ltd_prod,
1255 X_dpr_row.adj_cost,
1256 X_dpr_row.reval_amo_basis,
1257 X_dpr_row.bonus_rate,
1258 X_dpr_row.deprn_source_code,
1259 X_dpr_row.add_cost_to_clear,
1260 X_dpr_row.prior_fy_exp,
1261 X_dpr_row.prior_fy_bonus_exp,
1262 X_dpr_row.capital_adjustment, -- Bug 6666666
1263 X_dpr_row.general_fund, -- Bug 6666666
1264 X_dpr_row.reval_loss_balance;
1265
1266 if (GET_MC_DS%NOTFOUND) then
1267
1268 X_dpr_row.cost := 0;
1269 X_dpr_row.deprn_rsv := 0;
1270 X_dpr_row.deprn_exp := 0;
1271 X_dpr_row.reval_rsv := 0;
1272 X_dpr_row.ytd_deprn := 0;
1273 X_dpr_row.ytd_reval_deprn_exp := 0;
1274 X_dpr_row.reval_deprn_exp := 0;
1275 X_dpr_row.reval_amo := 0;
1276 X_dpr_row.bonus_deprn_rsv := 0;
1277 X_dpr_row.bonus_ytd_deprn := 0;
1278 X_dpr_row.bonus_deprn_amount := 0;
1279 X_dpr_row.impairment_rsv := 0;
1280 X_dpr_row.ytd_impairment := 0;
1281 X_dpr_row.impairment_amount := 0;
1282 X_dpr_row.add_cost_to_clear := 0;
1283 X_found_per_ctr := 0;
1284 X_dpr_row.prod := 0;
1285 X_dpr_row.ytd_prod := 0;
1286 X_dpr_row.ltd_prod := 0;
1287 X_dpr_row.adj_cost := 0;
1288 X_dpr_row.reval_amo_basis := 0;
1289 X_dpr_row.bonus_rate := 0;
1290 X_dpr_row.deprn_source_code := '';
1291 X_dpr_row.prior_fy_exp := 0;
1292 X_dpr_row.prior_fy_bonus_exp := 0;
1293 X_dpr_row.capital_adjustment := 0; -- Bug 6666666
1294 X_dpr_row.general_fund := 0; -- Bug 6666666
1295 X_dpr_row.reval_loss_balance := 0;
1296
1297 end if;
1298
1299 else
1300 FETCH GET_DS INTO
1301 X_dpr_row.cost,
1302 X_dpr_row.deprn_rsv,
1303 X_dpr_row.deprn_exp,
1304 X_dpr_row.reval_rsv,
1305 X_dpr_row.ytd_deprn,
1306 X_dpr_row.ytd_reval_deprn_exp,
1307 X_dpr_row.reval_deprn_exp,
1308 X_dpr_row.reval_amo,
1309 X_dpr_row.bonus_deprn_rsv,
1310 X_dpr_row.bonus_ytd_deprn,
1311 X_dpr_row.bonus_deprn_amount,
1312 X_dpr_row.impairment_rsv,
1313 X_dpr_row.ytd_impairment,
1314 X_dpr_row.impairment_amount,
1315 X_found_per_ctr,
1316 X_dpr_row.prod,
1317 X_dpr_row.ytd_prod,
1318 X_dpr_row.ltd_prod,
1319 X_dpr_row.adj_cost,
1320 X_dpr_row.reval_amo_basis,
1321 X_dpr_row.bonus_rate,
1322 X_dpr_row.deprn_source_code,
1323 X_dpr_row.add_cost_to_clear,
1324 X_dpr_row.prior_fy_exp,
1325 X_dpr_row.prior_fy_bonus_exp,
1326 X_dpr_row.capital_adjustment, -- Bug 6666666
1327 X_dpr_row.general_fund, -- Bug 6666666
1328 X_dpr_row.reval_loss_balance;
1329
1330 -- If no fa_deprn_summary row exists, then return all zeroes.
1331
1332 if (GET_DS%NOTFOUND) then
1333
1334 X_dpr_row.cost := 0;
1335 X_dpr_row.deprn_rsv := 0;
1336 X_dpr_row.deprn_exp := 0;
1337 X_dpr_row.reval_rsv := 0;
1338 X_dpr_row.ytd_deprn := 0;
1339 X_dpr_row.ytd_reval_deprn_exp := 0;
1340 X_dpr_row.reval_deprn_exp := 0;
1341 X_dpr_row.reval_amo := 0;
1342 X_dpr_row.bonus_deprn_rsv := 0;
1343 X_dpr_row.bonus_ytd_deprn := 0;
1344 X_dpr_row.bonus_deprn_amount := 0;
1345 X_dpr_row.impairment_rsv := 0;
1346 X_dpr_row.ytd_impairment := 0;
1347 X_dpr_row.impairment_amount := 0;
1348 X_dpr_row.add_cost_to_clear := 0;
1349 X_found_per_ctr := 0;
1350 X_dpr_row.prod := 0;
1351 X_dpr_row.ytd_prod := 0;
1352 X_dpr_row.ltd_prod := 0;
1353 X_dpr_row.adj_cost := 0;
1354 X_dpr_row.reval_amo_basis := 0;
1355 X_dpr_row.bonus_rate := 0;
1356 X_dpr_row.deprn_source_code := '';
1357 X_dpr_row.prior_fy_exp := 0;
1358 X_dpr_row.prior_fy_bonus_exp := 0;
1359 X_dpr_row.capital_adjustment := 0; -- Bug 6666666
1360 X_dpr_row.general_fund := 0; -- Bug 6666666
1361 X_dpr_row.reval_loss_balance := 0;
1362
1363 end if;
1364
1365 end if;
1366
1367 end if; -- (X_dpr_row.asset_type = 'GROUP' and
1368
1369 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
1370
1371 if (X_dpr_row.asset_type = 'GROUP' and
1372 X_dpr_row.member_rollup_flag = 'Y') then
1373 if (x_dpr_row.mrc_sob_type_code = 'R') then
1374 CLOSE GET_MC_DM;
1375 else
1376 CLOSE GET_DM;
1377 end if;
1378 else
1379 if (x_dpr_row.mrc_sob_type_code = 'R') then
1380 CLOSE GET_MC_DS;
1381 else
1382 CLOSE GET_DS;
1383 end if;
1384 end if;
1385
1386 X_success := TRUE;
1387
1388 EXCEPTION
1389 when others then
1390 if (p_log_level_rec.statement_level) then
1391 FA_DEBUG_PKG.ADD (
1392 fname => 'FA_QUERY_BALANCES_PKG.QUERY_DEPRN_SUMMARY',
1393 element => 'ASSET_ID',
1394 value => X_DPR_ROW.ASSET_ID, p_log_level_rec => p_log_level_rec);
1395 FA_DEBUG_PKG.ADD (
1396 fname => 'FA_QUERY_BALANCES_PKG.QUERY_DEPRN_SUMMARY',
1397 element => 'BOOK',
1398 value => X_DPR_ROW.BOOK, p_log_level_rec => p_log_level_rec);
1399 FA_DEBUG_PKG.ADD (
1400 fname => 'FA_QUERY_BALANCES_PKG.QUERY_DEPRN_SUMMARY',
1401 element => 'h_cur_per_ctr',
1402 value => h_cur_per_ctr, p_log_level_rec => p_log_level_rec);
1403 FA_DEBUG_PKG.ADD (
1404 fname => 'FA_QUERY_BALANCES_PKG.QUERY_DEPRN_SUMMARY',
1405 element => 'RUN_MODE',
1406 value => X_RUN_MODE, p_log_level_rec => p_log_level_rec);
1407 end if;
1408
1409 fa_srvr_msg.add_sql_error(
1410 calling_fn => 'FA_QUERY_BALANCES_PKG.QUERY_DEPRN_SUMMARY', p_log_level_rec => p_log_level_rec);
1411 fa_srvr_msg.add_message(
1412 calling_fn => 'FA_QUERY_BALANCES_PKG.QUERY_DEPRN_SUMMARY',
1413 name => h_mesg_name, p_log_level_rec => p_log_level_rec);
1414 fa_standard_pkg.raise_error(
1415 CALLED_FN => 'QUERY_DEPRN_SUMMARY',
1416 CALLING_FN => X_CALLING_FN,
1417 NAME => h_mesg_name, p_log_level_rec => p_log_level_rec);
1418
1419
1420 END QUERY_DEPRN_SUMMARY;
1421
1422 -----------------------------------------------------------------------
1423
1424 -- Use this procedure to query detail-level information
1425 -- from fa_deprn_detail for given distribution
1426 -- in given period (or current period if 0)
1427
1428
1429 PROCEDURE QUERY_DEPRN_DETAIL (
1430 X_DPR_ROW IN OUT NOCOPY FA_STD_TYPES.FA_DEPRN_ROW_STRUCT,
1431 X_FOUND_PER_CTR IN OUT NOCOPY NUMBER,
1432 X_IS_ACC_NULL IN OUT NOCOPY BOOLEAN,
1433 X_RUN_MODE VARCHAR2,
1434 X_SUCCESS OUT NOCOPY BOOLEAN,
1435 X_CALLING_FN VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
1436
1437 h_cur_per_ctr number;
1438 h_num_pers_fy number;
1439 h_cur_fy number;
1440 h_fy number;
1441 h_period_counter number;
1442 h_is_acc_null_num number;
1443 h_proc_success boolean;
1444
1445 h_mesg_name varchar2(30);
1446
1447
1448 -- Main select statement. Get detail-level info for given distribution
1449 -- in most recent period at or before given period.
1450 -- If RUN_MODE = 'DEPRN', return 0 for deprn_amount, reval_deprn_expense,
1451 -- and reval_amortization. Also decrement period counter tto get last
1452 -- period's info (used in depreciation program).
1453
1454 CURSOR GET_MC_DD IS
1455 SELECT NVL(COST, 0),
1456 DEPRN_RESERVE,
1457 decode (X_RUN_MODE,
1458 'DEPRN', 0,
1459 DECODE(PERIOD_COUNTER,
1460 h_period_counter, DEPRN_AMOUNT,
1461 0)),
1462 NVL(REVAL_RESERVE, 0),
1463 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
1464 h_fy, YTD_DEPRN,
1465 0),
1466 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
1467 h_fy, NVL(YTD_REVAL_DEPRN_EXPENSE, 0),
1468 0),
1469 decode (X_RUN_MODE,
1470 'DEPRN', 0,
1471 DECODE(PERIOD_COUNTER,
1472 h_period_counter, NVL(REVAL_DEPRN_EXPENSE, 0),
1473 0)),
1474 decode (X_RUN_MODE,
1475 'DEPRN', 0,
1476 DECODE(PERIOD_COUNTER,
1477 h_period_counter, NVL(REVAL_AMORTIZATION, 0),
1478 0)),
1479 NVL(BONUS_DEPRN_RESERVE,0),
1480 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
1481 h_fy, BONUS_YTD_DEPRN,
1482 0),
1483 decode (X_RUN_MODE,
1484 'DEPRN', 0,
1485 DECODE(PERIOD_COUNTER,
1486 h_period_counter, BONUS_DEPRN_AMOUNT,
1487 0)),
1488 NVL(impairment_reserve,0),
1489 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
1490 h_fy, YTD_IMPAIRMENT,
1491 0),
1492 decode (X_RUN_MODE,
1493 'DEPRN', 0,
1494 DECODE(PERIOD_COUNTER,
1495 h_period_counter, IMPAIRMENT_AMOUNT,
1496 0)),
1497 PERIOD_COUNTER,
1498 NVL(ADDITION_COST_TO_CLEAR, 0),
1499 DECODE(ADDITION_COST_TO_CLEAR,
1500 NULL, 1,
1501 0),
1502 NVL(CAPITAL_ADJUSTMENT,0), -- Bug 6666666
1503 NVL(GENERAL_FUND,0) -- Bug 6666666
1504 FROM FA_MC_DEPRN_DETAIL DD
1505 WHERE DD.ASSET_ID = X_dpr_row.asset_id
1506 AND DD.BOOK_TYPE_CODE = X_dpr_row.book
1507 AND DD.PERIOD_COUNTER <= h_period_counter
1508 AND DD.DISTRIBUTION_ID = X_dpr_row.dist_id
1509 AND DD.SET_OF_BOOKS_ID = x_dpr_row.set_of_books_id
1510 ORDER BY PERIOD_COUNTER DESC;
1511
1512 CURSOR GET_DD IS
1513 SELECT NVL(COST, 0),
1514 DEPRN_RESERVE,
1515 decode (X_RUN_MODE,
1516 'DEPRN', 0,
1517 DECODE(PERIOD_COUNTER,
1518 h_period_counter, DEPRN_AMOUNT,
1519 0)),
1520 NVL(REVAL_RESERVE, 0),
1521 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
1522 h_fy, YTD_DEPRN,
1523 0),
1524 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
1525 h_fy, NVL(YTD_REVAL_DEPRN_EXPENSE, 0),
1526 0),
1527 decode (X_RUN_MODE,
1528 'DEPRN', 0,
1529 DECODE(PERIOD_COUNTER,
1530 h_period_counter, NVL(REVAL_DEPRN_EXPENSE, 0),
1531 0)),
1532 decode (X_RUN_MODE,
1533 'DEPRN', 0,
1534 DECODE(PERIOD_COUNTER,
1535 h_period_counter, NVL(REVAL_AMORTIZATION, 0),
1536 0)),
1537 NVL(BONUS_DEPRN_RESERVE,0),
1538 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
1539 h_fy, BONUS_YTD_DEPRN,
1540 0),
1541 decode (X_RUN_MODE,
1542 'DEPRN', 0,
1543 DECODE(PERIOD_COUNTER,
1544 h_period_counter, BONUS_DEPRN_AMOUNT,
1545 0)),
1546 NVL(impairment_reserve,0),
1547 DECODE(FLOOR((PERIOD_COUNTER - 1) / h_num_pers_fy),
1548 h_fy, YTD_IMPAIRMENT,
1549 0),
1550 decode (X_RUN_MODE,
1551 'DEPRN', 0,
1552 DECODE(PERIOD_COUNTER,
1553 h_period_counter, IMPAIRMENT_AMOUNT,
1554 0)),
1555 PERIOD_COUNTER,
1556 NVL(ADDITION_COST_TO_CLEAR, 0),
1557 DECODE(ADDITION_COST_TO_CLEAR,
1558 NULL, 1,
1559 0),
1560 NVL(CAPITAL_ADJUSTMENT,0), -- Bug 6666666
1561 NVL(GENERAL_FUND,0) -- Bug 6666666
1562 FROM FA_DEPRN_DETAIL DD
1563 WHERE DD.ASSET_ID = X_dpr_row.asset_id
1564 AND DD.BOOK_TYPE_CODE = X_dpr_row.book
1565 AND DD.PERIOD_COUNTER <= h_period_counter
1566 AND DD.DISTRIBUTION_ID = X_dpr_row.dist_id
1567 ORDER BY PERIOD_COUNTER DESC;
1568
1569 BEGIN
1570
1571
1572 X_success := FALSE;
1573
1574 -- Get all the period info
1575
1576 -- Get the current period's counter, fiscal year, number of periods
1577 -- in fiscal year.
1578
1579 h_mesg_name := 'FA_EXP_GET_CUR_PERIOD_INFO';
1580
1581 get_period_info(
1582 X_BOOK => X_DPR_ROW.BOOK,
1583 X_CUR_PER_CTR => h_cur_per_ctr,
1584 X_CUR_FY => h_cur_fy,
1585 X_NUM_PERS_FY => h_num_pers_fy,
1586 X_SUCCESS => h_proc_success,
1587 X_CALLING_FN => 'QUERY_DEPRN_DETAIL',
1588 p_log_level_rec => p_log_level_rec);
1589
1590 -- Determine current period_counter given RUN_MODE and
1591 -- period_ctr given in X_DPR_ROW
1592
1593 -- If running in DEPRN mode, decrement period counter to get LAST period
1594 -- info.
1595
1596 if (X_RUN_MODE = 'DEPRN') then
1597 h_period_counter := h_cur_per_ctr - 1;
1598 h_fy := h_cur_fy;
1599
1600 -- If period counter not given, set to current period.
1601 elsif (X_dpr_row.period_ctr = 0) then
1602 h_period_counter := h_cur_per_ctr;
1603 h_fy := h_cur_fy;
1604
1605 -- If period counter given AND not DEPRN mode, then
1606 -- need to reselect fiscal year.
1607 else
1608 h_period_counter := X_dpr_row.period_ctr;
1609 h_mesg_name := 'FA_PURGE_GET_FISCAL_YEAR';
1610 --bugfix 3666915 starts
1611 begin
1612 select fiscal_year
1613 into h_fy
1614 from fa_deprn_periods
1615 where book_type_code = X_dpr_row.book
1616 and period_counter = h_period_counter;
1617 Exception
1618 when no_data_found then
1619 null;
1620 end;
1621 --bugfix 3666915 ends
1622
1623 end if;
1624
1625 -- Retrieve row that matches current period counter.
1626 -- If such a row doesn't exist, then get row that
1627 -- matches most recent period counter.
1628
1629 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
1630
1631 if (x_dpr_row.mrc_sob_type_code = 'R') then
1632 OPEN GET_MC_DD;
1633 else
1634 OPEN GET_DD;
1635 end if;
1636
1637 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
1638
1639 if (x_dpr_row.mrc_sob_type_code = 'R') then
1640 FETCH GET_MC_DD INTO
1641 X_dpr_row.cost,
1642 X_dpr_row.deprn_rsv,
1643 X_dpr_row.deprn_exp,
1644 X_dpr_row.reval_rsv,
1645 X_dpr_row.ytd_deprn,
1646 X_dpr_row.ytd_reval_deprn_exp,
1647 X_dpr_row.reval_deprn_exp,
1648 X_dpr_row.reval_amo,
1649 X_dpr_row.bonus_deprn_rsv,
1650 X_dpr_row.bonus_ytd_deprn,
1651 X_dpr_row.bonus_deprn_amount,
1652 X_dpr_row.impairment_rsv,
1653 X_dpr_row.ytd_impairment,
1654 X_dpr_row.impairment_amount,
1655 X_found_per_ctr,
1656 X_dpr_row.add_cost_to_clear,
1657 h_is_acc_null_num,
1658 X_dpr_row.capital_adjustment, -- Bug 6666666
1659 X_dpr_row.general_fund; -- Bug 6666666
1660
1661 -- If no fa_deprn_summary row exists, then return all zeroes.
1662
1663 if (GET_MC_DD%NOTFOUND) then
1664 X_dpr_row.cost := 0;
1665 X_dpr_row.deprn_rsv := 0;
1666 X_dpr_row.deprn_exp := 0;
1667 X_dpr_row.reval_rsv := 0;
1668 X_dpr_row.ytd_deprn := 0;
1669 X_dpr_row.ytd_reval_deprn_exp := 0;
1670 X_dpr_row.reval_deprn_exp := 0;
1671 X_dpr_row.reval_amo := 0;
1672 X_dpr_row.bonus_deprn_rsv := 0;
1673 X_dpr_row.bonus_ytd_deprn := 0;
1674 X_dpr_row.bonus_deprn_amount := 0;
1675 X_dpr_row.impairment_rsv := 0;
1676 X_dpr_row.ytd_impairment := 0;
1677 X_dpr_row.impairment_amount := 0;
1678 X_dpr_row.add_cost_to_clear := 0;
1679 X_found_per_ctr := 0;
1680 X_is_acc_null := TRUE;
1681 X_dpr_row.capital_adjustment := 0; -- Bug 6666666
1682 X_dpr_row.general_fund := 0; -- Bug 6666666
1683 end if;
1684
1685 else
1686 FETCH GET_DD INTO
1687 X_dpr_row.cost,
1688 X_dpr_row.deprn_rsv,
1689 X_dpr_row.deprn_exp,
1690 X_dpr_row.reval_rsv,
1691 X_dpr_row.ytd_deprn,
1692 X_dpr_row.ytd_reval_deprn_exp,
1693 X_dpr_row.reval_deprn_exp,
1694 X_dpr_row.reval_amo,
1695 X_dpr_row.bonus_deprn_rsv,
1696 X_dpr_row.bonus_ytd_deprn,
1697 X_dpr_row.bonus_deprn_amount,
1698 X_dpr_row.impairment_rsv,
1699 X_dpr_row.ytd_impairment,
1700 X_dpr_row.impairment_amount,
1701 X_found_per_ctr,
1702 X_dpr_row.add_cost_to_clear,
1703 h_is_acc_null_num,
1704 X_dpr_row.capital_adjustment, -- Bug 6666666
1705 X_dpr_row.general_fund; -- Bug 6666666
1706
1707 -- If no fa_deprn_summary row exists, then return all zeroes.
1708
1709 if (GET_DD%NOTFOUND) then
1710 X_dpr_row.cost := 0;
1711 X_dpr_row.deprn_rsv := 0;
1712 X_dpr_row.deprn_exp := 0;
1713 X_dpr_row.reval_rsv := 0;
1714 X_dpr_row.ytd_deprn := 0;
1715 X_dpr_row.ytd_reval_deprn_exp := 0;
1716 X_dpr_row.reval_deprn_exp := 0;
1717 X_dpr_row.reval_amo := 0;
1718 X_dpr_row.bonus_deprn_rsv := 0;
1719 X_dpr_row.bonus_ytd_deprn := 0;
1720 X_dpr_row.bonus_deprn_amount := 0;
1721 X_dpr_row.impairment_rsv := 0;
1722 X_dpr_row.ytd_impairment := 0;
1723 X_dpr_row.impairment_amount := 0;
1724 X_dpr_row.add_cost_to_clear := 0;
1725 X_found_per_ctr := 0;
1726 X_is_acc_null := TRUE;
1727 X_dpr_row.capital_adjustment := 0; -- Bug 6666666
1728 X_dpr_row.general_fund := 0; -- Bug 6666666
1729 end if;
1730 end if;
1731
1732
1733 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
1734 if (x_dpr_row.mrc_sob_type_code = 'R') then
1735 CLOSE GET_MC_DD;
1736 else
1737 CLOSE GET_DD;
1738 end if;
1739
1740 -- Return zeroes for production, adjusted_cost, reval_amo_basis,
1741 -- and bonus rate, as this info is kept at summary level only.
1742
1743 X_dpr_row.prod := 0;
1744 X_dpr_row.ytd_prod := 0;
1745 X_dpr_row.ltd_prod := 0;
1746 X_dpr_row.adj_cost := 0;
1747 X_dpr_row.reval_amo_basis := 0;
1748 X_dpr_row.bonus_rate := 0;
1749 X_dpr_row.deprn_source_code := '';
1750
1751 -- Indicate whether cost has been cleared.
1752 -- (is_acc_null = TRUE indicates that cost has been cleared)
1753
1754 if (not(X_is_acc_null) and h_is_acc_null_num = 1) then
1755 X_is_acc_null := TRUE;
1756 else
1757 X_is_acc_null := FALSE;
1758 end if;
1759
1760 X_success := TRUE;
1761
1762 EXCEPTION
1763 when others then
1764 if (p_log_level_rec.statement_level) then
1765 FA_DEBUG_PKG.ADD (
1766 fname => 'FA_QUERY_BALANCES_PKG.QUERY_DEPRN_DETAIL',
1767 element => 'ASSET_ID',
1768 value => X_DPR_ROW.ASSET_ID, p_log_level_rec => p_log_level_rec);
1769 FA_DEBUG_PKG.ADD (
1770 fname => 'FA_QUERY_BALANCES_PKG.QUERY_DEPRN_DETAIL',
1771 element => 'BOOK',
1772 value => X_DPR_ROW.BOOK, p_log_level_rec => p_log_level_rec);
1773 FA_DEBUG_PKG.ADD (
1774 fname => 'FA_QUERY_BALANCES_PKG.QUERY_DEPRN_DETAIL',
1775 element => 'h_cur_per_ctr',
1776 value => h_cur_per_ctr, p_log_level_rec => p_log_level_rec);
1777 FA_DEBUG_PKG.ADD (
1778 fname => 'FA_QUERY_BALANCES_PKG.QUERY_DEPRN_DETAIL',
1779 element => 'RUN_MODE',
1780 value => X_RUN_MODE, p_log_level_rec => p_log_level_rec);
1781 end if;
1782 fa_srvr_msg.add_sql_error(
1783 calling_fn => 'FA_QUERY_BALANCES_PKG.QUERY_DEPRN_DETAIL', p_log_level_rec => p_log_level_rec);
1784 fa_srvr_msg.add_message(
1785 calling_fn => 'FA_QUERY_BALANCES_PKG.QUERY_DEPRN_DETAIL',
1786 name => h_mesg_name, p_log_level_rec => p_log_level_rec);
1787 fa_standard_pkg.raise_error(
1788 CALLED_FN => 'QUERY_DEPRN_DETAIL',
1789 CALLING_FN => X_CALLING_FN,
1790 NAME => h_mesg_name, p_log_level_rec => p_log_level_rec);
1791
1792
1793 END QUERY_DEPRN_DETAIL;
1794
1795 -----------------------------------------------------------------------------------
1796
1797
1798 -- Get info for any adjustments that occurred after the creation of
1799 -- the deprn row from which we selected financial info.
1800
1801 PROCEDURE GET_ADJUSTMENTS_INFO (
1802 X_ADJ_ROW IN OUT NOCOPY FA_STD_TYPES.FA_DEPRN_ROW_STRUCT,
1803 X_FOUND_PER_CTR IN OUT NOCOPY NUMBER,
1804 X_RUN_MODE VARCHAR2,
1805 X_TRANSACTION_HEADER_ID NUMBER,
1806 X_SUCCESS OUT NOCOPY BOOLEAN,
1807 X_CALLING_FN VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
1808
1809 h_found_per_ctr number;
1810 h_num_adjs number;
1811 h_mode_str varchar2(8);
1812 h_asset_clearing_adjs number;
1813 h_cip_clearing_adjs number;
1814 h_book_class varchar2(15);
1815
1816 h_mesg_name varchar2(30);
1817
1818 BEGIN
1819
1820
1821 X_success := FALSE;
1822
1823 -- If period counter not set, then set to current period.
1824
1825 if (nvl(X_ADJ_ROW.period_ctr,0) = 0) then
1826
1827 h_mesg_name := 'FA_EXP_GET_CUR_PERIOD_INFO';
1828
1829 X_ADJ_ROW.period_ctr := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
1830 end if;
1831
1832 -- If running in ADJUSTED mode (querying from TAX book) then we're
1833 -- also interested in tax book's deprn adjustments. If in
1834 -- STANDARD or DEPRN mode, then we want to disregard these.
1835
1836 if (X_RUN_MODE = 'ADJUSTED') then
1837 h_mode_str := 'ADJUSTED';
1838 else
1839 h_mode_str := 'STANDARD';
1840 end if;
1841
1842 -- Get book class
1843 h_book_class := fa_cache_pkg.fazcbc_record.book_class;
1844
1845 -- Look for adjustments... sum the amounts under each
1846 -- adjustment type.
1847 -- bonus Implemented.
1848 h_mesg_name := 'FA_AMT_SEL_AJ';
1849
1850 if (X_adj_row.asset_type = 'GROUP' and
1851 X_adj_row.member_rollup_flag = 'Y') then
1852 if (x_adj_row.mrc_sob_type_code = 'R') then
1853 SELECT NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
1854 'COST',
1855 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1856 'DR', ADJ.ADJUSTMENT_AMOUNT,
1857 'CR',-1*ADJ.ADJUSTMENT_AMOUNT),
1858 'CIP COST',
1859 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1860 'DR', ADJ.ADJUSTMENT_AMOUNT,
1861 'CR',-1*ADJ.ADJUSTMENT_AMOUNT))),
1862 0),
1863 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
1864 'RESERVE',
1865 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1866 'DR', ADJ.ADJUSTMENT_AMOUNT,
1867 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
1868 0),
1869 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
1870 'EXPENSE',
1871 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1872 'DR', ADJ.ADJUSTMENT_AMOUNT,
1873 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
1874 0),
1875 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
1876 'BONUS RESERVE',
1877 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1878 'DR', ADJ.ADJUSTMENT_AMOUNT,
1879 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
1880 0),
1881 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
1882 'BONUS EXPENSE',
1883 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1884 'DR', ADJ.ADJUSTMENT_AMOUNT,
1885 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
1886 0),
1887 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
1888 'IMPAIR RESERVE',
1889 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1890 'DR', ADJ.ADJUSTMENT_AMOUNT,
1891 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
1892 0),
1893 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
1894 'IMPAIR EXPENSE',
1895 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1896 'DR', ADJ.ADJUSTMENT_AMOUNT,
1897 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT),
1898 'LINK IMPAIR EXP',
1899 DECODE(ADJ.DEBIT_CREDIT_FLAG, /*For Bug 7460979, we need to Consider LINK IMP EXP while calculating YTD_IMPAIRMENT*/
1900 'DR', ADJ.ADJUSTMENT_AMOUNT,
1901 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
1902 0),
1903 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
1904 'DEPRN ADJUST',
1905 DECODE(h_book_class,'TAX',
1906 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1907 'DR', ADJ.ADJUSTMENT_AMOUNT,
1908 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT)))),
1909 0),
1910 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
1911 'REVAL EXPENSE',
1912 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1913 'DR', ADJ.ADJUSTMENT_AMOUNT,
1914 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
1915 0),
1916 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
1917 'REVAL AMORT',
1918 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1919 'DR', ADJ.ADJUSTMENT_AMOUNT,
1920 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
1921 0),
1922 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
1923 'REVAL RESERVE',
1924 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1925 'DR', ADJ.ADJUSTMENT_AMOUNT,
1926 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
1927 0),
1928 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
1929 'ASSET CLEARING',
1930 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1931 'DR', ADJ.ADJUSTMENT_AMOUNT,
1932 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
1933 0),
1934 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
1935 'CIP CLEARING',
1936 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1937 'DR', ADJ.ADJUSTMENT_AMOUNT,
1938 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
1939 0),
1940 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE, -- Bug 6666666
1941 'CAPITAL ADJ',
1942 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1943 'DR', ADJ.ADJUSTMENT_AMOUNT,
1944 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
1945 0),
1946 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE, -- Bug 6666666
1947 'GENERAL FUND',
1948 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1949 'CR', ADJ.ADJUSTMENT_AMOUNT,
1950 'DR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
1951 0),
1952 COUNT(*)
1953 INTO X_ADJ_ROW.cost,
1954 X_ADJ_ROW.deprn_rsv,
1955 X_ADJ_ROW.deprn_exp,
1956 X_ADJ_ROW.bonus_deprn_rsv,
1957 X_ADJ_ROW.bonus_deprn_amount,
1958 X_ADJ_ROW.impairment_rsv,
1959 X_ADJ_ROW.impairment_amount,
1960 X_ADJ_ROW.deprn_adjust_exp,
1961 X_ADJ_ROW.reval_deprn_exp,
1962 X_ADJ_ROW.reval_amo,
1963 X_ADJ_ROW.reval_rsv,
1964 h_asset_clearing_adjs,
1965 h_cip_clearing_adjs,
1966 X_ADJ_ROW.capital_adjustment, --Bug 6666666
1967 X_ADJ_ROW.general_fund, --Bug 6666666
1968 h_num_adjs
1969 FROM FA_MC_ADJUSTMENTS ADJ
1970 WHERE ADJ.ASSET_ID in (select bk.asset_id
1971 from fa_mc_books bk
1972 where bk.book_type_code = x_adj_row.book
1973 and bk.transaction_header_id_out is null
1974 and bk.group_asset_id = x_adj_row.asset_id
1975 and bk.set_of_books_id = x_adj_row.set_of_books_id) AND
1976 ADJ.BOOK_TYPE_CODE = X_ADJ_ROW.book AND
1977 ADJ.PERIOD_COUNTER_CREATED > X_found_per_ctr AND
1978 DECODE(h_book_class,
1979 'TAX', ADJ.PERIOD_COUNTER_ADJUSTED,
1980 ADJ.PERIOD_COUNTER_CREATED)
1981 <= X_ADJ_ROW.period_ctr AND
1982 DECODE(X_transaction_header_id,-1,transaction_header_id,
1983 X_transaction_header_id) >= transaction_header_id AND
1984 ADJ.DISTRIBUTION_ID =
1985 DECODE(X_ADJ_ROW.dist_id,
1986 0, ADJ.DISTRIBUTION_ID,
1987 X_ADJ_ROW.dist_id) AND
1988 ADJ.SET_OF_BOOKS_ID = X_ADJ_ROW.set_of_books_id;
1989
1990
1991 else
1992 SELECT NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
1993 'COST',
1994 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1995 'DR', ADJ.ADJUSTMENT_AMOUNT,
1996 'CR',-1*ADJ.ADJUSTMENT_AMOUNT),
1997 'CIP COST',
1998 DECODE(ADJ.DEBIT_CREDIT_FLAG,
1999 'DR', ADJ.ADJUSTMENT_AMOUNT,
2000 'CR',-1*ADJ.ADJUSTMENT_AMOUNT))),
2001 0),
2002 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2003 'RESERVE',
2004 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2005 'DR', ADJ.ADJUSTMENT_AMOUNT,
2006 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2007 0),
2008 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2009 'EXPENSE',
2010 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2011 'DR', ADJ.ADJUSTMENT_AMOUNT,
2012 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2013 0),
2014 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2015 'BONUS RESERVE',
2016 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2017 'DR', ADJ.ADJUSTMENT_AMOUNT,
2018 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2019 0),
2020 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2021 'BONUS EXPENSE',
2022 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2023 'DR', ADJ.ADJUSTMENT_AMOUNT,
2024 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2025 0),
2026 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2027 'IMPAIR RESERVE',
2028 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2029 'DR', ADJ.ADJUSTMENT_AMOUNT,
2030 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2031 0),
2032 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2033 'IMPAIR EXPENSE',
2034 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2035 'DR', ADJ.ADJUSTMENT_AMOUNT,
2036 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT),
2037 'LINK IMPAIR EXP',
2038 DECODE(ADJ.DEBIT_CREDIT_FLAG, /*For Bug 7460979, we need to Consider LINK IMP EXP while calculating YTD_IMPAIRMENT*/
2039 'DR', ADJ.ADJUSTMENT_AMOUNT,
2040 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2041 0),
2042 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2043 'DEPRN ADJUST',
2044 DECODE(h_book_class,'TAX',
2045 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2046 'DR', ADJ.ADJUSTMENT_AMOUNT,
2047 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT)))),
2048 0),
2049 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2050 'REVAL EXPENSE',
2051 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2052 'DR', ADJ.ADJUSTMENT_AMOUNT,
2053 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2054 0),
2055 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2056 'REVAL AMORT',
2057 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2058 'DR', ADJ.ADJUSTMENT_AMOUNT,
2059 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2060 0),
2061 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2062 'REVAL RESERVE',
2063 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2064 'DR', ADJ.ADJUSTMENT_AMOUNT,
2065 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2066 0),
2067 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2068 'ASSET CLEARING',
2069 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2070 'DR', ADJ.ADJUSTMENT_AMOUNT,
2071 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2072 0),
2073 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2074 'CIP CLEARING',
2075 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2076 'DR', ADJ.ADJUSTMENT_AMOUNT,
2077 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2078 0),
2079 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE, -- Bug 6666666
2080 'CAPITAL ADJ',
2081 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2082 'DR', ADJ.ADJUSTMENT_AMOUNT,
2083 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2084 0),
2085 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE, -- Bug 6666666
2086 'GENERAL FUND',
2087 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2088 'CR', ADJ.ADJUSTMENT_AMOUNT,
2089 'DR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2090 0),
2091 COUNT(*)
2092 INTO X_ADJ_ROW.cost,
2093 X_ADJ_ROW.deprn_rsv,
2094 X_ADJ_ROW.deprn_exp,
2095 X_ADJ_ROW.bonus_deprn_rsv,
2096 X_ADJ_ROW.bonus_deprn_amount,
2097 X_ADJ_ROW.impairment_rsv,
2098 X_ADJ_ROW.impairment_amount,
2099 X_ADJ_ROW.deprn_adjust_exp,
2100 X_ADJ_ROW.reval_deprn_exp,
2101 X_ADJ_ROW.reval_amo,
2102 X_ADJ_ROW.reval_rsv,
2103 h_asset_clearing_adjs,
2104 h_cip_clearing_adjs,
2105 X_ADJ_ROW.capital_adjustment, --Bug 6666666
2106 X_ADJ_ROW.general_fund, --Bug 6666666
2107 h_num_adjs
2108 FROM FA_ADJUSTMENTS ADJ
2109 WHERE ADJ.ASSET_ID in (select bk.asset_id
2110 from fa_books bk
2111 where bk.book_type_code = x_adj_row.book
2112 and bk.transaction_header_id_out is null
2113 and bk.group_asset_id = x_adj_row.asset_id) AND
2114 ADJ.BOOK_TYPE_CODE = X_ADJ_ROW.book AND
2115 ADJ.PERIOD_COUNTER_CREATED > X_found_per_ctr AND
2116 DECODE(h_book_class,
2117 'TAX', ADJ.PERIOD_COUNTER_ADJUSTED,
2118 ADJ.PERIOD_COUNTER_CREATED)
2119 <= X_ADJ_ROW.period_ctr AND
2120 DECODE(X_transaction_header_id,-1,transaction_header_id,
2121 X_transaction_header_id) >= transaction_header_id AND
2122 ADJ.DISTRIBUTION_ID =
2123 DECODE(X_ADJ_ROW.dist_id,
2124 0, ADJ.DISTRIBUTION_ID,
2125 X_ADJ_ROW.dist_id);
2126
2127 end if;
2128
2129
2130 else -- non sumup assets
2131 if (x_adj_row.mrc_sob_type_code = 'R') then
2132 SELECT NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2133 'COST',
2134 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2135 'DR', ADJ.ADJUSTMENT_AMOUNT,
2136 'CR',-1*ADJ.ADJUSTMENT_AMOUNT),
2137 'CIP COST',
2138 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2139 'DR', ADJ.ADJUSTMENT_AMOUNT,
2140 'CR',-1*ADJ.ADJUSTMENT_AMOUNT))),
2141 0),
2142 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2143 'RESERVE',
2144 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2145 'DR', ADJ.ADJUSTMENT_AMOUNT,
2146 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2147 0),
2148 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2149 'EXPENSE',
2150 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2151 'DR', ADJ.ADJUSTMENT_AMOUNT,
2152 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2153 0),
2154 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2155 'BONUS RESERVE',
2156 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2157 'DR', ADJ.ADJUSTMENT_AMOUNT,
2158 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2159 0),
2160 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2161 'BONUS EXPENSE',
2162 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2163 'DR', ADJ.ADJUSTMENT_AMOUNT,
2164 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2165 0),
2166 /*8520733 to return correct impairment reserve*/
2167 NVL(SUM(DECODE(ADJ.source_type_code,
2168 'DEPRECIATION',
2169 DECODE(ADJ.ADJUSTMENT_TYPE,
2170 'IMPAIR EXPENSE',
2171 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2172 'CR', ADJ.ADJUSTMENT_AMOUNT,
2173 'DR', -1 * ADJ.ADJUSTMENT_AMOUNT),0),
2174 DECODE(ADJ.ADJUSTMENT_TYPE,
2175 'IMPAIR RESERVE',
2176 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2177 'DR', ADJ.ADJUSTMENT_AMOUNT,
2178 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT)))),
2179 0),
2180 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2181 'IMPAIR EXPENSE',
2182 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2183 'DR', ADJ.ADJUSTMENT_AMOUNT,
2184 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT),
2185 'LINK IMPAIR EXP',
2186 DECODE(ADJ.DEBIT_CREDIT_FLAG, /*For Bug 7460979, we need to Consider LINK IMP EXP while calculating YTD_IMPAIRMENT*/
2187 'DR', ADJ.ADJUSTMENT_AMOUNT,
2188 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2189 0),
2190 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2191 'DEPRN ADJUST',
2192 DECODE(h_book_class,'TAX',
2193 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2194 'DR', ADJ.ADJUSTMENT_AMOUNT,
2195 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT)))),
2196 0),
2197 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2198 'REVAL EXPENSE',
2199 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2200 'DR', ADJ.ADJUSTMENT_AMOUNT,
2201 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2202 0),
2203 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2204 'REVAL AMORT',
2205 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2206 'DR', ADJ.ADJUSTMENT_AMOUNT,
2207 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2208 0),
2209 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2210 'REVAL RESERVE',
2211 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2212 'DR', ADJ.ADJUSTMENT_AMOUNT,
2213 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2214 0),
2215 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2216 'ASSET CLEARING',
2217 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2218 'DR', ADJ.ADJUSTMENT_AMOUNT,
2219 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2220 0),
2221 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2222 'CIP CLEARING',
2223 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2224 'DR', ADJ.ADJUSTMENT_AMOUNT,
2225 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2226 0),
2227 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE, -- Bug 6666666
2228 'CAPITAL ADJ',
2229 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2230 'DR', ADJ.ADJUSTMENT_AMOUNT,
2231 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2232 0),
2233 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE, -- Bug 6666666
2234 'GENERAL FUND',
2235 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2236 'CR', ADJ.ADJUSTMENT_AMOUNT,
2237 'DR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2238 0),
2239 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2240 'REVAL LOSS',
2241 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2242 'DR', ADJ.ADJUSTMENT_AMOUNT,
2243 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2244 0),
2245 COUNT(*)
2246 INTO X_ADJ_ROW.cost,
2247 X_ADJ_ROW.deprn_rsv,
2248 X_ADJ_ROW.deprn_exp,
2249 X_ADJ_ROW.bonus_deprn_rsv,
2250 X_ADJ_ROW.bonus_deprn_amount,
2251 X_ADJ_ROW.impairment_rsv,
2252 X_ADJ_ROW.impairment_amount,
2253 X_ADJ_ROW.deprn_adjust_exp,
2254 X_ADJ_ROW.reval_deprn_exp,
2255 X_ADJ_ROW.reval_amo,
2256 X_ADJ_ROW.reval_rsv,
2257 h_asset_clearing_adjs,
2258 h_cip_clearing_adjs,
2259 X_ADJ_ROW.capital_adjustment, --Bug 6666666
2260 X_ADJ_ROW.general_fund, --Bug 6666666
2261 X_ADJ_ROW.reval_loss_balance,
2262 h_num_adjs
2263 FROM FA_MC_ADJUSTMENTS ADJ
2264 WHERE ADJ.ASSET_ID = X_ADJ_ROW.asset_id AND
2265 ADJ.BOOK_TYPE_CODE = X_ADJ_ROW.book AND
2266 ADJ.PERIOD_COUNTER_CREATED > X_found_per_ctr AND
2267 DECODE(h_book_class,
2268 'TAX', ADJ.PERIOD_COUNTER_ADJUSTED,
2269 ADJ.PERIOD_COUNTER_CREATED)
2270 <= X_ADJ_ROW.period_ctr AND
2271 DECODE(X_transaction_header_id,-1,transaction_header_id,
2272 X_transaction_header_id) >= transaction_header_id AND
2273 ADJ.DISTRIBUTION_ID =
2274 DECODE(X_ADJ_ROW.dist_id,
2275 0, ADJ.DISTRIBUTION_ID,
2276 X_ADJ_ROW.dist_id) AND
2277 ADJ.SET_OF_BOOKS_ID = X_ADJ_ROW.set_of_books_id;
2278
2279
2280 else
2281 SELECT NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2282 'COST',
2283 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2284 'DR', ADJ.ADJUSTMENT_AMOUNT,
2285 'CR',-1*ADJ.ADJUSTMENT_AMOUNT),
2286 'CIP COST',
2287 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2288 'DR', ADJ.ADJUSTMENT_AMOUNT,
2289 'CR',-1*ADJ.ADJUSTMENT_AMOUNT))),
2290 0),
2291 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2292 'RESERVE',
2293 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2294 'DR', ADJ.ADJUSTMENT_AMOUNT,
2295 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2296 0),
2297 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2298 'EXPENSE',
2299 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2300 'DR', ADJ.ADJUSTMENT_AMOUNT,
2301 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2302 0),
2303 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2304 'BONUS RESERVE',
2305 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2306 'DR', ADJ.ADJUSTMENT_AMOUNT,
2307 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2308 0),
2309 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2310 'BONUS EXPENSE',
2311 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2312 'DR', ADJ.ADJUSTMENT_AMOUNT,
2313 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2314 0),
2315 /*8520733 to return correct impairment reserve*/
2316 NVL(SUM(DECODE(ADJ.source_type_code,
2317 'DEPRECIATION',
2318 DECODE(ADJ.ADJUSTMENT_TYPE,
2319 'IMPAIR EXPENSE',
2320 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2321 'CR', ADJ.ADJUSTMENT_AMOUNT,
2322 'DR', -1 * ADJ.ADJUSTMENT_AMOUNT),0),
2323 DECODE(ADJ.ADJUSTMENT_TYPE,
2324 'IMPAIR RESERVE',
2325 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2326 'DR', ADJ.ADJUSTMENT_AMOUNT,
2327 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT)))),
2328 0),
2329 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2330 'IMPAIR EXPENSE',
2331 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2332 'DR', ADJ.ADJUSTMENT_AMOUNT,
2333 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT),
2334 'LINK IMPAIR EXP',
2335 DECODE(ADJ.DEBIT_CREDIT_FLAG, /*For Bug 7460979, we need to Consider LINK IMP EXP while calculating YTD_IMPAIRMENT*/
2336 'DR', ADJ.ADJUSTMENT_AMOUNT,
2337 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2338 0),
2339 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2340 'DEPRN ADJUST',
2341 DECODE(h_book_class,'TAX',
2342 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2343 'DR', ADJ.ADJUSTMENT_AMOUNT,
2344 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT)))),
2345 0),
2346 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2347 'REVAL EXPENSE',
2348 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2349 'DR', ADJ.ADJUSTMENT_AMOUNT,
2350 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2351 0),
2352 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2353 'REVAL AMORT',
2354 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2355 'DR', ADJ.ADJUSTMENT_AMOUNT,
2356 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2357 0),
2358 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2359 'REVAL RESERVE',
2360 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2361 'DR', ADJ.ADJUSTMENT_AMOUNT,
2362 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2363 0),
2364 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2365 'ASSET CLEARING',
2366 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2367 'DR', ADJ.ADJUSTMENT_AMOUNT,
2368 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2369 0),
2370 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2371 'CIP CLEARING',
2372 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2373 'DR', ADJ.ADJUSTMENT_AMOUNT,
2374 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2375 0),
2376 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE, -- Bug 6666666
2377 'CAPITAL ADJ',
2378 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2379 'DR', ADJ.ADJUSTMENT_AMOUNT,
2380 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2381 0),
2382 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE, -- Bug 6666666
2383 'GENERAL FUND',
2384 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2385 'CR', ADJ.ADJUSTMENT_AMOUNT,
2386 'DR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2387 0),
2388 NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
2389 'REVAL LOSS',
2390 DECODE(ADJ.DEBIT_CREDIT_FLAG,
2391 'DR', ADJ.ADJUSTMENT_AMOUNT,
2392 'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
2393 0),
2394 COUNT(*)
2395 INTO X_ADJ_ROW.cost,
2396 X_ADJ_ROW.deprn_rsv,
2397 X_ADJ_ROW.deprn_exp,
2398 X_ADJ_ROW.bonus_deprn_rsv,
2399 X_ADJ_ROW.bonus_deprn_amount,
2400 X_ADJ_ROW.impairment_rsv,
2401 X_ADJ_ROW.impairment_amount,
2402 X_ADJ_ROW.deprn_adjust_exp,
2403 X_ADJ_ROW.reval_deprn_exp,
2404 X_ADJ_ROW.reval_amo,
2405 X_ADJ_ROW.reval_rsv,
2406 h_asset_clearing_adjs,
2407 h_cip_clearing_adjs,
2408 X_ADJ_ROW.capital_adjustment, --Bug 6666666
2409 X_ADJ_ROW.general_fund, --Bug 6666666
2410 X_ADJ_ROW.reval_loss_balance,
2411 h_num_adjs
2412 FROM FA_ADJUSTMENTS ADJ
2413 WHERE ADJ.ASSET_ID = X_ADJ_ROW.asset_id AND
2414 ADJ.BOOK_TYPE_CODE = X_ADJ_ROW.book AND
2415 ADJ.PERIOD_COUNTER_CREATED > X_found_per_ctr AND
2416 DECODE(h_book_class,
2417 'TAX', ADJ.PERIOD_COUNTER_ADJUSTED,
2418 ADJ.PERIOD_COUNTER_CREATED)
2419 <= X_ADJ_ROW.period_ctr AND
2420 DECODE(X_transaction_header_id,-1,transaction_header_id,
2421 X_transaction_header_id) >= transaction_header_id AND
2422 ADJ.DISTRIBUTION_ID =
2423 DECODE(X_ADJ_ROW.dist_id,
2424 0, ADJ.DISTRIBUTION_ID,
2425 X_ADJ_ROW.dist_id);
2426
2427 end if;
2428 end if;
2429
2430
2431
2432 -- Interested only in total cost-to-clear, regardless of whether it's
2433 -- CIP or capitalized.
2434
2435 X_ADJ_ROW.add_cost_to_clear := h_asset_clearing_adjs + h_cip_clearing_adjs;
2436
2437 -- Indicate if any adjustments were encountered.
2438
2439 if (h_num_adjs <> 0) then
2440 X_ADJ_ROW.adjusted_flag := TRUE;
2441 else
2442 X_ADJ_ROW.adjusted_flag := FALSE;
2443 end if;
2444
2445 X_success := TRUE;
2446
2447 EXCEPTION
2448 when others then
2449 if (SQL%NOTFOUND) then
2450 X_ADJ_ROW.cost := 0;
2451 X_ADJ_ROW.deprn_rsv := 0;
2452 X_ADJ_ROW.deprn_adjust_exp := 0;
2453 X_ADJ_ROW.reval_deprn_exp := 0;
2454 X_ADJ_ROW.bonus_deprn_rsv := 0;
2455 X_ADJ_ROW.bonus_deprn_amount := 0;
2456 X_ADJ_ROW.reval_amo := 0;
2457 X_ADJ_ROW.reval_rsv := 0;
2458 X_ADJ_ROW.reval_loss_balance := 0;
2459 h_num_adjs := 0;
2460 X_success := TRUE;
2461 else
2462 if (p_log_level_rec.statement_level) then
2463 FA_DEBUG_PKG.ADD (
2464 fname => 'FA_QUERY_BALANCES_PKG.GET_ADJUSTMENTS_INFO',
2465 element => 'FOUND_PER_CTR',
2466 value => X_FOUND_PER_CTR, p_log_level_rec => p_log_level_rec);
2467 FA_DEBUG_PKG.ADD (
2468 fname => 'FA_QUERY_BALANCES_PKG.GET_ADJUSTMENTS_INFO',
2469 element => 'RUN_MODE',
2470 value => h_mode_str, p_log_level_rec => p_log_level_rec);
2471 FA_DEBUG_PKG.ADD (
2472 fname => 'FA_QUERY_BALANCES_PKG.GET_ADJUSTMENTS_INFO',
2473 element => 'ASSET_ID',
2474 value => X_ADJ_ROW.ASSET_ID, p_log_level_rec => p_log_level_rec);
2475 FA_DEBUG_PKG.ADD (
2476 fname => 'FA_QUERY_BALANCES_PKG.GET_ADJUSTMENTS_INFO',
2477 element => 'BOOK',
2478 value => X_ADJ_ROW.BOOK, p_log_level_rec => p_log_level_rec);
2479 FA_DEBUG_PKG.ADD (
2480 fname => 'FA_QUERY_BALANCES_PKG.GET_ADJUSTMENTS_INFO',
2481 element => 'DIST_ID',
2482 value => X_ADJ_ROW.DIST_ID, p_log_level_rec => p_log_level_rec);
2483 FA_DEBUG_PKG.ADD (
2484 fname => 'FA_QUERY_BALANCES_PKG.GET_ADJUSTMENTS_INFO',
2485 element => 'TRANSACTION_HEADER_ID',
2486 value => X_TRANSACTION_HEADER_ID, p_log_level_rec => p_log_level_rec);
2487 end if;
2488 fa_srvr_msg.add_sql_error(
2489 calling_fn => 'FA_QUERY_BALANCES_PKG.GET_ADJUSTMENTS_INFO', p_log_level_rec => p_log_level_rec);
2490 fa_srvr_msg.add_message(
2491 calling_fn => 'FA_QUERY_BALANCES_PKG.GET_ADJUSTMENTS_INFO',
2492 name => h_mesg_name, p_log_level_rec => p_log_level_rec);
2493 fa_standard_pkg.raise_error(
2494 CALLED_FN => 'GET_ADJUSTMENTS_INFO',
2495 CALLING_FN => X_CALLING_FN,
2496 NAME => 'FA_QADD_DET_ADJS', p_log_level_rec => p_log_level_rec);
2497 end if;
2498
2499 END GET_ADJUSTMENTS_INFO;
2500
2501 END FA_QUERY_BALANCES_PKG;