[Home] [Help]
PACKAGE BODY: APPS.FA_RES_LDG_PKG
Source
1 PACKAGE BODY FA_RES_LDG_PKG AS
2 -- $Header: FARESLDGPB.pls 120.3.12010000.1 2008/07/28 13:13:39 appldev ship $
3 -- ****************************************************************************************
4 -- Copyright (c) 2000 Oracle Solution Services (India) Product Development
5 -- All rights reserved
6 -- ****************************************************************************************
7 --
8 -- PROGRAM NAME
9 -- FARESLDGPB.pls
10 --
11 -- DESCRIPTION
12 -- This script creates the package body of FA_RES_LDG_PKG
13 -- This package is used to generate Bulgarian Reserve Ledger Report
14 --
15 -- USAGE
16 -- To install How to Install
17 -- To execute How to Execute
18 --
19 -- DEPENDENCIES
20 -- None.
21 --
22 --
23 -- LAST UPDATE DATE 26-JAN-2007
24 -- Date the program has been modified for the last time
25 --
26 -- HISTORY
27 -- =======
28 --
29 -- VERSION DATE AUTHOR(S) DESCRIPTION
30 -- ------- ----------- --------------- ------------------------------------
31 -- 1.0 26-JAN-2007 Praveen Gollu M Creation
32 --
33 --****************************************************************************************
34 -------------------------------------------------------------------------------------------------------------------------------------------------------------
35 -------------------------------------------------------------------------------------------------------------------------------------------------------------
36 -------------------------------------------------------------------------------------------------------------------------------------------------------------
37 FUNCTION BookFormula RETURN VARCHAR2 IS
38 lc_book VARCHAR2(15);
39 lc_book_class VARCHAR2(15);
40 ln_accounting_flex_structure NUMBER(15);
41 lc_currency_code VARCHAR2(15);
42 lc_distribution_source_book VARCHAR2(15);
43 ln_precision NUMBER(15);
44 BEGIN
45 SELECT BC.book_type_code
46 ,BC.book_class
47 ,BC.accounting_flex_structure
48 ,BC.distribution_source_book
49 ,SOB.currency_code
50 ,CUR.precision
51 INTO lc_book
52 ,lc_book_class
53 ,ln_accounting_flex_structure
54 ,lc_distribution_source_book
55 ,lc_currency_code
56 ,ln_precision
57 FROM fa_book_controls BC
58 ,gl_ledgers SOB
59 ,fnd_currencies CUR
60 WHERE BC.book_type_code = P_BOOK
61 AND SOB.ledger_id = BC.set_of_books_id
62 AND SOB.currency_code = CUR.currency_code;
63
64 gc_book_class := lc_book_class;
65 gn_accounting_flex_structure := ln_accounting_flex_structure;
66 gc_distribution_source_book := lc_distribution_source_book;
67 gc_currency_code := lc_currency_code;
68 RETURN(lc_book);
69 END;
70 -------------------------------------------------------------------------------------------------------------------------------------------------------------
71 FUNCTION Period1Formula RETURN VARCHAR2 IS
72 BEGIN
73
74 DECLARE
75 lc_period_name VARCHAR2(15);
76 ld_period_POD DATE;
77 ld_period_PCD DATE;
78 lc_period_closed VARCHAR2(4);
79 ln_period_PC NUMBER(15);
80 ln_period_FY NUMBER(15);
81 BEGIN
82 SELECT FDP.period_name
83 ,FDP.period_counter
84 ,FDP.period_open_date
85 ,NVL(FDP.period_close_date, SYSDATE)
86 ,DECODE(FDP.period_close_date, NULL, 'NO', 'YES')
87 ,FDP.fiscal_year
88 INTO lc_period_name
89 ,ln_period_PC
90 ,ld_period_POD
91 ,ld_period_PCD
92 ,lc_period_closed
93 ,ln_period_FY
94 FROM fa_deprn_periods FDP
95 WHERE FDP.book_type_code = P_BOOK
96 AND FDP.period_name = P_PERIOD1;
97
98 gn_period1_pc := ln_period_PC;
99 gd_period1_pod := ld_period_POD;
100 gd_period1_pcd := ld_period_PCD;
101 gc_period_closed := lc_period_closed;
102 gn_period1_fy := ln_period_FY;
103 RETURN(lc_period_name);
104 END;
105 RETURN NULL;
106 END;
107 -------------------------------------------------------------------------------------------------------------------------------------------------------------
108
109 -------------------------------------------------------------------------------------------------------------------------------------------------------------
110 FUNCTION BeforeReport RETURN BOOLEAN
111 IS
112 lc_book VARCHAR2(15);
113 BEGIN
114 lc_book :=BookFormula();
115 RETURN (TRUE);
116 END;
117 -------------------------------------------------------------------------------------------------------------------------------------------------------------
118 FUNCTION AfterReport RETURN BOOLEAN IS
119 BEGIN
120 BEGIN
121 ROLLBACK;
122 END;
123 RETURN (TRUE);
124 END;
125 -------------------------------------------------------------------------------------------------------------------------------------------------------------
126 FUNCTION c_do_insertformula(Book IN VARCHAR2, Period1 IN VARCHAR2) RETURN NUMBER IS
127 BEGIN
128
129 DECLARE
130 lc_book VARCHAR2(15);
131 lc_period VARCHAR2(15);
132 lc_errbuf VARCHAR2(250);
133 ln_retcode NUMBER;
134 BEGIN
135
136 lc_book := Book;
137 lc_period := Period1;
138 FA_RSVLDG (lc_book, lc_period, lc_errbuf, ln_retcode);
139 C_Errbuf := lc_errbuf;
140 C_RetCode := ln_retcode;
141
142 RETURN (1);
143 END;
144 RETURN NULL;
145 END;
146 -------------------------------------------------------------------------------------------------------------------------------------------------------------
147 FUNCTION d_lifeformula(LIFE IN NUMBER
148 , ADJ_RATE IN NUMBER
149 , BONUS_RATE IN NUMBER
150 , PROD IN NUMBER) RETURN VARCHAR2 IS
151 BEGIN
152 DECLARE
153 ln_life NUMBER;
154 ln_adj_rate NUMBER;
155 ln_bonus_rate NUMBER;
156 ln_prod NUMBER;
157 lc_d_life VARCHAR2(7);
158
159 BEGIN
160 ln_life := LIFE;
161 ln_adj_rate := ADJ_RATE;
162 ln_bonus_rate := BONUS_RATE;
163 ln_prod := PROD;
164 lc_d_life := fadolif(ln_life, ln_adj_rate, ln_bonus_rate, ln_prod);
165 RETURN(lc_d_life);
166 END;
167 RETURN NULL;
168 END;
169 -------------------------------------------------------------------------------------------------------------------------------------------------------------
170
171 -------------------------------------------------------------------------------------------------------------------------------------------------------------
172 --Functions to refer Oracle report placeholders--
173
174 FUNCTION Accounting_Flex_Structure_p RETURN NUMBER IS
175 BEGIN
176 RETURN gn_accounting_flex_structure;
177 END;
178 FUNCTION Currency_Code_p RETURN VARCHAR2 IS
179 BEGIN
180 RETURN gc_currency_code;
181 END;
182
183 -------------------------------------------------------------------------------------------------------------------------------------------------------------
184 PROCEDURE FA_RSVLDG
185 (book IN VARCHAR2
186 ,period IN VARCHAR2
187 ,errbuf OUT NOCOPY VARCHAR2
188 ,retcode OUT NOCOPY NUMBER)
189 IS
190 PRAGMA AUTONOMOUS_TRANSACTION;
191 OPERATION VARCHAR2(200);
192 dist_book VARCHAR2(15);
193 ucd DATE;
194 upc NUMBER;
195 tod DATE;
196 tpc NUMBER;
197
198 h_set_of_books_id NUMBER;
199 h_reporting_flag VARCHAR2(1);
200 BEGIN
201 /* not needed with global temp fix
202 operation := 'Deleting from FA_RESERVE_LEDGER';
203 DELETE FROM FA_RESERVE_LEDGER;
204
205 if (SQL%ROWCOUNT > 0) then
206 operation := 'Committing Delete';
207 COMMIT;
208 else
209 operation := 'Rolling Back Delete';
210 ROLLBACK;
211 end if;
212 */
213
214 -- get mrc related info
215 BEGIN
216 SELECT TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),45,10))
217 INTO h_set_of_books_id FROM dual;
218 EXCEPTION
219 WHEN OTHERS THEN
220 h_set_of_books_id := NULL;
221 END;
222
223 IF (h_set_of_books_id IS NOT NULL) THEN
224 IF NOT fa_cache_pkg.fazcsob
225 (X_set_of_books_id => h_set_of_books_id
226 ,X_mrc_sob_type_code => h_reporting_flag) THEN
227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
228 END IF;
229 ELSE
230 h_reporting_flag := 'P';
231 END IF;
232
233 OPERATION := 'Selecting Book and Period information';
234 IF (h_reporting_flag = 'R') THEN
235 SELECT BC.distribution_source_book dbk
236 ,NVL (DP.period_close_date, SYSDATE) ucd
237 ,DP.period_counter upc
238 ,MIN (DP_FY.period_open_date) tod
239 ,MIN (DP_FY.period_counter) tpc
240 INTO
241 dist_book
242 ,ucd
243 ,upc
244 ,tod
245 ,tpc
246 FROM
247 fa_deprn_periods_mrc_v DP
248 ,fa_deprn_periods_mrc_v DP_FY
249 ,fa_book_controls_mrc_v BC
250 WHERE
251 DP.book_type_code = book
252 AND DP.period_name = period
253 AND DP_FY.book_type_code = book
254 AND DP_FY.fiscal_year = DP.fiscal_year
255 AND BC.book_type_code = book
256 GROUP BY
257 BC.distribution_source_book
258 ,DP.period_close_date
259 ,DP.period_counter;
260 ELSE
261 SELECT
262 BC.distribution_source_book dbk
263 ,NVL (DP.period_close_date, SYSDATE) ucd
264 ,DP.period_counter upc
265 ,MIN (DP_FY.period_open_date) tod
266 ,MIN (DP_FY.period_counter) tpc
267 INTO
268 dist_book
269 ,ucd
270 ,upc
271 ,tod
272 ,tpc
273 FROM
274 fa_deprn_periods DP
275 ,fa_deprn_periods DP_FY
276 ,fa_book_controls BC
277 WHERE
278 DP.book_type_code = book
279 AND DP.period_name = period
280 AND DP_FY.book_type_code = book
281 AND DP_FY.fiscal_year = DP.fiscal_year
282 AND BC.book_type_code = book
283 GROUP BY
284 BC.distribution_source_book
285 ,DP.period_close_date
286 ,DP.period_counter;
287 END IF;
288
289 OPERATION := 'Inserting into FA_RESERVE_LEDGER_GT';
290
291 -- run only if CRL not installed
292 IF (NVL(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'N' ) THEN
293
294 IF (h_reporting_flag = 'R') THEN
295 --FND_FILE.PUT_LINE(FND_FILE.LOG,'1)INSERT INTO ');
296 INSERT INTO FA_RESERVE_LEDGER_GT
297 (asset_id
298 ,dh_ccid
299 ,deprn_reserve_acct
300 ,date_placed_in_service
301 ,method_code
302 ,life
303 ,rate
304 ,capacity
305 ,cost
306 ,deprn_amount
307 ,ytd_deprn
308 ,deprn_reserve
309 ,percent
310 ,transaction_type
311 ,period_counter
312 ,date_effective
313 ,reserve_acct)
314 SELECT
315 DH.asset_id asset_id
316 ,DH.code_combination_id dh_ccid
317 ,CB.deprn_reserve_acct rsv_account
318 ,BOOKS.date_placed_in_service start_date
319 ,BOOKS.deprn_method_code method
320 ,BOOKS.life_in_months life
321 ,BOOKS.adjusted_rate rate
322 ,BOOKS.production_capacity capacity
323 ,DD_BONUS.cost cost
324 ,DECODE (DD_BONUS.period_counter, upc,
325 DD_BONUS.deprn_amount - DD_BONUS.bonus_deprn_amount, 0) deprn_amount
326 ,DECODE (SIGN (tpc - DD_BONUS.period_counter)
327 , 1, 0, DD_BONUS.ytd_deprn - DD_BONUS.bonus_ytd_deprn) ytd_deprn
328 ,DD_BONUS.deprn_reserve - DD_BONUS.bonus_deprn_reserve deprn_reserve
329 ,DECODE (TH.transaction_type_code, NULL
330 ,DH.units_assigned / AH.units * 100) percent
331 ,DECODE (TH.transaction_type_code, NULL,
332 DECODE (TH_RT.transaction_type_code,
333 'FULL RETIREMENT', 'F',
334 DECODE (BOOKS.depreciate_flag, 'NO', 'N')),
335 'TRANSFER', 'T',
336 'TRANSFER OUT', 'P',
337 'RECLASS', 'R') t_type
338 ,DD_BONUS.period_counter
339 ,NVL(TH.date_effective, ucd)
340 ,''
341 FROM
342 fa_deprn_detail_mrc_v DD_BONUS
343 ,fa_asset_history AH
344 ,fa_transaction_headers TH
345 ,fa_transaction_headers TH_RT
346 ,fa_books_mrc_v BOOKS
347 ,fa_distribution_history DH
348 ,fa_category_books CB
349 WHERE
350 CB.book_type_code = book
351 AND CB.category_id = AH.category_id
352 AND AH.asset_id = DH.asset_id
353 AND AH.date_effective < NVL(TH.date_effective, ucd)
354 AND NVL(AH.date_ineffective,SYSDATE)>= NVL(TH.date_effective, ucd)
355 AND AH.asset_type = 'CAPITALIZED'
356 AND DD_BONUS.book_type_code = book
357 AND DD_BONUS.distribution_id = DH.distribution_id
358 AND DD_BONUS.period_counter = (SELECT MAX (DD_SUB.period_counter)
359 FROM fa_deprn_detail_mrc_v DD_SUB
360 WHERE DD_SUB.book_type_code = book
361 AND DD_SUB.asset_id = DH.asset_id
362 AND DD_SUB.distribution_id = DH.distribution_id
363 AND DD_SUB.period_counter <= upc)
364 AND TH_RT.book_type_code = book
365 AND TH_RT.transaction_header_id = BOOKS.transaction_header_id_in
366 AND BOOKS.book_type_code = book
367 AND BOOKS.asset_id = DH.asset_id
368 AND NVL(BOOKS.period_counter_fully_retired, upc) >= tpc
369 AND BOOKS.date_effective <= NVL(TH.date_effective, ucd)
370 AND NVL(BOOKS.date_ineffective,SYSDATE+1) > NVL(TH.date_effective, ucd)
371 AND TH.book_type_code (+) = dist_book
372 AND TH.transaction_header_id (+) = DH.transaction_header_id_out
373 AND TH.date_effective (+) BETWEEN tod AND ucd
374 AND DH.book_type_code = dist_book
375 AND DH.date_effective <= ucd
376 AND NVL(DH.date_ineffective, SYSDATE) > tod
377 UNION ALL
378 SELECT
379 DH.asset_id asset_id
380 ,DH.code_combination_id dh_ccid
381 ,CB.bonus_deprn_reserve_acct rsv_account
382 ,BOOKS.date_placed_in_service start_date
383 ,BOOKS.deprn_method_code method
384 ,BOOKS.life_in_months life
385 ,BOOKS.adjusted_rate rate
386 ,BOOKS.production_capacity capacity
387 ,0 cost
388 ,DECODE (DD.period_counter, upc, DD.bonus_deprn_amount, 0) deprn_amount
389 ,DECODE (SIGN (tpc - DD.period_counter)
390 , 1, 0, DD.bonus_ytd_deprn) ytd_deprn
391 ,DD.bonus_deprn_reserve deprn_reserve
392 ,0 percent
393 ,'B' t_type
394 ,DD.period_counter
395 ,NVL(TH.date_effective, ucd)
396 ,CB.bonus_deprn_expense_acct
397 FROM
398 fa_deprn_detail_mrc_v DD
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
407 AND CB.category_id = AH.category_id
408 AND AH.asset_id = DH.asset_id
409 AND AH.date_effective < NVL(TH.date_effective, ucd)
410 AND NVL(AH.date_ineffective,SYSDATE)
411 >= NVL(TH.DATE_EFFECTIVE, ucd)
412 AND AH.asset_type = 'CAPITALIZED'
413 AND DD.book_type_code = book
414 AND DD.distribution_id = DH.distribution_id
415 AND DD.period_counter = (SELECT max (DD_SUB.period_counter)
416 FROM fa_deprn_detail_mrc_v DD_SUB
417 WHERE DD_SUB.book_type_code = book
418 AND DD_SUB.asset_id = DH.asset_id
419 AND DD_SUB.distribution_id = DH.distribution_id
420 AND DD_SUB.period_counter <= upc)
421 AND TH_RT.book_type_code = book
422 AND TH_RT.transaction_header_id = BOOKS.transaction_header_id_in
423 AND BOOKS.book_type_code = book
424 AND BOOKS.asset_id = DH.asset_id
425 AND NVL(BOOKS.period_counter_fully_retired, upc) >= tpc
426 AND BOOKS.date_effective <= NVL(TH.date_effective, ucd)
427 AND NVL(BOOKS.date_ineffective,SYSDATE+1) > NVL(TH.date_effective, ucd)
428 AND BOOKS.bonus_rule IS NOT NULL
429 AND TH.book_type_code (+) = dist_book
430 AND TH.transaction_header_id (+) = DH.transaction_header_id_out
431 AND TH.date_effective (+) BETWEEN tod AND ucd
432 AND DH.book_type_code = dist_book
433 AND DH.date_effective <= ucd
434 AND NVL(DH.date_ineffective, SYSDATE) > tod;
435 ELSE
436 --FND_FILE.PUT_LINE(FND_FILE.LOG,'2)INSERT INTO ');
437 INSERT INTO FA_RESERVE_LEDGER_GT
438 (asset_id
439 ,dh_ccid
440 ,deprn_reserve_acct
441 ,date_placed_in_service
442 ,method_code
443 ,life
444 ,rate
445 ,capacity
446 ,cost
447 ,deprn_amount
448 ,ytd_deprn
449 ,deprn_reserve
450 ,percent
451 ,transaction_type
452 ,period_counter
453 ,date_effective
454 ,reserve_acct)
455 SELECT
456 DH.asset_id asset_id
457 ,DH.code_combination_id dh_ccid
458 ,CB.deprn_reserve_acct rsv_account
459 ,BOOKS.date_placed_in_service start_date
460 ,BOOKS.deprn_method_code method
461 ,BOOKS.life_in_months life
462 ,BOOKS.adjusted_rate rate
463 ,BOOKS.production_capacity capacity
464 ,DD_BONUS.cost cost
465 ,DECODE (DD_BONUS.period_counter, upc, DD_BONUS.deprn_amount - DD_BONUS.bonus_deprn_amount, 0) deprn_amount
466 ,DECODE (SIGN (tpc - DD_BONUS.period_counter), 1, 0, DD_BONUS.ytd_deprn - DD_BONUS.bonus_ytd_deprn) ytd_deprn
467 ,DD_BONUS.deprn_reserve - DD_BONUS.bonus_deprn_reserve deprn_reserve
468 ,DECODE (TH.transaction_type_code, NULL,
469 DH.units_assigned / AH.units * 100) PERCENT
470 ,DECODE (TH.transaction_type_code, NULL,
471 DECODE (TH_RT.transaction_type_code,
472 'FULL RETIREMENT', 'F',
473 DECODE (BOOKS.depreciate_flag, 'NO', 'N')),
474 'TRANSFER', 'T',
475 'TRANSFER OUT', 'P',
476 'RECLASS', 'R') t_type
477 ,DD_BONUS.period_counter
478 ,NVL(TH.date_effective, ucd)
479 ,''
480 FROM
481 fa_deprn_detail DD_BONUS
482 ,fa_asset_history AH
483 ,fa_transaction_headers TH
484 ,fa_transaction_headers TH_RT
485 ,fa_books BOOKS
486 ,fa_distribution_history DH
487 ,fa_category_books CB
488 WHERE
489 CB.book_type_code = book
490 AND CB.category_id = AH.category_id
491 AND AH.asset_id = DH.ASSET_ID
492 AND AH.date_effective < NVL(TH.date_effective, ucd)
493 AND NVL(AH.date_ineffective,SYSDATE) >= NVL(TH.date_effective, ucd)
494 AND AH.asset_type = 'CAPITALIZED'
495 AND DD_BONUS.book_type_code = book
496 AND DD_BONUS.distribution_id = DH.distribution_id
497 AND DD_BONUS.period_counter = (SELECT MAX (DD_SUB.period_counter)
498 FROM fa_deprn_detail DD_SUB
499 WHERE DD_SUB.book_type_code = book
500 AND DD_SUB.asset_id = DH.asset_id
501 AND DD_SUB.distribution_id = DH.distribution_id
502 AND DD_SUB.period_counter <= upc)
503 AND TH_RT.book_type_code = book
504 AND TH_RT.transaction_header_id = BOOKS.transaction_header_id_in
505 AND BOOKS.book_type_code = book
506 AND BOOKS.asset_id = DH.asset_id
507 AND NVL(BOOKS.period_counter_fully_retired, upc) >= tpc
508 AND BOOKS.date_effective <= NVL(TH.date_effective, ucd)
509 AND NVL(BOOKS.date_ineffective,SYSDATE+1) > NVL(TH.date_effective, ucd)
510 AND TH.book_type_code (+) = dist_book
511 AND TH.transaction_header_id (+) = DH.transaction_header_id_out
512 AND TH.date_effective (+) BETWEEN tod AND ucd
513 AND DH.book_type_code = dist_book
514 AND DH.date_effective <= ucd
515 AND NVL(DH.date_ineffective, SYSDATE) > tod
516 UNION ALL
517 SELECT
518 DH.asset_id asset_id
519 ,DH.code_combination_id dh_ccid
520 ,CB.bonus_deprn_reserve_acct rsv_account
521 ,BOOKS.date_placed_in_service start_date
522 ,BOOKS.deprn_method_code method
523 ,BOOKS.life_in_months life
524 ,BOOKS.adjusted_rate rate
525 ,BOOKS.production_capacity capacity
526 ,0 cost
527 ,DECODE (DD.period_counter, upc, DD.bonus_deprn_amount, 0) deprn_amount
528 ,DECODE (SIGN (tpc - DD.period_counter), 1, 0, DD.bonus_ytd_deprn) ytd_deprn
529 ,DD.bonus_deprn_reserve deprn_reserve
530 ,0 percent
531 ,'b' t_type
532 ,DD.period_counter
533 ,NVL(TH.date_effective, ucd)
534 ,CB.bonus_deprn_expense_acct
535 FROM
536 fa_deprn_detail DD
537 ,fa_asset_history AH
538 ,fa_transaction_headers TH
539 ,fa_transaction_headers TH_RT
540 ,fa_books BOOKS
541 ,fa_distribution_history DH
542 ,fa_category_books CB
543 WHERE
544 CB.book_type_code = book
545 AND CB.category_id = AH.category_id
546 AND AH.asset_id = DH.asset_id
547 AND AH.date_effective < NVL(TH.date_effective, ucd)
548 AND NVL(AH.date_ineffective,SYSDATE) >= NVL(TH.date_effective, ucd)
549 AND AH.asset_type = 'CAPITALIZED'
550 AND DD.book_type_code = book
551 AND DD.distribution_id = DH.distribution_id
552 AND DD.period_counter = (SELECT MAX (DD_SUB.period_counter)
553 FROM fa_deprn_detail DD_SUB
554 WHERE DD_SUB.book_type_code = book
555 AND DD_SUB.asset_id = DH.asset_id
556 AND DD_SUB.distribution_id = DH.distribution_id
557 AND DD_SUB.period_counter <= upc)
558 AND TH_RT.book_type_code = book
559 AND TH_RT.transaction_header_id = BOOKS.transaction_header_id_in
560 AND BOOKS.book_type_code = book
561 AND BOOKS.asset_id = DH.asset_id
562 AND nvl(BOOKS.period_counter_fully_retired, upc) >= tpc
563 AND BOOKS.date_effective <= nvl(TH.date_effective, ucd)
564 AND nvl(BOOKS.date_ineffective,SYSDATE+1) > nvl(TH.date_effective, ucd)
565 AND BOOKS.bonus_rule IS NOT NULL
566 AND TH.book_type_code (+) = dist_book
567 AND TH.transaction_header_id (+) = DH.transaction_header_id_out
568 AND TH.date_effective (+) BETWEEN tod AND ucd
569 AND DH.book_type_code = dist_book
570 AND DH.date_effective <= ucd
571 AND NVL(DH.date_ineffective, SYSDATE) > tod;
572 END IF;
573
574
575 -- run only if CRL installed
576 ELSIF (NVL(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y' ) THEN
577
578 IF (h_reporting_flag = 'R') THEN
579 --FND_FILE.PUT_LINE(FND_FILE.LOG,'3)INSERT INTO ');
580 INSERT INTO FA_RESERVE_LEDGER_GT
581 (asset_id
582 ,dh_ccid
583 ,deprn_reserve_acct
584 ,date_placed_in_service
585 ,method_code
586 ,life
587 ,rate
588 ,capacity
589 ,cost
590 ,deprn_amount
591 ,ytd_deprn
592 ,deprn_reserve
593 ,percent
594 ,transaction_type
595 ,period_counter
596 ,date_effective)
597 SELECT
598 DH.asset_id asset_id
599 ,DH.code_combination_id dh_ccid
600 ,CB.deprn_reserve_acct rsv_account
601 ,BOOKS.date_placed_in_service start_date
602 ,BOOKS.deprn_method_code method
603 ,BOOKS.life_in_months life
604 ,BOOKS.adjusted_rate rate
605 ,BOOKS.production_capacity capacity
606 ,DD.cost cost
607 ,DECODE (DD.period_counter, upc, DD.deprn_amount, 0) deprn_amount
608 ,DECODE (SIGN (tpc - DD.period_counter), 1, 0, DD.ytd_deprn) ytd_deprn
609 ,DD.deprn_reserve deprn_reserve
610 ,DECODE (TH.transaction_type_code, NULL,DH.units_assigned / AH.units * 100) percent
611 ,DECODE (TH.transaction_type_code, NULL,DECODE (TH_RT.transaction_type_code,
612 'FULL RETIREMENT', 'F',DECODE (BOOKS.depreciate_flag, 'NO', 'N')),
613 'TRANSFER', 'T',
614 'TRANSFER OUT', 'P',
615 'RECLASS', 'R') t_type
616 ,DD.period_counter
617 ,NVL(TH.date_effective, ucd)
618 FROM
619 fa_deprn_detail_mrc_v DD
620 ,fa_asset_history AH
621 ,fa_transaction_headers TH
622 ,fa_transaction_headers TH_RT
623 ,fa_books_mrc_v BOOKS
624 ,fa_distribution_history DH
625 ,fa_category_books CB
626 WHERE BOOKS.group_asset_id IS NULL
627 AND CB.book_type_code = book
628 AND CB.category_id = AH.category_id
629 AND AH.asset_id = DH.asset_id
630 AND AH.date_effective < NVL(TH.date_effective, ucd)
631 AND NVL(AH.date_ineffective,SYSDATE)>= NVL(TH.date_effective, ucd)
632 AND AH.asset_type = 'CAPITALIZED'
633 AND DD.book_type_code = book
634 AND DD.distribution_id = DH.distribution_id
635 AND DD.period_counter = (SELECT MAX (DD_SUB.period_counter)
636 FROM fa_deprn_detail_mrc_v DD_SUB
637 WHERE DD_SUB.book_type_code = book
638 AND DD_SUB.asset_id = DH.asset_id
639 AND DD_SUB.distribution_id = DH.distribution_id
640 AND DD_SUB.period_counter <= upc)
641 AND TH_RT.book_type_code = book
642 AND TH_RT.transaction_header_id = BOOKS.transaction_header_id_in
643 AND BOOKS.book_type_code = book
644 AND BOOKS.asset_id = DH.asset_id
645 AND NVL(BOOKS.period_counter_fully_retired, upc) >= tpc
646 AND BOOKS.date_effective <= NVL(TH.date_effective, ucd)
647 AND NVL(BOOKS.date_ineffective,SYSDATE+1) > NVL(TH.date_effective, ucd)
648 AND TH.book_type_code (+) = dist_book
649 AND TH.transaction_header_id (+) = DH.transaction_header_id_out
650 AND TH.date_effective (+) BETWEEN tod AND ucd
651 AND DH.book_type_code = dist_book
652 AND DH.date_effective <= ucd
653 AND NVL(DH.date_ineffective, SYSDATE) > tod
654 AND BOOKS.group_asset_id IS NULL;
655 ELSE
656 --FND_FILE.PUT_LINE(FND_FILE.LOG,'4)INSERT INTO ');
657 INSERT INTO FA_RESERVE_LEDGER_GT
658 (asset_id
659 ,dh_ccid
660 ,deprn_reserve_acct
661 ,date_placed_in_service
662 ,method_code
663 ,life
664 ,rate
665 ,capacity
666 ,cost
667 ,deprn_amount
668 ,ytd_deprn
669 ,deprn_reserve
670 ,percent
671 ,transaction_type
672 ,period_counter
673 ,date_effective)
674 SELECT
675 DH.asset_id asset_id
676 ,DH.code_combination_id dh_ccid
677 ,CB.deprn_reserve_acct rsv_account
678 ,BOOKS.date_placed_in_service start_date
679 ,BOOKS.deprn_method_code method
680 ,BOOKS.life_in_months life
681 ,BOOKS.adjusted_rate rate
682 ,BOOKS.production_capacity capacity
683 ,DD.cost cost
684 ,DECODE (DD.period_counter, upc, DD.deprn_amount, 0) deprn_amount
685 ,DECODE (SIGN (tpc - DD.period_counter), 1, 0, DD.ytd_deprn) ytd_deprn
686 ,DD.deprn_reserve deprn_reserve
687 ,DECODE (TH.transaction_type_code, NULL,DH.units_assigned / AH.units * 100) percent
688 ,DECODE (TH.transaction_type_code, NULL,DECODE (TH_RT.transaction_type_code,'FULL RETIREMENT', 'F',
689 DECODE (BOOKS.depreciate_flag, 'NO', 'N')),'TRANSFER', 'T','TRANSFER OUT', 'P',
690 'RECLASS', 'R') t_type
691 ,DD.period_counter
692 ,NVL(TH.date_effective, ucd)
693 FROM
694 fa_deprn_detail DD
695 ,fa_asset_history AH
696 ,fa_transaction_headers TH
697 ,fa_transaction_headers TH_RT
698 ,fa_books BOOKS
699 ,fa_distribution_history DH
700 ,fa_category_books CB
701 WHERE BOOKS.group_asset_id IS NULL
702 AND CB.book_type_code = book
703 AND CB.category_id = AH.category_id
704 AND AH.asset_id = DH.asset_id
705 AND AH.date_effective < NVL(TH.date_effective, ucd)
706 AND NVL(AH.date_ineffective,SYSDATE)>= NVL(TH.date_effective, ucd)
707 AND AH.asset_type = 'CAPITALIZED'
708 AND DD.book_type_code = book
709 AND DD.distribution_id = DH.distribution_id
710 AND DD.period_counter =(SELECT MAX (DD_SUB.period_counter)
711 FROM fa_deprn_detail DD_SUB
712 WHERE DD_SUB.book_type_code = book
713 AND DD_SUB.asset_id = DH.asset_id
714 AND DD_SUB.distribution_id = DH.distribution_id
715 AND DD_SUB.period_counter <= upc)
716 AND TH_RT.book_type_code = book
717 AND TH_RT.transaction_header_id = BOOKS.transaction_header_id_in
718 AND BOOKS.book_type_code = book
719 AND BOOKS.asset_id = DH.asset_id
720 AND NVL(BOOKS.period_counter_fully_retired, upc) >= tpc
721 AND BOOKS.date_effective <= NVL(TH.date_effective, ucd)
722 AND NVL(BOOKS.date_ineffective,SYSDATE+1) > NVL(TH.date_effective, ucd)
723 AND TH.book_type_code (+) = dist_book
724 AND TH.transaction_header_id (+) = DH.transaction_header_id_out
725 AND TH.date_effective (+) BETWEEN tod AND ucd
726 AND DH.book_type_code = dist_book
727 AND DH.date_effective <= ucd
728 AND NVL(DH.date_ineffective, SYSDATE) > tod
729 AND BOOKS.group_asset_id IS NULL;
730 END IF;
731 IF (h_reporting_flag = 'R') THEN
732 --FND_FILE.PUT_LINE(FND_FILE.LOG,'5)INSERT INTO ');
733 INSERT INTO FA_RESERVE_LEDGER_GT
734 (asset_id
735 ,dh_ccid
736 ,deprn_reserve_acct
737 ,date_placed_in_service
738 ,method_code
739 ,life
740 ,rate
741 ,capacity
742 ,cost
743 ,deprn_amount
744 ,ytd_deprn
745 ,deprn_reserve
746 ,percent
747 ,transaction_type
748 ,period_counter
749 ,date_effective)
750 SELECT
751 GAR.group_asset_id asset_id
752 ,GAD.deprn_expense_acct_ccid ch_ccid
753 ,GAD.deprn_reserve_acct_ccid rsv_account
754 ,GAR.deprn_start_date start_date
755 ,GAR.deprn_method_code method
756 ,GAR.life_in_months life
757 ,GAR.adjusted_rate rate
758 ,GAR.production_capacity capacity
759 ,DD.adjusted_cost cost
760 ,DECODE (DD.period_counter, upc, DD.deprn_amount, 0) deprn_amount
761 ,DECODE (SIGN (tpc - DD.period_counter), 1, 0, DD.ytd_deprn) ytd_deprn
762 ,DD.deprn_reserve deprn_reserve
763 ,100 percent
764 ,'G' t_type
765 ,DD.period_counter
766 ,UCD
767 FROM
768 fa_deprn_summary_mrc_v DD
769 ,fa_group_asset_rules GAR
770 ,fa_group_asset_default GAD
771 ,fa_deprn_periods_mrc_v DP
772 WHERE
773 DD.book_type_code = book
774 AND DD.asset_id = GAR.group_asset_id
775 AND GAD.super_group_id IS NULL
776 AND GAR.book_type_code = DD.book_type_code
777 AND GAD.book_type_code = GAR.book_type_code
778 AND GAD.group_asset_id = GAR.group_asset_id
779 AND DD.period_counter = (SELECT MAX (DD_SUB.period_counter)
780 FROM fa_deprn_detail_mrc_v DD_SUB
781 WHERE DD_SUB.book_type_code = book
782 AND DD_SUB.asset_id = GAR.group_asset_id
783 AND DD_SUB.period_counter <= upc)
784 AND DD.period_counter = DP.period_counter
785 AND DD.book_type_code = DP.book_type_code
786 AND GAR.date_effective <= DP.calendar_period_close_date
787 AND NVL(GAR.date_ineffective, (DP.calendar_period_close_date + 1))> DP.calendar_period_close_date;
788 ELSE
789 --FND_FILE.PUT_LINE(FND_FILE.LOG,'6)INSERT INTO ');
790 INSERT INTO FA_RESERVE_LEDGER_GT
791 (asset_id
792 ,dh_ccid
793 ,deprn_reserve_acct
794 ,date_placed_in_service
795 ,method_code
796 ,life
797 ,rate
798 ,capacity
799 ,cost
800 ,deprn_amount
801 ,ytd_deprn
802 ,deprn_reserve
803 ,percent
804 ,transaction_type
805 ,period_counter
806 ,date_effective)
807 SELECT GAR.group_asset_id asset_id
808 ,GAD.deprn_expense_acct_ccid ch_ccid
809 ,GAD.deprn_reserve_acct_ccid rsv_account
810 ,GAR.deprn_start_date start_date
811 ,GAR.deprn_method_code method
812 ,GAR.life_in_months life
813 ,GAR.adjusted_rate rate
814 ,GAR.production_capacity capacity
815 ,DD.adjusted_cost cost
816 ,DECODE (DD.period_counter, upc, DD.deprn_amount, 0) deprn_amount
817 ,DECODE (SIGN (tpc - DD.period_counter), 1, 0, DD.ytd_deprn) ytd_deprn
818 ,DD.deprn_reserve deprn_reserve
819 ,100 percent
820 ,'G' t_type
821 ,DD.period_counter
822 ,UCD
823 FROM
824 fa_deprn_summary DD
825 ,fa_group_asset_rules GAR
826 ,fa_group_asset_default GAD
827 ,fa_deprn_periods DP
828 WHERE
829 DD.book_type_code = book
830 AND DD.asset_id = GAR.group_asset_id
831 AND GAD.super_group_id IS NULL -- MPOWELL
832 AND GAR.book_type_code = DD.book_type_code
833 AND GAD.book_type_code = GAR.book_type_code
834 AND GAD.group_asset_id = GAR.group_asset_id
835 AND DD.period_counter = (SELECT MAX (DD_SUB.period_counter)
836 FROM fa_deprn_detail DD_SUB
837 WHERE DD_SUB.book_type_code = book
838 AND DD_SUB.asset_id = GAR.group_asset_id
839 AND DD_SUB.period_counter <= upc
840 )
841 AND DD.period_counter = DP.period_counter
842 AND DD.book_type_code = DP.book_type_code
843 AND GAR.date_effective <= DP.calendar_period_close_date -- mwoodwar
844 AND NVL(GAR.date_ineffective, (DP.calendar_period_close_date + 1))
845 > DP.calendar_period_close_date;
846 END IF;
847
848
849
850 IF (h_reporting_flag = 'R') THEN
851 --FND_FILE.PUT_LINE(FND_FILE.LOG,'7)INSERT INTO ');
852 INSERT INTO FA_RESERVE_LEDGER_GT
853 (asset_id
854 ,dh_ccid
855 ,deprn_reserve_acct
856 ,date_placed_in_service
857 ,method_code
858 ,life
859 ,rate
860 ,capacity
861 ,cost
862 ,deprn_amount
863 ,ytd_deprn
864 ,deprn_reserve
865 ,percent
866 ,transaction_type
867 ,period_counter
868 ,date_effective)
869 SELECT
870 GAR.group_asset_id asset_id
871 ,GAD.deprn_expense_acct_ccid dh_ccid
872 ,GAD.deprn_reserve_acct_ccid rsv_account
873 ,GAR.deprn_start_date start_date
874 ,SGR.deprn_method_code method
875 ,GAR.life_in_months life
876 ,SGR.adjusted_rate rate
877 ,GAR.production_capacity capacity
878 ,DD.adjusted_cost cost
879 ,DECODE (DD.period_counter, upc, DD.deprn_amount, 0) deprn_amount
880 ,DECODE (SIGN (tpc - DD.period_counter), 1, 0, DD.ytd_deprn) ytd_deprn
881 ,DD.deprn_reserve deprn_reserve
882 ,100 percent
883 ,'G' t_type
884 ,DD.period_counter
885 ,UCD
886 FROM fa_deprn_summary_mrc_v DD
887 ,fa_group_asset_rules GAR
888 ,fa_group_asset_default GAD
889 ,fa_super_group_rules SGR
890 ,fa_deprn_periods_mrc_v DP
891 WHERE DD.book_type_code = book
892 AND DD.asset_id = GAR.group_asset_id
893 AND GAR.book_type_code = DD.book_type_code
894 AND GAD.super_group_id = SGR.super_group_id
895 AND GAD.book_type_code = SGR.book_type_code
896 AND GAD.book_type_code = GAR.book_type_code
897 AND GAD.group_asset_id = GAR.group_asset_id
898 AND DD.period_counter = (SELECT MAX (DD_SUB.period_counter)
899 FROM fa_deprn_detail_mrc_v DD_SUB
900 WHERE DD_SUB.book_type_code = book
901 AND DD_SUB.asset_id = GAR.group_asset_id
902 AND DD_SUB.period_counter <= upc)
903 AND DD.period_counter = DP.period_counter
904 AND DD.book_type_code = DP.book_type_code
905 AND GAR.date_effective <= DP.calendar_period_close_date
906 AND nvl(GAR.date_ineffective, (DP.calendar_period_close_date + 1))> DP.calendar_period_close_date
907 AND SGR.date_effective <= DP.calendar_period_close_date
908 AND nvl(SGR.date_ineffective, (DP.calendar_period_close_date + 1))> DP.calendar_period_close_date;
909 ELSE
910 --FND_FILE.PUT_LINE(FND_FILE.LOG,'8)INSERT INTO ');
911 INSERT INTO FA_RESERVE_LEDGER_GT
912 (asset_id
913 ,dh_ccid
914 ,deprn_reserve_acct
915 ,date_placed_in_service
916 ,method_code
917 ,life
918 ,rate
919 ,capacity
920 ,cost
921 ,deprn_amount
922 ,ytd_deprn
923 ,deprn_reserve
924 ,percent
925 ,transaction_type
926 ,period_counter
927 ,date_effective)
928 SELECT
929 GAR.group_asset_id asset_id
930 ,GAD.deprn_expense_acct_ccid dh_ccid
931 ,GAD.deprn_reserve_acct_ccid rsv_account
932 ,GAR.deprn_start_date start_date
933 ,SGR.deprn_method_code method
934 ,GAR.life_in_months life
935 ,SGR.adjusted_rate rate
936 ,GAR.production_capacity capacity
937 ,DD.adjusted_cost cost
938 ,DECODE (DD.period_counter, upc, DD.deprn_amount, 0) deprn_amount
939 ,DECODE (SIGN (tpc - DD.period_counter), 1, 0, DD.ytd_deprn) ytd_deprn
940 ,DD.deprn_reserve deprn_reserve
941 ,100 percent
942 ,'G' t_type
943 ,DD.period_counter
944 ,UCD
945 FROM fa_deprn_summary DD
946 ,fa_group_asset_rules GAR
947 ,fa_group_asset_default GAD
948 ,fa_super_group_rules SGR
949 ,fa_deprn_periods DP
950 WHERE DD.book_type_code = book
951 AND DD.asset_id = GAR.group_asset_id
952 AND GAR.book_type_code = DD.book_type_code
953 AND GAD.super_group_id = SGR.super_group_id -- MPOWELL
954 AND GAD.book_type_code = SGR.book_type_code -- MPOWELL
955 AND GAD.book_type_code = GAR.book_type_code
956 AND GAD.group_asset_id = GAR.group_asset_id
957 AND DD.period_counter = (SELECT max (DD_SUB.period_counter)
958 FROM fa_deprn_detail DD_SUB
959 WHERE DD_SUB.book_type_code = book
960 AND DD_SUB.asset_id = GAR.group_asset_id
961 AND DD_SUB.period_counter <= upc)
962 AND DD.period_counter = DP.period_counter
963 AND DD.book_type_code = DP.book_type_code
964 AND GAR.date_effective <= DP.calendar_period_close_date
965 AND nvl(GAR.date_ineffective, (DP.calendar_period_close_date + 1))> DP.calendar_period_close_date
966 AND SGR.date_effective <= DP.calendar_period_close_date
967 AND nvl(SGR.date_ineffective, (DP.calendar_period_close_date + 1))> DP.calendar_period_close_date;
968 END IF;
969
970 END IF;
971 COMMIT;
972 EXCEPTION
973 WHEN OTHERS THEN
974 retcode := SQLCODE;
975 errbuf := SQLERRM;
976 END FA_RSVLDG;
977
978 FUNCTION fadolif(life NUMBER,adj_rate NUMBER,bonus_rate NUMBER,prod NUMBER)
979 RETURN CHAR IS
980 retval CHAR(7);
981 num_chars NUMBER;
982 temp_retval NUMBER;
983 BEGIN
984 IF life IS NOT NULL
985 THEN
986 temp_retval := fnd_number.canonical_to_number((LPAD(SUBSTR(TO_CHAR(TRUNC(life/12, 0), '999'), 2, 3),3,' ') || '.' ||
987 SUBSTR(TO_CHAR(MOD(life, 12), '00'), 2, 2)) );
988 retval := TO_CHAR(temp_retval,'999D99');
989 ELSIF adj_rate IS NOT NULL
990 THEN
991 retval := SUBSTR(TO_CHAR(ROUND((adj_rate + NVL(bonus_rate, 0))*100, 2), '990.99'),2,6) || '%';
992 ELSIF prod IS NOT NULL
993 THEN
994 retval := '';
995 ELSE
996 retval := ' ';
997 END IF;
998
999 RETURN(retval);
1000
1001 END;
1002
1003 END FA_RES_LDG_PKG ;