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