[Home] [Help]
PACKAGE BODY: APPS.FA_RSVLDG_REP_INS_PKG
Source
1 PACKAGE BODY FA_RSVLDG_REP_INS_PKG AS
2 /*$Header: farsvinb.pls 120.12 2011/01/30 22:13:31 glchen noship $*/
3 PROCEDURE RSVLDG (book in varchar2,
4 period in varchar2,
5 errbuf out NOCOPY varchar2,
6 retcode out NOCOPY number,
7 operation out nocopy varchar2,
8 request_id in number) --bug 9235908
9 IS
10 --operation varchar2(200);
11 dist_book varchar2(15);
12 ucd date;
13 upc number;
14 tod date;
15 tpc number;
16
17 h_set_of_books_id number;
18 h_reporting_flag varchar2(1);
19 bonus_count number := 0 ; -- bugfix 6677528 (initialize)
20 l_request_id Number;
21
22 CURSOR Launch_worker(request_id_in NUMBER) IS
23 SELECT Start_range ,
24 End_range
25 FROM FA_WORKER_JOBS
26 WHERE request_id = request_id_in;
27 begin
28 -- get mrc related info
29 begin
30 select to_number(substrb(userenv('CLIENT_INFO'),45,10))
31 into h_set_of_books_id from dual;
32 exception
33 when others then
34 h_set_of_books_id := null;
35 end;
36 if (h_set_of_books_id is not null) then
37 if not fa_cache_pkg.fazcsob
38 (X_set_of_books_id => h_set_of_books_id,
39 X_mrc_sob_type_code => h_reporting_flag) then
40 raise FND_API.G_EXC_UNEXPECTED_ERROR;
41 end if;
42 else
43 h_reporting_flag := 'P';
44 end if;
45 operation := 'Selecting Book and Period information';
46 if (h_reporting_flag = 'R') then
47 SELECT
48 BC.DISTRIBUTION_SOURCE_BOOK dbk,
49 nvl (DP.PERIOD_CLOSE_DATE, sysdate) ucd,
50 DP.PERIOD_COUNTER upc,
51 min (DP_FY.PERIOD_OPEN_DATE) tod,
52 min (DP_FY.PERIOD_COUNTER) tpc
53 INTO
54 dist_book,
55 ucd,
56 upc,
57 tod,
58 tpc
59 FROM
60 FA_DEPRN_PERIODS_MRC_V DP,
61 FA_DEPRN_PERIODS_MRC_V DP_FY,
62 FA_BOOK_CONTROLS_MRC_V BC
63 WHERE
64 DP.BOOK_TYPE_CODE = book AND
65 DP.PERIOD_NAME = period AND
66 DP_FY.BOOK_TYPE_CODE = book AND
67 DP_FY.FISCAL_YEAR = DP.FISCAL_YEAR
68 AND BC.BOOK_TYPE_CODE = book
69 GROUP BY
70 BC.DISTRIBUTION_SOURCE_BOOK,
71 DP.PERIOD_CLOSE_DATE,
72 DP.PERIOD_COUNTER;
73 else
74 SELECT
75 BC.DISTRIBUTION_SOURCE_BOOK dbk,
76 nvl (DP.PERIOD_CLOSE_DATE, sysdate) ucd,
77 DP.PERIOD_COUNTER upc,
78 min (DP_FY.PERIOD_OPEN_DATE) tod,
79 min (DP_FY.PERIOD_COUNTER) tpc
80 INTO
81 dist_book,
82 ucd,
83 upc,
84 tod,
85 tpc
86 FROM
87 FA_DEPRN_PERIODS DP,
88 FA_DEPRN_PERIODS DP_FY,
89 FA_BOOK_CONTROLS BC
90 WHERE
91 DP.BOOK_TYPE_CODE = book AND
92 DP.PERIOD_NAME = period AND
93 DP_FY.BOOK_TYPE_CODE = book AND
94 DP_FY.FISCAL_YEAR = DP.FISCAL_YEAR
95 AND BC.BOOK_TYPE_CODE = book
96 GROUP BY
97 BC.DISTRIBUTION_SOURCE_BOOK,
98 DP.PERIOD_CLOSE_DATE,
99 DP.PERIOD_COUNTER;
100 end if;
101
102
103 /* Bugfix 6677528 : query should have more conditions. Use exists instead of count */
104 BEGIN
105 Select 1
106 Into bonus_count
107 From dual
108 where exists (select 1 from FA_Books
109 Where book_type_code = book
110 and bonus_rule is not null
114 bonus_count := 0;
111 and transaction_header_id_out is null);
112 EXCEPTION
113 WHEN NO_DATA_FOUND THEN
115 END;
116 -- end bugfix 6677528
117 operation := 'Inserting into FA_RESERVE_LEDGER_GT';
118
119 --==================================================
120 --OPEN LOAD WORKER CURSOR HERE.
121 --==================================================
122 -- fnd_profile.get('CONC_REQUEST_ID', l_request_id); bug 9235908
123 -- request_id will be passed from report directly instead of
124 -- getting its value from profile option.
125 l_request_id := request_id;
126
127 --Call another PL/SQL here to insert this data into FA_WORKER_JOBS
128 FA_BALREP_PKG.LOAD_WORKERS( Book,l_request_id, errbuf, retcode);
129
130 if (retcode <> 1 ) then
131 null;
132 --Error
133 End if;
134 commit;
135 --==================================================
136
137 For Rec1 in Launch_worker(l_request_id) loop
138 Exit when Launch_worker%notfound;
139
140 -- run only if CRL not installed
141
142 If (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'N' ) then
143
144 If bonus_count > 0 then
145
146 if (h_reporting_flag = 'R') then
147 INSERT INTO FA_RESERVE_LEDGER_GT
148 (
149 ASSET_ID ,
150 DH_CCID ,
151 DEPRN_RESERVE_ACCT ,
152 DATE_PLACED_IN_SERVICE,
153 METHOD_CODE ,
154 LIFE ,
155 RATE ,
156 CAPACITY ,
157 COST ,
158 DEPRN_AMOUNT ,
159 YTD_DEPRN ,
160 DEPRN_RESERVE ,
161 PERCENT ,
162 TRANSACTION_TYPE ,
163 PERIOD_COUNTER ,
164 DATE_EFFECTIVE ,
165 RESERVE_ACCT ,
166 ASSET_NUMBER ,
167 BONUS_RATE ,
168 ASSET_COST_ACCT
169 )
170 SELECT /*+ ORDERED
171 Index(DD_BONUS FA_DEPRN_DETAIL_U1)
172 index(DH FA_DISTRIBUTION_HISTORY_U1)
173 Index(AH FA_ASSET_HISTORY_N2)
174 */
175 DH.ASSET_ID ASSET_ID ,
176 DH.CODE_COMBINATION_ID DH_CCID ,
177 CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT ,
178 BOOKS.DATE_PLACED_IN_SERVICE START_DATE ,
179 BOOKS.DEPRN_METHOD_CODE METHOD ,
180 BOOKS.LIFE_IN_MONTHS LIFE ,
181 BOOKS.ADJUSTED_RATE RATE ,
182 BOOKS.PRODUCTION_CAPACITY CAPACITY ,
183 DD_BONUS.COST COST ,
184 DECODE (DD_BONUS.PERIOD_COUNTER, upc, DD_BONUS.DEPRN_AMOUNT - DD_BONUS.BONUS_DEPRN_AMOUNT, 0) DEPRN_AMOUNT ,
185 DECODE (SIGN (tpc - DD_BONUS.PERIOD_COUNTER), 1, 0, DD_BONUS.YTD_DEPRN - DD_BONUS.BONUS_YTD_DEPRN) YTD_DEPRN ,
186 DD_BONUS.DEPRN_RESERVE - DD_BONUS.BONUS_DEPRN_RESERVE DEPRN_RESERVE ,
187 DECODE (DH.TRANSACTION_HEADER_ID_OUT, NULL, DH.UNITS_ASSIGNED / AH.UNITS * 100) PERCENT ,
188 DECODE (DH.TRANSACTION_HEADER_ID_OUT, NULL, DECODE (TH_RT.TRANSACTION_TYPE_CODE, 'FULL RETIREMENT', 'F', DECODE (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')), 'TRANSFER', 'T', 'TRANSFER OUT', 'P', 'RECLASS', 'R') T_TYPE,
189 DD_BONUS.PERIOD_COUNTER ,
190 ucd ,
191 '',
192 AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
196 --FA_DEPRN_DETAIL_MRC_V DD_BONUS,
193 BR.BONUS_RATE ,
194 CB.ASSET_COST_ACCT
195 FROM
197 ( SELECT /*+ Index (FA_DEPRN_DETAIL FA_DEPRN_DETAIL_N1 ) */ DISTRIBUTION_ID ,
198 MAX(PERIOD_COUNTER) PERIOD_COUNTER
199 FROM FA_DEPRN_DETAIL_MRC_V
200 WHERE BOOK_TYPE_CODE = book
201 AND PERIOD_COUNTER <= upc
202 AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
203 GROUP BY DISTRIBUTION_ID
204 ) dd1,
205 FA_DEPRN_DETAIL_MRC_V DD_BONUS,
206 FA_DISTRIBUTION_HISTORY DH ,
207 FA_ASSET_HISTORY AH ,
208 FA_BOOKS_MRC_V BOOKS ,
209 FA_TRANSACTION_HEADERS TH_RT,
210 FA_CATEGORY_BOOKS CB,
211 FA_ADDITIONS AD,
212 FA_BONUS_RATES BR,
213 FA_DEPRN_PERIODS DP
214 WHERE BOOKS.BOOK_TYPE_CODE = book
215 AND BOOKS.ASSET_ID = DD_BONUS.ASSET_ID --7721457
216 AND BOOKS.ASSET_ID = AD.ASSET_ID
217 AND NVL(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc
218 AND BOOKS.DATE_EFFECTIVE <= ucd
219 AND NVL(BOOKS.DATE_INEFFECTIVE,sysdate+1) > ucd
220 AND CB.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
221 AND CB.CATEGORY_ID = AH.CATEGORY_ID
222 AND AH.ASSET_ID = DD_BONUS.ASSET_ID --7721457
223 AND AH.DATE_EFFECTIVE < ucd
224 AND NVL(AH.DATE_INEFFECTIVE,sysdate) >= ucd
225 AND AH.ASSET_TYPE = 'CAPITALIZED'
226 AND DD_BONUS.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
227 AND DD_BONUS.DISTRIBUTION_ID = DD1.DISTRIBUTION_ID --7721457
228 AND DD_BONUS.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
229 AND DD_BONUS.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
230 AND DD_BONUS.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
231 AND BOOKS.BONUS_RULE = BR.BONUS_RULE (+)
232 AND DP.BOOK_TYPE_CODE = DD_BONUS.BOOK_TYPE_CODE
233 AND DP.PERIOD_COUNTER = DD_BONUS.PERIOD_COUNTER
234 AND DP.FISCAL_YEAR BETWEEN DECODE(BOOKS.BONUS_RULE,
235 null, DP.FISCAL_YEAR,
236 BR.START_YEAR)
237 AND DECODE(BOOKS.BONUS_RULE,
238 null, DP.FISCAL_YEAR,
239 NVL(BR.END_YEAR, DP.FISCAL_YEAR))
240 AND TH_RT.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
241 AND TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
242 AND DH.BOOK_TYPE_CODE = dist_book
243 AND DH.DATE_EFFECTIVE <= ucd
244 AND NVL(DH.DATE_INEFFECTIVE, sysdate) > tod
245 UNION ALL
246 SELECT /*+ ORDERED
247 Index(DD FA_DEPRN_DETAIL_U1)
248 index(DH FA_DISTRIBUTION_HISTORY_U1)
249 Index(AH FA_ASSET_HISTORY_N2)
250 */
251 DH.ASSET_ID ASSET_ID ,
252 DH.CODE_COMBINATION_ID DH_CCID ,
253 CB.BONUS_DEPRN_RESERVE_ACCT RSV_ACCOUNT ,
254 BOOKS.DATE_PLACED_IN_SERVICE START_DATE ,
255 BOOKS.DEPRN_METHOD_CODE METHOD ,
256 BOOKS.LIFE_IN_MONTHS LIFE ,
257 BOOKS.ADJUSTED_RATE RATE ,
258 BOOKS.PRODUCTION_CAPACITY CAPACITY ,
259 0 COST ,
260 DECODE (DD.PERIOD_COUNTER, upc, DD.BONUS_DEPRN_AMOUNT, 0) DEPRN_AMOUNT ,
261 DECODE (SIGN (tpc - DD.PERIOD_COUNTER), 1, 0, DD.BONUS_YTD_DEPRN) YTD_DEPRN,
262 DD.BONUS_DEPRN_RESERVE DEPRN_RESERVE ,
263 0 PERCENT ,
264 'B' T_TYPE ,
265 DD.PERIOD_COUNTER ,
266 ucd ,
267 CB.BONUS_DEPRN_EXPENSE_ACCT,
268 AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
269 BR.BONUS_RATE ,
270 CB.ASSET_COST_ACCT
271 FROM
272 --FA_DEPRN_DETAIL_MRC_V DD,
273 ( SELECT /*+ Index (FA_DEPRN_DETAIL FA_DEPRN_DETAIL_N1 ) */ DISTRIBUTION_ID ,
274 MAX(PERIOD_COUNTER) PERIOD_COUNTER
275 FROM FA_DEPRN_DETAIL_MRC_V
276 WHERE BOOK_TYPE_CODE = book
277 AND PERIOD_COUNTER <= upc
278 AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
279 GROUP BY DISTRIBUTION_ID
280 ) dd1,
281 FA_DEPRN_DETAIL_MRC_V DD,
282 FA_DISTRIBUTION_HISTORY DH ,
283 FA_ASSET_HISTORY AH ,
284 FA_BOOKS_MRC_V BOOKS ,
285 FA_TRANSACTION_HEADERS TH_RT,
286 FA_CATEGORY_BOOKS CB,
287 FA_ADDITIONS AD,
291 AND BOOKS.ASSET_ID = DD.ASSET_ID --7721457
288 FA_BONUS_RATES BR,
289 FA_DEPRN_PERIODS DP
290 WHERE BOOKS.BOOK_TYPE_CODE = book
292 AND BOOKS.ASSET_ID = AD.ASSET_ID
293 AND NVL(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc
294 AND BOOKS.DATE_EFFECTIVE <= ucd
295 AND NVL(BOOKS.DATE_INEFFECTIVE,sysdate+1) > ucd
296 AND BOOKS.BONUS_RULE IS NOT NULL
297 AND DD.DISTRIBUTION_ID = DD1.DISTRIBUTION_ID --7721457
298 AND DD.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
299 AND DD.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
300 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
301 AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
302 AND CB.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
303 AND CB.CATEGORY_ID = AH.CATEGORY_ID
304 AND AH.ASSET_ID = DD.ASSET_ID --7721457
305 AND AH.DATE_EFFECTIVE < ucd
306 AND NVL(AH.DATE_INEFFECTIVE,sysdate) >= ucd
307 AND AH.ASSET_TYPE = 'CAPITALIZED'
308 AND TH_RT.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
309 AND TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
310 AND BOOKS.BONUS_RULE = BR.BONUS_RULE
311 AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
312 AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
313 AND DP.FISCAL_YEAR BETWEEN BR.START_YEAR AND
314 NVL(BR.END_YEAR, DP.FISCAL_YEAR)
315 AND DH.BOOK_TYPE_CODE = dist_book
316 AND DH.DATE_EFFECTIVE <= ucd
317 AND NVL(DH.DATE_INEFFECTIVE, sysdate) > tod ;
318
319 else
320 /* ie h_reporting_flag <> 'R' */
321
322 -- start here...
323 INSERT INTO FA_RESERVE_LEDGER_GT
324 (
325 ASSET_ID ,
326 DH_CCID ,
327 DEPRN_RESERVE_ACCT ,
328 DATE_PLACED_IN_SERVICE,
329 METHOD_CODE ,
330 LIFE ,
331 RATE ,
332 CAPACITY ,
333 COST ,
334 DEPRN_AMOUNT ,
335 YTD_DEPRN ,
336 DEPRN_RESERVE ,
337 PERCENT ,
338 TRANSACTION_TYPE ,
339 PERIOD_COUNTER ,
340 DATE_EFFECTIVE ,
341 RESERVE_ACCT ,
342 ASSET_NUMBER ,
343 BONUS_RATE ,
344 ASSET_COST_ACCT
345 )
346 SELECT /*+ ORDERED
347 Index(DD_BONUS FA_DEPRN_DETAIL_U1)
348 index(DH FA_DISTRIBUTION_HISTORY_U1)
349 Index(AH FA_ASSET_HISTORY_N2)
350 */
351 DH.ASSET_ID ASSET_ID ,
352 DH.CODE_COMBINATION_ID DH_CCID ,
353 CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT ,
354 BOOKS.DATE_PLACED_IN_SERVICE START_DATE ,
355 BOOKS.DEPRN_METHOD_CODE METHOD ,
356 BOOKS.LIFE_IN_MONTHS LIFE ,
357 BOOKS.ADJUSTED_RATE RATE ,
358 BOOKS.PRODUCTION_CAPACITY CAPACITY ,
359 DD_BONUS.COST COST ,
360 DECODE (DD_BONUS.PERIOD_COUNTER, upc, DD_BONUS.DEPRN_AMOUNT - DD_BONUS.BONUS_DEPRN_AMOUNT, 0) DEPRN_AMOUNT ,
361 DECODE (SIGN (tpc - DD_BONUS.PERIOD_COUNTER), 1, 0, DD_BONUS.YTD_DEPRN - DD_BONUS.BONUS_YTD_DEPRN) YTD_DEPRN ,
362 DD_BONUS.DEPRN_RESERVE - DD_BONUS.BONUS_DEPRN_RESERVE DEPRN_RESERVE ,
363 DECODE (DH.TRANSACTION_HEADER_ID_OUT, NULL, DH.UNITS_ASSIGNED / AH.UNITS * 100) PERCENT ,
364 DECODE (DH.TRANSACTION_HEADER_ID_OUT, NULL, DECODE (TH_RT.TRANSACTION_TYPE_CODE, 'FULL RETIREMENT', 'F', DECODE (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')), 'TRANSFER', 'T', 'TRANSFER OUT', 'P', 'RECLASS', 'R') T_TYPE,
368 AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
365 DD_BONUS.PERIOD_COUNTER ,
366 ucd ,
367 '' ,
369 BR.BONUS_RATE ,
370 CB.ASSET_COST_ACCT
371 FROM
372 --FA_DEPRN_DETAIL DD_BONUS,
373 ( SELECT /*+ Index (FA_DEPRN_DETAIL FA_DEPRN_DETAIL_N1 ) */ DISTRIBUTION_ID ,
374 MAX(PERIOD_COUNTER) PERIOD_COUNTER
375 FROM FA_DEPRN_DETAIL
376 WHERE BOOK_TYPE_CODE = book
377 AND PERIOD_COUNTER <= upc
378 AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
379 GROUP BY DISTRIBUTION_ID
380 ) DD1 ,
381 FA_DEPRN_DETAIL DD_BONUS ,
382 FA_DISTRIBUTION_HISTORY DH ,
383 FA_ASSET_HISTORY AH ,
384 FA_BOOKS BOOKS ,
385 FA_TRANSACTION_HEADERS TH_RT,
386 FA_CATEGORY_BOOKS CB ,
387 FA_ADDITIONS AD,
388 FA_BONUS_RATES BR,
389 FA_DEPRN_PERIODS DP
390 WHERE BOOKS.BOOK_TYPE_CODE = book
391 AND BOOKS.ASSET_ID = DD_BONUS.ASSET_ID --7721457
392 AND BOOKS.ASSET_ID = AD.ASSET_ID
393 AND NVL(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc
394 AND BOOKS.DATE_EFFECTIVE <= ucd
395 AND NVL(BOOKS.DATE_INEFFECTIVE,sysdate+1) > ucd
396 AND CB.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
397 AND CB.CATEGORY_ID = AH.CATEGORY_ID
398 AND AH.ASSET_ID = DD_BONUS.ASSET_ID --7721457
399 AND DD_BONUS.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
400 AND DD_BONUS.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
401 AND DD_BONUS.DISTRIBUTION_ID = dd1.DISTRIBUTION_ID --7721457
402 AND DD_BONUS.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
403 AND DD_BONUS.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
404 AND AH.DATE_EFFECTIVE < ucd
405 AND NVL(AH.DATE_INEFFECTIVE,sysdate) >= ucd
406 AND AH.ASSET_TYPE = 'CAPITALIZED'
407 AND TH_RT.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
408 AND TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
409 AND DH.BOOK_TYPE_CODE = dist_book
410 AND DH.DATE_EFFECTIVE <= ucd
411 AND NVL(DH.DATE_INEFFECTIVE, sysdate) > tod
412 AND BOOKS.BONUS_RULE = BR.BONUS_RULE (+)
413 AND DP.BOOK_TYPE_CODE = DD_BONUS.BOOK_TYPE_CODE
414 AND DP.PERIOD_COUNTER = DD_BONUS.PERIOD_COUNTER
415 AND DP.FISCAL_YEAR BETWEEN DECODE(BOOKS.BONUS_RULE,
416 null, DP.FISCAL_YEAR,
417 BR.START_YEAR)
418 AND DECODE(BOOKS.BONUS_RULE,
419 null, DP.FISCAL_YEAR,
420 NVL(BR.END_YEAR, DP.FISCAL_YEAR))
421 UNION ALL
422 SELECT /*+ ORDERED
423 Index(DD FA_DEPRN_DETAIL_U1)
424 index(DH FA_DISTRIBUTION_HISTORY_U1)
425 Index(AH FA_ASSET_HISTORY_N2)
426 */
427 DH.ASSET_ID ASSET_ID ,
428 DH.CODE_COMBINATION_ID DH_CCID ,
429 CB.BONUS_DEPRN_RESERVE_ACCT RSV_ACCOUNT ,
430 BOOKS.DATE_PLACED_IN_SERVICE START_DATE ,
431 BOOKS.DEPRN_METHOD_CODE METHOD ,
432 BOOKS.LIFE_IN_MONTHS LIFE ,
433 BOOKS.ADJUSTED_RATE RATE ,
434 BOOKS.PRODUCTION_CAPACITY CAPACITY ,
435 0 COST ,
436 DECODE (DD.PERIOD_COUNTER, upc, DD.BONUS_DEPRN_AMOUNT, 0) DEPRN_AMOUNT ,
437 DECODE (SIGN (tpc - DD.PERIOD_COUNTER), 1, 0, DD.BONUS_YTD_DEPRN) YTD_DEPRN,
438 DD.BONUS_DEPRN_RESERVE DEPRN_RESERVE ,
439 0 PERCENT ,
440 'B' T_TYPE ,
441 DD.PERIOD_COUNTER ,
442 ucd ,
443 CB.BONUS_DEPRN_EXPENSE_ACCT,
444 AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
445 BR.BONUS_RATE ,
446 CB.ASSET_COST_ACCT
447 FROM
448 --FA_DEPRN_DETAIL DD,
449 ( SELECT /*+ Index (FA_DEPRN_DETAIL FA_DEPRN_DETAIL_N1 ) */ DISTRIBUTION_ID ,
450 MAX(PERIOD_COUNTER) PERIOD_COUNTER
451 FROM FA_DEPRN_DETAIL
452 WHERE BOOK_TYPE_CODE = book
453 AND PERIOD_COUNTER <= upc
454 AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
455 GROUP BY DISTRIBUTION_ID
456 ) DD1,
457 FA_DEPRN_DETAIL DD ,
458 FA_DISTRIBUTION_HISTORY DH ,
459 FA_ASSET_HISTORY AH ,
460 FA_BOOKS BOOKS ,
461 FA_TRANSACTION_HEADERS TH_RT,
465 FA_DEPRN_PERIODS DP
462 FA_CATEGORY_BOOKS CB ,
463 FA_ADDITIONS AD,
464 FA_BONUS_RATES BR,
466 WHERE BOOKS.BOOK_TYPE_CODE = book
467 AND BOOKS.ASSET_ID = DD.ASSET_ID --7721457
468 AND BOOKS.ASSET_ID = AD.ASSET_ID
469 AND NVL(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc
470 AND BOOKS.DATE_EFFECTIVE <= ucd
471 AND NVL(BOOKS.DATE_INEFFECTIVE,sysdate+1) > ucd
472 AND BOOKS.BONUS_RULE IS NOT NULL
473 AND CB.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
474 AND CB.CATEGORY_ID = AH.CATEGORY_ID
475 AND AH.ASSET_ID = DD.ASSET_ID --7721457
476 AND AH.DATE_EFFECTIVE < ucd
477 AND NVL(AH.DATE_INEFFECTIVE,sysdate) >= ucd
478 AND AH.ASSET_TYPE = 'CAPITALIZED'
479 AND DD.DISTRIBUTION_ID = dd1.DISTRIBUTION_ID --7721457
480 AND DD.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
481 AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
482 AND DD.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
483 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
484 AND BOOKS.BONUS_RULE = BR.BONUS_RULE
485 AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
486 AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
487 AND DP.FISCAL_YEAR BETWEEN BR.START_YEAR AND
488 NVL(BR.END_YEAR, DP.FISCAL_YEAR)
489 AND TH_RT.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
490 AND TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
491 AND DH.BOOK_TYPE_CODE = dist_book
492 AND DH.DATE_EFFECTIVE <= ucd
493 AND NVL(DH.DATE_INEFFECTIVE, sysdate) > tod ;
494 END IF;
495
496 Else -- bonus_count i.e. if no bonus assets this branch.
497
498 /* ie. bonus_count = 0 */
499
500 -- run only if CRL not installed
501 if (h_reporting_flag = 'R') then
502 INSERT INTO FA_RESERVE_LEDGER_GT
503 ( ASSET_ID ,
504 DH_CCID ,
505 DEPRN_RESERVE_ACCT ,
506 DATE_PLACED_IN_SERVICE,
507 METHOD_CODE ,
508 LIFE ,
509 RATE ,
510 CAPACITY ,
511 COST ,
512 DEPRN_AMOUNT ,
513 YTD_DEPRN ,
514 DEPRN_RESERVE ,
515 PERCENT ,
516 TRANSACTION_TYPE ,
517 PERIOD_COUNTER ,
518 DATE_EFFECTIVE ,
519 RESERVE_ACCT ,
520 ASSET_NUMBER ,
521 BONUS_RATE ,
522 ASSET_COST_ACCT
523 )
524 SELECT
525 DH.ASSET_ID ASSET_ID ,
526 DH.CODE_COMBINATION_ID DH_CCID ,
527 CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT ,
528 BOOKS.DATE_PLACED_IN_SERVICE START_DATE ,
529 BOOKS.DEPRN_METHOD_CODE METHOD ,
530 BOOKS.LIFE_IN_MONTHS LIFE ,
531 BOOKS.ADJUSTED_RATE RATE ,
532 BOOKS.PRODUCTION_CAPACITY CAPACITY ,
533 DD_BONUS.COST COST ,
534 DECODE (DD_BONUS.PERIOD_COUNTER, upc, DD_BONUS.DEPRN_AMOUNT - DD_BONUS.BONUS_DEPRN_AMOUNT, 0) DEPRN_AMOUNT ,
535 DECODE (SIGN (tpc - DD_BONUS.PERIOD_COUNTER), 1, 0, DD_BONUS.YTD_DEPRN - DD_BONUS.BONUS_YTD_DEPRN) YTD_DEPRN ,
536 DD_BONUS.DEPRN_RESERVE - DD_BONUS.BONUS_DEPRN_RESERVE DEPRN_RESERVE ,
537 DECODE (DH.TRANSACTION_HEADER_ID_OUT, NULL, DH.UNITS_ASSIGNED / AH.UNITS * 100) PERCENT ,
541 '',
538 DECODE (DH.TRANSACTION_HEADER_ID_OUT, NULL, DECODE (TH_RT.TRANSACTION_TYPE_CODE, 'FULL RETIREMENT', 'F', DECODE (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')), 'TRANSFER', 'T', 'TRANSFER OUT', 'P', 'RECLASS', 'R') T_TYPE,
539 DD_BONUS.PERIOD_COUNTER ,
540 ucd ,
542 AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
543 BR.BONUS_RATE ,
544 CB.ASSET_COST_ACCT
545 FROM
546 --FA_DEPRN_DETAIL_MRC_V DD_BONUS,
547 ( SELECT
548 DISTRIBUTION_ID ,
549 MAX(PERIOD_COUNTER) PERIOD_COUNTER
550 FROM FA_DEPRN_DETAIL_MRC_V
551 WHERE BOOK_TYPE_CODE = book
552 AND PERIOD_COUNTER <= upc
553 AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
554 GROUP BY DISTRIBUTION_ID
555 ) dd1,
556 FA_DEPRN_DETAIL_MRC_V DD_BONUS,
557 FA_DISTRIBUTION_HISTORY DH ,
558 FA_ASSET_HISTORY AH ,
559 FA_BOOKS_MRC_V BOOKS ,
560 FA_TRANSACTION_HEADERS TH_RT,
561 FA_CATEGORY_BOOKS CB,
562 FA_ADDITIONS AD,
563 FA_BONUS_RATES BR,
564 FA_DEPRN_PERIODS DP
565 WHERE BOOKS.BOOK_TYPE_CODE = book
566 AND BOOKS.ASSET_ID = DD_BONUS.ASSET_ID --7721457
567 AND BOOKS.ASSET_ID = AD.ASSET_ID
568 AND NVL(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc
569 AND BOOKS.DATE_EFFECTIVE <= ucd
570 AND NVL(BOOKS.DATE_INEFFECTIVE,sysdate+1) > ucd
571 AND DD_BONUS.DISTRIBUTION_ID = DD1.DISTRIBUTION_ID --7721457
572 AND DD_BONUS.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
573 AND DD_BONUS.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
574 AND CB.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
575 AND CB.CATEGORY_ID = AH.CATEGORY_ID
576 AND AH.ASSET_ID = DD_BONUS.ASSET_ID --7721457
577 AND AH.DATE_EFFECTIVE < ucd
578 AND NVL(AH.DATE_INEFFECTIVE,sysdate) >= ucd
579 AND AH.ASSET_TYPE = 'CAPITALIZED'
580 AND DD_BONUS.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
581 AND -- BOOKS.BOOK_TYPE_CODE CHNGD
582 DD_BONUS.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
583 AND BOOKS.BONUS_RULE = BR.BONUS_RULE (+)
584 AND DP.BOOK_TYPE_CODE = DD_BONUS.BOOK_TYPE_CODE
585 AND DP.PERIOD_COUNTER = DD_BONUS.PERIOD_COUNTER
586 AND DP.FISCAL_YEAR BETWEEN DECODE(BOOKS.BONUS_RULE,
587 null, DP.FISCAL_YEAR,
588 BR.START_YEAR)
589 AND DECODE(BOOKS.BONUS_RULE,
590 null, DP.FISCAL_YEAR,
591 NVL(BR.END_YEAR, DP.FISCAL_YEAR))
592 AND TH_RT.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
593 AND --chngd
594 TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
595 AND DH.BOOK_TYPE_CODE = dist_book
596 AND DH.DATE_EFFECTIVE <= ucd
597 AND NVL(DH.DATE_INEFFECTIVE, sysdate) > tod;
598 Else -- reporting vs primary
599 /* ie. h_reporting_flag <> 'R' */
600 INSERT INTO FA_RESERVE_LEDGER_GT
601 (
602 ASSET_ID ,
603 DH_CCID ,
604 DEPRN_RESERVE_ACCT ,
605 DATE_PLACED_IN_SERVICE,
606 METHOD_CODE ,
607 LIFE ,
608 RATE ,
609 CAPACITY ,
610 COST ,
611 DEPRN_AMOUNT ,
612 YTD_DEPRN ,
613 DEPRN_RESERVE ,
614 PERCENT ,
615 TRANSACTION_TYPE ,
616 PERIOD_COUNTER ,
617 DATE_EFFECTIVE ,
618 RESERVE_ACCT ,
619 ASSET_NUMBER ,
620 BONUS_RATE ,
621 ASSET_COST_ACCT
622 )
623 SELECT
624 DH.ASSET_ID ASSET_ID ,
625 DH.CODE_COMBINATION_ID DH_CCID ,
626 CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT ,
627 BOOKS.DATE_PLACED_IN_SERVICE START_DATE ,
628 BOOKS.DEPRN_METHOD_CODE METHOD ,
629 BOOKS.LIFE_IN_MONTHS LIFE ,
633 DECODE (DD_BONUS.PERIOD_COUNTER, upc, DD_BONUS.DEPRN_AMOUNT - DD_BONUS.BONUS_DEPRN_AMOUNT, 0) DEPRN_AMOUNT ,
630 BOOKS.ADJUSTED_RATE RATE ,
631 BOOKS.PRODUCTION_CAPACITY CAPACITY ,
632 DD_BONUS.COST COST ,
634 DECODE (SIGN (tpc - DD_BONUS.PERIOD_COUNTER), 1, 0, DD_BONUS.YTD_DEPRN - DD_BONUS.BONUS_YTD_DEPRN) YTD_DEPRN ,
635 DD_BONUS.DEPRN_RESERVE - DD_BONUS.BONUS_DEPRN_RESERVE DEPRN_RESERVE ,
636 DECODE (DH.TRANSACTION_HEADER_ID_OUT, NULL, DH.UNITS_ASSIGNED / AH.UNITS * 100) PERCENT ,
637 DECODE (DH.TRANSACTION_HEADER_ID_OUT, NULL,
638 DECODE (TH_RT.TRANSACTION_TYPE_CODE, 'FULL RETIREMENT', 'F', DECODE (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')),
639 DECODE(TH_DIST_END.TRANSACTION_TYPE_CODE,'TRANSFER', 'T', 'TRANSFER OUT', 'P', 'RECLASS', 'R')) T_TYPE,
640 DD_BONUS.PERIOD_COUNTER ,
641 ucd ,
642 '',
643 AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
644 BR.BONUS_RATE ,
645 CB.ASSET_COST_ACCT
646 FROM
647 --FA_DEPRN_DETAIL DD_BONUS,
648 ( SELECT
649 DISTRIBUTION_ID ,
650 MAX(PERIOD_COUNTER) PERIOD_COUNTER
651 FROM FA_DEPRN_DETAIL
652 WHERE BOOK_TYPE_CODE = book
653 AND PERIOD_COUNTER <= upc
654 AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
655 GROUP BY DISTRIBUTION_ID
656 ) DD1,
657 FA_DEPRN_DETAIL DD_BONUS ,
658 FA_DISTRIBUTION_HISTORY DH ,
659 FA_ASSET_HISTORY AH ,
660 FA_BOOKS BOOKS ,
661 FA_TRANSACTION_HEADERS TH_RT,
662 FA_TRANSACTION_HEADERS TH_DIST_END,
663 FA_CATEGORY_BOOKS CB ,
664 FA_ADDITIONS AD,
665 FA_BONUS_RATES BR,
666 FA_DEPRN_PERIODS DP
667 WHERE BOOKS.BOOK_TYPE_CODE = book
668 AND BOOKS.ASSET_ID = DD_BONUS.ASSET_ID --7721457
669 AND BOOKS.ASSET_ID = AD.ASSET_ID
670 AND NVL(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc
671 AND BOOKS.DATE_EFFECTIVE <= ucd
672 AND NVL(BOOKS.DATE_INEFFECTIVE,sysdate+1) > ucd
673 AND CB.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
674 AND DD_BONUS.DISTRIBUTION_ID = DD1.DISTRIBUTION_ID --7721457
675 AND DD_BONUS.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
676 AND DD_BONUS.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
677 AND CB.CATEGORY_ID = AH.CATEGORY_ID
678 AND AH.ASSET_ID = DD_BONUS.ASSET_ID --7721457
679 AND AH.DATE_EFFECTIVE < ucd
680 AND NVL(AH.DATE_INEFFECTIVE,sysdate) >= ucd
681 AND AH.ASSET_TYPE = 'CAPITALIZED'
682 AND DD_BONUS.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
683 AND DD_BONUS.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
684 AND BOOKS.BONUS_RULE = BR.BONUS_RULE (+)
685 AND DP.BOOK_TYPE_CODE = DD_BONUS.BOOK_TYPE_CODE
686 AND DP.PERIOD_COUNTER = DD_BONUS.PERIOD_COUNTER
687 AND DP.FISCAL_YEAR BETWEEN DECODE(BOOKS.BONUS_RULE,
688 null, DP.FISCAL_YEAR,
689 BR.START_YEAR)
690 AND DECODE(BOOKS.BONUS_RULE,
691 null, DP.FISCAL_YEAR,
692 NVL(BR.END_YEAR, DP.FISCAL_YEAR))
693 AND TH_RT.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
694 AND TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
695 AND TH_DIST_END.BOOK_TYPE_CODE = DH.BOOK_TYPE_CODE --bug#10259151
696 AND TH_DIST_END.ASSET_ID = TH_RT.ASSET_ID
697 AND TH_DIST_END.TRANSACTION_HEADER_ID = DECODE(DH.TRANSACTION_HEADER_ID_OUT, NULL,DH.TRANSACTION_HEADER_ID_IN,DH.TRANSACTION_HEADER_ID_OUT)
698 AND DH.BOOK_TYPE_CODE = dist_book
699 AND DH.DATE_EFFECTIVE <= ucd
700 AND NVL(DH.DATE_INEFFECTIVE, sysdate) > tod;
701
702 End if;
703
704 End if;
705
706 -- run only if CRL installed
707 elsif (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y' ) then
708
709 -- Insert Non-Group Details
710 if (h_reporting_flag = 'R') then
711 INSERT INTO FA_RESERVE_LEDGER_GT
712 (
713 ASSET_ID ,
714 DH_CCID ,
718 LIFE ,
715 DEPRN_RESERVE_ACCT ,
716 DATE_PLACED_IN_SERVICE,
717 METHOD_CODE ,
719 RATE ,
720 CAPACITY ,
721 COST ,
722 DEPRN_AMOUNT ,
723 YTD_DEPRN ,
724 DEPRN_RESERVE ,
725 PERCENT ,
726 TRANSACTION_TYPE ,
727 PERIOD_COUNTER ,
728 DATE_EFFECTIVE ,
729 ASSET_NUMBER ,
730 BONUS_RATE ,
731 ASSET_COST_ACCT
732 )
733 SELECT /*+ ORDERED
734 Index(DD FA_DEPRN_DETAIL_U1)
735 index(DH FA_DISTRIBUTION_HISTORY_U1)
736 Index(AH FA_ASSET_HISTORY_N2)
737 */
738 DH.ASSET_ID ASSET_ID ,
739 DH.CODE_COMBINATION_ID DH_CCID ,
740 CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT ,
741 BOOKS.DATE_PLACED_IN_SERVICE START_DATE ,
742 BOOKS.DEPRN_METHOD_CODE METHOD ,
743 BOOKS.LIFE_IN_MONTHS LIFE ,
744 BOOKS.ADJUSTED_RATE RATE ,
745 BOOKS.PRODUCTION_CAPACITY CAPACITY ,
746 DD.COST COST ,
747 DECODE (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0) DEPRN_AMOUNT ,
748 DECODE (SIGN (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN) YTD_DEPRN ,
749 DD.DEPRN_RESERVE DEPRN_RESERVE ,
750 DECODE (TH.TRANSACTION_TYPE_CODE, NULL, DH.UNITS_ASSIGNED / AH.UNITS * 100) PERCENT ,
751 DECODE (TH.TRANSACTION_TYPE_CODE, NULL, DECODE (TH_RT.TRANSACTION_TYPE_CODE, 'FULL RETIREMENT', 'F', DECODE (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')), 'TRANSFER', 'T', 'TRANSFER OUT', 'P', 'RECLASS', 'R') T_TYPE,
752 DD.PERIOD_COUNTER ,
753 NVL(TH.DATE_EFFECTIVE, ucd),
754 AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
755 BR.BONUS_RATE ,
756 CB.ASSET_COST_ACCT
757 FROM
758 --FA_DEPRN_DETAIL_MRC_V DD,
759 ( SELECT /*+ Index (FA_DEPRN_DETAIL FA_DEPRN_DETAIL_N1 ) */ DISTRIBUTION_ID ,
760 MAX(PERIOD_COUNTER) PERIOD_COUNTER
761 FROM FA_DEPRN_DETAIL_MRC_V
762 WHERE BOOK_TYPE_CODE = book
763 AND PERIOD_COUNTER <= upc
764 AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
765 GROUP BY DISTRIBUTION_ID
766 ) dd1,
767 FA_DEPRN_DETAIL_MRC_V DD ,
768 FA_DISTRIBUTION_HISTORY DH ,
769 FA_ASSET_HISTORY AH ,
770 FA_BOOKS_MRC_V BOOKS ,
771 FA_TRANSACTION_HEADERS TH ,
772 FA_TRANSACTION_HEADERS TH_RT,
773 FA_CATEGORY_BOOKS CB ,
774 FA_ADDITIONS AD,
775 FA_BONUS_RATES BR,
776 FA_DEPRN_PERIODS DP
777 WHERE books.group_asset_id IS NULL
778 AND CB.BOOK_TYPE_CODE = book
779 AND CB.CATEGORY_ID = AH.CATEGORY_ID
780 AND AH.ASSET_ID = DD.ASSET_ID --7721457
781 AND DD.DISTRIBUTION_ID = DD1.DISTRIBUTION_ID --7721457
782 AND DD.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
783 AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
784 AND AH.DATE_EFFECTIVE < NVL(TH.DATE_EFFECTIVE, ucd)
785 AND NVL(AH.DATE_INEFFECTIVE,sysdate) >= NVL(TH.DATE_EFFECTIVE, ucd)
786 AND AH.ASSET_TYPE = 'CAPITALIZED'
787 AND DD.BOOK_TYPE_CODE = book
788 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
789 AND TH_RT.BOOK_TYPE_CODE = book
790 AND TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
791 AND BOOKS.BOOK_TYPE_CODE = book
795 AND BOOKS.DATE_EFFECTIVE <= NVL(TH.DATE_EFFECTIVE, ucd)
792 AND BOOKS.ASSET_ID = DD.ASSET_ID --7721457
793 AND BOOKS.ASSET_ID = AD.ASSET_ID
794 AND NVL(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc
796 AND NVL(BOOKS.DATE_INEFFECTIVE,sysdate+1) > NVL(TH.DATE_EFFECTIVE, ucd)
797 AND BOOKS.BONUS_RULE = BR.BONUS_RULE (+)
798 AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
799 AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
800 AND DP.FISCAL_YEAR BETWEEN DECODE(BOOKS.BONUS_RULE,
801 null, DP.FISCAL_YEAR,
802 BR.START_YEAR)
803 AND DECODE(BOOKS.BONUS_RULE,
804 null, DP.FISCAL_YEAR,
805 NVL(BR.END_YEAR, DP.FISCAL_YEAR))
806 AND TH.BOOK_TYPE_CODE (+) = dist_book
807 AND TH.TRANSACTION_HEADER_ID (+) = DH.TRANSACTION_HEADER_ID_OUT
808 AND TH.DATE_EFFECTIVE (+) BETWEEN tod AND ucd
809 AND DH.BOOK_TYPE_CODE = dist_book
810 AND DH.DATE_EFFECTIVE <= ucd
811 AND NVL(DH.DATE_INEFFECTIVE, sysdate) > tod -- start cua - exclude the group Assets
812 AND books.group_asset_id IS NULL;
813 else
814 INSERT INTO FA_RESERVE_LEDGER_GT
815 (
816 ASSET_ID ,
817 DH_CCID ,
818 DEPRN_RESERVE_ACCT ,
819 DATE_PLACED_IN_SERVICE,
820 METHOD_CODE ,
821 LIFE ,
822 RATE ,
823 CAPACITY ,
824 COST ,
825 DEPRN_AMOUNT ,
826 YTD_DEPRN ,
827 DEPRN_RESERVE ,
828 PERCENT ,
829 TRANSACTION_TYPE ,
830 PERIOD_COUNTER ,
831 DATE_EFFECTIVE ,
832 ASSET_NUMBER ,
833 BONUS_RATE ,
834 ASSET_COST_ACCT
835 )
836 SELECT
837 /*+ ORDERED
838 Index(DD FA_DEPRN_DETAIL_U1)
839 index(DH FA_DISTRIBUTION_HISTORY_U1)
840 Index(AH FA_ASSET_HISTORY_N2)
841 */
842 DH.ASSET_ID ASSET_ID ,
843 DH.CODE_COMBINATION_ID DH_CCID ,
844 CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT ,
845 BOOKS.DATE_PLACED_IN_SERVICE START_DATE ,
846 BOOKS.DEPRN_METHOD_CODE METHOD ,
847 BOOKS.LIFE_IN_MONTHS LIFE ,
848 BOOKS.ADJUSTED_RATE RATE ,
849 BOOKS.PRODUCTION_CAPACITY CAPACITY ,
850 DD.COST COST ,
851 DECODE (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0) DEPRN_AMOUNT ,
852 DECODE (SIGN (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN) YTD_DEPRN ,
853 DD.DEPRN_RESERVE DEPRN_RESERVE ,
854 DECODE (TH.TRANSACTION_TYPE_CODE, NULL, DH.UNITS_ASSIGNED / AH.UNITS * 100) PERCENT ,
855 DECODE (TH.TRANSACTION_TYPE_CODE, NULL, DECODE (TH_RT.TRANSACTION_TYPE_CODE, 'FULL RETIREMENT', 'F', DECODE (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')), 'TRANSFER', 'T', 'TRANSFER OUT', 'P', 'RECLASS', 'R') T_TYPE,
856 DD.PERIOD_COUNTER ,
857 NVL(TH.DATE_EFFECTIVE, ucd),
858 AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
859 BR.BONUS_RATE ,
860 CB.ASSET_COST_ACCT
861 FROM
862 --FA_DEPRN_DETAIL DD,
866 WHERE BOOK_TYPE_CODE = book
863 ( SELECT /*+ Index (FA_DEPRN_DETAIL FA_DEPRN_DETAIL_N1 ) */ DISTRIBUTION_ID ,
864 MAX(PERIOD_COUNTER) PERIOD_COUNTER
865 FROM FA_DEPRN_DETAIL
867 AND PERIOD_COUNTER <= upc
868 AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
869 GROUP BY DISTRIBUTION_ID
870 ) DD1,
871 FA_DEPRN_DETAIL DD ,
872 FA_DISTRIBUTION_HISTORY DH ,
873 FA_ASSET_HISTORY AH ,
874 FA_BOOKS BOOKS ,
875 FA_TRANSACTION_HEADERS TH ,
876 FA_TRANSACTION_HEADERS TH_RT,
877 FA_CATEGORY_BOOKS CB ,
878 FA_ADDITIONS AD,
879 FA_BONUS_RATES BR,
880 FA_DEPRN_PERIODS DP
881 WHERE books.group_asset_id IS NULL
882 AND CB.BOOK_TYPE_CODE = book
883 AND CB.CATEGORY_ID = AH.CATEGORY_ID
884 AND DD.DISTRIBUTION_ID = DD1.DISTRIBUTION_ID --7721457
885 AND DD.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
886 AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
887 AND AH.ASSET_ID = DD.ASSET_ID --7721457
888 AND AH.DATE_EFFECTIVE < NVL(TH.DATE_EFFECTIVE, ucd)
889 AND NVL(AH.DATE_INEFFECTIVE,sysdate) >= NVL(TH.DATE_EFFECTIVE, ucd)
890 AND AH.ASSET_TYPE = 'CAPITALIZED'
891 AND DD.BOOK_TYPE_CODE = book
892 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
893 AND TH_RT.BOOK_TYPE_CODE = book
894 AND TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
895 AND BOOKS.BOOK_TYPE_CODE = book
896 AND BOOKS.ASSET_ID = DD.ASSET_ID --7721457
897 AND BOOKS.ASSET_ID = AD.ASSET_ID
898 AND NVL(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc
899 AND BOOKS.DATE_EFFECTIVE <= NVL(TH.DATE_EFFECTIVE, ucd)
900 AND NVL(BOOKS.DATE_INEFFECTIVE,sysdate+1) > NVL(TH.DATE_EFFECTIVE, ucd)
901 AND TH.BOOK_TYPE_CODE (+) = dist_book
902 AND TH.TRANSACTION_HEADER_ID (+) = DH.TRANSACTION_HEADER_ID_OUT
903 AND TH.DATE_EFFECTIVE (+) BETWEEN tod AND ucd
904 AND BOOKS.BONUS_RULE = BR.BONUS_RULE (+)
905 AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
906 AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
907 AND DP.FISCAL_YEAR BETWEEN DECODE(BOOKS.BONUS_RULE,
908 null, DP.FISCAL_YEAR,
909 BR.START_YEAR)
910 AND DECODE(BOOKS.BONUS_RULE,
911 null, DP.FISCAL_YEAR,
912 NVL(BR.END_YEAR, DP.FISCAL_YEAR))
913 AND DH.BOOK_TYPE_CODE = dist_book
914 AND DH.DATE_EFFECTIVE <= ucd
915 AND NVL(DH.DATE_INEFFECTIVE, sysdate) > tod
916 AND books.group_asset_id IS NULL;
917 END IF;
918 -- end cua
919
920
921 -- Insert the Group Depreciation Details
922 IF (h_reporting_flag = 'R') THEN
923 INSERT INTO FA_RESERVE_LEDGER_GT
924 ( ASSET_ID ,
925 DH_CCID ,
926 DEPRN_RESERVE_ACCT ,
927 DATE_PLACED_IN_SERVICE,
928 METHOD_CODE ,
929 LIFE ,
930 RATE ,
931 CAPACITY ,
932 COST ,
933 DEPRN_AMOUNT ,
934 YTD_DEPRN ,
935 DEPRN_RESERVE ,
936 PERCENT ,
937 TRANSACTION_TYPE ,
938 PERIOD_COUNTER ,
939 DATE_EFFECTIVE ,
940 ASSET_NUMBER ,
941 BONUS_RATE ,
942 ASSET_COST_ACCT
943 )
944 SELECT GAR.GROUP_ASSET_ID ASSET_ID ,
945 GAD.DEPRN_EXPENSE_ACCT_CCID CH_CCID ,
946 GAD.DEPRN_RESERVE_ACCT_CCID RSV_ACCOUNT ,
947 GAR.DEPRN_START_DATE START_DATE ,
948 GAR.DEPRN_METHOD_CODE METHOD ,
949 GAR.LIFE_IN_MONTHS LIFE ,
950 GAR.ADJUSTED_RATE RATE ,
951 GAR.PRODUCTION_CAPACITY CAPACITY ,
952 DD.ADJUSTED_COST COST ,
953 DECODE (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0) DEPRN_AMOUNT ,
954 DECODE (SIGN (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN) YTD_DEPRN,
955 DD.DEPRN_RESERVE DEPRN_RESERVE ,
956 /* round (decode (TH.TRANSACTION_TYPE_CODE, null,
957 DH.UNITS_ASSIGNED / AH.UNITS * 100),2)
958 PERCENT,
962 decode (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')),
959 decode (TH.TRANSACTION_TYPE_CODE, null,
960 decode (TH_RT.TRANSACTION_TYPE_CODE,
961 'FULL RETIREMENT', 'F',
963 'TRANSFER', 'T',
964 'TRANSFER OUT', 'P',
965 'RECLASS', 'R') T_TYPE,
966 DD.PERIOD_COUNTER,
967 NVL(TH.DATE_EFFECTIVE, ucd) */
968 100 PERCENT ,
969 'G' T_TYPE ,
970 DD.PERIOD_COUNTER,
971 UCD,
972 AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
973 BR.BONUS_RATE ,
974 GAD.ASSET_COST_ACCT
975 FROM FA_DEPRN_SUMMARY_MRC_V DD ,
976 FA_GROUP_ASSET_RULES GAR ,
977 FA_GROUP_ASSET_DEFAULT GAD,
978 FA_DEPRN_PERIODS_MRC_V DP,
979 FA_ADDITIONS AD,
980 FA_BONUS_RATES BR
981 WHERE DD.BOOK_TYPE_CODE = book
982 AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
983 AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
984 AND DD.ASSET_ID = AD.ASSET_ID
985 AND GAD.SUPER_GROUP_ID IS NULL -- MPOWELL
986 AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
987 AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
988 AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
989 AND DD.PERIOD_COUNTER =
990 (SELECT MAX (DD_SUB.PERIOD_COUNTER)
991 FROM FA_DEPRN_DETAIL_MRC_V DD_SUB
992 WHERE DD_SUB.BOOK_TYPE_CODE = book
993 AND DD_SUB.ASSET_ID = GAR.GROUP_ASSET_ID
994 AND DD_SUB.PERIOD_COUNTER <= upc
995 )
996 AND DD.PERIOD_COUNTER = DP.PERIOD_COUNTER
997 AND DD.BOOK_TYPE_CODE = DP.BOOK_TYPE_CODE
998 AND GAR.BONUS_RULE = BR.BONUS_RULE (+)
999 AND DP.FISCAL_YEAR BETWEEN DECODE(GAR.BONUS_RULE,
1000 null, DP.FISCAL_YEAR,
1001 BR.START_YEAR)
1002 AND DECODE(GAR.BONUS_RULE,
1003 null, DP.FISCAL_YEAR,
1004 NVL(BR.END_YEAR, DP.FISCAL_YEAR))
1005 AND GAR.DATE_EFFECTIVE <= DP.CALENDAR_PERIOD_CLOSE_DATE -- mwoodwar
1006 AND NVL(GAR.DATE_INEFFECTIVE, (DP.CALENDAR_PERIOD_CLOSE_DATE + 1)) > DP.CALENDAR_PERIOD_CLOSE_DATE; -- mwoodwar
1007 ELSE
1008 INSERT INTO FA_RESERVE_LEDGER_GT
1009 ( ASSET_ID ,
1010 DH_CCID ,
1011 DEPRN_RESERVE_ACCT ,
1012 DATE_PLACED_IN_SERVICE,
1013 METHOD_CODE ,
1014 LIFE ,
1015 RATE ,
1016 CAPACITY ,
1017 COST ,
1018 DEPRN_AMOUNT ,
1019 YTD_DEPRN ,
1020 DEPRN_RESERVE ,
1021 PERCENT ,
1022 TRANSACTION_TYPE ,
1023 PERIOD_COUNTER ,
1024 DATE_EFFECTIVE ,
1025 ASSET_NUMBER ,
1026 BONUS_RATE ,
1027 ASSET_COST_ACCT
1028 )
1029 SELECT GAR.GROUP_ASSET_ID ASSET_ID ,
1030 GAD.DEPRN_EXPENSE_ACCT_CCID CH_CCID ,
1031 GAD.DEPRN_RESERVE_ACCT_CCID RSV_ACCOUNT ,
1032 GAR.DEPRN_START_DATE START_DATE ,
1033 GAR.DEPRN_METHOD_CODE METHOD ,
1034 GAR.LIFE_IN_MONTHS LIFE ,
1035 GAR.ADJUSTED_RATE RATE ,
1036 GAR.PRODUCTION_CAPACITY CAPACITY ,
1037 DD.ADJUSTED_COST COST ,
1038 DECODE (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0) DEPRN_AMOUNT ,
1039 DECODE (SIGN (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN) YTD_DEPRN,
1040 DD.DEPRN_RESERVE DEPRN_RESERVE ,
1041 /* round (decode (TH.TRANSACTION_TYPE_CODE, null,
1042 DH.UNITS_ASSIGNED / AH.UNITS * 100),2)
1043 PERCENT,
1044 decode (TH.TRANSACTION_TYPE_CODE, null,
1045 decode (TH_RT.TRANSACTION_TYPE_CODE,
1046 'FULL RETIREMENT', 'F',
1047 decode (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')),
1048 'TRANSFER', 'T',
1049 'TRANSFER OUT', 'P',
1050 'RECLASS', 'R') T_TYPE,
1051 DD.PERIOD_COUNTER,
1052 NVL(TH.DATE_EFFECTIVE, ucd) */
1053 100 PERCENT ,
1054 'G' T_TYPE ,
1055 DD.PERIOD_COUNTER,
1056 UCD,
1057 AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
1061 FA_GROUP_ASSET_RULES GAR ,
1058 BR.BONUS_RATE ,
1059 GAD.ASSET_COST_ACCT
1060 FROM FA_DEPRN_SUMMARY DD ,
1062 FA_GROUP_ASSET_DEFAULT GAD,
1063 FA_DEPRN_PERIODS DP,
1064 FA_ADDITIONS AD,
1065 FA_BONUS_RATES BR
1066 WHERE DD.BOOK_TYPE_CODE = book
1067 AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
1068 AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
1069 AND DD.ASSET_ID = AD.ASSET_ID
1070 AND GAD.SUPER_GROUP_ID IS NULL -- MPOWELL
1071 AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
1072 AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
1073 AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
1074 AND DD.PERIOD_COUNTER =
1075 (SELECT MAX (DD_SUB.PERIOD_COUNTER)
1076 FROM FA_DEPRN_DETAIL DD_SUB
1077 WHERE DD_SUB.BOOK_TYPE_CODE = book
1078 AND DD_SUB.ASSET_ID = GAR.GROUP_ASSET_ID
1079 AND DD_SUB.PERIOD_COUNTER <= upc
1080 )
1081 AND DD.PERIOD_COUNTER = DP.PERIOD_COUNTER
1082 AND DD.BOOK_TYPE_CODE = DP.BOOK_TYPE_CODE
1083 AND GAR.BONUS_RULE = BR.BONUS_RULE (+)
1084 AND DP.FISCAL_YEAR BETWEEN DECODE(GAR.BONUS_RULE,
1085 null, DP.FISCAL_YEAR,
1086 BR.START_YEAR)
1087 AND DECODE(GAR.BONUS_RULE,
1088 null, DP.FISCAL_YEAR,
1089 NVL(BR.END_YEAR, DP.FISCAL_YEAR))
1090
1091 AND GAR.DATE_EFFECTIVE <= DP.CALENDAR_PERIOD_CLOSE_DATE -- mwoodwar
1092 AND NVL(GAR.DATE_INEFFECTIVE, (DP.CALENDAR_PERIOD_CLOSE_DATE + 1)) > DP.CALENDAR_PERIOD_CLOSE_DATE; -- mwoodwar
1093 END IF;
1094
1095 -- Insert the SuperGroup Depreciation Details MPOWELL
1096
1097 IF (h_reporting_flag = 'R') THEN
1098 INSERT INTO FA_RESERVE_LEDGER_GT
1099 (
1100 ASSET_ID ,
1101 DH_CCID ,
1102 DEPRN_RESERVE_ACCT ,
1103 DATE_PLACED_IN_SERVICE,
1104 METHOD_CODE ,
1105 LIFE ,
1106 RATE ,
1107 CAPACITY ,
1108 COST ,
1109 DEPRN_AMOUNT ,
1110 YTD_DEPRN ,
1111 DEPRN_RESERVE ,
1112 PERCENT ,
1113 TRANSACTION_TYPE ,
1114 PERIOD_COUNTER ,
1115 DATE_EFFECTIVE ,
1116 ASSET_NUMBER ,
1117 BONUS_RATE ,
1118 ASSET_COST_ACCT
1119 )
1120 SELECT GAR.GROUP_ASSET_ID ASSET_ID ,
1121 GAD.DEPRN_EXPENSE_ACCT_CCID DH_CCID ,
1122 GAD.DEPRN_RESERVE_ACCT_CCID RSV_ACCOUNT ,
1123 GAR.DEPRN_START_DATE START_DATE ,
1124 SGR.DEPRN_METHOD_CODE METHOD , -- MPOWELL
1125 GAR.LIFE_IN_MONTHS LIFE ,
1126 SGR.ADJUSTED_RATE RATE , -- MPOWELL
1127 GAR.PRODUCTION_CAPACITY CAPACITY ,
1128 DD.ADJUSTED_COST COST ,
1129 DECODE (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0) DEPRN_AMOUNT ,
1130 DECODE (SIGN (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN) YTD_DEPRN,
1131 DD.DEPRN_RESERVE DEPRN_RESERVE ,
1132 100 PERCENT ,
1133 'G' T_TYPE ,
1134 DD.PERIOD_COUNTER ,
1135 UCD,
1136 AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
1137 BR.BONUS_RATE ,
1138 GAD.ASSET_COST_ACCT
1139 FROM FA_DEPRN_SUMMARY_MRC_V DD ,
1140 fa_GROUP_ASSET_RULES GAR ,
1141 fa_GROUP_ASSET_DEFAULT GAD,
1142 fa_SUPER_GROUP_RULES SGR ,
1143 FA_DEPRN_PERIODS_MRC_V DP,
1144 FA_ADDITIONS AD,
1145 FA_BONUS_RATES BR
1146 WHERE DD.BOOK_TYPE_CODE = book
1147 AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
1148 AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
1149 AND DD.ASSET_ID = AD.ASSET_ID
1150 AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
1151 AND GAD.SUPER_GROUP_ID = SGR.SUPER_GROUP_ID -- MPOWELL
1152 AND GAD.BOOK_TYPE_CODE = SGR.BOOK_TYPE_CODE -- MPOWELL
1153 AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
1154 AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
1155 AND DD.PERIOD_COUNTER =
1156 (SELECT MAX (DD_SUB.PERIOD_COUNTER)
1157 FROM FA_DEPRN_DETAIL_MRC_V DD_SUB
1158 WHERE DD_SUB.BOOK_TYPE_CODE = book
1159 AND DD_SUB.ASSET_ID = GAR.GROUP_ASSET_ID
1160 AND DD_SUB.PERIOD_COUNTER <= upc
1161 )
1162 AND DD.PERIOD_COUNTER = DP.PERIOD_COUNTER
1163 AND DD.BOOK_TYPE_CODE = DP.BOOK_TYPE_CODE
1164 AND GAR.BONUS_RULE = BR.BONUS_RULE (+)
1165 AND DP.FISCAL_YEAR BETWEEN DECODE(GAR.BONUS_RULE,
1166 null, DP.FISCAL_YEAR,
1167 BR.START_YEAR)
1168 AND DECODE(GAR.BONUS_RULE,
1169 null, DP.FISCAL_YEAR,
1170 NVL(BR.END_YEAR, DP.FISCAL_YEAR))
1171 AND GAR.DATE_EFFECTIVE <= DP.CALENDAR_PERIOD_CLOSE_DATE
1172 AND NVL(GAR.DATE_INEFFECTIVE, (DP.CALENDAR_PERIOD_CLOSE_DATE + 1)) > DP.CALENDAR_PERIOD_CLOSE_DATE
1173 AND SGR.DATE_EFFECTIVE <= DP.CALENDAR_PERIOD_CLOSE_DATE
1174 AND NVL(SGR.DATE_INEFFECTIVE, (DP.CALENDAR_PERIOD_CLOSE_DATE + 1)) > DP.CALENDAR_PERIOD_CLOSE_DATE;
1175 ELSE
1176 INSERT INTO FA_RESERVE_LEDGER_GT
1177 (
1178 ASSET_ID ,
1179 DH_CCID ,
1180 DEPRN_RESERVE_ACCT ,
1181 DATE_PLACED_IN_SERVICE,
1182 METHOD_CODE ,
1183 LIFE ,
1184 RATE ,
1185 CAPACITY ,
1186 COST ,
1187 DEPRN_AMOUNT ,
1188 YTD_DEPRN ,
1189 DEPRN_RESERVE ,
1190 PERCENT ,
1191 TRANSACTION_TYPE ,
1192 PERIOD_COUNTER ,
1193 DATE_EFFECTIVE ,
1194 ASSET_NUMBER ,
1195 BONUS_RATE ,
1196 ASSET_COST_ACCT
1197 )
1198 SELECT GAR.GROUP_ASSET_ID ASSET_ID ,
1199 GAD.DEPRN_EXPENSE_ACCT_CCID DH_CCID ,
1200 GAD.DEPRN_RESERVE_ACCT_CCID RSV_ACCOUNT ,
1201 GAR.DEPRN_START_DATE START_DATE ,
1202 SGR.DEPRN_METHOD_CODE METHOD , -- MPOWELL
1203 GAR.LIFE_IN_MONTHS LIFE ,
1204 SGR.ADJUSTED_RATE RATE , -- MPOWELL
1205 GAR.PRODUCTION_CAPACITY CAPACITY ,
1206 DD.ADJUSTED_COST COST ,
1207 DECODE (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0) DEPRN_AMOUNT ,
1208 DECODE (SIGN (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN) YTD_DEPRN,
1209 DD.DEPRN_RESERVE DEPRN_RESERVE ,
1210 100 PERCENT ,
1211 'G' T_TYPE ,
1212 DD.PERIOD_COUNTER ,
1213 UCD,
1214 AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
1215 BR.BONUS_RATE ,
1216 GAD.ASSET_COST_ACCT
1217 FROM FA_DEPRN_SUMMARY DD ,
1218 fa_GROUP_ASSET_RULES GAR ,
1219 fa_GROUP_ASSET_DEFAULT GAD,
1220 fa_SUPER_GROUP_RULES SGR ,
1221 FA_DEPRN_PERIODS DP,
1222 FA_ADDITIONS AD,
1223 FA_BONUS_RATES BR
1224 WHERE DD.BOOK_TYPE_CODE = book
1225 AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
1226 AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
1227 AND DD.ASSET_ID = AD.ASSET_ID
1228 AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
1229 AND GAD.SUPER_GROUP_ID = SGR.SUPER_GROUP_ID -- MPOWELL
1230 AND GAD.BOOK_TYPE_CODE = SGR.BOOK_TYPE_CODE -- MPOWELL
1231 AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
1232 AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
1233 AND DD.PERIOD_COUNTER =
1234 (SELECT MAX (DD_SUB.PERIOD_COUNTER)
1235 FROM FA_DEPRN_DETAIL DD_SUB
1236 WHERE DD_SUB.BOOK_TYPE_CODE = book
1237 AND DD_SUB.ASSET_ID = GAR.GROUP_ASSET_ID
1238 AND DD_SUB.PERIOD_COUNTER <= upc
1239 )
1240 AND DD.PERIOD_COUNTER = DP.PERIOD_COUNTER
1241 AND DD.BOOK_TYPE_CODE = DP.BOOK_TYPE_CODE
1242 AND GAR.BONUS_RULE = BR.BONUS_RULE (+)
1243 AND DP.FISCAL_YEAR BETWEEN DECODE(GAR.BONUS_RULE,
1244 null, DP.FISCAL_YEAR,
1245 BR.START_YEAR)
1246 AND DECODE(GAR.BONUS_RULE,
1247 null, DP.FISCAL_YEAR,
1248 NVL(BR.END_YEAR, DP.FISCAL_YEAR))
1249 AND GAR.DATE_EFFECTIVE <= DP.CALENDAR_PERIOD_CLOSE_DATE
1250 AND NVL(GAR.DATE_INEFFECTIVE, (DP.CALENDAR_PERIOD_CLOSE_DATE + 1)) > DP.CALENDAR_PERIOD_CLOSE_DATE
1251 AND SGR.DATE_EFFECTIVE <= DP.CALENDAR_PERIOD_CLOSE_DATE
1252 AND NVL(SGR.DATE_INEFFECTIVE, (DP.CALENDAR_PERIOD_CLOSE_DATE + 1)) > DP.CALENDAR_PERIOD_CLOSE_DATE;
1253
1254 END IF;
1255 END IF; --end of CRL check
1256 End Loop;
1257 commit;
1258
1259 exception
1260 when others then
1261 retcode := SQLCODE;
1262 errbuf := SQLERRM;
1263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1264 END RSVLDG;
1265 END FA_RSVLDG_REP_INS_PKG;