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