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