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