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