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