[Home] [Help]
PACKAGE BODY: APPS.FARX_BL
Source
1 PACKAGE BODY FARX_BL as
2 /* $Header: farxblb.pls 120.42.12020000.4 2013/04/09 11:10:55 deemitta ship $ */
3
4
5 -- balances_reports is Intended as private function...
6 -- please do not call directly.
7 -- Performs all report activities, given book, period range,
8 -- and report type as parameters. Adj_Mode is currently not used.
9
10 -- General Overview: Consists of two main steps:
11 -- 1. Insert info into temporary table FA_BALANCES_REPORT_GT:
12 -- A. Beginning balances for all accounts (subdivided by assets)
13 -- B. Ending balances
14 -- C. Effects of depreciation/amortization
15 -- D. Effects of transactional adjustments
16 -- 2. Select from this table in a more reportable format, insert
17 -- into interface table.
18 --
19 --
20
21 procedure balances_reports
22 (Book in varchar2,
23 Start_Period_Name in varchar2,
24 End_Period_Name in varchar2,
25 Report_Type in varchar2,
26 Adj_Mode in varchar2,
27 sob_id in varchar2 default NULL, -- MRC
28 Report_Style in varchar2,
29 Request_id in number,
30 user_id in number,
31 calling_fn in varchar2,
32 mesg out nocopy varchar2,
33 success out nocopy boolean)
34
35 is
36 Period1_PC number;
37 Period1_POD date;
38 Period1_PCD date;
39 Period2_PC number;
40 Period2_PCD date;
41 Distribution_Source_Book varchar2(30);
42 Balance_Type varchar2(2);
43 bal_seg varchar2(25);
44 cost_seg varchar2(25);
45 acct_seg varchar2(25);
46 acct_flex_structure number;
47 acct_all_segs fa_rx_shared_pkg.Seg_Array;
48 aj_acct_all_segs fa_rx_shared_pkg.Seg_Array;
49 seg_no number;
50 n_segs number;
51 n_ajsegs number;
52 gl_balancing_seg number;
53 gl_account_seg number;
54 fa_cost_ctr_seg number;
55
56 h_concat_key varchar2(500);
57 h_concat_cat varchar2(500);
58
59
60 h_login_id number;
61 h_request_id number;
62
63 h_ccid number;
64 h_account FA_BALANCES_REPORT_GT.Category_Books_Account%TYPE; --bug 8432604
65 h_ajccid number;
66 h_asset FA_ADDITIONS.Asset_Number%TYPE; --bug 8432604
67 h_tag_number FA_ADDITIONS.tag_number%TYPE; --bug 8432604
68 h_serial_number FA_ADDITIONS.serial_number%TYPE; --bug 8432604
69 h_inventorial FA_ADDITIONS.inventorial%TYPE; --bug 8432604
70 h_description FA_ADDITIONS.description%TYPE; --bug 8432604
71 h_begin number;
72 h_end number;
73 h_addition number;
74 h_adjustment number;
75 h_reclass number;
76 h_retirement number;
77 h_revaluation number;
78 h_transfer number;
79 h_depreciation number;
80 /* SORP2 */
81 h_sorp_enabled_flag FA_BOOK_CONTROLS.sorp_enabled_flag%TYPE;
82 /* END SORP2 */
83 h_capitalization number;
84 h_tax number;
85 h_out_of_bal_flag varchar2(1);
86
87 h_ccid_error number;
88 h_br_ajccid number;
89 h_br_account varchar2(15);
90 h_br_rowid rowid;
91
92 h_nonqual_col_name varchar2(30);
93 h_nonqual_seg_name varchar2(30);
94 h_nonqual_seg_num varchar2(30);
95
96 h_nq_col_names fa_rx_shared_pkg.Seg_Array;
97 h_nq_seg_names fa_rx_shared_pkg.Seg_Array;
98 h_nq_seg_nums fa_rx_shared_pkg.Seg_Array;
99 h_ctr number;
100
101 get_segments_success boolean;
102
103 get_segments_failure exception;
104
105
106 h_mesg_name varchar2(50);
107 h_mesg_str varchar2(2000);
108 h_table_token varchar2(30);
109 h_flex_error varchar2(30);
110
111 -- Additional variables added for globalization -- statutory reporting requirements
112
113 h_key_flex_struct number; /* StatReq */
114 h_cat_flex_struct number; /* StatReq */
115
116 h_asset_key_ccid number;
117 h_category_id number; /* StatReq */
118
119 h_key_segs fa_rx_shared_pkg.Seg_Array; /* StatReq */
120 h_cat_segs fa_rx_shared_pkg.Seg_Array; /* StatReq */
121
122 h_asset_id FA_ADDITIONS.asset_id%TYPE; /* StatReq */
123 h_stl_depreciation_rate NUMBER; /* StatReq */
124 h_date_placed_in_service DATE; /* StatReq */
125 h_life_in_months NUMBER; /* StatReq */
126 h_account_description VARCHAR2(500); /* StatReq */
127
128 h_short_account_description VARCHAR2(240);
129 h_short_location VARCHAR2(500);
130 h_shortconcat_key VARCHAR2(240);
131 h_stl_method_flag FA_METHODS.stl_method_flag%TYPE; --bug8432604 /* StatReq */
132 h_rate_source_rule FA_METHODS.rate_source_rule%TYPE; --bug8432604 /* StatReq */
133
134 h_method_code FA_METHODS.method_code%TYPE; --bug8432604 /* StatReq */
135
136 h_Cost_Account FA_BALANCES_REPORT_GT.Cost_Account%TYPE; --bug8432604 /* StatReq */
137 h_Cost_Begin_Balance NUMBER; /* StatReq */
138 h_location VARCHAR2(1500); /* StatReq */
139
140 -- bug 5944006 (Increased length to 1500 from 500 for following variables)
141 h_invoice_number VARCHAR2(1500); /* StatReq */
142 h_invoice_descr VARCHAR2(1500); /* StatReq */
143 h_vendor_name VARCHAR2(1500); /* StatReq */
144 h_retirement_type VARCHAR2(1500); /* StatReq */
145 -- End of changes for bug 5944006
146
147 -- Added for bug 5944006
148 h_short_invoice_number VARCHAR2(500);
149 h_short_invoice_descr VARCHAR2(500);
150 h_short_vendor_name VARCHAR2(500);
151 h_short_retirement_type VARCHAR2(500);
152 -- End of changes for bug 5944006
153
154 return_status BOOLEAN; /* StatReq */
155 acct_appl_col VARCHAR2(240); /* StatReq */
156 acct_segname VARCHAR2(240); /* StatReq */
157 acct_prompt VARCHAR2(240); /* StatReq */
158 acct_valueset_name VARCHAR2(240); /* StatReq */
159
160 -- Additional variables for Drill Down Report
161 h_group_asset FA_LOOKUPS.MEANING%TYPE; --bug8432604 -- Group Asset_number
162
163 -- MRC
164 h_sob_id NUMBER;
165 h_mrcsobtype VARCHAR2(1);
166 -- End MRC
167
168 -- Used in finding account segments for FA_BALANCES_REPORT_GT
169
170 CURSOR BAL_REPORT_AJCCID IS
171 SELECT DISTINCT ADJUSTMENT_CCID, CATEGORY_BOOKS_ACCOUNT, ROWID
172 FROM FA_BALANCES_REPORT_GT;
173
174 -- Main selector from FA_BALANCES_REPORT_GT for asset cost
175 -- and CIP cost reports.
176
177 CURSOR COST_REPORT (c_book VARCHAR2, c_to_date DATE, c_from_date DATE) IS /* StatReq */
178 SELECT DISTINCT
179 ad.asset_id, /* StatReq */
180 DHCC.CODE_COMBINATION_ID,
181 BAL.Category_Books_Account,
182 BAL.Cost_Account, /* StatReq */
183 AD.Asset_Number,
184 AD.tag_number,
185 AD.description,
186 ad.serial_number, ad.inventorial, ad.asset_key_ccid, /* StatReq */
187 ah.category_id, /* StatReq */
188 b.date_placed_in_service, /* StatReq */
189 m.method_code, /* StatReq */
190 b.life_in_months, /* StatReq */
191 m.stl_method_flag, /* StatReq */
192 m.rate_source_rule, /* StatReq */
193 NVL(SUM(DECODE(BAL.Source_Type_Code,
194 'BEGIN', NVL(BAL.Cost_Begin_Balance, 0), NULL)), 0), /* StatReq */
195 NVL(SUM (DECODE (BAL.Source_Type_Code,
196 'BEGIN', NVL(BAL.Amount,0), NULL)), 0),
197 SUM (DECODE (BAL.Source_Type_Code,
198 DECODE(REPORT_TYPE,'COST','ADDITION', 'CIP ADDITION'),
199 NVL(BAL.Amount,0), NULL)),
200 SUM (DECODE (BAL.Source_Type_Code,
201 DECODE(REPORT_TYPE, 'CIP COST', 'ADDITION'),
202 -NVL(BAL.Amount,0), NULL)),
203 SUM (DECODE (BAL.Source_Type_Code,
204 DECODE(REPORT_TYPE,'COST','ADJUSTMENT','CIP ADJUSTMENT'),
205 NVL(BAL.Amount,0), NULL)),
206 SUM (DECODE (BAL.Source_Type_Code,
207 'RECLASS', NVL(BAL.Amount,0), NULL)),
208 SUM (DECODE (BAL.Source_Type_Code,
209 DECODE(REPORT_TYPE,'COST','RETIREMENT','CIP RETIREMENT'),
210 -NVL(BAL.Amount,0), NULL)),
211 SUM (DECODE (BAL.Source_Type_Code,
212 'REVALUATION', NVL(BAL.Amount,0), NULL)),
213 SUM (DECODE (BAL.Source_Type_Code,
214 'TRANSFER', NVL(BAL.Amount,0), NULL)),
215 NVL(SUM (DECODE (BAL.Source_Type_Code,
216 'END', NVL(BAL.Amount,0), NULL)), 0) /*Bug# 9293000 */
217 FROM
218 FA_ASSET_HISTORY AH, /* StatReq */
219 fa_category_books cb,
220 FA_METHODS M, /* StatReq */
221 FA_BOOKS B, /* StatReq */
222 FA_BALANCES_REPORT_GT BAL,
223 FA_ADDITIONS AD,
224 GL_CODE_COMBINATIONS DHCC,
225 GL_CODE_COMBINATIONS AJCC,
226 FA_ADDITIONS AD1,
227 FA_LOOKUPS LU
228 WHERE AD.Asset_ID = BAL.Asset_ID
229 AND DHCC.Code_Combination_ID = BAL.Distribution_CCID
230 AND AJCC.Code_Combination_ID (+) = BAL.Adjustment_CCID
231 AND B.ASSET_ID = AD.ASSET_ID
232 AND B.Book_Type_Code = c_book
233 AND B.Date_Ineffective is NULL
234 AND B.Transaction_Header_Id_Out is NULL
235 AND nvl(M.LIFE_IN_MONTHS, -9) = nvl(B.LIFE_IN_MONTHS, -9)
236 AND M.METHOD_CODE = B.DEPRN_METHOD_CODE
237 AND AH.ASSET_ID = AD.ASSET_ID
238 /* Commented for bug #5015612. It was cousing No data found in Rxi: Asset Cost Balance Reprort.
239 -- Added for bug#4860955
240 and ah.transaction_header_id_in = ( select min(ah1.transaction_header_id_in)
241 from fa_asset_history ah1
242 where ah1.asset_id=ah.asset_id
243 and ah1.category_id=ah.category_id
244 and ah1.asset_type='CIP') */
245 and ah.category_id = cb.category_id
246 and cb.book_type_code = c_book
247 and decode(ah.asset_type,'CIP',cb.cip_cost_acct, cb.asset_cost_acct) = BAL.Category_Books_Account
248 -- Added for bug#4860955
249 and nvl(ah.date_effective,c_to_date) <= c_to_date
250 -- Added for bug #5015612
251 --bug 6668625
252 -- Bug#9384471 Modified for displaying correct category after reclass
253 and ah.transaction_header_id_in = ( select max(ah1.transaction_header_id_in)
254 from fa_asset_history ah1,
255 fa_category_books cb1
256 where ah1.asset_id=ah.asset_id
257 -- and ah1.category_id=ah.category_id Bug#9384471
258 and ah1.asset_type=decode(report_type,'CIP COST','CIP',
259 'COST','CAPITALIZED',ah1.asset_type)
260 and cb1.book_type_code=c_book
261 and cb1.category_id=ah1.category_id
262 and decode(report_type,'CIP COST',cb1.cip_cost_acct,cb1.asset_cost_acct)
263 = decode(report_type,'CIP COST',cb.cip_cost_acct,cb.asset_cost_acct)
264 and nvl(ah1.date_effective,c_to_date) <= c_to_date )
265
266 -- Commented for bug#4860955
267 -- AND c_to_date between ah.date_effective and nvl(ah.date_ineffective, c_to_date)
268 AND AD1.Asset_ID(+) = nvl(BAL.Group_Asset_ID,-99) -- Added for Drill Down Report
269 AND LU.Lookup_Type (+) = 'ASSET TYPE'
270 AND LU.Lookup_Code (+) = AD.Asset_Type
271 GROUP BY
272 ad.asset_id,
273 dhcc.code_combination_id,
274 BAL.Category_Books_Account,
275 BAL.Cost_Account,
276 AD.ASSET_NUMBER,
277 AD.tag_number,
278 AD.description,
279 ad.serial_number, ad.inventorial, ad.asset_key_ccid,
280 ah.category_id,
281 b.date_placed_in_service,
282 m.method_code,
283 b.life_in_months,
284 m.stl_method_flag,
285 m.rate_source_rule;
286
287 -- MRC
288 CURSOR MC_COST_REPORT (c_book VARCHAR2, c_to_date DATE, c_from_date DATE) IS /* StatReq */
289 SELECT DISTINCT
290 ad.asset_id, /* StatReq */
291 DHCC.CODE_COMBINATION_ID,
292 BAL.Category_Books_Account,
293 BAL.Cost_Account, /* StatReq */
294 AD.Asset_Number,
295 AD.tag_number,
296 AD.description,
297 ad.serial_number, ad.inventorial, ad.asset_key_ccid, /* StatReq */
298 ah.category_id, /* StatReq */
299 b.date_placed_in_service, /* StatReq */
300 m.method_code, /* StatReq */
301 b.life_in_months, /* StatReq */
302 m.stl_method_flag, /* StatReq */
303 m.rate_source_rule, /* StatReq */
304 NVL(SUM(DECODE(BAL.Source_Type_Code,
305 'BEGIN', NVL(BAL.Cost_Begin_Balance, 0), NULL)), 0), /* StatReq */
306 NVL(SUM (DECODE (BAL.Source_Type_Code,
307 'BEGIN', NVL(BAL.Amount,0), NULL)), 0),
308 SUM (DECODE (BAL.Source_Type_Code,
309 DECODE(REPORT_TYPE,'COST','ADDITION', 'CIP ADDITION'),
310 NVL(BAL.Amount,0), NULL)),
311 SUM (DECODE (BAL.Source_Type_Code,
312 DECODE(REPORT_TYPE, 'CIP COST', 'ADDITION'),
313 -NVL(BAL.Amount,0), NULL)),
314 SUM (DECODE (BAL.Source_Type_Code,
315 DECODE(REPORT_TYPE,'COST','ADJUSTMENT','CIP ADJUSTMENT'),
316 NVL(BAL.Amount,0), NULL)),
317 SUM (DECODE (BAL.Source_Type_Code,
318 'RECLASS', NVL(BAL.Amount,0), NULL)),
319 SUM (DECODE (BAL.Source_Type_Code,
320 DECODE(REPORT_TYPE,'COST','RETIREMENT','CIP RETIREMENT'),
321 -NVL(BAL.Amount,0), NULL)),
322 SUM (DECODE (BAL.Source_Type_Code,
323 'REVALUATION', NVL(BAL.Amount,0), NULL)),
324 SUM (DECODE (BAL.Source_Type_Code,
325 'TRANSFER', NVL(BAL.Amount,0), NULL)),
326 NVL(SUM (DECODE (BAL.Source_Type_Code,
327 'END', NVL(BAL.Amount,0), NULL)), 0) /*Bug# 9293000 */
328 FROM
329 FA_ASSET_HISTORY AH, /* StatReq */
330 fa_category_books cb,
331 FA_METHODS M, /* StatReq */
332 FA_MC_BOOKS B, /* StatReq */
333 FA_BALANCES_REPORT_GT BAL,
334 FA_ADDITIONS AD,
335 GL_CODE_COMBINATIONS DHCC,
336 GL_CODE_COMBINATIONS AJCC,
337 FA_ADDITIONS AD1,
338 FA_LOOKUPS LU
339 WHERE AD.Asset_ID = BAL.Asset_ID
340 AND DHCC.Code_Combination_ID = BAL.Distribution_CCID
341 AND AJCC.Code_Combination_ID (+) = BAL.Adjustment_CCID
342 AND B.ASSET_ID = AD.ASSET_ID
343 AND B.Book_Type_Code = c_book
344 AND B.Date_Ineffective is NULL
345 AND B.Transaction_Header_Id_Out is NULL
346 AND nvl(M.LIFE_IN_MONTHS, -9) = nvl(B.LIFE_IN_MONTHS, -9)
347 AND M.METHOD_CODE = B.DEPRN_METHOD_CODE
348 AND AH.ASSET_ID = AD.ASSET_ID
349 /* Commented for bug #5015612. It was cousing No data found in Rxi: Asset Cost Balance Reprort.
350 -- Added for bug#4860955
351 and ah.transaction_header_id_in = ( select min(ah1.transaction_header_id_in)
352 from fa_asset_history ah1
353 where ah1.asset_id=ah.asset_id
354 and ah1.category_id=ah.category_id
355 and ah1.asset_type='CIP') */
356 and ah.category_id = cb.category_id
357 and cb.book_type_code = c_book
358 and decode(ah.asset_type,'CIP',cb.cip_cost_acct, cb.asset_cost_acct) = BAL.Category_Books_Account
359 -- Added for bug#4860955
360 and nvl(ah.date_effective,c_to_date) <= c_to_date
361 -- Added for bug #5015612
362 --bug 6668625
363 and ah.transaction_header_id_in = ( select max(ah1.transaction_header_id_in)
364 from fa_asset_history ah1,
365 fa_category_books cb1
366 where ah1.asset_id=ah.asset_id
367 -- and ah1.category_id=ah.category_id Bug#9384471
368 and ah1.asset_type=decode(report_type,'CIP COST','CIP',
369 'COST','CAPITALIZED',ah1.asset_type)
370 and cb1.book_type_code=c_book
371 and cb1.category_id=ah1.category_id
372 and decode(report_type,'CIP COST',cb1.cip_cost_acct,cb1.asset_cost_acct)
373 = decode(report_type,'CIP COST',cb.cip_cost_acct,cb.asset_cost_acct)
374 and nvl(ah1.date_effective,c_to_date) <= c_to_date )
375
376 -- Commented for bug#4860955
377 -- AND c_to_date between ah.date_effective and nvl(ah.date_ineffective, c_to_date)
378 AND AD1.Asset_ID(+) = nvl(BAL.Group_Asset_ID,-99) -- Added for Drill Down Report
379 AND LU.Lookup_Type (+) = 'ASSET TYPE'
380 AND LU.Lookup_Code (+) = AD.Asset_Type
381 AND B.set_of_books_id = h_sob_id
382 GROUP BY
383 ad.asset_id,
384 dhcc.code_combination_id,
385 BAL.Category_Books_Account,
386 BAL.Cost_Account,
387 AD.ASSET_NUMBER,
388 AD.tag_number,
389 AD.description,
390 ad.serial_number, ad.inventorial, ad.asset_key_ccid,
391 ah.category_id,
392 b.date_placed_in_service,
393 m.method_code,
394 b.life_in_months,
395 m.stl_method_flag,
396 m.rate_source_rule;
397 -- End MRC
398
399 -- Main selector from FA_BALANCES_REPORT_GT for accum deprn
400 -- and reval reserve reports.
401
402
403 CURSOR RESERVE_REPORT (c_book VARCHAR2, c_to_date DATE) IS /* StatReq */
404 SELECT DISTINCT
405 ad.asset_id, /* StatReq */
406 DHCC.CODE_COMBINATION_ID,
407 BAL.Category_Books_Account,
408 BAL.Cost_Account, /* StatReq */
409 AD.Asset_Number,
410 AD.tag_number,
411 AD.description,
412 ad.serial_number, ad.inventorial, ad.asset_key_ccid,
413 ah.category_id, /* StatReq */
414 b.date_placed_in_service, /* StatReq */
415 m.method_code, /* StatReq */
416 b.life_in_months, /* StatReq */
417 m.stl_method_flag, /* StatReq */
418 m.rate_source_rule, /* StatReq */
419 ad1.asset_number,
420 NVL(SUM(DECODE(BAL.Source_Type_Code,
421 'BEGIN', NVL(BAL.Cost_Begin_Balance, 0), NULL)), 0), /* StatReq */
422 SUM (DECODE (BAL.Source_Type_Code,
423 'BEGIN', NVL(BAL.Amount,0), NULL)),
424 SUM (DECODE (BAL.Source_Type_Code,
425 'ADDITION', NVL(BAL.Amount,0), NULL)),
426 SUM (DECODE (BAL.Source_Type_Code,
427 'ADJUSTMENT', NVL(BAL.Amount,0), NULL)), /*9293000 */
428 SUM (DECODE (BAL.Source_Type_Code,
429 'DEPRECIATION', NVL(BAL.Amount,0), NULL)),
430 SUM (DECODE (BAL.Source_Type_Code,
431 'RECLASS', NVL(BAL.Amount,0), NULL)),
432 SUM (DECODE (BAL.Source_Type_Code,
433 'RETIREMENT', -NVL(BAL.Amount,0), NULL)),
434 SUM (DECODE (BAL.Source_Type_Code,
435 'REVALUATION', NVL(BAL.Amount,0), NULL)),
436 SUM (DECODE (BAL.Source_Type_Code,
437 'TAX', NVL(BAL.Amount,0), NULL)),
438 SUM (DECODE (BAL.Source_Type_Code,
439 'TRANSFER', NVL(BAL.Amount,0), NULL)),
440 SUM (DECODE (BAL.Source_Type_Code,
441 'END', NVL(BAL.Amount,0), NULL)) /*Bug# 9293000 */
442 FROM
443 FA_ASSET_HISTORY AH, /* StatReq */
444 FA_METHODS M, /* StatReq */
445 FA_BOOKS B, /* StatReq */
446 FA_BALANCES_REPORT_GT BAL,
447 FA_ADDITIONS AD,
448 GL_CODE_COMBINATIONS DHCC,
449 GL_CODE_COMBINATIONS AJCC,
450 FA_ADDITIONS AD1, -- This is only used to get Group Asset Number
451 FA_LOOKUPS LU,
452 FA_BOOKS BKG
453 WHERE AD.Asset_ID = BAL.Asset_ID
454 AND DHCC.Code_Combination_ID = BAL.Distribution_CCID
455 AND AJCC.Code_Combination_ID (+) = BAL.Adjustment_CCID
456 AND B.Book_Type_Code = c_book /* StatReq */
457 AND B.ASSET_ID = AD.ASSET_ID /* StatReq */
458 AND B.Date_Ineffective is NULL /* StatReq */
459 AND B.Transaction_Header_Id_Out is NULL /* StatReq */
460 AND nvl(M.LIFE_IN_MONTHS, -9) = nvl(B.LIFE_IN_MONTHS, -9) /* StatReq */
461 AND M.METHOD_CODE = B.DEPRN_METHOD_CODE /* StatReq */
462 AND AH.ASSET_ID = AD.ASSET_ID /* StatReq */
463 AND AH.DATE_EFFECTIVE <= c_to_date /* StatReq */
464 AND NVL(AH.DATE_INEFFECTIVE, SYSDATE + 1) > c_to_date /* StatReq */
465 AND LU.Lookup_Type (+) = 'ASSET TYPE'
466 AND LU.Lookup_Code (+) = AD.Asset_Type
467 AND BKG.Book_Type_Code = c_book /* Bug 16424827 */
468 AND BKG.ASSET_ID = AD.ASSET_ID
469 AND BKG.DATE_EFFECTIVE <= c_to_date
470 AND NVL(BKG.DATE_INEFFECTIVE, SYSDATE + 1) > c_to_date
471 AND AD1.Asset_ID(+) = BKG.GROUP_ASSET_ID
472 GROUP BY
473 ad.asset_id, /* StatReq */
474 DHCC.CODE_COMBINATION_ID,
475 BAL.Category_Books_Account,
476 BAL.Cost_Account, /* StatReq */
477 AD.ASSET_NUMBER,
478 AD.TAG_NUMBER,
479 AD.DESCRIPTION, ad.serial_number, ad.inventorial, ad.asset_key_ccid,
480 ah.category_id, /* StatReq */
481 b.date_placed_in_service, /* StatReq */
482 m.method_code, /* StatReq */
483 b.life_in_months, /* StatReq */
484 m.stl_method_flag, /* StatReq */
485 m.rate_source_rule, /* StatReq */
486 ad1.asset_number;
487
488 -- MRC
489 CURSOR MC_RESERVE_REPORT (c_book VARCHAR2, c_to_date DATE) IS /* StatReq */
490 SELECT DISTINCT
491 ad.asset_id, /* StatReq */
492 DHCC.CODE_COMBINATION_ID,
493 BAL.Category_Books_Account,
494 BAL.Cost_Account, /* StatReq */
495 AD.Asset_Number,
496 AD.tag_number,
497 AD.description,
498 ad.serial_number, ad.inventorial, ad.asset_key_ccid,
499 ah.category_id, /* StatReq */
500 b.date_placed_in_service, /* StatReq */
501 m.method_code, /* StatReq */
502 b.life_in_months, /* StatReq */
503 m.stl_method_flag, /* StatReq */
504 m.rate_source_rule, /* StatReq */
505 ad1.asset_number,
506 NVL(SUM(DECODE(BAL.Source_Type_Code,
507 'BEGIN', NVL(BAL.Cost_Begin_Balance, 0), NULL)), 0), /* StatReq */
508 SUM (DECODE (BAL.Source_Type_Code,
509 'BEGIN', NVL(BAL.Amount,0), NULL)),
510 SUM (DECODE (BAL.Source_Type_Code,
511 'ADDITION', NVL(BAL.Amount,0), NULL)),
512 SUM (DECODE (BAL.Source_Type_Code,
513 'ADJUSTMENT', NVL(BAL.Amount,0), NULL)), /*9293000 */
514 SUM (DECODE (BAL.Source_Type_Code,
515 'DEPRECIATION', NVL(BAL.Amount,0), NULL)),
516 SUM (DECODE (BAL.Source_Type_Code,
517 'RECLASS', NVL(BAL.Amount,0), NULL)),
518 SUM (DECODE (BAL.Source_Type_Code,
519 'RETIREMENT', -NVL(BAL.Amount,0), NULL)),
520 SUM (DECODE (BAL.Source_Type_Code,
521 'REVALUATION', NVL(BAL.Amount,0), NULL)),
522 SUM (DECODE (BAL.Source_Type_Code,
523 'TAX', NVL(BAL.Amount,0), NULL)),
524 SUM (DECODE (BAL.Source_Type_Code,
525 'TRANSFER', NVL(BAL.Amount,0), NULL)),
526 SUM (DECODE (BAL.Source_Type_Code,
527 'END', NVL(BAL.Amount,0), NULL))/*Bug# 9293000 */
528 FROM
529 FA_ASSET_HISTORY AH, /* StatReq */
530 FA_METHODS M, /* StatReq */
531 FA_MC_BOOKS B, /* StatReq */
532 FA_BALANCES_REPORT_GT BAL,
533 FA_ADDITIONS AD,
534 GL_CODE_COMBINATIONS DHCC,
535 GL_CODE_COMBINATIONS AJCC,
536 FA_ADDITIONS AD1, -- This is only used to get Group Asset Number
537 FA_LOOKUPS LU,
538 FA_MC_BOOKS BKG
539 WHERE AD.Asset_ID = BAL.Asset_ID
540 AND DHCC.Code_Combination_ID = BAL.Distribution_CCID
541 AND AJCC.Code_Combination_ID (+) = BAL.Adjustment_CCID
542 AND B.Book_Type_Code = c_book /* StatReq */
543 AND B.ASSET_ID = AD.ASSET_ID /* StatReq */
544 AND B.Date_Ineffective is NULL /* StatReq */
545 AND B.Transaction_Header_Id_Out is NULL /* StatReq */
546 AND nvl(M.LIFE_IN_MONTHS, -9) = nvl(B.LIFE_IN_MONTHS, -9) /* StatReq */
547 AND M.METHOD_CODE = B.DEPRN_METHOD_CODE /* StatReq */
548 AND AH.ASSET_ID = AD.ASSET_ID /* StatReq */
549 AND AH.DATE_EFFECTIVE <= c_to_date /* StatReq */
550 AND NVL(AH.DATE_INEFFECTIVE, SYSDATE + 1) > c_to_date /* StatReq */
551 AND AD1.Asset_ID(+) = nvl(BAL.Group_Asset_ID,-99)
552 AND LU.Lookup_Type (+) = 'ASSET TYPE'
553 AND LU.Lookup_Code (+) = AD.Asset_Type
554 AND B.set_of_books_id = h_sob_id
555 AND BKG.Book_Type_Code = c_book /* Bug 16424827 */
556 AND BKG.ASSET_ID = AD.ASSET_ID
557 AND BKG.DATE_EFFECTIVE <= c_to_date
558 AND NVL(BKG.DATE_INEFFECTIVE, SYSDATE + 1) > c_to_date
559 AND AD1.Asset_ID(+) = BKG.GROUP_ASSET_ID
560 AND BKG.set_of_books_id = h_sob_id
561 GROUP BY
562 ad.asset_id, /* StatReq */
563 DHCC.CODE_COMBINATION_ID,
564 BAL.Category_Books_Account,
565 BAL.Cost_Account, /* StatReq */
566 AD.ASSET_NUMBER,
567 AD.TAG_NUMBER,
568 AD.DESCRIPTION, ad.serial_number, ad.inventorial, ad.asset_key_ccid,
569 ah.category_id, /* StatReq */
570 b.date_placed_in_service, /* StatReq */
571 m.method_code, /* StatReq */
572 b.life_in_months, /* StatReq */
573 m.stl_method_flag, /* StatReq */
574 m.rate_source_rule; /* StatReq */
575 -- End MRC
576
577 CURSOR PERIOD_INFO IS
578 SELECT P1.Period_Counter,
579 P1.Period_Open_Date,
580 P1.Period_Close_Date,
581 P2.Period_Counter,
582 NVL(P2.Period_Close_Date, SYSDATE),
583 BC.Distribution_Source_Book,
584 BC.Accounting_Flex_Structure
585 FROM FA_DEPRN_PERIODS P1,
586 FA_DEPRN_PERIODS P2,
587 FA_BOOK_CONTROLS BC
588 WHERE BC.Book_Type_Code = Book
589 AND P1.Book_Type_Code = Book AND
590 P1.Period_Name = Start_Period_Name
591 AND P2.Book_Type_Code = Book AND
592 P2.Period_Name = End_Period_Name;
593
594 -- Cursor for Drill Down Report
595 CURSOR GROUP_ASSETS IS
596 SELECT DISTINCT BAL.Group_Asset_ID Group_Asset_ID
597 FROM FA_BALANCES_REPORT_GT BAL
598 WHERE BAL.Asset_ID = BAL.Group_Asset_ID;
599
600 CURSOR GROUP_RESERVE_AMOUNTS (p_group_asset_id number) is
601 SELECT
602 NVL(SUM(DECODE(BAL.Source_Type_Code,
603 'BEGIN', NVL(BAL.Cost_Begin_Balance, 0), NULL)), 0) /* StatReq */
604 FROM FA_BALANCES_REPORT_GT BAL
605 WHERE BAL.Group_Asset_id = p_group_asset_id
606 AND BAL.Group_Asset_id <> BAL.Asset_id;
607
608 CURSOR GROUP_COST_AMOUNTS (p_group_asset_id number) is
609 SELECT
610 NVL(SUM(DECODE(BAL.Source_Type_Code,
611 'BEGIN', NVL(BAL.Cost_Begin_Balance, 0), NULL)), 0),
612 NVL(SUM (DECODE (BAL.Source_Type_Code,
613 'BEGIN', NVL(BAL.Amount,0), NULL)), 0),
614 SUM (DECODE (BAL.Source_Type_Code,
615 DECODE(REPORT_TYPE,'COST','ADDITION', 'CIP ADDITION'),
616 NVL(BAL.Amount,0), NULL)),
617 SUM (DECODE (BAL.Source_Type_Code,
618 DECODE(REPORT_TYPE, 'CIP COST', 'ADDITION'),
619 -NVL(BAL.Amount,0), NULL)),
620 SUM (DECODE (BAL.Source_Type_Code,
621 DECODE(REPORT_TYPE,'COST','ADJUSTMENT','CIP ADJUSTMENT'),
622 NVL(BAL.Amount,0), NULL)),
623 SUM (DECODE (BAL.Source_Type_Code,
624 'RECLASS', NVL(BAL.Amount,0), NULL)),
625 SUM (DECODE (BAL.Source_Type_Code,
626 DECODE(REPORT_TYPE,'COST','RETIREMENT','CIP RETIREMENT'),
627 -NVL(BAL.Amount,0), NULL)),
628 SUM (DECODE (BAL.Source_Type_Code,
629 'REVALUATION', NVL(BAL.Amount,0), NULL)),
630 SUM (DECODE (BAL.Source_Type_Code,
631 'TRANSFER', NVL(BAL.Amount,0), NULL)),
632 NVL(SUM (DECODE (BAL.Source_Type_Code,
633 'END', NVL(BAL.Amount,0), NULL)), 0)
634 FROM FA_BALANCES_REPORT_GT BAL
635 WHERE BAL.Group_Asset_id = p_group_asset_id
636 AND BAL.Group_Asset_id <> BAL.Asset_id;
637
638 -- cursor non_qualified_segs is
639 -- SELECT s.application_column_name,
640 -- s.segment_name,
641 -- s.segment_num
642 -- FROM fnd_id_flex_segments_vl s
643 -- WHERE s.application_id = 101
644 -- AND s.id_flex_code = 'GL#'
645 -- AND s.id_flex_num = Acct_Flex_STructure
646 -- AND s.enabled_flag = 'Y'
647 -- and 1 = (select count(*)
648 -- from fnd_segment_attribute_values
649 -- where application_id = s.application_id
650 -- and id_flex_code = s.id_flex_code
651 -- and id_flex_num = s.id_flex_num and attribute_value = 'Y'
652 -- and application_column_name = s.application_column_name);
653
654
655 begin
656
657 h_sob_id := to_number(sob_id); -- MRC
658
659 /* begin sorp2 changes */
660 select nvl(sorp_enabled_flag,'N')
661 into h_sorp_enabled_flag
662 from fa_book_controls
663 where book_type_code = Book;
664 /* end sorp2 changes */
665
666 fa_debug_pkg.add('farxblb','report_style in main (1)', Report_Style);
667
668 -- acct_all_segs(1) := '';
669 -- acct_all_segs(2) := '';
670 -- acct_all_segs(3) := '';
671 -- acct_all_segs(4) := '';
672 -- acct_all_segs(5) := '';
673 -- acct_all_segs(6) := '';
674 -- acct_all_segs(7) := '';
675 -- acct_all_segs(8) := '';
676 -- acct_all_segs(9) := '';
677 -- acct_all_segs(10) := '';
678 -- acct_all_segs(11) := '';
679 -- acct_all_segs(12) := '';
680 -- acct_all_segs(13) := '';
681 -- acct_all_segs(14) := '';
682 -- acct_all_segs(15) := '';
683 -- acct_all_segs(16) := '';
684 -- acct_all_segs(17) := '';
685 -- acct_all_segs(18) := '';
686 -- acct_all_segs(19) := '';
687 -- acct_all_segs(20) := '';
688 -- acct_all_segs(21) := '';
689 -- acct_all_segs(22) := '';
690 -- acct_all_segs(23) := '';
691 -- acct_all_segs(24) := '';
692 -- acct_all_segs(25) := '';
693 -- acct_all_segs(26) := '';
694 -- acct_all_segs(27) := '';
695 -- acct_all_segs(28) := '';
696 -- acct_all_segs(29) := '';
697 -- acct_all_segs(30) := '';
698
699
700
701 success := FALSE;
702
703 h_request_id := request_id;
704
705
706 fnd_profile.get('LOGIN_ID',h_login_id);
707
708 -- MRC
709 if h_sob_id is not null then
710 begin
711 select 'P'
712 into h_mrcsobtype
713 from fa_book_controls
714 where book_type_code = book
715 and set_of_books_id = h_sob_id;
716 exception
717 when no_data_found then
718 h_mrcsobtype := 'R';
719 end;
720 else
721 h_mrcsobtype := 'P';
722 end if;
723 -- End MRC
724
725
726 -- Select dates corresponding to given period range.
727 -- Error out if periods given in range do not exist.
728 -- Also get structure_id
729
730 h_mesg_name := 'FA_AMT_SEL_PERIODS';
731
732 OPEN PERIOD_INFO;
733 FETCH PERIOD_INFO INTO
734 Period1_PC,
735 Period1_POD,
736 Period1_PCD,
737 Period2_PC,
738 Period2_PCD,
739 Distribution_Source_Book,
740 Acct_Flex_Structure;
741
742 if (PERIOD_INFO%NOTFOUND) then
743 h_mesg_name := 'FA_SHARED_SEL_DEPRN_PERIODS';
744 h_mesg_str := fnd_message.get;
745 fa_rx_conc_mesg_pkg.log(h_mesg_str);
746
747 CLOSE PERIOD_INFO;
748 return;
749 end if;
750 CLOSE PERIOD_INFO;
751
752 if (Report_Type = 'RESERVE' or Report_Type = 'REVAL RESERVE') then
753 Balance_Type := 'CR';
754 else
755 Balance_Type := 'DR';
756 end if;
757
758 h_mesg_name := 'FA_SHARED_DELETE_FAILED';
759 h_table_token := 'FA_BALANCES_REPORT_GT';
760
761 -- no longer needed with GT
762 -- DELETE FROM FA_BALANCES_REPORT_GT;
763
764 h_table_token := 'FA_LOOKUPS';
765
766 /* StatReq - Added category flex structure to the following
767 SQL Statement */
768 select
769 category_flex_structure,
770 asset_key_flex_structure
771 into
772 h_cat_flex_struct,
773 h_key_flex_struct
774 from
775 fa_system_controls;
776
777
778 /* Get Beginning Balance */
779 /* Use Period1_PC-1, to get balance as of end of period immediately
780 preceding Period1_PC */
781 -- Get_Balance (Book, Distribution_Source_Book,
782 -- Period1_PC-1, Period1_POD,
783 -- Report_Type, Balance_Type,
784 -- 'BEGIN');
785
786 h_mesg_name := 'FA_RX_BEGIN_BALANCES';
787
788 if(h_mrcsobtype <> 'R') then -- MRC
789 INSERT INTO FA_BALANCES_REPORT_GT
790 (Asset_ID,
791 Group_Asset_ID, -- Added for Member Track
792 Distribution_CCID,
793 Adjustment_CCID,
794 Category_Books_Account,
795 Cost_Account,
796 Source_Type_Code,
797 Amount,
798 Cost_Begin_Balance)
799 SELECT
800 DH.Asset_ID,
801 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID),
802 DH.Code_Combination_ID,
803 null,
804 DECODE (Report_Type,
805 'COST', CB.Asset_Cost_Acct,
806 'CIP COST', CB.CIP_Cost_Acct,
807 'RESERVE', CB.Deprn_Reserve_Acct,
808 'REVAL RESERVE', CB.Reval_Reserve_Acct),
809 CB.Asset_Cost_Acct,
810 DECODE(Report_Type,
811 'RESERVE', DECODE(DD.Deprn_Source_Code,
812 'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
813 'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
814 'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
815 'BEGIN'),
816 DECODE (Report_Type,
817 'COST', DD.Cost,
818 'CIP COST', DD.Cost,
819 'RESERVE', DD.Deprn_Reserve,
820 'REVAL RESERVE', DD.Reval_Reserve)
821 +
822 nvl( SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) * AJ.Adjustment_Amount),0)
823 ,
824 DD.COST
825 FROM
826 FA_CATEGORY_BOOKS CB,
827 FA_DISTRIBUTION_HISTORY DH,
828 FA_ASSET_HISTORY AH,
829 FA_DEPRN_DETAIL DD,
830 FA_BOOKS BK, -- Added for Member Track
831 FA_ADJUSTMENTS AJ
832 WHERE DH.Book_Type_Code = Distribution_Source_Book
833 AND DD.Asset_ID = AH.Asset_ID AND
834 DD.Book_Type_Code = Book AND
835 DD.Distribution_ID = DH.Distribution_ID AND
836 DD.Period_Counter <= Period1_Pc - 1 AND
837 DD.Period_Counter =
838 (SELECT MAX (SUB_DD.Period_Counter)
839 FROM FA_DEPRN_DETAIL SUB_DD
840 WHERE SUB_DD.Asset_ID = DH.Asset_ID AND
841 SUB_DD.Book_Type_Code = Book AND
842 SUB_DD.Distribution_ID = DH.Distribution_ID AND
843 SUB_DD.Period_Counter <= Period1_Pc - 1) AND
844 AJ.Asset_ID(+) = DD.Asset_ID AND
845 AJ.Book_type_code(+) = Book AND
846 AJ.Distribution_ID(+) = DD.Distribution_ID AND
847 AJ.Adjustment_Type(+) = Report_Type AND
848 AJ.Period_Counter_Created(+) BETWEEN
849 (DD.Period_Counter+1) AND (Period1_PC-1)
850 AND (( DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9407401 */
851 AND (( 0 = (select count(1)
852 from fa_deprn_detail dd1
853 where dd1.asset_id = DD.Asset_ID
854 AND dd1.Deprn_Source_Code = 'T'
855 AND dd1.book_type_code = Book
856 AND dd1.Period_Counter = DD.Period_Counter
857 AND dd1.distribution_id > dd.distribution_id) AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D')
858 AND AH.Asset_ID = DH.Asset_ID AND
859 ((AH.Asset_Type <> 'EXPENSED' AND
860 Report_Type IN ('COST', 'CIP COST')) OR
861 (AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
862 Report_Type IN ('RESERVE', 'REVAL RESERVE')) OR
863 (AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D'))
864 AND ((Period1_Pod BETWEEN AH.Date_Effective AND
865 NVL(AH.Date_Ineffective, Period1_Pod) AND
866 DD.Deprn_Source_Code = 'D') OR
867 (DD.Deprn_Run_Date BETWEEN AH.Date_Effective AND
868 NVL(AH.Date_Ineffective, DD.Deprn_Run_Date) AND
869 DD.Deprn_Source_Code = 'B') OR
870 (Period1_Pod BETWEEN AH.Date_Effective AND
871 NVL(AH.Date_Ineffective, Period1_Pod) AND
872 -- DD.Deprn_Source_Code = 'T' AND
873 nvl(Report_Style,'S')= decode(substr(report_type,-4),'COST', nvl(Report_Style,'S'),'D'))) /*Bug# 9293000 */
874 AND CB.Category_ID = AH.Category_ID
875 AND CB.Book_Type_Code = Book
876 AND DECODE (Report_Type,
877 'COST', DECODE (AH.Asset_Type,
878 'CAPITALIZED', CB.Asset_Cost_Acct,
879 DECODE(Report_Style,'D',DECODE(AH.Asset_Type,'GROUP',CB.Asset_Cost_Acct,null),
880 null)), -- Added for second decode for drill down report
881 'CIP COST',
882 DECODE (AH.Asset_Type,
883 'CIP', CB.CIP_Cost_Acct,
884 null),
885 'RESERVE', CB.Deprn_Reserve_Acct,
886 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
887 AND BK.Book_Type_Code = CB.book_type_code
888 AND BK.Asset_ID = DD.Asset_ID AND
889 DECODE(DD.Deprn_Source_Code, 'D', Period1_pod,
890 nvl(Period1_pcd,Period1_pod)) BETWEEN /*Bug# 9293000 */
891 BK.Date_Effective AND
892 NVL(BK.Date_Ineffective, sysdate) AND
893 NVL(BK.Period_Counter_Fully_Retired, Period1_PC)
894 > period1_pc - 1
895 GROUP BY
896 DH.Asset_ID,
897 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID),
898 DH.Code_Combination_ID,
899 DH.LOCATION_ID,-- bug 16587270 null,
900 DECODE (Report_Type,
901 'COST', CB.Asset_Cost_Acct,
902 'CIP COST', CB.CIP_Cost_Acct,
903 'RESERVE', CB.Deprn_Reserve_Acct,
904 'REVAL RESERVE', CB.Reval_Reserve_Acct),
905 CB.Asset_Cost_Acct,
906 DECODE(Report_Type,
907 'RESERVE', DECODE(DD.Deprn_Source_Code,
908 'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
909 'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
910 'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
911 'BEGIN'),
912 DECODE (Report_Type,
913 'COST', DD.Cost,
914 'CIP COST', DD.Cost,
915 'RESERVE', DD.Deprn_Reserve,
916 'REVAL RESERVE', DD.Reval_Reserve),
917 DD.COST
918 UNION ALL-- Added to get assets added with reserve when multiple periods intervall bug 3756517
919 SELECT
920 DH.Asset_ID,
921 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID),
922 DH.Code_Combination_ID,
923 null,
924 DECODE (Report_Type,
925 'RESERVE', CB.Deprn_Reserve_Acct,
926 'REVAL RESERVE', CB.Reval_Reserve_Acct),
927 CB.Asset_Cost_Acct,
928 DECODE(Report_Type,
929 'RESERVE', DECODE(DD.Deprn_Source_Code,
930 'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
931 'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
932 'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
933 'BEGIN'),
934 DECODE (Report_Type,
935 'COST', DD.Cost,
936 'CIP COST', DD.Cost,
937 'RESERVE', DD.Deprn_Reserve,
938 'REVAL RESERVE', DD.Reval_Reserve),
939 DD.COST
940 FROM
941 FA_CATEGORY_BOOKS CB,
942 FA_DISTRIBUTION_HISTORY DH,
943 FA_ASSET_HISTORY AH,
944 FA_DEPRN_DETAIL DD,
945 FA_BOOKS BK,
946 FA_DEPRN_PERIODS DP_BROW
947 WHERE DH.Book_Type_Code = distribution_source_book
948 AND DD.Asset_ID = AH.Asset_ID AND
949 DD.Book_Type_Code = book AND
950 DD.Distribution_ID = DH.Distribution_ID AND
951 dd.deprn_reserve <> 0 AND
952 dd.deprn_source_code = 'B' AND
953 DD.Period_Counter between period1_pc-1 and period2_pc
954 AND AH.Asset_ID = DH.Asset_ID AND
955 ( (AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
956 Report_Type IN ('RESERVE', 'REVAL RESERVE')))
957 AND
958 DP_BROW.book_type_code = dd.book_type_code and
959 DP_BROW.period_counter = dd.period_counter +1
960 and NVL(DP_BROW.period_close_date,SYSDATE) between ah.date_effective and nvl(ah.date_ineffective,sysdate)
961 AND CB.Category_ID = AH.Category_ID AND
962 CB.Book_Type_Code = book
963 AND DECODE (Report_Type,
964 'RESERVE', CB.Deprn_Reserve_Acct,
965 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
966 AND BK.Book_Type_Code = CB.book_type_code
967 AND ((DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
968 AND BK.Asset_ID = DD.Asset_ID AND
969 ah.date_effective between BK.Date_Effective AND
970 NVL(BK.Date_Ineffective, sysdate);
971
972 -- MRC
973 else
974 INSERT INTO FA_BALANCES_REPORT_GT
975 (Asset_ID,
976 Group_Asset_ID, -- Added for Member Track
977 Distribution_CCID,
978 Adjustment_CCID,
979 Category_Books_Account,
980 Cost_Account,
981 Source_Type_Code,
982 Amount,
983 Cost_Begin_Balance)
984 SELECT
985 DH.Asset_ID,
986 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID),
987 DH.Code_Combination_ID,
988 null,
989 DECODE (Report_Type,
990 'COST', CB.Asset_Cost_Acct,
991 'CIP COST', CB.CIP_Cost_Acct,
992 'RESERVE', CB.Deprn_Reserve_Acct,
993 'REVAL RESERVE', CB.Reval_Reserve_Acct),
994 CB.Asset_Cost_Acct,
995 DECODE(Report_Type,
996 'RESERVE', DECODE(DD.Deprn_Source_Code,
997 'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
998 'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
999 'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
1000 'BEGIN'),
1001 DECODE (Report_Type,
1002 'COST', DD.Cost,
1003 'CIP COST', DD.Cost,
1004 'RESERVE', DD.Deprn_Reserve,
1005 'REVAL RESERVE', DD.Reval_Reserve)
1006 +
1007 nvl( SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) * AJ.Adjustment_Amount),0)
1008 ,
1009 DD.COST
1010 FROM
1011 FA_CATEGORY_BOOKS CB,
1012 FA_DISTRIBUTION_HISTORY DH,
1013 FA_ASSET_HISTORY AH,
1014 FA_MC_DEPRN_DETAIL DD,
1015 FA_MC_BOOKS BK, -- Added for Member Track
1016 FA_MC_ADJUSTMENTS AJ
1017 WHERE DH.Book_Type_Code = Distribution_Source_Book
1018 AND DD.Asset_ID = AH.Asset_ID AND
1019 DD.Book_Type_Code = Book AND
1020 DD.Distribution_ID = DH.Distribution_ID AND
1021 DD.Period_Counter <= Period1_Pc - 1 AND
1022 DD.Period_Counter =
1023 (SELECT MAX (SUB_DD.Period_Counter)
1024 FROM FA_MC_DEPRN_DETAIL SUB_DD
1025 WHERE SUB_DD.Asset_ID = DH.Asset_ID AND
1026 SUB_DD.Book_Type_Code = Book AND
1027 SUB_DD.Distribution_ID = DH.Distribution_ID AND
1028 SUB_DD.Period_Counter <= Period1_Pc - 1 AND
1029 SUB_DD.set_of_books_id = h_sob_id) AND
1030 AJ.Asset_ID(+) = DD.Asset_ID AND
1031 AJ.Book_type_code(+) = Book AND
1032 AJ.Distribution_ID(+) = DD.Distribution_ID AND
1033 AJ.Adjustment_Type(+) = Report_Type AND
1034 AJ.set_of_books_id(+) = h_sob_id AND
1035 AJ.Period_Counter_Created(+) BETWEEN
1036 (DD.Period_Counter+1) AND (Period1_Pc - 1)
1037 AND (( DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
1038 AND (( 0 = (select count(1)
1039 from fa_mc_deprn_detail dd1
1040 where dd1.asset_id = DD.Asset_ID
1041 AND dd1.Deprn_Source_Code = 'T'
1042 AND dd1.book_type_code = Book
1043 AND dd1.Period_Counter = DD.Period_Counter
1044 AND dd1.set_of_books_id = h_sob_id
1045 AND dd1.distribution_id > dd.distribution_id) AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D')
1046 AND AH.Asset_ID = DH.Asset_ID AND
1047 ((AH.Asset_Type <> 'EXPENSED' AND
1048 Report_Type IN ('COST', 'CIP COST')) OR
1049 (AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
1050 Report_Type IN ('RESERVE', 'REVAL RESERVE')) OR
1051 (AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D'))
1052 AND ((Period1_Pod BETWEEN AH.Date_Effective AND
1053 NVL(AH.Date_Ineffective, Period1_Pod) AND
1054 DD.Deprn_Source_Code = 'D') OR
1055 (DD.Deprn_Run_Date BETWEEN AH.Date_Effective AND
1056 NVL(AH.Date_Ineffective, DD.Deprn_Run_Date) AND
1057 DD.Deprn_Source_Code = 'B') OR
1058 (Period1_Pod BETWEEN AH.Date_Effective AND
1059 NVL(AH.Date_Ineffective, Period1_Pod) AND
1060 -- DD.Deprn_Source_Code = 'T' AND
1061 nvl(Report_Style,'S')= decode(substr(report_type,-4),'COST', nvl(Report_Style,'S'),'D'))) /*Bug# 9293000 */
1062 AND CB.Category_ID = AH.Category_ID
1063 AND CB.Book_Type_Code = Book
1064 AND DECODE (Report_Type,
1065 'COST', DECODE (AH.Asset_Type,
1066 'CAPITALIZED', CB.Asset_Cost_Acct,
1067 DECODE(Report_Style,'D',DECODE(AH.Asset_Type,'GROUP',CB.Asset_Cost_Acct,null),
1068 null)), -- Added for second decode for drill down report
1069 'CIP COST',
1070 DECODE (AH.Asset_Type,
1071 'CIP', CB.CIP_Cost_Acct,
1072 null),
1073 'RESERVE', CB.Deprn_Reserve_Acct,
1074 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
1075 AND BK.Book_Type_Code = CB.book_type_code
1076 AND BK.Asset_ID = DD.Asset_ID AND
1077 DECODE(DD.Deprn_Source_Code, 'D', Period1_pod,
1078 nvl(Period1_pcd,Period1_pod)) BETWEEN /*Bug# 9293000 */
1079 BK.Date_Effective AND
1080 NVL(BK.Date_Ineffective, sysdate) AND
1081 NVL(BK.Period_Counter_Fully_Retired, Period1_PC)
1082 > period1_pc - 1
1083 AND DD.set_of_books_id = h_sob_id
1084 AND BK.set_of_books_id = h_sob_id
1085 GROUP BY
1086 DH.Asset_ID,
1087 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID),
1088 DH.Code_Combination_ID,
1089 DH.LOCATION_ID,-- bug 16587270 null,
1090 DECODE (Report_Type,
1091 'COST', CB.Asset_Cost_Acct,
1092 'CIP COST', CB.CIP_Cost_Acct,
1093 'RESERVE', CB.Deprn_Reserve_Acct,
1094 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1095 CB.Asset_Cost_Acct,
1096 DECODE(Report_Type,
1097 'RESERVE', DECODE(DD.Deprn_Source_Code,
1098 'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
1099 'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
1100 'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
1101 'BEGIN'),
1102 DECODE (Report_Type,
1103 'COST', DD.Cost,
1104 'CIP COST', DD.Cost,
1105 'RESERVE', DD.Deprn_Reserve,
1106 'REVAL RESERVE', DD.Reval_Reserve),
1107 DD.COST
1108 UNION ALL-- Added to get assets added with reserve when multiple periods intervall bug 3756517
1109 SELECT
1110 DH.Asset_ID,
1111 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID),
1112 DH.Code_Combination_ID,
1113 null,
1114 DECODE (Report_Type,
1115 'RESERVE', CB.Deprn_Reserve_Acct,
1116 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1117 CB.Asset_Cost_Acct,
1118 DECODE(Report_Type,
1119 'RESERVE', DECODE(DD.Deprn_Source_Code,
1120 'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
1121 'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
1122 'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
1123 'BEGIN'),
1124 DECODE (Report_Type,
1125 'COST', DD.Cost,
1126 'CIP COST', DD.Cost,
1127 'RESERVE', DD.Deprn_Reserve,
1128 'REVAL RESERVE', DD.Reval_Reserve),
1129 DD.COST
1130 FROM
1131 FA_CATEGORY_BOOKS CB,
1132 FA_DISTRIBUTION_HISTORY DH,
1133 FA_ASSET_HISTORY AH,
1134 FA_MC_DEPRN_DETAIL DD,
1135 FA_MC_BOOKS BK,
1136 FA_MC_DEPRN_PERIODS DP_BROW
1137 WHERE DH.Book_Type_Code = distribution_source_book
1138 AND DD.Asset_ID = AH.Asset_ID AND
1139 DD.Book_Type_Code = book AND
1140 DD.Distribution_ID = DH.Distribution_ID AND
1141 dd.deprn_reserve <> 0 AND
1142 dd.deprn_source_code = 'B' AND
1143 DD.Period_Counter between period1_pc-1 and period2_pc
1144 AND AH.Asset_ID = DH.Asset_ID AND
1145 ( (AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
1146 Report_Type IN ('RESERVE', 'REVAL RESERVE')))
1147 AND
1148 DP_BROW.book_type_code = dd.book_type_code and
1149 DP_BROW.period_counter = dd.period_counter +1
1150 and NVL(DP_BROW.period_close_date,SYSDATE) between ah.date_effective and nvl(ah.date_ineffective,sysdate)
1151 AND CB.Category_ID = AH.Category_ID AND
1152 CB.Book_Type_Code = book
1153 AND DECODE (Report_Type,
1154 'RESERVE', CB.Deprn_Reserve_Acct,
1155 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
1156 AND BK.Book_Type_Code = CB.book_type_code
1157 AND ((DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
1158 AND BK.Asset_ID = DD.Asset_ID AND
1159 ah.date_effective between BK.Date_Effective AND
1160 NVL(BK.Date_Ineffective, sysdate)
1161 AND DD.set_of_books_id = h_sob_id
1162 AND BK.set_of_books_id = h_sob_id
1163 AND DP_BROW.set_of_books_id = h_sob_id;
1164 end if;
1165 -- End MRC
1166
1167 h_mesg_name := 'FA_RX_END_BALANCES';
1168 if(h_mrcsobtype <> 'R') then -- MRC
1169 INSERT INTO FA_BALANCES_REPORT_GT
1170 (Asset_ID,
1171 Group_Asset_ID, -- Added for Drill Down Report
1172 Distribution_CCID,
1173 Adjustment_CCID,
1174 Category_Books_Account,
1175 Cost_Account,
1176 Source_Type_Code,
1177 Amount)
1178 SELECT
1179 DH.Asset_ID,
1180 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
1181 DH.Code_Combination_ID,
1182 null,
1183 DECODE (Report_Type,
1184 'COST', CB.Asset_Cost_Acct,
1185 'CIP COST', CB.CIP_Cost_Acct,
1186 'RESERVE', CB.Deprn_Reserve_Acct,
1187 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1188 CB.Asset_Cost_Acct,
1189 DECODE(Report_Type,
1190 'RESERVE', DECODE(DD.Deprn_Source_Code,
1191 'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
1192 'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
1193 'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
1194 'END'),
1195 DECODE (Report_Type,
1196 'COST', DD.Cost,
1197 'CIP COST', DD.Cost,
1198 'RESERVE', DD.Deprn_Reserve,
1199 'REVAL RESERVE', DD.Reval_Reserve)
1200 +
1201 nvl( SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) * AJ.Adjustment_Amount),0)
1202 FROM
1203 FA_CATEGORY_BOOKS CB,
1204 FA_DISTRIBUTION_HISTORY DH,
1205 FA_ASSET_HISTORY AH,
1206 FA_DEPRN_DETAIL DD,
1207 FA_BOOKS BK,
1208 FA_ADJUSTMENTS AJ
1209 WHERE DH.Book_Type_Code = Distribution_Source_Book
1210 AND DD.Asset_ID = AH.Asset_ID AND
1211 DD.Book_Type_Code = Book AND
1212 DD.Distribution_ID = DH.Distribution_ID AND
1213 DD.Period_Counter <= period2_pc AND
1214 DD.Period_Counter =
1215 (SELECT MAX (SUB_DD.Period_Counter)
1216 FROM FA_DEPRN_DETAIL SUB_DD
1217 WHERE SUB_DD.Asset_ID = DH.Asset_ID AND
1218 SUB_DD.Book_Type_Code = Book AND
1219 SUB_DD.Distribution_ID = DH.Distribution_ID AND
1220 SUB_DD.Period_Counter <= period2_pc) AND
1221 AJ.Asset_ID(+) = DD.Asset_ID AND
1222 AJ.Book_type_code(+) = Book AND
1223 AJ.Distribution_ID(+) = DD.Distribution_ID AND
1224 AJ.Adjustment_Type(+) = Report_Type AND
1225 AJ.Period_Counter_Created(+) BETWEEN
1226 (DD.Period_Counter+1) AND Period2_PC
1227 -- Added for bug#4860955
1228 and Period2_pcd between dh.date_effective and nvl(dh.date_ineffective,Period2_pcd)
1229 AND AH.Asset_ID = DH.Asset_ID AND
1230 ((AH.Asset_Type <> 'EXPENSED' AND
1231 Report_Type IN ('COST', 'CIP COST')) OR
1232 (AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
1233 Report_Type IN ('RESERVE', 'REVAL RESERVE')) OR
1234 (AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D'))
1235 AND ((Period2_Pcd BETWEEN AH.Date_Effective AND
1236 NVL(AH.Date_Ineffective, Period2_Pcd) AND
1237 DD.Deprn_Source_Code = 'D') OR
1238 (DD.deprn_run_date between ah.date_effective AND
1239 NVL(ah.date_ineffective, DD.Deprn_run_date) AND
1240 DD.deprn_source_code = 'B') OR
1241 (Period2_Pcd BETWEEN AH.Date_Effective AND
1242 NVL(AH.Date_Ineffective, Period2_Pcd) AND
1243 DD.Deprn_Source_Code = decode(substr(report_type,-4),'COST', DD.Deprn_Source_Code, 'T') AND
1244 nvl(Report_Style,'S')= decode(substr(report_type,-4),'COST', nvl(Report_Style,'S'),'D') ))
1245 AND DD.deprn_source_code in ('D', 'T')
1246 AND CB.Category_ID = AH.Category_ID AND
1247 CB.Book_Type_Code = Book
1248 AND DECODE (Report_Type,
1249 'COST', DECODE (AH.Asset_Type,
1250 'CAPITALIZED', CB.Asset_Cost_Acct,
1251 DECODE(Report_Style,'D',DECODE(AH.Asset_Type,'GROUP',CB.Asset_Cost_Acct,null),
1252 null)),
1253 'CIP COST',
1254 DECODE (AH.Asset_Type,
1255 'CIP', CB.CIP_Cost_Acct,
1256 null),
1257 'RESERVE', CB.Deprn_Reserve_Acct,
1258 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
1259 AND ((DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
1260 AND BK.Book_Type_Code = CB.book_type_code AND
1261 BK.Asset_ID = DD.Asset_ID AND
1262 Period2_pcd BETWEEN
1263 BK.Date_Effective AND
1264 NVL(BK.Date_Ineffective, sysdate) AND
1265 NVL(BK.Period_Counter_Fully_Retired, Period2_PC+1)
1266 > period1_pc - 1
1267 GROUP BY
1268 DH.Asset_ID,
1269 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
1270 DH.Code_Combination_ID,
1271 DH.LOCATION_ID,-- bug 16587270 null,
1272 DECODE (Report_Type,
1273 'COST', CB.Asset_Cost_Acct,
1274 'CIP COST', CB.CIP_Cost_Acct,
1275 'RESERVE', CB.Deprn_Reserve_Acct,
1276 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1277 CB.Asset_Cost_Acct,
1278 DECODE(Report_Type,
1279 'RESERVE', DECODE(DD.Deprn_Source_Code,
1280 'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
1281 'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
1282 'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
1283 'END'),
1284 DECODE (Report_Type,
1285 'COST', DD.Cost,
1286 'CIP COST', DD.Cost,
1287 'RESERVE', DD.Deprn_Reserve,
1288 'REVAL RESERVE', DD.Reval_Reserve) ;
1289
1290 -- MRC
1291 else
1292 INSERT INTO FA_BALANCES_REPORT_GT
1293 (Asset_ID,
1294 Group_Asset_ID, -- Added for Drill Down Report
1295 Distribution_CCID,
1296 Adjustment_CCID,
1297 Category_Books_Account,
1298 Cost_Account,
1299 Source_Type_Code,
1300 Amount)
1301 SELECT
1302 DH.Asset_ID,
1303 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
1304 DH.Code_Combination_ID,
1305 null,
1306 DECODE (Report_Type,
1307 'COST', CB.Asset_Cost_Acct,
1308 'CIP COST', CB.CIP_Cost_Acct,
1309 'RESERVE', CB.Deprn_Reserve_Acct,
1310 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1311 CB.Asset_Cost_Acct,
1312 DECODE(Report_Type,
1313 'RESERVE', DECODE(DD.Deprn_Source_Code,
1314 'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
1315 'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
1316 'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
1317 'END'),
1318 DECODE (Report_Type,
1319 'COST', DD.Cost,
1320 'CIP COST', DD.Cost,
1321 'RESERVE', DD.Deprn_Reserve,
1322 'REVAL RESERVE', DD.Reval_Reserve)
1323 +
1324 nvl( SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) * AJ.Adjustment_Amount),0)
1325 FROM
1326 FA_CATEGORY_BOOKS CB,
1327 FA_DISTRIBUTION_HISTORY DH,
1328 FA_ASSET_HISTORY AH,
1329 FA_MC_DEPRN_DETAIL DD,
1330 FA_MC_BOOKS BK,
1331 FA_MC_ADJUSTMENTS AJ
1332 WHERE DH.Book_Type_Code = Distribution_Source_Book
1333 AND DD.Asset_ID = AH.Asset_ID AND
1334 DD.Book_Type_Code = Book AND
1335 DD.Distribution_ID = DH.Distribution_ID AND
1336 DD.Period_Counter <= period2_pc AND
1337 DD.Period_Counter =
1338 (SELECT MAX (SUB_DD.Period_Counter)
1339 FROM FA_MC_DEPRN_DETAIL SUB_DD
1340 WHERE SUB_DD.Asset_ID = DH.Asset_ID AND
1341 SUB_DD.Book_Type_Code = Book AND
1342 SUB_DD.Distribution_ID = DH.Distribution_ID AND
1343 SUB_DD.Period_Counter <= period2_pc AND
1344 SUB_DD.set_of_books_id = h_sob_id) AND
1345 AJ.Asset_ID(+) = DD.Asset_ID AND
1346 AJ.Book_type_code(+) = Book AND
1347 AJ.Distribution_ID(+) = DD.Distribution_ID AND
1348 AJ.Adjustment_Type(+) = Report_Type AND
1349 AJ.set_of_books_id(+) = h_sob_id AND
1350 AJ.Period_Counter_Created(+) BETWEEN
1351 (DD.Period_Counter+1) AND Period2_PC
1352 -- Added for bug#4860955
1353 and Period2_pcd between dh.date_effective and nvl(dh.date_ineffective,Period2_pcd)
1354 AND AH.Asset_ID = DH.Asset_ID AND
1355 ((AH.Asset_Type <> 'EXPENSED' AND
1356 Report_Type IN ('COST', 'CIP COST')) OR
1357 (AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
1358 Report_Type IN ('RESERVE', 'REVAL RESERVE')) OR
1359 (AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D'))
1360 AND ((Period2_Pcd BETWEEN AH.Date_Effective AND
1361 NVL(AH.Date_Ineffective, Period2_Pcd) AND
1362 DD.Deprn_Source_Code = 'D') OR
1363 (DD.deprn_run_date between ah.date_effective AND
1364 NVL(ah.date_ineffective, DD.Deprn_run_date) AND
1365 DD.deprn_source_code = 'B') OR
1366 (Period2_Pcd BETWEEN AH.Date_Effective AND
1367 NVL(AH.Date_Ineffective, Period2_Pcd) AND
1368 DD.Deprn_Source_Code = decode(substr(report_type,-4),'COST', DD.Deprn_Source_Code, 'T') AND
1369 nvl(Report_Style,'S')= decode(substr(report_type,-4),'COST', nvl(Report_Style,'S'),'D') ))
1370 AND DD.deprn_source_code in ('D', 'T')
1371 AND CB.Category_ID = AH.Category_ID AND
1372 CB.Book_Type_Code = Book
1373 AND DECODE (Report_Type,
1374 'COST', DECODE (AH.Asset_Type,
1375 'CAPITALIZED', CB.Asset_Cost_Acct,
1376 DECODE(Report_Style,'D',DECODE(AH.Asset_Type,'GROUP',CB.Asset_Cost_Acct,null),
1377 null)),
1378 'CIP COST',
1379 DECODE (AH.Asset_Type,
1380 'CIP', CB.CIP_Cost_Acct,
1381 null),
1382 'RESERVE', CB.Deprn_Reserve_Acct,
1383 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
1384 AND ((DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
1385 AND BK.Book_Type_Code = CB.book_type_code AND
1386 BK.Asset_ID = DD.Asset_ID AND
1387 Period2_pcd BETWEEN
1388 BK.Date_Effective AND
1389 NVL(BK.Date_Ineffective, sysdate) AND
1390 NVL(BK.Period_Counter_Fully_Retired, Period2_PC+1)
1391 > period1_pc - 1
1392 AND DD.set_of_books_id = h_sob_id
1393 AND BK.set_of_books_id = h_sob_id
1394 GROUP BY
1395 DH.Asset_ID,
1396 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
1397 DH.Code_Combination_ID,
1398 DH.LOCATION_ID,-- bug 16587270 null,
1399 DECODE (Report_Type,
1400 'COST', CB.Asset_Cost_Acct,
1401 'CIP COST', CB.CIP_Cost_Acct,
1402 'RESERVE', CB.Deprn_Reserve_Acct,
1403 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1404 CB.Asset_Cost_Acct,
1405 DECODE(Report_Type,
1406 'RESERVE', DECODE(DD.Deprn_Source_Code,
1407 'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
1408 'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
1409 'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
1410 'END'),
1411 DECODE (Report_Type,
1412 'COST', DD.Cost,
1413 'CIP COST', DD.Cost,
1414 'RESERVE', DD.Deprn_Reserve,
1415 'REVAL RESERVE', DD.Reval_Reserve);
1416
1417 end if;
1418 -- End MRC
1419
1420 h_mesg_name := 'FA_INS_ADJ_GET_VOST_ADJS';
1421
1422 if(h_mrcsobtype <> 'R') then -- MRC
1423 INSERT INTO FA_BALANCES_REPORT_GT
1424 (Asset_ID,
1425 Group_Asset_ID, -- Added for Drill Down Report
1426 Distribution_CCID,
1427 Adjustment_CCID,
1428 Category_Books_Account,
1429 Cost_Account,
1430 Source_Type_Code,
1431 Amount)
1432 SELECT
1433 DH.Asset_ID,
1434 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
1435 DH.Code_Combination_ID,
1436 decode(nvl(Report_Style,'S'),'D',null,lines.code_combination_id),
1437 DECODE (Report_Type,
1438 'COST', CB.Asset_Cost_Acct,
1439 'CIP COST', CB.CIP_Cost_Acct,
1440 'RESERVE', CB.Deprn_Reserve_Acct,
1441 'REVAL RESERVE', CB.Reval_Reserve_Acct), -- Bug 8616752
1442 CB.Asset_Cost_Acct,
1443 AJ.Source_Type_Code,
1444 SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
1445 AJ.Adjustment_Amount)
1446 FROM
1447 FA_CATEGORY_BOOKS CB,
1448 FA_ASSET_HISTORY AH1,
1449 FA_DISTRIBUTION_HISTORY DH,
1450 FA_TRANSACTION_HEADERS TH,
1451 FA_ASSET_HISTORY AH,
1452 FA_ADJUSTMENTS AJ,
1453 FA_BOOKS BK
1454 /* SLA changes */
1455 ,xla_ae_headers headers
1456 ,xla_ae_lines lines
1457 ,xla_distribution_links links
1458 ,fa_book_controls bc
1459 WHERE BC.BOOK_TYPE_CODE = Book
1460 AND DH.Book_Type_Code = Distribution_Source_Book
1461 AND AJ.Asset_ID = DH.Asset_ID AND
1462 AJ.Book_Type_Code = Book AND
1463 AJ.Distribution_ID = DH.Distribution_ID AND
1464 AJ.Adjustment_Type in
1465 (Report_Type, DECODE(Report_Type,
1466 'REVAL RESERVE', 'REVAL AMORT')) AND
1467 AJ.Period_Counter_Created BETWEEN
1468 Period1_PC AND Period2_PC
1469 AND TH.Transaction_Header_ID = AJ.Transaction_Header_ID
1470 AND AH.Asset_ID = DH.Asset_ID AND
1471 ((AH.Asset_Type <> 'EXPENSED' AND
1472 Report_Type IN ('COST', 'CIP COST')) OR
1473 (AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
1474 Report_Type IN ('RESERVE', 'REVAL RESERVE')) OR
1475 (AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D')) AND
1476 TH.Transaction_Header_ID BETWEEN
1477 AH.Transaction_Header_ID_In AND
1478 NVL (AH.Transaction_Header_ID_Out - 1,
1479 TH.Transaction_Header_ID)
1480 AND AJ.Adjustment_Amount <> 0
1481 AND AH1.ASSET_ID = AJ.ASSET_ID /* StatReq */
1482 AND AH1.DATE_EFFECTIVE <= DH.DATE_EFFECTIVE /* StatReq */
1483 AND NVL(AH1.DATE_INEFFECTIVE, SYSDATE) > DH.DATE_EFFECTIVE /* StatReq */
1484 AND CB.Category_ID = AH1.Category_ID AND /* StatReq */
1485 CB.Book_Type_Code = Book /* StatReq */
1486 AND BK.Book_Type_Code = Book -- Added for Drill Down Report
1487 AND BK.Asset_ID = DH.Asset_ID
1488 -- AND BK.DATE_EFFECTIVE <= DH.DATE_EFFECTIVE
1489 -- AND NVL(BK.DATE_INEFFECTIVE, SYSDATE) > DH.DATE_EFFECTIVE
1490 AND BK.DATE_EFFECTIVE <= nvl(DH.DATE_INEFFECTIVE, sysdate)
1491 AND NVL(BK.DATE_INEFFECTIVE, SYSDATE + 1) > nvl(DH.DATE_INEFFECTIVE, sysdate)
1492 AND (AJ.track_member_flag is null OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
1493 /* SLA Changes */
1494 and links.Source_distribution_id_num_1(+) = aj.transaction_header_id
1495 and links.Source_distribution_id_num_2(+) = aj.adjustment_line_id
1496 and links.application_id(+) = 140
1497 and links.source_distribution_type(+) = 'TRX'
1498 and headers.ae_header_id(+) = links.ae_header_id
1499 and headers.application_id(+) = 140
1500 and lines.ae_header_id(+) = links.ae_header_id
1501 and lines.ae_line_num(+) = links.ae_line_num
1502 and lines.application_id(+) = 140
1503 GROUP BY
1504 DH.Asset_ID,
1505 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
1506 DH.Code_Combination_ID,
1507 decode(nvl(Report_Style,'S'),'D',null,lines.code_combination_id), --AJ.Code_Combination_ID
1508 DECODE (Report_Type,
1509 'COST', CB.Asset_Cost_Acct,
1510 'CIP COST', CB.CIP_Cost_Acct,
1511 'RESERVE', CB.Deprn_Reserve_Acct,
1512 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1513 CB.Asset_Cost_Acct,
1514 AJ.Source_Type_Code;
1515
1516 -- MRC
1517 else
1518 INSERT INTO FA_BALANCES_REPORT_GT
1519 (Asset_ID,
1520 Group_Asset_ID, -- Added for Drill Down Report
1521 Distribution_CCID,
1522 Adjustment_CCID,
1523 Category_Books_Account,
1524 Cost_Account,
1525 Source_Type_Code,
1526 Amount)
1527 SELECT
1528 DH.Asset_ID,
1529 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
1530 DH.Code_Combination_ID,
1531 decode(nvl(Report_Style,'S'),'D',null,lines.code_combination_id), --AJ.Code_Combination_ID,
1532 DECODE (Report_Type,
1533 'COST', CB.Asset_Cost_Acct,
1534 'CIP COST', CB.CIP_Cost_Acct,
1535 'RESERVE', CB.Deprn_Reserve_Acct,
1536 'REVAL RESERVE', CB.Reval_Reserve_Acct), -- Bug 8616752
1537 CB.Asset_Cost_Acct,
1538 AJ.Source_Type_Code,
1539 SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
1540 AJ.Adjustment_Amount)
1541 FROM
1542 FA_CATEGORY_BOOKS CB,
1543 FA_ASSET_HISTORY AH1,
1544 FA_DISTRIBUTION_HISTORY DH,
1545 FA_TRANSACTION_HEADERS TH,
1546 FA_ASSET_HISTORY AH,
1547 FA_MC_ADJUSTMENTS AJ,
1548 FA_MC_BOOKS BK
1549 /* SLA changes */
1550 ,xla_ae_headers headers
1551 ,xla_ae_lines lines
1552 ,xla_distribution_links links
1553 ,fa_mc_book_controls bc
1554 WHERE BC.BOOK_TYPE_CODE = Book
1555 AND DH.Book_Type_Code = Distribution_Source_Book
1556 AND AJ.Asset_ID = DH.Asset_ID AND
1557 AJ.Book_Type_Code = Book AND
1558 AJ.Distribution_ID = DH.Distribution_ID AND
1559 AJ.Adjustment_Type in
1560 (Report_Type, DECODE(Report_Type,
1561 'REVAL RESERVE', 'REVAL AMORT')) AND
1562 AJ.Period_Counter_Created BETWEEN
1563 Period1_PC AND Period2_PC
1564 AND TH.Transaction_Header_ID = AJ.Transaction_Header_ID
1565 AND AH.Asset_ID = DH.Asset_ID AND
1566 ((AH.Asset_Type <> 'EXPENSED' AND
1567 Report_Type IN ('COST', 'CIP COST')) OR
1568 (AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
1569 Report_Type IN ('RESERVE', 'REVAL RESERVE')) OR
1570 (AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D')) AND
1571 TH.Transaction_Header_ID BETWEEN
1572 AH.Transaction_Header_ID_In AND
1573 NVL (AH.Transaction_Header_ID_Out - 1,
1574 TH.Transaction_Header_ID)
1575 AND AJ.Adjustment_Amount <> 0
1576 AND AH1.ASSET_ID = AJ.ASSET_ID /* StatReq */
1577 AND AH1.DATE_EFFECTIVE <= DH.DATE_EFFECTIVE /* StatReq */
1578 AND NVL(AH1.DATE_INEFFECTIVE, SYSDATE) > DH.DATE_EFFECTIVE /* StatReq */
1579 AND CB.Category_ID = AH1.Category_ID AND /* StatReq */
1580 CB.Book_Type_Code = Book /* StatReq */
1581 AND BK.Book_Type_Code = Book -- Added for Drill Down Report
1582 AND BK.Asset_ID = DH.Asset_ID
1583 -- AND BK.DATE_EFFECTIVE <= DH.DATE_EFFECTIVE
1584 -- AND NVL(BK.DATE_INEFFECTIVE, SYSDATE) > DH.DATE_EFFECTIVE
1585 AND BK.DATE_EFFECTIVE <= nvl(DH.DATE_INEFFECTIVE, sysdate)
1586 AND NVL(BK.DATE_INEFFECTIVE, SYSDATE + 1) > nvl(DH.DATE_INEFFECTIVE, sysdate)
1587 AND AJ.set_of_books_id = h_sob_id
1588 AND BK.set_of_books_id = h_sob_id
1589 AND (AJ.track_member_flag is null OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
1590 /* SLA Changes */
1591 and links.Source_distribution_id_num_1(+) = aj.transaction_header_id
1592 and links.Source_distribution_id_num_2(+) = aj.adjustment_line_id
1593 and links.application_id(+) = 140
1594 and links.source_distribution_type(+) = 'TRX'
1595 and headers.ae_header_id(+) = links.ae_header_id
1596 and headers.application_id(+) = 140
1597 and lines.ae_header_id(+) = links.ae_header_id
1598 and lines.ae_line_num(+) = links.ae_line_num
1599 and lines.application_id(+) = 140
1600 GROUP BY
1601 DH.Asset_ID,
1602 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
1603 DH.Code_Combination_ID,
1604 decode(nvl(Report_Style,'S'),'D',null,lines.code_combination_id), --AJ.Code_Combination_ID,
1605 DECODE (Report_Type,
1606 'COST', CB.Asset_Cost_Acct,
1607 'CIP COST', CB.CIP_Cost_Acct,
1608 'RESERVE', CB.Deprn_Reserve_Acct,
1609 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1610 CB.Asset_Cost_Acct,
1611 AJ.Source_Type_Code;
1612 end if;
1613 -- End MRC
1614
1615 if (Report_Type = 'RESERVE' or Report_Type = 'REVAL RESERVE') then
1616 -- Get_Deprn_Effects (Book, Distribution_Source_Book,
1617 -- Period1_PC, Period2_PC,
1618 -- Report_Type);
1619
1620 h_mesg_name := 'FA_INS_ADJ_GET_RSV_ADJ';
1621 if(h_mrcsobtype <> 'R') then -- MRC
1622 INSERT INTO FA_BALANCES_REPORT_GT
1623 (Asset_ID,
1624 Group_Asset_ID, -- Added for Drill Down Report
1625 Distribution_CCID,
1626 Adjustment_CCID,
1627 Category_Books_Account,
1628 Cost_Account,
1629 Source_Type_Code,
1630 Amount)
1631 SELECT DH.Asset_ID,
1632 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
1633 DH.Code_Combination_ID,
1634 null,
1635 DECODE (Report_Type,
1636 'RESERVE', CB.Deprn_Reserve_Acct,
1637 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1638 CB.Asset_Cost_Acct,
1639 DECODE(DD.Deprn_Source_Code,
1640 'D', 'DEPRECIATION', 'T', 'DEPRECIATION', 'ADDITION'),
1641 SUM (DECODE (Report_Type,
1642 'RESERVE', DD.Deprn_Amount,
1643 'REVAL RESERVE', -DD.Reval_Amortization))
1644 FROM
1645 FA_CATEGORY_BOOKS CB,
1646 FA_DISTRIBUTION_HISTORY DH,
1647 FA_ASSET_HISTORY AH,
1648 FA_DEPRN_DETAIL DD,
1649 FA_DEPRN_PERIODS DP,
1650 FA_BOOKS BK -- Added for Drill Down Report
1651 WHERE DH.Book_Type_Code = Distribution_Source_Book
1652 AND AH.Asset_ID = DH.Asset_ID
1653 AND ((AH.Asset_Type in ('CAPITALIZED', 'GROUP')) OR
1654 AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D') -- Added for Drill Down Report
1655 AND AH.Date_Effective <= nvl(DP.Period_Close_Date,sysdate) AND
1656 NVL (AH.Date_Ineffective, SYSDATE) >= nvl(DP.Period_Open_Date,sysdate)
1657 AND AH.Date_Effective < nvl(DH.Date_Ineffective,sysdate) AND
1658 NVL (AH.Date_Ineffective, SYSDATE) > nvl(DH.Date_Effective,sysdate)
1659 AND CB.Category_ID = AH.Category_ID AND
1660 CB.Book_Type_Code = Book
1661 AND ((DD.Deprn_Source_Code = 'B'
1662 AND DD.Period_Counter < Period2_PC) OR
1663 (DD.Deprn_Source_Code = 'D') OR
1664 (DD.Deprn_Source_Code = 'T' and nvl(Report_Style,'S') = 'D')) AND
1665 DD.Book_Type_Code = Book AND
1666 DD.Asset_ID = DH.Asset_ID AND
1667 DD.Distribution_ID = DH.Distribution_ID AND
1668 DD.Period_Counter between
1669 Period1_PC and Period2_PC
1670 AND DP.Book_Type_Code = DD.Book_Type_Code AND
1671 DP.Period_Counter = DD.Period_Counter
1672 AND DECODE (Report_Type,
1673 'RESERVE', CB.Deprn_Reserve_Acct,
1674 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
1675 AND ((DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
1676 AND DECODE (Report_Type,
1677 'RESERVE', DD.Deprn_Amount,
1678 'REVAL RESERVE', NVL(DD.Reval_Amortization,0)) <> 0
1679 AND BK.Book_Type_Code = Book -- Added for Drill Down Report
1680 AND BK.Asset_ID = DH.Asset_ID
1681 AND nvl(DP.Period_Close_Date,sysdate) BETWEEN BK.Date_Effective and nvl(BK.Date_Ineffective, Sysdate)
1682 GROUP BY
1683 DH.Asset_ID,
1684 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
1685 DH.Code_Combination_ID,
1686 DECODE (Report_Type,
1687 'RESERVE', CB.Deprn_Reserve_Acct,
1688 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1689 CB.Asset_Cost_Acct,
1690 DD.Deprn_Source_Code;
1691
1692 -- MRC
1693 else
1694 INSERT INTO FA_BALANCES_REPORT_GT
1695 (Asset_ID,
1696 Group_Asset_ID, -- Added for Drill Down Report
1697 Distribution_CCID,
1698 Adjustment_CCID,
1699 Category_Books_Account,
1700 Cost_Account,
1701 Source_Type_Code,
1702 Amount)
1703 SELECT DH.Asset_ID,
1704 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
1705 DH.Code_Combination_ID,
1706 null,
1707 DECODE (Report_Type,
1708 'RESERVE', CB.Deprn_Reserve_Acct,
1709 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1710 CB.Asset_Cost_Acct,
1711 DECODE(DD.Deprn_Source_Code,
1712 'D', 'DEPRECIATION', 'T', 'DEPRECIATION', 'ADDITION'),
1713 SUM (DECODE (Report_Type,
1714 'RESERVE', DD.Deprn_Amount,
1715 'REVAL RESERVE', -DD.Reval_Amortization))
1716 FROM
1717 FA_CATEGORY_BOOKS CB,
1718 FA_DISTRIBUTION_HISTORY DH,
1719 FA_ASSET_HISTORY AH,
1720 FA_MC_DEPRN_DETAIL DD,
1721 FA_MC_DEPRN_PERIODS DP,
1722 FA_MC_BOOKS BK -- Added for Drill Down Report
1723 WHERE DH.Book_Type_Code = Distribution_Source_Book
1724 AND AH.Asset_ID = DH.Asset_ID
1725 AND ((AH.Asset_Type in ('CAPITALIZED', 'GROUP')) OR
1726 AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D') -- Added for Drill Down Report
1727 AND AH.Date_Effective <= nvl(DP.Period_Close_Date,sysdate) AND
1728 NVL (AH.Date_Ineffective, SYSDATE) >= nvl(DP.Period_Open_Date,sysdate)
1729 AND AH.Date_Effective < nvl(DH.Date_Ineffective,sysdate) AND
1730 NVL (AH.Date_Ineffective, SYSDATE) > nvl(DH.Date_Effective,sysdate)
1731 AND CB.Category_ID = AH.Category_ID AND
1732 CB.Book_Type_Code = Book
1733 AND ((DD.Deprn_Source_Code = 'B'
1734 AND DD.Period_Counter < Period2_PC) OR
1735 (DD.Deprn_Source_Code = 'D') OR
1736 (DD.Deprn_Source_Code = 'T' and nvl(Report_Style,'S') = 'D')) AND
1737 DD.Book_Type_Code = Book AND
1738 DD.Asset_ID = DH.Asset_ID AND
1739 DD.Distribution_ID = DH.Distribution_ID AND
1740 DD.Period_Counter between
1741 Period1_PC and Period2_PC
1742 AND DP.Book_Type_Code = DD.Book_Type_Code AND
1743 DP.Period_Counter = DD.Period_Counter
1744 AND DECODE (Report_Type,
1745 'RESERVE', CB.Deprn_Reserve_Acct,
1746 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
1747 AND ((DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
1748 AND DECODE (Report_Type,
1749 'RESERVE', DD.Deprn_Amount,
1750 'REVAL RESERVE', NVL(DD.Reval_Amortization,0)) <> 0
1751 AND BK.Book_Type_Code = Book -- Added for Drill Down Report
1752 AND BK.Asset_ID = DH.Asset_ID
1753 AND nvl(DP.Period_Close_Date,sysdate) BETWEEN BK.Date_Effective and nvl(BK.Date_Ineffective, Sysdate)
1754 AND DD.set_of_books_id = h_sob_id
1755 AND DP.set_of_books_id = h_sob_id
1756 AND BK.set_of_books_id = h_sob_id
1757 GROUP BY
1758 DH.Asset_ID,
1759 DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
1760 DH.Code_Combination_ID,
1761 DECODE (Report_Type,
1762 'RESERVE', CB.Deprn_Reserve_Acct,
1763 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1764 CB.Asset_Cost_Acct,
1765 DD.Deprn_Source_Code;
1766 end if;
1767 -- End MRC
1768
1769 end if;
1770
1771
1772 -- Get segment numbers corresponding to the given structure_id.
1773 -- Will need these later for getting segments for given ccids.
1774
1775 h_mesg_name := 'FA_RX_SEGNUMS';
1776
1777 fa_rx_shared_pkg.get_acct_segment_numbers (
1778 BOOK => Book,
1779 BALANCING_SEGNUM => gl_balancing_seg,
1780 ACCOUNT_SEGNUM => gl_account_seg,
1781 CC_SEGNUM => fa_cost_ctr_seg,
1782 CALLING_FN => 'FA_BALANCES_REPORT');
1783
1784 /* StatReq - The following statement has been added to get the natural account segment's valueset */
1785
1786 return_status := FND_FLEX_APIS.GET_SEGMENT_INFO
1787 (101, 'GL#', Acct_Flex_Structure, Gl_Account_Seg,
1788 Acct_Appl_Col, Acct_Segname, Acct_Prompt, Acct_Valueset_Name);
1789
1790 fa_rx_shared_pkg.get_acct_segment_index (
1791 BOOK => Book,
1792 BALANCING_SEGNUM => gl_balancing_seg,
1793 ACCOUNT_SEGNUM => gl_account_seg,
1794 CC_SEGNUM => fa_cost_ctr_seg,
1795 CALLING_FN => 'FA_BALANCES_REPORT');
1796
1797
1798
1799 -- open non_qualified_segs;
1800 -- loop
1801 -- fetch non_qualified_segs into
1802 -- h_nonqual_col_name,
1803 -- h_nonqual_seg_name,
1804 -- h_nonqual_seg_num;
1805 --
1806 -- if (non_qualified_segs%NOTFOUND) then exit; end if;
1807 --
1808 -- fa_rx_dynamic_columns_pkg.add_column (
1809 -- X_request_id => h_request_id,
1810 -- X_attribute_name => h_nonqual_seg_name,
1811 -- X_column_name => h_nonqual_col_name,
1812 -- X_ordering => 'NONE',
1813 -- X_BREAK => 'N',
1814 -- X_DISPLAY_LENGTH => 30,
1815 -- X_DISPLAY_FORMAT => 'VARCHAR2',
1816 -- X_DISPLAY_STATUS => 'YES',
1817 -- calling_fn => 'BALANCES REP');
1818 --
1819 -- end loop;
1820 -- close non_qualified_segs;
1821 --
1822 -- mesg := 'Error looping through adjustment ccids';
1823
1824 -- Each FA_BALANCES_REPORT_GT row corresponds to one of the following:
1825 -- (1) begin balance, (2) end balance, (3) an adjustment, (4)
1826 -- depreciation. Each corresponds to a given account. Sometimes,
1827 -- the account is the default from FA_CATEGORY_BOOKS; in this case
1828 -- the account segment itself is stored here and we can simply select
1829 -- it later. However, sometimes the "account" is stored as a ccid.
1830 -- In this case, we must find the accounting segment corresponding
1831 -- to that ccid, and store it now, so we can select it later.
1832 -- Use get_segments to find the accounting segment now. Doing it now
1833 -- vastly simplifies selecting later.
1834
1835 h_mesg_name := 'FA_RX_ADJ_SEGMENTS';
1836
1837 OPEN BAL_REPORT_AJCCID;
1838 loop
1839 fetch BAL_REPORT_AJCCID into
1840 h_br_ajccid,
1841 h_br_account,
1842 h_br_rowid;
1843
1844 if (BAL_REPORT_AJCCID%NOTFOUND) then exit; end if;
1845
1846 if (h_br_account is null and h_br_ajccid is not null ) then
1847
1848 h_mesg_name := 'FA_RX_CONCAT_SEGS';
1849 h_ccid_error := h_br_ajccid;
1850
1851 fa_rx_shared_pkg.get_acct_segments (
1852 combination_id => h_br_ajccid,
1853 n_segments => n_segs,
1854 segments => acct_all_segs,
1855 calling_fn => 'FA_BALANCES_REPORT');
1856
1857 h_mesg_name := 'FA_POST_SQL_UPDATE_TABLE';
1858 h_table_token := 'FA_BALANCES_REPORT_GT';
1859
1860 update fa_balances_report_gt
1861 set category_books_account = acct_all_segs(gl_account_seg)
1862 where rowid = h_br_rowid;
1863
1864 end if;
1865 end loop;
1866 CLOSE BAL_REPORT_AJCCID;
1867
1868
1869 -- Now report in data in FA_BALANCES_REPORT_GT (if an accum deprn
1870 -- or reval reserve report).
1871
1872 if (report_type in ('RESERVE','REVAL RESERVE')) then
1873
1874 mesg := 'Error getting reserve balances';
1875
1876
1877 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
1878
1879 if(h_mrcsobtype <> 'R') then -- MRC
1880 open RESERVE_REPORT (book, Period2_PCD); /* StatReq */
1881 -- MRC
1882 else
1883 open MC_RESERVE_REPORT (book, Period2_PCD);
1884 end if;
1885 -- End MRC
1886
1887 loop
1888
1889 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
1890
1891 if(h_mrcsobtype <> 'R') then -- MRC
1892 fetch RESERVE_REPORT into
1893 h_asset_id, /* StatReq */
1894 h_ccid,
1895 h_account,
1896 h_cost_account, /* StatReq */
1897 h_asset,
1898 h_tag_number,
1899 h_description, h_serial_number, h_inventorial, h_asset_key_ccid,
1900 h_category_id, /* StatReq */
1901 h_date_placed_in_service, /* StatReq */
1902 h_method_code, /* StatReq */
1903 h_life_in_months, /* StatReq */
1904 h_stl_method_flag, /* StatReq */
1905 h_rate_source_rule, /* StatReq */
1906 h_group_asset,
1907 h_cost_begin_balance, /* StatReq */
1908 h_begin,
1909 h_addition,
1910 h_adjustment, /*9293000 */
1911 h_depreciation,
1912 h_reclass,
1913 h_retirement,
1914 h_revaluation,
1915 h_tax,
1916 h_transfer,
1917 h_end;/*Bug# 9293000 */
1918
1919 if (RESERVE_REPORT%NOTFOUND) then exit; end if;
1920
1921 -- MRC
1922 else
1923 fetch MC_RESERVE_REPORT into
1924 h_asset_id, /* StatReq */
1925 h_ccid,
1926 h_account,
1927 h_cost_account, /* StatReq */
1928 h_asset,
1929 h_tag_number,
1930 h_description, h_serial_number, h_inventorial, h_asset_key_ccid,
1931 h_category_id, /* StatReq */
1932 h_date_placed_in_service, /* StatReq */
1933 h_method_code, /* StatReq */
1934 h_life_in_months, /* StatReq */
1935 h_stl_method_flag, /* StatReq */
1936 h_rate_source_rule, /* StatReq */
1937 h_group_asset,
1938 h_cost_begin_balance, /* StatReq */
1939 h_begin,
1940 h_addition,
1941 h_adjustment, /*9293000 */
1942 h_depreciation,
1943 h_reclass,
1944 h_retirement,
1945 h_revaluation,
1946 h_tax,
1947 h_transfer,
1948 h_end;/*Bug# 9293000 */
1949
1950 if (MC_RESERVE_REPORT%NOTFOUND) then exit; end if;
1951 end if;
1952 -- End MRC
1953
1954 h_mesg_name := 'FA_RX_CONCAT_SEGS';
1955 h_ccid_error := h_ccid;
1956
1957 fa_rx_shared_pkg.get_acct_segments (
1958 combination_id => h_ccid,
1959 n_segments => n_segs,
1960 segments => acct_all_segs,
1961 calling_fn => 'FA_BALANCES_REPORT');
1962
1963 /* StatReq - The following 6 function calls have been added to retrieve more
1964 detailed asset information */
1965
1966 h_account_description :=
1967 fa_rx_shared_pkg.get_flex_val_meaning(NULL, acct_valueset_name, h_account);
1968
1969 h_vendor_name :=
1970 fa_rx_shared_pkg.get_asset_info('VENDOR_NAME', h_asset_id, period1_pod, period2_pcd, book,
1971 acct_all_segs(gl_balancing_seg));
1972 h_invoice_number :=
1973 fa_rx_shared_pkg.get_asset_info('INVOICE_NUMBER', h_asset_id, period1_pod, period2_pcd, book,
1974 acct_all_segs(gl_balancing_seg));
1975 h_invoice_descr :=
1976 fa_rx_shared_pkg.get_asset_info('INVOICE_DESCR', h_asset_id, period1_pod, period2_pcd, book,
1977 acct_all_segs(gl_balancing_seg));
1978 h_location :=
1979 fa_rx_shared_pkg.get_asset_info('LOCATION', h_asset_id, period1_pod, period2_pcd, distribution_source_book,
1980 acct_all_segs(gl_balancing_seg));
1981 h_retirement_type :=
1982 fa_rx_shared_pkg.get_asset_info('RETIREMENT_TYPE',h_asset_id, period1_pod, period2_pcd, book,
1983 acct_all_segs(gl_balancing_seg));
1984
1985
1986 if h_sorp_enabled_flag = 'N' then
1987 h_adjustment := nvl(h_adjustment,0) + nvl(h_tax,0) + nvl(h_revaluation,0); /*9293000 */
1988 else
1989 h_adjustment := nvl(h_adjustment,0) + nvl(h_tax,0);
1990 end if;
1991
1992 if h_sorp_enabled_flag = 'Y' then
1993 if (nvl(h_begin,0) + nvl(h_addition,0) + nvl(h_depreciation,0)
1994 + nvl(h_reclass,0) - nvl(h_retirement,0) + nvl(h_transfer,0)
1995 + nvl(h_adjustment,0) + nvl(h_revaluation,0) = nvl(h_end,0))
1996 then
1997 h_out_of_bal_flag := 'N';
1998 else h_out_of_bal_flag := 'Y';
1999 end if;
2000 else
2001 if (nvl(h_begin,0) + nvl(h_addition,0) + nvl(h_depreciation,0)
2002 + nvl(h_reclass,0) - nvl(h_retirement,0) + nvl(h_transfer,0)
2003 + nvl(h_adjustment,0) = nvl(h_end,0)) then
2004 h_out_of_bal_flag := 'N';
2005 else h_out_of_bal_flag := 'Y';
2006 end if;
2007 end if;
2008
2009 if h_asset_key_ccid is not null then
2010 h_mesg_name := 'FA_RX_CONCAT_SEGS';
2011 h_flex_error := 'KEY#';
2012 h_ccid_error := h_asset_key_ccid;
2013
2014 fa_rx_shared_pkg.concat_asset_key (
2015 struct_id => h_key_flex_struct,
2016 ccid => h_asset_key_ccid,
2017 concat_string => h_concat_key,
2018 segarray => h_key_segs);
2019
2020 else
2021 h_concat_key := null;
2022
2023 end if;
2024
2025 /* StatReq - The following if statement has been added to retrieve the category
2026 each asset */
2027
2028 if h_category_id is not null then
2029 h_mesg_name := 'FA_RX_CONCAT_SEGS';
2030 h_flex_error := 'CAT#';
2031 h_ccid_error := h_category_id;
2032
2033 fa_rx_shared_pkg.concat_category (
2034 struct_id => h_cat_flex_struct,
2035 ccid => h_category_id,
2036 concat_string => h_concat_cat,
2037 segarray => h_cat_segs);
2038
2039 else
2040 h_concat_cat := null;
2041 end if;
2042
2043 /* StatReq - The following if statement has been added to calculate the annual depreciation rate
2044 for straight-line, calculated depreciation methods */
2045
2046 if (h_stl_method_flag = 'YES' and h_rate_source_rule = 'CALCULATED')
2047 then
2048 h_stl_depreciation_rate := 12 / h_life_in_months * 100;
2049
2050 -- MRC
2051 IF NOT FA_UTILS_PKG.faxrnd(
2052 X_amount => h_stl_depreciation_rate,
2053 X_book => book,
2054 X_set_of_books_id => h_sob_id,
2055 p_log_level_rec => null) then
2056
2057 success := FALSE;
2058 end if;
2059 -- End MRC
2060
2061 else
2062 h_stl_depreciation_rate := NULL;
2063 end if;
2064
2065 h_short_account_description := substr(h_account_description,1,239);
2066
2067 h_short_location := substr(h_location, 1, 499);
2068
2069 h_shortconcat_key := substr(h_concat_key,1,239);
2070
2071 -- Added following code for bug 5944006
2072 h_short_invoice_number := substrb(h_invoice_number, 1, 499);
2073 h_short_invoice_descr := substrb(h_invoice_descr, 1, 499);
2074 h_short_vendor_name := substrb(h_vendor_name, 1, 499);
2075 h_short_retirement_type := substrb(h_retirement_type, 1, 499);
2076 -- End of bug fix 5944006
2077
2078 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
2079 h_table_token := 'FA_BALANCES_REP_ITF';
2080
2081 -- insert into interface table
2082 insert /*+ noappend */ into fa_balances_rep_itf
2083 (request_id, company, cost_center, account,
2084 cost_account,
2085 inventorial, asset_key, asset_number, tag_number, description,
2086 category, deprn_method,
2087 account_description, date_placed_in_service, book_type_code,
2088 life_in_months, stl_depreciation_rate,
2089 concat_vendor_name, concat_invoice_number,
2090 concat_invoice_description, concat_location,
2091 concat_retirement_type, cost_begin_balance,
2092 begin_balance, additions,adjustments, /*9293000 */
2093 retirements, revaluations, reclasses, transfers, depreciation,
2094 amortization, end_balance, out_of_balance_flag, serial_number,
2095 created_by, creation_date,
2096 last_updated_by, last_update_date, last_update_login,
2097 group_asset_number ) values (
2098 Request_Id, acct_all_segs(gl_balancing_seg),
2099 acct_all_segs(fa_cost_ctr_seg), h_account,
2100 h_cost_account,
2101 h_inventorial, h_shortconcat_key,
2102 -- 'BAL','CC','ACCT',
2103 h_asset, h_tag_number, h_description,
2104 h_concat_cat, h_method_code,
2105 h_short_account_description, h_date_placed_in_service, book,
2106 h_life_in_months, h_stl_depreciation_rate,
2107 h_short_vendor_name, h_short_invoice_number, h_short_invoice_descr, -- bug 5944006
2108 h_short_location,
2109 h_short_retirement_type, h_cost_begin_balance, -- bug 5944006
2110 h_begin, h_addition,h_adjustment, h_retirement,
2111 h_revaluation, h_reclass, h_transfer, h_depreciation,
2112 h_depreciation, nvl(h_end,0), h_out_of_bal_flag, h_serial_number,
2113 User_Id, sysdate, user_id, sysdate, h_Login_id,
2114 h_group_asset);
2115
2116
2117 end loop;
2118
2119 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
2120
2121 if(h_mrcsobtype <> 'R') then -- MRC
2122 close RESERVE_REPORT;
2123 -- MRC
2124 else
2125 close MC_RESERVE_REPORT;
2126 end if;
2127 -- End MRC
2128 /* commented for 10131111. Do not uncomment unless required. See bug
2129 if nvl(Report_Style,'S') = 'D' then
2130 -- Following logic is summarize the member level amounts into group level
2131 For group_asset in GROUP_ASSETS loop
2132
2133 open GROUP_RESERVE_AMOUNTS(group_asset.group_asset_id);
2134 fetch GROUP_RESERVE_AMOUNTS into h_cost_begin_balance;
2135 close GROUP_RESERVE_AMOUNTS;
2136
2137 select distinct asset_number
2138 into h_group_asset
2139 from fa_additions
2140 where asset_id=group_asset.group_asset_id;
2141
2142 Update fa_balances_rep_itf
2143 set cost_begin_balance = h_cost_begin_balance
2144 where request_id = Request_Id
2145 and asset_number = h_group_asset;
2146
2147 end loop;
2148 end if; -- Report_Style
2149 /* commented for 10131111 */
2150 else
2151
2152
2153 -- Now report on data in FA_BALANCES_REPORT_GT (for asset cost and
2154 -- CIP cost reports).
2155
2156 mesg := 'Error selecting cost balances';
2157
2158 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
2159
2160 if(h_mrcsobtype <> 'R') then -- MRC
2161 open COST_REPORT (book, Period2_PCD, period1_pod);
2162 -- MRC
2163 else
2164 open MC_COST_REPORT (book, Period2_PCD, period1_pod);
2165 end if;
2166 -- End MRC
2167
2168 loop
2169 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
2170
2171 if(h_mrcsobtype <> 'R') then -- MRC
2172 fetch COST_REPORT into
2173 h_asset_id,
2174 h_ccid,
2175 h_account,
2176 h_cost_account,
2177 h_asset,
2178 h_tag_number,
2179 h_description,
2180 h_serial_number, h_inventorial, h_asset_key_ccid,
2181 h_category_id,
2182 h_date_placed_in_service,
2183 h_method_code,
2184 h_life_in_months,
2185 h_stl_method_flag,
2186 h_rate_source_rule,
2187 h_cost_begin_balance,
2188 h_begin,
2189 h_addition,
2190 h_capitalization,
2191 h_adjustment,
2192 h_reclass,
2193 h_retirement,
2194 h_revaluation,
2195 h_transfer,
2196 h_end;/*Bug# 9293000 */
2197
2198 if (COST_REPORT%NOTFOUND) then exit; end if;
2199
2200 -- MRC
2201 else
2202 fetch MC_COST_REPORT into
2203 h_asset_id,
2204 h_ccid,
2205 h_account,
2206 h_cost_account,
2207 h_asset,
2208 h_tag_number,
2209 h_description,
2210 h_serial_number, h_inventorial, h_asset_key_ccid,
2211 h_category_id,
2212 h_date_placed_in_service,
2213 h_method_code,
2214 h_life_in_months,
2215 h_stl_method_flag,
2216 h_rate_source_rule,
2217 h_cost_begin_balance,
2218 h_begin,
2219 h_addition,
2220 h_capitalization,
2221 h_adjustment,
2222 h_reclass,
2223 h_retirement,
2224 h_revaluation,
2225 h_transfer,
2226 h_end;/*Bug# 9293000 */
2227
2228 if (MC_COST_REPORT%NOTFOUND) then exit; end if;
2229 end if;
2230 -- End MRC
2231
2232 h_mesg_name := 'FA_RX_CONCAT_SEGS';
2233 h_ccid_error := h_ccid;
2234
2235 fa_rx_shared_pkg.get_acct_segments (
2236 combination_id => h_ccid,
2237 n_segments => n_segs,
2238 segments => acct_all_segs,
2239 calling_fn => 'FA_BALANCES_REPORT');
2240
2241 /* StatReq - The following 6 function calls have been added to retrieve more
2242 detailed asset information */
2243
2244 h_account_description :=
2245 fa_rx_shared_pkg.get_flex_val_meaning(NULL, acct_valueset_name, h_account);
2246
2247 h_vendor_name :=
2248 fa_rx_shared_pkg.get_asset_info('VENDOR_NAME', h_asset_id, period1_pod, period2_pcd, book,
2249 acct_all_segs(gl_balancing_seg));
2250 h_invoice_number :=
2251 fa_rx_shared_pkg.get_asset_info('INVOICE_NUMBER', h_asset_id, period1_pod, period2_pcd, book,
2252 acct_all_segs(gl_balancing_seg));
2253 h_invoice_descr :=
2254 fa_rx_shared_pkg.get_asset_info('INVOICE_DESCR', h_asset_id, period1_pod, period2_pcd, book,
2255 acct_all_segs(gl_balancing_seg));
2256 h_location :=
2257 fa_rx_shared_pkg.get_asset_info('LOCATION', h_asset_id, period1_pod, period2_pcd, distribution_source_book,
2258 acct_all_segs(gl_balancing_seg));
2259 h_retirement_type :=
2260 fa_rx_shared_pkg.get_asset_info('RETIREMENT_TYPE',h_asset_id, period1_pod, period2_pcd, book,
2261 acct_all_segs(gl_balancing_seg));
2262
2263
2264 if (nvl(h_begin,0) + nvl(h_addition,0) - nvl(h_capitalization,0)
2265 + nvl(h_reclass,0) - nvl(h_retirement,0) + nvl(h_transfer,0)
2266 + nvl(h_adjustment,0) + nvl(h_revaluation,0) = nvl(h_end,0)) then
2267 h_out_of_bal_flag := 'N';
2268 else h_out_of_bal_flag := 'Y';
2269 end if;
2270
2271 if h_asset_key_ccid is not null then
2272 h_mesg_name := 'FA_RX_CONCAT_SEGS';
2273 h_flex_error := 'KEY#';
2274 h_ccid_error := h_asset_key_ccid;
2275
2276 fa_rx_shared_pkg.concat_asset_key (
2277 struct_id => h_key_flex_struct,
2278 ccid => h_asset_key_ccid,
2279 concat_string => h_concat_key,
2280 segarray => h_key_segs);
2281
2282 else
2283 h_concat_key := null;
2284
2285 end if;
2286
2287
2288 /* StatReq - The following if statement has been added to retrieve the category
2289 for each asset */
2290
2291 if h_category_id is not null then
2292 h_mesg_name := 'FA_RX_CONCAT_SEGS';
2293 h_flex_error := 'CAT#';
2294 h_ccid_error := h_category_id;
2295
2296 fa_rx_shared_pkg.concat_category (
2297 struct_id => h_cat_flex_struct,
2298 ccid => h_category_id,
2299 concat_string => h_concat_cat,
2300 segarray => h_cat_segs);
2301
2302 else
2303 h_concat_cat := null;
2304 end if;
2305
2306 /* StatReq - The following if statement has been added to calculate the annual depreciation rate
2307 for straight-line, calculated depreciation methods */
2308
2309 if (h_stl_method_flag = 'YES' and h_rate_source_rule = 'CALCULATED')
2310 then
2311 h_stl_depreciation_rate := 12 / h_life_in_months * 100;
2312
2313 -- MRC
2314 IF NOT FA_UTILS_PKG.faxrnd(
2315 X_amount => h_stl_depreciation_rate,
2316 X_book => book,
2317 X_set_of_books_id => h_sob_id,
2318 p_log_level_rec => null) then
2319
2320 success := FALSE;
2321 end if;
2322 -- End MRC
2323
2324 else
2325 h_stl_depreciation_rate := NULL;
2326 end if;
2327
2328 h_short_account_description := substr(h_account_description,1,239);
2329
2330 h_short_location := substr(h_location, 1, 499);
2331
2332 h_shortconcat_key := substr(h_concat_key,1, 239);
2333
2334
2335 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
2336 h_table_token := 'FA_BALANCES_REP_ITF';
2337
2338 -- Added following code for bug 5944006
2339 h_short_invoice_number := substrb(h_invoice_number, 1, 499);
2340 h_short_invoice_descr := substrb(h_invoice_descr, 1, 499);
2341 h_short_vendor_name := substrb(h_vendor_name, 1, 499);
2342 h_short_retirement_type := substrb(h_retirement_type, 1, 499);
2343 -- End of bug fix 5944006
2344
2345 --insert into interface table
2346 insert /*+ noappend */ into fa_balances_rep_itf
2347 (request_id, company, cost_center, account,
2348 cost_account, /* StatReq */
2349 asset_key, asset_number, tag_number, description,
2350 inventorial, serial_number,
2351 category, deprn_method, /* StatReq */
2352 account_description, date_placed_in_service, book_type_code, /* StatReq */
2353 life_in_months, stl_depreciation_rate, /* StatReq */
2354 concat_vendor_name, concat_invoice_number, /* StatReq */
2355 concat_invoice_description, concat_location, /* StatReq */
2356 concat_retirement_type, cost_begin_balance, /* StatReq */
2357 begin_balance, additions, adjustments,
2358 retirements, revaluations, reclasses, capitalizations, transfers,
2359 end_balance, out_of_balance_flag, created_by, creation_date,
2360 last_updated_by, last_update_date, last_update_login, group_asset_number
2361 -- , segment1, segment2, segment3, segment4, segment5,
2362 -- segment6, segment7, segment8, segment9, segment10,
2363 -- segment11, segment12, segment13, segment14, segment15,
2364 -- segment16, segment17, segment18, segment19, segment20,
2365 -- segment21, segment22, segment23, segment24, segment25,
2366 -- segment26, segment27, segment28, segment29, segment30
2367 ) values (
2368 Request_Id, acct_all_segs(gl_balancing_seg),
2369 acct_all_segs(fa_cost_ctr_seg), h_account,
2370 h_cost_account,
2371 h_shortconcat_key,
2372 -- 'BAL','CC','ACCT',
2373 h_asset, h_tag_number, h_description,
2374 h_inventorial, h_serial_number,
2375 h_concat_cat, h_method_code,
2376 h_short_account_description, h_date_placed_in_service, book,
2377 h_life_in_months, h_stl_depreciation_rate,
2378 h_short_vendor_name, h_short_invoice_number, h_short_invoice_descr, -- bug 5944006
2379 h_short_location,
2380 h_short_retirement_type, h_cost_begin_balance, -- bug 5944006
2381 h_begin, h_addition, h_adjustment, h_retirement,
2382 h_revaluation, h_reclass, h_capitalization, h_transfer,
2383 h_end, h_out_of_bal_flag, User_Id, sysdate, User_Id,
2384 sysdate, h_Login_Id, h_group_Asset);
2385
2386 end loop;
2387 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
2388
2389 if(h_mrcsobtype <> 'R') then -- MRC
2390 close COST_REPORT;
2391 -- MRC
2392 else
2393 close MC_COST_REPORT;
2394 end if;
2395 -- End MRC
2396
2397 if nvl(Report_Style,'S') = 'D' then
2398 -- Following logic is summarize the member level amounts into group level
2399 For group_asset in GROUP_ASSETS loop
2400
2401 open GROUP_COST_AMOUNTS(group_asset.group_asset_id);
2402 fetch GROUP_COST_AMOUNTS into h_cost_begin_balance,
2403 h_begin,
2404 h_addition,
2405 h_capitalization,
2406 h_adjustment,
2407 h_reclass,
2408 h_retirement,
2409 h_revaluation,
2410 h_transfer,
2411 h_end;
2412 close GROUP_COST_AMOUNTS;
2413
2414 select distinct asset_number
2415 into h_group_asset
2416 from fa_additions
2417 where asset_id=group_asset.group_asset_id;
2418
2419 Update fa_balances_rep_itf
2420 set cost_begin_balance = h_cost_begin_balance,
2421 begin_balance = h_begin,
2422 additions = h_addition,
2423 capitalizations = h_capitalization,
2424 adjustments = h_adjustment,
2425 reclasses = h_reclass,
2426 retirements = h_retirement,
2427 revaluations = h_revaluation,
2428 transfers = h_transfer,
2429 end_balance = h_end
2430 where request_id = Request_Id
2431 and asset_number = h_group_asset;
2432
2433 end loop;
2434 end if; -- Report_Style
2435
2436 end if; -- if report_type like %RESERVE
2437
2438 success := TRUE;
2439
2440 exception
2441 when others then success := FALSE;
2442
2443 fa_rx_conc_mesg_pkg.log(SQLERRM);
2444
2445 fnd_message.set_name('OFA',h_mesg_name);
2446
2447 if h_mesg_name in ('FA_SHARED_DETELE_FAILED','FA_SHARED_INSERT_FAILED') then
2448 fnd_message.set_token('TABLE',h_table_token,FALSE);
2449 end if;
2450 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
2451 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
2452 fnd_message.set_token('FLEX_CODE','GL#',FALSE);
2453 end if;
2454
2455 h_mesg_str := fnd_message.get;
2456 fa_rx_conc_mesg_pkg.log(h_mesg_str);
2457
2458 end balances_reports;
2459
2460
2461 PROCEDURE CIP_BALANCES_RPT (
2462 book in varchar2,
2463 start_period_name in varchar2,
2464 end_period_name in varchar2,
2465 request_id in number,
2466 user_id in number,
2467 retcode out nocopy number,
2468 errbuf out nocopy varchar2) is
2469
2470 h_success boolean;
2471 BEGIN
2472
2473 farx_bl.balances_reports (
2474 book => book,
2475 start_period_name => start_period_name,
2476 end_period_name => end_period_name,
2477 report_type => 'CIP COST',
2478 adj_mode => 'ADJUSTMENTS',
2479 request_id => request_id,
2480 user_id => user_id,
2481 calling_fn => 'CIP_BALANCES_RPT',
2482 mesg => errbuf,
2483 success => h_success);
2484
2485 if (h_success) then retcode := 0; else retcode := 2; end if;
2486
2487 commit;
2488 END CIP_BALANCES_RPT;
2489
2490
2491 PROCEDURE ASSET_COST_BALANCES_RPT (
2492 book in varchar2,
2493 start_period_name in varchar2,
2494 end_period_name in varchar2,
2495 sob_id in varchar2 default NULL, -- MRC: Set of books id
2496 report_style in varchar2,
2497 request_id in number,
2498 user_id in number,
2499 retcode out nocopy number,
2500 errbuf out nocopy varchar2) is
2501
2502 h_success boolean;
2503 BEGIN
2504
2505 fa_debug_pkg.initialize;
2506 fa_debug_pkg.add('farxblb','report_style', report_style);
2507
2508
2509 farx_bl.balances_reports (
2510 book => book,
2511 start_period_name => start_period_name,
2512 end_period_name => end_period_name,
2513 report_type => 'COST',
2514 adj_mode => 'ADJUSTMENTS',
2515 sob_id => sob_id, -- MRC
2516 report_style => report_style,
2517 request_id => request_id,
2518 user_id => user_id,
2519 calling_fn => 'ASSET_COST_BALANCES_RPT',
2520 mesg => errbuf,
2521 success => h_success);
2522
2523
2524 if (h_success) then retcode := 0; else retcode := 2; end if;
2525
2526 commit;
2527 END ASSET_COST_BALANCES_RPT;
2528
2529 PROCEDURE ACCUM_DEPRN_BALANCES_RPT (
2530 book in varchar2,
2531 start_period_name in varchar2,
2532 end_period_name in varchar2,
2533 sob_id in varchar2 default NULL, -- MRC: Set of books id
2534 report_style in varchar2,
2535 request_id in number,
2536 user_id in number,
2537 retcode out nocopy number,
2538 errbuf out nocopy varchar2) is
2539
2540 h_success boolean;
2541
2542 BEGIN
2543
2544 fa_debug_pkg.initialize;
2545 fa_debug_pkg.add('farxblb','report_style', report_style);
2546
2547 farx_bl.balances_reports (
2548 book => book,
2549 start_period_name => start_period_name,
2550 end_period_name => end_period_name,
2551 report_type => 'RESERVE',
2552 adj_mode => 'ADJUSTMENTS',
2553 sob_id => sob_id, -- MRC
2554 report_style => report_style,
2555 request_id => request_id,
2556 user_id => user_id,
2557 calling_fn => 'ACCUM_DEPRN_BALANCES_RPT',
2558 mesg => errbuf,
2559 success => h_success);
2560
2561 if (h_success) then retcode := 0; else retcode := 2; end if;
2562
2563 commit;
2564 END ACCUM_DEPRN_BALANCES_RPT;
2565
2566
2567
2568 PROCEDURE REVAL_RESERVE_BALANCES_RPT (
2569 book in varchar2,
2570 start_period_name in varchar2,
2571 end_period_name in varchar2,
2572 request_id in number,
2573 user_id in number,
2574 retcode out nocopy number,
2575 errbuf out nocopy varchar2) is
2576
2577 h_success boolean;
2578 BEGIN
2579
2580 farx_bl.balances_reports (
2581 book => book,
2582 start_period_name => start_period_name,
2583 end_period_name => end_period_name,
2584 report_type => 'REVAL RESERVE',
2585 adj_mode => 'ADJUSTMENTS',
2586 request_id => request_id,
2587 user_id => user_id,
2588 calling_fn => 'REVAL_RESERVE_BALANCES_RPT',
2589 mesg => errbuf,
2590 success => h_success);
2591
2592 if (h_success) then retcode := 0; else retcode := 2; end if;
2593
2594 commit;
2595 END REVAL_RESERVE_BALANCES_RPT;
2596
2597
2598 END FARX_BL;