[Home] [Help]
PACKAGE BODY: APPS.FA_FASRSVES_XMLP_PKG
Source
1 PACKAGE BODY FA_FASRSVES_XMLP_PKG AS
2 /* $Header: FASRSVESB.pls 120.0.12010000.1 2008/07/28 13:17:32 appldev ship $ */
3
4 function report_nameformula(Company_Name in varchar2) return varchar2 is
5 begin
6 P_CONC_REQUEST_ID := fnd_global.CONC_REQUEST_ID;
7
8 DECLARE
9 l_report_name VARCHAR2(80);
10 l_conc_program_id NUMBER;
11 BEGIN
12 RP_Company_Name := Company_Name;
13
14 SELECT cr.concurrent_program_id
15 INTO l_conc_program_id
16 FROM FND_CONCURRENT_REQUESTS cr
17 WHERE cr.program_application_id = 140
18 AND cr.request_id = P_CONC_REQUEST_ID;
19
20 SELECT cp.user_concurrent_program_name
21 INTO l_report_name
22 FROM FND_CONCURRENT_PROGRAMS_VL cp
23 WHERE
24 cp.concurrent_program_id= l_conc_program_id
25 and cp.application_id = 140;
26
27 l_report_name := substr(l_report_name,1,instr(l_report_name,' (XML)'));
28 RP_Report_Name := l_report_name;
29 RETURN(l_report_name);
30
31 EXCEPTION
32 WHEN OTHERS THEN
33 IF (P_REPORT_TYPE = 'REVAL RESERVE') THEN
34 RP_Report_Name := ':Revaluation Reserve Summary Report:';
35 ELSE IF (P_REPORT_TYPE = 'RESERVE') THEN
36 RP_Report_Name := ':Reserve Summary Report:';
37 ELSE RP_Report_Name := 'REPORT ERROR';
38 END IF; END IF;
39
40 RETURN(RP_Report_Name);
41 END;
42 RETURN NULL; end;
43
44 function BeforeReport return boolean is
45 begin
46
47 /*SRW.USER_EXIT('FND SRWINIT');*/null;
48
49
50 IF upper(p_mrcsobtype) = 'R'
51 THEN
52 fnd_client_info.set_currency_context(p_ca_set_of_books_id_1);
53 END IF;
54
55 return (TRUE);
56 end;
57
58 function AfterReport return boolean is
59 begin
60
61 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
62
63 BEGIN
64 ROLLBACK;
65 EXCEPTION
66 WHEN OTHERS THEN NULL;
67 END; return (TRUE);
68 end;
69
70 function Period1_PCFormula return Number is
71 begin
72
73 DECLARE
74 l_period_POD DATE;
75 l_period_PCD DATE;
76 l_period_PC NUMBER(15);
77 l_period_FY NUMBER(15);
78 BEGIN
79
80
81
82
83 IF upper(p_mrcsobtype) = 'R'
84 THEN
85 SELECT period_counter,
86 period_open_date,
87 nvl(period_close_date, sysdate),
88 fiscal_year
89 INTO l_period_PC,
90 l_period_POD,
91 l_period_PCD,
92 l_period_FY
93 FROM FA_DEPRN_PERIODS_MRC_V
94 WHERE book_type_code = P_BOOK
95 AND period_name = P_PERIOD1;
96 ELSE
97 SELECT period_counter,
98 period_open_date,
99 nvl(period_close_date, sysdate),
100 fiscal_year
101 INTO l_period_PC,
102 l_period_POD,
103 l_period_PCD,
104 l_period_FY
105 FROM FA_DEPRN_PERIODS
106 WHERE book_type_code = P_BOOK
107 AND period_name = P_PERIOD1;
108 END IF;
109
110
111 Period1_POD := l_period_POD;
112 Period1_PCD := l_period_PCD;
113 Period1_FY := l_period_FY;
114 return(l_period_PC);
115 END;
116 RETURN NULL; end;
117
118 function Period2_PCFormula return Number is
119 begin
120
121 DECLARE
122 l_period_POD DATE;
123 l_period_PCD DATE;
124 l_period_PC NUMBER(15);
125 l_period_FY NUMBER(15);
126 BEGIN
127
128
129
130
131 IF upper(p_mrcsobtype) = 'R'
132 THEN
133 SELECT period_counter,
134 period_open_date,
135 nvl(period_close_date, sysdate),
136 fiscal_year
137 INTO l_period_PC,
138 l_period_POD,
139 l_period_PCD,
140 l_period_FY
141 FROM FA_DEPRN_PERIODS_MRC_V
142 WHERE book_type_code = P_BOOK
143 AND period_name = P_PERIOD2;
144 ELSE
145 SELECT period_counter,
146 period_open_date,
147 nvl(period_close_date, sysdate),
148 fiscal_year
149 INTO l_period_PC,
150 l_period_POD,
151 l_period_PCD,
152 l_period_FY
153 FROM FA_DEPRN_PERIODS
154 WHERE book_type_code = P_BOOK
155 AND period_name = P_PERIOD2;
156 END IF;
157
158
159 Period2_POD := l_period_POD;
160 Period2_PCD := l_period_PCD;
161 Period2_FY := l_period_FY;
162 return(l_period_PC);
163 END;
164 RETURN NULL; end;
165
166 function DO_INSERTFormula return Number is
167 begin
168
169 BEGIN
170 IF (P_REPORT_TYPE = 'RESERVE' OR P_REPORT_TYPE = 'REVAL RESERVE') THEN
171 Insert_Info (P_BOOK, P_PERIOD1,
172 P_PERIOD2, P_REPORT_TYPE, P_ADJ_MODE);
173 RETURN(1);
174 ELSE RETURN(0);
175 END IF;
176 END;
177 RETURN NULL; end;
178
179 function out_of_balanceformula(BEGIN_P in number, ADDITION in number, DEPRECIATION in number, RECLASS in number, RETIREMENT in number, ADJUST in number, TRANSFER in number, END_P in number) return varchar2 is
180 begin
181
182 DECLARE
183
184 MOCK_TOTAL NUMBER;
185
186 BEGIN
187
188 MOCK_TOTAL := NVL(BEGIN_P,0) + NVL(ADDITION,0) + NVL(DEPRECIATION,0)
189 + NVL(RECLASS,0) - NVL(RETIREMENT,0) + NVL(ADJUST,0)
190 + NVL(TRANSFER,0);
191
192 IF (MOCK_TOTAL = NVL(END_P,0))
193 THEN RETURN (' ');
194 ELSE RETURN('*');
195 END IF;
196 END;
197 RETURN NULL; end;
198
199 function acct_out_of_balanceformula(ACCT_BEGIN in number, ACCT_ADD in number, ACCT_DEPRN in number, ACCT_RECLASS in number, ACCT_RETIRE in number, ACCT_ADJUST in number, ACCT_TRANS in number, ACCT_END in number) return varchar2 is
200 begin
201
202 DECLARE
203
204 MOCK_TOTAL NUMBER;
205
206 BEGIN
207
208 MOCK_TOTAL := NVL(ACCT_BEGIN,0) + NVL(ACCT_ADD,0) + NVL(ACCT_DEPRN,0)
209 + NVL(ACCT_RECLASS,0) - NVL(ACCT_RETIRE,0) + NVL(ACCT_ADJUST,0)
210 + NVL(ACCT_TRANS,0);
211
212 IF (MOCK_TOTAL = NVL(ACCT_END,0))
213 THEN RETURN (' ');
214 ELSE RETURN('*');
215 END IF;
216 END;
217 RETURN NULL; end;
218
219 function bal_out_of_balanceformula(BAL_BEGIN in number, BAL_ADD in number, BAL_DEPRN in number, BAL_RECLASS in number, BAL_RETIRE in number, BAL_ADJUST in number, BAL_TRANS in number, BAL_END in number) return varchar2 is
220 begin
221
222 DECLARE
223
224 MOCK_TOTAL NUMBER;
225
226 BEGIN
227
228 MOCK_TOTAL := NVL(BAL_BEGIN,0) + NVL(BAL_ADD,0) + NVL(BAL_DEPRN,0)
229 + NVL(BAL_RECLASS,0) - NVL(BAL_RETIRE,0) + NVL(BAL_ADJUST,0)
230 + NVL(BAL_TRANS,0);
231
232 IF (MOCK_TOTAL = NVL(BAL_END,0))
233 THEN RETURN (' ');
234 ELSE RETURN('*');
235 END IF;
236 END;
237 RETURN NULL; end;
238
239 function rp_out_of_balanceformula(RP_BEGIN in number, RP_ADD in number, RP_DEPRN in number, RP_RECLASS in number, RP_RETIRE in number, RP_ADJUST in number, RP_TRANS in number, RP_END in number) return varchar2 is
240 begin
241
242 DECLARE
243
244 MOCK_TOTAL NUMBER;
245
246 BEGIN
247
248 MOCK_TOTAL := NVL(RP_BEGIN,0) + NVL(RP_ADD,0) + NVL(RP_DEPRN,0)
249 + NVL(RP_RECLASS,0) - NVL(RP_RETIRE,0) + NVL(RP_ADJUST,0)
250 + NVL(RP_TRANS,0);
251
252 IF (MOCK_TOTAL = NVL(RP_END,0))
253 THEN RETURN (' ');
254 ELSE RETURN('*');
255 END IF;
256 END;
257 RETURN NULL; end;
258
259 function adjustformula(TAX in number, REVALUATION in number) return number is
260 begin
261
262 BEGIN
263
264 RETURN(nvl(TAX,0) + nvl(REVALUATION,0));
265
266 END;
267 RETURN NULL; end;
268
269 function reval_tax_flagformula(REVALUATION in number, TAX in number) return varchar2 is
270 begin
271
272 BEGIN
273
274 IF ((REVALUATION <> 0) AND (TAX = 0))
275 THEN IF (P_REPORT_TYPE = 'RESERVE')
276 THEN RETURN('R');
277 ELSE RETURN(' ');
278 END IF;
279 ELSE IF ((REVALUATION <> 0) AND (TAX <> 0))
280 THEN RETURN('B');
281 ELSE IF ((REVALUATION = 0) AND (TAX <> 0))
282 THEN RETURN('T');
283 ELSE RETURN(' ');
284 END IF; END IF; END IF;
285 END;
286 RETURN NULL; end;
287
288 function AfterPForm return boolean is
289 begin
290 p_ca_set_of_books_id_1:=nvl(p_ca_set_of_books_id,-1);
291 IF p_ca_set_of_books_id_1 <> -1999
292 THEN
293
294 BEGIN
295 select mrc_sob_type_code, currency_code
296 into p_mrcsobtype, lp_currency_code
297 from gl_sets_of_books
298 where set_of_books_id = p_ca_set_of_books_id_1;
299 EXCEPTION
300 WHEN OTHERS THEN
301 p_mrcsobtype := 'P';
302 END;
303 ELSE
304 p_mrcsobtype := 'P';
305 END IF;
306
307
308 IF upper(p_mrcsobtype) = 'R'
309 THEN
310 lp_fa_book_controls := 'FA_BOOK_CONTROLS_MRC_V';
311 ELSE
312 lp_fa_book_controls := 'FA_BOOK_CONTROLS';
313 END IF;
314
315
316 return (TRUE);
317 end;
318
319 function G_SETUPGroupFilter return boolean is
320 begin
321
322 return (TRUE);
323 end;
324
325 --Functions to refer Oracle report placeholders--
326
327 Function ACCT_BAL_APROMPT_p return varchar2 is
328 Begin
329 return ACCT_BAL_APROMPT;
330 END;
331 Function ACCT_CC_APROMPT_p return varchar2 is
332 Begin
333 return ACCT_CC_APROMPT;
334 END;
335 Function CAT_MAJ_RPROMPT_p return varchar2 is
336 Begin
337 return CAT_MAJ_RPROMPT;
338 END;
339 Function Period1_POD_p return date is
340 Begin
341 return Period1_POD;
342 END;
343 Function Period1_PCD_p return date is
344 Begin
345 return Period1_PCD;
346 END;
347 Function Period1_FY_p return number is
348 Begin
349 return Period1_FY;
350 END;
351 Function Period2_POD_p return date is
352 Begin
353 return Period2_POD;
354 END;
355 Function Period2_PCD_p return date is
356 Begin
357 return Period2_PCD;
358 END;
359 Function Period2_FY_p return number is
360 Begin
361 return Period2_FY;
362 END;
363 Function RP_COMPANY_NAME_p return varchar2 is
364 Begin
365 return RP_COMPANY_NAME;
366 END;
367 Function RP_REPORT_NAME_p return varchar2 is
368 Begin
369 return RP_REPORT_NAME;
370 END;
371 Function RP_BAL_LPROMPT_p(acct_bal_lprompt varchar2) return varchar2 is
372 Begin
373 RP_BAL_LPROMPT := ACCT_BAL_LPROMPT;
374 return RP_BAL_LPROMPT;
375 END;
376 Function RP_CTR_APROMPT_p return varchar2 is
377 Begin
378 return RP_CTR_APROMPT;
379 END;
380 --modified
381 Function PERIOD_FROM_p(p_period1 varchar2) return varchar2 is
382 Begin
383 period_from := p_period1;
384 return PERIOD_FROM;
385 END;
386 Function PERIOD_TO_p(p_period2 varchar2) return varchar2 is
387 Begin
388 period_to := p_period2;
389 return PERIOD_TO;
390 END;
391
392
393 --added during the pls compilation--
394
395
396 procedure Get_Adjustments
397 (Book in varchar2,
398 Distribution_Source_Book in varchar2,
399 Period1_PC in number,
400 Period2_PC in number,
401 Report_Type in varchar2,
402 Balance_Type in varchar2)
403 is
404
405 --Added during DT Fix
406 PRAGMA AUTONOMOUS_TRANSACTION;
407 --End of DT Fix
408 h_set_of_books_id number;
409 h_reporting_flag varchar2(1);
410 begin
411
412 -- get mrc related info
413 begin
414 -- h_set_of_books_id := to_number(substrb(userenv('CLIENT_INFO'),45,10));
415 select to_number(substrb(userenv('CLIENT_INFO'),45,10))
416 into h_set_of_books_id from dual;
417
418 if (h_set_of_books_id = -1) then
419 h_set_of_books_id := null;
420 end if;
421
422 exception
423 when others then
424 h_set_of_books_id := null;
425 end;
426
427
428 if (h_set_of_books_id is not null) then
429 if not fa_cache_pkg.fazcsob
430 (X_set_of_books_id => h_set_of_books_id,
431 X_mrc_sob_type_code => h_reporting_flag) then
432 raise FND_API.G_EXC_UNEXPECTED_ERROR;
433 end if;
434 else
435 select set_of_books_id
436 into h_set_of_books_id
437 from fa_book_controls
438 where book_type_code = book;
439
440 h_reporting_flag := 'P';
441 end if;
442
443 -- Fix for Bug #1892406. Run only if CRL not installed.
444 If (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'N' ) then
445
446
447 if (h_reporting_flag = 'R') then
448
449 INSERT INTO FA_BALANCES_REPORT_GT
450 (Asset_ID,
451 Distribution_CCID,
452 Adjustment_CCID,
453 Category_Books_Account,
454 Source_Type_Code,
455 Amount)
456 SELECT
457 DH.Asset_ID,
458 DH.Code_Combination_ID,
459 lines.code_combination_id, --AJ.Code_Combination_ID,
460 null,
461 AJ.Source_Type_Code,
462 SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
463 AJ.Adjustment_Amount)
464 FROM
465 FA_LOOKUPS RT,
466 FA_DISTRIBUTION_HISTORY DH,
467 FA_TRANSACTION_HEADERS TH,
468 FA_ASSET_HISTORY AH,
469 FA_ADJUSTMENTS_MRC_V AJ
470
471 /* SLA Changes */
472 ,xla_ae_headers headers
473 ,xla_ae_lines lines
474 ,xla_distribution_links links
475 WHERE
476 RT.Lookup_Type = 'REPORT TYPE' AND
477 RT.Lookup_Code = Report_Type
478 AND
479 DH.Book_Type_Code = Distribution_Source_Book
480 AND
481 AJ.Asset_ID = DH.Asset_ID AND
482 AJ.Book_Type_Code = Book AND
483 AJ.Distribution_ID = DH.Distribution_ID AND
484 AJ.Adjustment_Type in
485 (Report_Type, DECODE(Report_Type,
486 'REVAL RESERVE', 'REVAL AMORT')) AND
487 AJ.Period_Counter_Created BETWEEN
488 Period1_PC AND Period2_PC
489 AND
490 TH.Transaction_Header_ID = AJ.Transaction_Header_ID
491 AND
492 AH.Asset_ID = DH.Asset_ID AND
493 ((AH.Asset_Type <> 'EXPENSED' AND
494 Report_Type IN ('COST', 'CIP COST')) OR
495 (AH.Asset_Type in ('CAPITALIZED','CIP') AND
496 Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
497 TH.Transaction_Header_ID BETWEEN
498 AH.Transaction_Header_ID_In AND
499 NVL (AH.Transaction_Header_ID_Out - 1,
500 TH.Transaction_Header_ID)
501 AND
502 (DECODE (RT.Lookup_Code, AJ.Adjustment_Type, 1, 0) *
503 AJ.Adjustment_Amount) <> 0
504
505 /* SLA Changes */
506 and links.Source_distribution_id_num_1 = aj.transaction_header_id
507 and links.Source_distribution_id_num_2 = aj.adjustment_line_id
508 and links.application_id = 140
509 and links.source_distribution_type = 'TRX'
510 and headers.application_id = 140
511 and headers.ae_header_id = links.ae_header_id
512 and headers.ledger_id = h_set_of_books_id
513 and lines.ae_header_id = links.ae_header_id
514 and lines.ae_line_num = links.ae_line_num
515 and lines.application_id = 140
516 GROUP BY
517 DH.Asset_ID,
518 DH.Code_Combination_ID,
519 lines.code_combination_id, --AJ.Code_Combination_ID,
520 AJ.Source_Type_Code;
521
522 else
523
524 INSERT INTO FA_BALANCES_REPORT_GT
525 (Asset_ID,
526 Distribution_CCID,
527 Adjustment_CCID,
528 Category_Books_Account,
529 Source_Type_Code,
530 Amount)
531 SELECT
532 DH.Asset_ID,
533 DH.Code_Combination_ID,
534 lines.code_combination_id, --AJ.Code_Combination_ID,
535 null,
536 AJ.Source_Type_Code,
537 SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
538 AJ.Adjustment_Amount)
539 FROM
540 FA_LOOKUPS RT,
541 FA_DISTRIBUTION_HISTORY DH,
542 FA_TRANSACTION_HEADERS TH,
543 FA_ASSET_HISTORY AH,
544 FA_ADJUSTMENTS AJ
545
546 /* SLA Changes */
547 ,xla_ae_headers headers
548 ,xla_ae_lines lines
549 ,xla_distribution_links links
550
551 WHERE
552 RT.Lookup_Type = 'REPORT TYPE' AND
553 RT.Lookup_Code = Report_Type
554 AND
555 DH.Book_Type_Code = Distribution_Source_Book
556 AND
557 AJ.Asset_ID = DH.Asset_ID AND
558 AJ.Book_Type_Code = Book AND
559 AJ.Distribution_ID = DH.Distribution_ID AND
560 AJ.Adjustment_Type in
561 (Report_Type, DECODE(Report_Type,
562 'REVAL RESERVE', 'REVAL AMORT')) AND
563 AJ.Period_Counter_Created BETWEEN
564 Period1_PC AND Period2_PC
565 AND
566 TH.Transaction_Header_ID = AJ.Transaction_Header_ID
567 AND
568 AH.Asset_ID = DH.Asset_ID AND
569 ((AH.Asset_Type <> 'EXPENSED' AND
570 Report_Type IN ('COST', 'CIP COST')) OR
571 (AH.Asset_Type in ('CAPITALIZED','CIP') AND
572 Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
573 TH.Transaction_Header_ID BETWEEN
574 AH.Transaction_Header_ID_In AND
575 NVL (AH.Transaction_Header_ID_Out - 1,
576 TH.Transaction_Header_ID)
577 AND
578 (DECODE (RT.Lookup_Code, AJ.Adjustment_Type, 1, 0) *
579 AJ.Adjustment_Amount) <> 0
580
581 /* SLA Changes */
582 and links.Source_distribution_id_num_1 = aj.transaction_header_id
583 and links.Source_distribution_id_num_2 = aj.adjustment_line_id
584 and links.application_id = 140
585 and links.source_distribution_type = 'TRX'
586 and headers.application_id = 140
587 and headers.ae_header_id = links.ae_header_id
588 and headers.ledger_id = h_set_of_books_id
589 and lines.ae_header_id = links.ae_header_id
590 and lines.ae_line_num = links.ae_line_num
591 and lines.application_id = 140
592 GROUP BY
593 DH.Asset_ID,
594 DH.Code_Combination_ID,
595 lines.code_combination_id, --AJ.Code_Combination_ID,
596 AJ.Source_Type_Code;
597 end if;
598
599 -- Fix for Bug #1892406. Run only if CRL installed.
600 elsif (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y' ) then
601
602 if (h_reporting_flag = 'R') then
603
604 INSERT INTO FA_BALANCES_REPORT_GT
605 (Asset_ID,
606 Distribution_CCID,
607 Adjustment_CCID,
608 Category_Books_Account,
609 Source_Type_Code,
610 Amount)
611 SELECT
612 DH.Asset_ID,
613 DH.Code_Combination_ID,
614 lines.code_combination_id, --AJ.Code_Combination_ID,
615 null,
616 AJ.Source_Type_Code,
617 SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
618 AJ.Adjustment_Amount)
619 FROM
620 FA_LOOKUPS RT,
621 FA_DISTRIBUTION_HISTORY DH,
622 FA_TRANSACTION_HEADERS TH,
623 FA_ASSET_HISTORY AH,
624 FA_ADJUSTMENTS_MRC_V AJ
625
626 /* SLA Changes */
627 ,xla_ae_headers headers
628 ,xla_ae_lines lines
629 ,xla_distribution_links links
630
631 WHERE
632 RT.Lookup_Type = 'REPORT TYPE' AND
633 RT.Lookup_Code = Report_Type
634 AND
635 DH.Book_Type_Code = Distribution_Source_Book
636 AND
637 AJ.Asset_ID = DH.Asset_ID AND
638 AJ.Book_Type_Code = Book AND
639 AJ.Distribution_ID = DH.Distribution_ID AND
640 AJ.Adjustment_Type in
641 (Report_Type, DECODE(Report_Type,
642 'REVAL RESERVE', 'REVAL AMORT')) AND
643 AJ.Period_Counter_Created BETWEEN
644 Period1_PC AND Period2_PC
645 AND
646 TH.Transaction_Header_ID = AJ.Transaction_Header_ID
647 AND
648 AH.Asset_ID = DH.Asset_ID AND
649 ((AH.Asset_Type <> 'EXPENSED' AND
650 Report_Type IN ('COST', 'CIP COST')) OR
651 (AH.Asset_Type in ('CAPITALIZED','CIP') AND
652 Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
653 TH.Transaction_Header_ID BETWEEN
654 AH.Transaction_Header_ID_In AND
655 NVL (AH.Transaction_Header_ID_Out - 1,
656 TH.Transaction_Header_ID)
657 AND
658 (DECODE (RT.Lookup_Code, AJ.Adjustment_Type, 1, 0) *
659 AJ.Adjustment_Amount) <> 0
660 -- start of cua
661 and not exists ( select 'x' from fa_books_mrc_v bks
662 where bks.book_type_code = Book
663 and bks.asset_id = aj.asset_id
664 and bks.group_asset_id is not null
665 and bks.date_ineffective is not null )
666 -- end of cua
667 /* SLA Changes */
668 and links.Source_distribution_id_num_1 = aj.transaction_header_id
669 and links.Source_distribution_id_num_2 = aj.adjustment_line_id
670 and links.application_id = 140
671 and links.source_distribution_type = 'TRX'
672 and headers.application_id = 140
673 and headers.ae_header_id = links.ae_header_id
674 and headers.ledger_id = h_set_of_books_id
675 and lines.ae_header_id = links.ae_header_id
676 and lines.ae_line_num = links.ae_line_num
677 and lines.application_id = 140 GROUP BY
678 DH.Asset_ID,
679 DH.Code_Combination_ID,
680 lines.code_combination_id, --AJ.Code_Combination_ID,
681 AJ.Source_Type_Code;
682
683 else
684
685 INSERT INTO FA_BALANCES_REPORT_GT
686 (Asset_ID,
687 Distribution_CCID,
688 Adjustment_CCID,
689 Category_Books_Account,
690 Source_Type_Code,
691 Amount)
692 SELECT
693 DH.Asset_ID,
694 DH.Code_Combination_ID,
695 lines.code_combination_id, --AJ.Code_Combination_ID,
696 null,
697 AJ.Source_Type_Code,
698 SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
699 AJ.Adjustment_Amount)
700 FROM
701 FA_LOOKUPS RT,
702 FA_DISTRIBUTION_HISTORY DH,
703 FA_TRANSACTION_HEADERS TH,
704 FA_ASSET_HISTORY AH,
705 FA_ADJUSTMENTS AJ
706
707 /* SLA Changes */
708 ,xla_ae_headers headers
709 ,xla_ae_lines lines
710 ,xla_distribution_links links
711
712 WHERE
713 RT.Lookup_Type = 'REPORT TYPE' AND
714 RT.Lookup_Code = Report_Type
715 AND
716 DH.Book_Type_Code = Distribution_Source_Book
717 AND
718 AJ.Asset_ID = DH.Asset_ID AND
719 AJ.Book_Type_Code = Book AND
720 AJ.Distribution_ID = DH.Distribution_ID AND
721 AJ.Adjustment_Type in
722 (Report_Type, DECODE(Report_Type,
723 'REVAL RESERVE', 'REVAL AMORT')) AND
724 AJ.Period_Counter_Created BETWEEN
725 Period1_PC AND Period2_PC
726 AND
727 TH.Transaction_Header_ID = AJ.Transaction_Header_ID
728 AND
729 AH.Asset_ID = DH.Asset_ID AND
730 ((AH.Asset_Type <> 'EXPENSED' AND
731 Report_Type IN ('COST', 'CIP COST')) OR
732 (AH.Asset_Type in ('CAPITALIZED','CIP') AND
733 Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
734 TH.Transaction_Header_ID BETWEEN
735 AH.Transaction_Header_ID_In AND
736 NVL (AH.Transaction_Header_ID_Out - 1,
737 TH.Transaction_Header_ID)
738 AND
739 (DECODE (RT.Lookup_Code, AJ.Adjustment_Type, 1, 0) *
740 AJ.Adjustment_Amount) <> 0
741 -- start of cua
742 and not exists ( select 'x' from fa_books bks
743 where bks.book_type_code = Book
744 and bks.asset_id = aj.asset_id
745 and bks.group_asset_id is not null
746 and bks.date_ineffective is not null )
747 -- end of cua
748 /* SLA Changes */
749 and links.Source_distribution_id_num_1 = aj.transaction_header_id
750 and links.Source_distribution_id_num_2 = aj.adjustment_line_id
751 and links.application_id = 140
752 and links.source_distribution_type = 'TRX'
753 and headers.application_id = 140
754 and headers.ae_header_id = links.ae_header_id
755 and headers.ledger_id = h_set_of_books_id
756 and lines.ae_header_id = links.ae_header_id
757 and lines.ae_line_num = links.ae_line_num
758 and lines.application_id = 140
759 GROUP BY
760 DH.Asset_ID,
761 DH.Code_Combination_ID,
762 lines.code_combination_id, --AJ.Code_Combination_ID,
763 AJ.Source_Type_Code;
764
765 end if;
766
767 end if;
768
769 IF REPORT_TYPE = 'RESERVE' then
770 if (h_reporting_flag = 'R') then
771 insert into FA_BALANCES_REPORT_GT
772 (Asset_id,
773 Distribution_CCID,
774 Adjustment_CCID,
775 Category_books_account,
776 Source_type_code,
777 amount)
778 SELECT
779 dh.asset_id,
780 dh.code_combination_id,
781 null,
782 CB.Deprn_Reserve_Acct,
783 'ADDITION',
784 sum(DD.DEPRN_RESERVE)
785 FROM FA_DISTRIBUTION_HISTORY DH,
786 FA_CATEGORY_BOOKS CB,
787 FA_ASSET_HISTORY AH,
788 FA_DEPRN_DETAIL_MRC_V DD
789 WHERE NOT EXISTS (SELECT ASSET_ID
790 FROM FA_BALANCES_REPORT_GT
791 WHERE ASSET_ID = DH.ASSET_ID
792 AND DISTRIBUTION_CCID = DH.CODE_COMBINATION_ID
793 AND SOURCE_TYPE_CODE = 'ADDITION')
794 AND DD.BOOK_TYPE_CODE = BOOK
795 AND (DD.PERIOD_COUNTER+1) BETWEEN
796 PERIOD1_PC AND PERIOD2_PC
797 AND DD.DEPRN_SOURCE_CODE = 'B'
798 AND DD.ASSET_ID = DH.ASSET_ID
799 AND DD.DEPRN_RESERVE <> 0
800 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
801 AND DH.ASSET_ID = AH.ASSET_ID
802 AND AH.DATE_EFFECTIVE <
803 NVL(DH.DATE_INEFFECTIVE, SYSDATE)
804 AND NVL(DH.DATE_INEFFECTIVE,SYSDATE) <=
805 NVL(AH.DATE_INEFFECTIVE,SYSDATE)
806 AND DD.BOOK_TYPE_CODE = CB.BOOK_TYPE_CODE
807 AND AH.CATEGORY_ID = CB.CATEGORY_ID
808 GROUP BY
809 Dh.ASSET_ID,
810 DH.CODE_COMBINATION_ID,
811 CB.DEPRN_RESERVE_ACCT;
812 else
813 insert into FA_BALANCES_REPORT_GT
814 (Asset_id,
815 Distribution_CCID,
816 Adjustment_CCID,
817 Category_books_account,
818 Source_type_code,
819 amount)
820 SELECT
821 dh.asset_id,
822 dh.code_combination_id,
823 null,
824 CB.Deprn_Reserve_Acct,
825 'ADDITION',
826 sum(DD.DEPRN_RESERVE)
827 FROM FA_DISTRIBUTION_HISTORY DH,
828 FA_CATEGORY_BOOKS CB,
829 FA_ASSET_HISTORY AH,
830 FA_DEPRN_DETAIL DD
831 WHERE NOT EXISTS (SELECT ASSET_ID
832 FROM FA_BALANCES_REPORT_GT
833 WHERE ASSET_ID = DH.ASSET_ID
834 AND DISTRIBUTION_CCID = DH.CODE_COMBINATION_ID
835 AND SOURCE_TYPE_CODE = 'ADDITION')
836 AND DD.BOOK_TYPE_CODE = BOOK
837 AND (DD.PERIOD_COUNTER+1) BETWEEN
838 PERIOD1_PC AND PERIOD2_PC
839 AND DD.DEPRN_SOURCE_CODE = 'B'
840 AND DD.ASSET_ID = DH.ASSET_ID
841 AND DD.DEPRN_RESERVE <> 0
842 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
843 AND DH.ASSET_ID = AH.ASSET_ID
844 AND AH.DATE_EFFECTIVE <
845 NVL(DH.DATE_INEFFECTIVE, SYSDATE)
846 AND NVL(DH.DATE_INEFFECTIVE,SYSDATE) <=
847 NVL(AH.DATE_INEFFECTIVE,SYSDATE)
848 AND DD.BOOK_TYPE_CODE = CB.BOOK_TYPE_CODE
849 AND AH.CATEGORY_ID = CB.CATEGORY_ID
850 GROUP BY
851 Dh.ASSET_ID,
852 DH.CODE_COMBINATION_ID,
853 CB.DEPRN_RESERVE_ACCT;
854 end if;
855
856 end if;
857 --Added during DT Fix
858 commit;
859 --End of DT Fix
860
861 end Get_Adjustments;
862
863
864 PROCEDURE get_adjustments_for_group
865 (Book in varchar2,
866 Distribution_Source_Book in varchar2,
867 Period1_PC in number,
868 Period2_PC in number,
869 Report_Type in varchar2,
870 Balance_Type in varchar2)
871 is
872 --Added during DT Fix
873 PRAGMA AUTONOMOUS_TRANSACTION;
874 --End of DT Fix
875 h_set_of_books_id number;
876 h_reporting_flag varchar2(1);
877 begin
878
879 -- get mrc related info
880 begin
881 --h_set_of_books_id := to_number(substrb(userenv('CLIENT_INFO'),45,10));
882 select to_number(substrb(userenv('CLIENT_INFO'),45,10))
883 into h_set_of_books_id from dual;
884
885 if (h_set_of_books_id = -1) then
886 h_set_of_books_id := null;
887 end if;
888
889 exception
890 when others then
891 h_set_of_books_id := null;
892 end;
893
894 if (h_set_of_books_id is not null) then
895 if not fa_cache_pkg.fazcsob
896 (X_set_of_books_id => h_set_of_books_id,
897 X_mrc_sob_type_code => h_reporting_flag) then
898 raise FND_API.G_EXC_UNEXPECTED_ERROR;
899 end if;
900 else
901 select set_of_books_id
902 into h_set_of_books_id
903 from fa_book_controls
904 where book_type_code = book;
905
906 h_reporting_flag := 'P';
907 end if;
908
909 -- run only if CRL installed
910 if ( nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') then
911
912 if (h_reporting_flag = 'R') then
913
914 INSERT INTO FA_BALANCES_REPORT_GT
915 (Asset_ID,
916 Distribution_CCID,
917 Adjustment_CCID,
918 Category_Books_Account,
919 Source_Type_Code,
920 Amount)
921 SELECT
922 AJ.Asset_ID,
923 -- Changed for BMA1
924 -- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
925 GAD.DEPRN_EXPENSE_ACCT_CCID,
926 decode(aj.adjustment_type,'COST',GAD.ASSET_COST_ACCT_CCID,lines.code_combination_id /*AJ.Code_Combination_ID*/ ),
927 null,
928 AJ.Source_Type_Code,
929 SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
930 AJ.Adjustment_Amount)
931 FROM
932 FA_LOOKUPS RT,
933 FA_ADJUSTMENTS_MRC_V AJ,
934 fa_books_mrc_v bk,
935 fa_group_asset_default gad
936
937 /* SLA Changes */
938 ,xla_ae_headers headers
939 ,xla_ae_lines lines
940 ,xla_distribution_links links
941 WHERE
942 bk.asset_id = aj.asset_id
943 and bk.book_type_code = book
944 and bk.group_asset_id = gad.group_asset_id
945 and bk.book_type_code = gad.book_type_code
946 and bk.date_ineffective is null
947 and aj.asset_id in (select asset_id from fa_books_mrc_v
948 where group_asset_id is not null
949 and date_ineffective is null)
950 and
951 RT.Lookup_Type = 'REPORT TYPE' AND
952 RT.Lookup_Code = Report_Type
953 AND
954 AJ.Asset_ID = BK.Asset_ID AND
955 AJ.Book_Type_Code = Book AND
956 AJ.Adjustment_Type in
957 (Report_Type, DECODE(Report_Type,
958 'REVAL RESERVE', 'REVAL AMORT')) AND
959 AJ.Period_Counter_Created BETWEEN
960 Period1_PC AND Period2_PC
961 AND
962 (DECODE (RT.Lookup_Code, AJ.Adjustment_Type, 1, 0) *
963 AJ.Adjustment_Amount) <> 0
964
965 /* SLA Changes */
966 and links.Source_distribution_id_num_1 = aj.transaction_header_id
967 and links.Source_distribution_id_num_2 = aj.adjustment_line_id
968 and links.application_id = 140
969 and links.source_distribution_type = 'TRX'
970 and headers.application_id = 140
971 and headers.ae_header_id = links.ae_header_id
972 and headers.ledger_id = h_set_of_books_id
973 and lines.ae_header_id = links.ae_header_id
974 and lines.ae_line_num = links.ae_line_num
975 and lines.application_id = 140
976 GROUP BY
977 AJ.Asset_ID,
978 -- Changed for BMA1
979 -- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
980 GAD.DEPRN_EXPENSE_ACCT_CCID,
981 decode(aj.adjustment_type,'COST',GAD.ASSET_COST_ACCT_CCID,lines.code_combination_id /*AJ.Code_Combination_ID*/ ),
982 aJ.Source_Type_Code;
983 else
984
985 INSERT INTO FA_BALANCES_REPORT_GT
986 (Asset_ID,
987 Distribution_CCID,
988 Adjustment_CCID,
989 Category_Books_Account,
990 Source_Type_Code,
991 Amount)
992 SELECT
993 AJ.Asset_ID,
994 -- Changed for BMA1
995 -- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
996 GAD.DEPRN_EXPENSE_ACCT_CCID,
997 decode(aj.adjustment_type,'COST',GAD.ASSET_COST_ACCT_CCID,lines.code_combination_id /*AJ.Code_Combination_ID*/ ),
998 null,
999 AJ.Source_Type_Code,
1000 SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
1001 AJ.Adjustment_Amount)
1002 FROM
1003 FA_LOOKUPS RT,
1004 FA_ADJUSTMENTS AJ,
1005 fa_books bk,
1006 fa_group_asset_default gad
1007
1008 /* SLA Changes */
1009 ,xla_ae_headers headers
1010 ,xla_ae_lines lines
1011 ,xla_distribution_links links
1012 WHERE
1013 bk.asset_id = aj.asset_id
1014 and bk.book_type_code = book
1015 and bk.group_asset_id = gad.group_asset_id
1016 and bk.book_type_code = gad.book_type_code
1017 and bk.date_ineffective is null
1018 and aj.asset_id in (select asset_id from fa_books
1019 where group_asset_id is not null
1020 and date_ineffective is null)
1021 and
1022 RT.Lookup_Type = 'REPORT TYPE' AND
1023 RT.Lookup_Code = Report_Type
1024 AND
1025 AJ.Asset_ID = BK.Asset_ID AND
1026 AJ.Book_Type_Code = Book AND
1027 AJ.Adjustment_Type in
1028 (Report_Type, DECODE(Report_Type,
1029 'REVAL RESERVE', 'REVAL AMORT')) AND
1030 AJ.Period_Counter_Created BETWEEN
1031 Period1_PC AND Period2_PC
1032 AND
1033 (DECODE (RT.Lookup_Code, AJ.Adjustment_Type, 1, 0) *
1034 AJ.Adjustment_Amount) <> 0
1035
1036 /* SLA Changes */
1037 and links.Source_distribution_id_num_1 = aj.transaction_header_id
1038 and links.Source_distribution_id_num_2 = aj.adjustment_line_id
1039 and links.application_id = 140
1040 and links.source_distribution_type = 'TRX'
1041 and headers.application_id = 140
1042 and headers.ae_header_id = links.ae_header_id
1043 and headers.ledger_id = h_set_of_books_id
1044 and lines.ae_header_id = links.ae_header_id
1045 and lines.ae_line_num = links.ae_line_num
1046 and lines.application_id = 140
1047 GROUP BY
1048 AJ.Asset_ID,
1049 -- Changed for BMA1
1050 -- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
1051 GAD.DEPRN_EXPENSE_ACCT_CCID,
1052 decode(aj.adjustment_type,'COST',GAD.ASSET_COST_ACCT_CCID,lines.code_combination_id /* AJ.Code_Combination_ID*/ ),
1053 aJ.Source_Type_Code;
1054 end if;
1055
1056
1057 end if;
1058 --Added during DT Fix
1059 commit;
1060 --End of DT Fix
1061
1062 end Get_Adjustments_for_group;
1063
1064
1065 procedure Get_Balance
1066 (Book in varchar2,
1067 Distribution_Source_Book in varchar2,
1068 Period_PC in number,
1069 Earliest_PC in number,
1070 Period_Date in date,
1071 Additions_Date in date,
1072 Report_Type in varchar2,
1073 Balance_Type in varchar2,
1074 Begin_or_End in varchar2)
1075 is
1076
1077 --Added during DT Fix
1078 PRAGMA AUTONOMOUS_TRANSACTION;
1079 --End of DT Fix
1080
1081 P_Date date := Period_Date;
1082 A_Date date := Additions_Date;
1083 h_set_of_books_id number;
1084 h_reporting_flag varchar2(1);
1085 begin
1086
1087 -- get mrc related info
1088 begin
1089 -- h_set_of_books_id := to_number(substrb(userenv('CLIENT_INFO'),45,10));
1090 select to_number(substrb(userenv('CLIENT_INFO'),45,10))
1091 into h_set_of_books_id from dual;
1092
1093 exception
1094 when others then
1095 h_set_of_books_id := null;
1096 end;
1097
1098 if (h_set_of_books_id is not null) then
1099 if not fa_cache_pkg.fazcsob
1100 (X_set_of_books_id => h_set_of_books_id,
1101 X_mrc_sob_type_code => h_reporting_flag) then
1102 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1103 end if;
1104 else
1105 h_reporting_flag := 'P';
1106 end if;
1107
1108
1109 -- Fix for Bug #1892406. Run only if CRL not installed.
1110 If (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'N' ) then
1111
1112 if (h_reporting_flag = 'R') then
1113
1114 INSERT INTO FA_BALANCES_REPORT_GT
1115 (Asset_ID,
1116 Distribution_CCID,
1117 Adjustment_CCID,
1118 Category_Books_Account,
1119 Source_Type_Code,
1120 Amount)
1121 SELECT
1122 DH.Asset_ID,
1123 DH.Code_Combination_ID,
1124 null,
1125 DECODE (Report_Type,
1126 'COST', CB.Asset_Cost_Acct,
1127 'CIP COST', CB.CIP_Cost_Acct,
1128 'RESERVE', CB.Deprn_Reserve_Acct,
1129 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1130 DECODE(Report_Type,
1131 'RESERVE', DECODE(DD.Deprn_Source_Code,
1132 'D', Begin_or_End, 'ADDITION'),
1133 'REVAL RESERVE',
1134 DECODE(DD.Deprn_Source_Code,
1135 'D', Begin_or_End, 'ADDITION'),
1136 Begin_or_End),
1137 DECODE (Report_Type,
1138 'COST', DD.Cost,
1139 'CIP COST', DD.Cost,
1140 'RESERVE', DD.Deprn_Reserve,
1141 'REVAL RESERVE', DD.Reval_Reserve)
1142 FROM
1143 FA_DISTRIBUTION_HISTORY DH,
1144 FA_DEPRN_DETAIL_MRC_V DD,
1145 FA_ASSET_HISTORY AH,
1146 FA_CATEGORY_BOOKS CB,
1147 FA_BOOKS_MRC_V BK
1148 WHERE
1149 DH.Book_Type_Code = Distribution_Source_Book AND
1150 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1151 A_Date) BETWEEN
1152 DH.Date_Effective AND
1153 NVL(DH.Date_Ineffective, SYSDATE)
1154 AND
1155 DD.Asset_ID = DH.Asset_ID AND
1156 DD.Book_Type_Code = Book AND
1157 DD.Distribution_ID = DH.Distribution_ID AND
1158 DD.Period_Counter <= Period_PC AND
1159 -- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
1160 DECODE(Report_Type, 'CIP COST', DD.Deprn_Source_Code,
1161 DECODE(Begin_or_End,
1162 'BEGIN', DD.Deprn_Source_Code, 'D')) =
1163 DD.Deprn_Source_Code AND
1164 /* DECODE(Begin_or_End,
1165 'BEGIN', DD.Deprn_Source_Code, 'D') =
1166 DD.Deprn_Source_Code AND */
1167 -- End bug fix 5076193
1168 DD.Period_Counter =
1169 (SELECT MAX (SUB_DD.Period_Counter)
1170 FROM FA_DEPRN_DETAIL_MRC_V SUB_DD
1171 WHERE SUB_DD.Book_Type_Code = Book AND
1172 SUB_DD.Distribution_ID = DH.Distribution_ID AND
1173 DH.Distribution_ID = DD.Distribution_ID AND
1174 SUB_DD.Period_Counter <= Period_PC)
1175 AND
1176 AH.Asset_ID = DH.Asset_ID AND
1177 ((AH.Asset_Type <> 'EXPENSED' AND
1178 Report_Type IN ('COST', 'CIP COST')) OR
1179 (AH.Asset_Type in ('CAPITALIZED','CIP') AND
1180 Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
1181 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1182 A_Date) BETWEEN
1183 AH.Date_Effective AND
1184 NVL(AH.Date_Ineffective, SYSDATE)
1185 AND
1186 CB.Category_ID = AH.Category_ID AND
1187 CB.Book_Type_Code = DD.book_type_code -- changed from book var to column
1188 AND
1189 BK.Book_Type_Code = CB.book_type_code AND -- changed from book var to column
1190 BK.Asset_ID = DD.Asset_ID AND
1191 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1192 A_Date) BETWEEN
1193 BK.Date_Effective AND
1194 NVL(BK.Date_Ineffective, SYSDATE) AND
1195 NVL(BK.Period_Counter_Fully_Retired, Period_PC+1)
1196 > Earliest_PC
1197 AND
1198 DECODE (Report_Type,
1199 'COST', DECODE (AH.Asset_Type,
1200 'CAPITALIZED', CB.Asset_Cost_Acct,
1201 null),
1202 'CIP COST',
1203 DECODE (AH.Asset_Type,
1204 'CIP', CB.CIP_Cost_Acct,
1205 null),
1206 'RESERVE', CB.Deprn_Reserve_Acct,
1207 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null;
1208 else
1209 -- split for 'COST','CIP COST' and 'RESERVE','REVAL RESERVE' for better performance.
1210 if report_type in ('COST', 'CIP COST') then
1211 INSERT INTO FA_BALANCES_REPORT_GT
1212 (Asset_ID,
1213 Distribution_CCID,
1214 Adjustment_CCID,
1215 Category_Books_Account,
1216 Source_Type_Code,
1217 Amount)
1218 SELECT
1219 DH.Asset_ID,
1220 DH.Code_Combination_ID,
1221 null,
1222 DECODE (Report_Type,
1223 'COST', CB.Asset_Cost_Acct,
1224 'CIP COST', CB.CIP_Cost_Acct,
1225 'RESERVE', CB.Deprn_Reserve_Acct,
1226 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1227 DECODE(Report_Type,
1228 'RESERVE', DECODE(DD.Deprn_Source_Code,
1229 'D', Begin_or_End, 'ADDITION'),
1230 'REVAL RESERVE',
1231 DECODE(DD.Deprn_Source_Code,
1232 'D', Begin_or_End, 'ADDITION'),
1233 Begin_or_End),
1234 DECODE (Report_Type,
1235 'COST', DD.Cost,
1236 'CIP COST', DD.Cost,
1237 'RESERVE', DD.Deprn_Reserve,
1238 'REVAL RESERVE', DD.Reval_Reserve)
1239 FROM
1240 FA_DISTRIBUTION_HISTORY DH,
1241 FA_DEPRN_DETAIL DD,
1242 FA_ASSET_HISTORY AH,
1243 FA_CATEGORY_BOOKS CB,
1244 FA_BOOKS BK
1245 WHERE
1246 DH.Book_Type_Code = Distribution_Source_Book AND
1247 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1248 A_Date) BETWEEN
1249 DH.Date_Effective AND
1250 NVL(DH.Date_Ineffective, SYSDATE)
1251 AND
1252 DD.Asset_ID = DH.Asset_ID AND
1253 DD.Book_Type_Code = Book AND
1254 DD.Distribution_ID = DH.Distribution_ID AND
1255 DD.Period_Counter <= Period_PC AND
1256 -- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
1257 DECODE(Report_Type, 'CIP COST', DD.Deprn_Source_Code,
1258 DECODE(Begin_or_End,
1259 'BEGIN', DD.Deprn_Source_Code, 'D')) =
1260 DD.Deprn_Source_Code AND
1261 /* DECODE(Begin_or_End,
1262 'BEGIN', DD.Deprn_Source_Code, 'D') =
1263 DD.Deprn_Source_Code AND */
1264 -- End bug fix 5076193
1265 DD.Period_Counter =
1266 (SELECT MAX (SUB_DD.Period_Counter)
1267 FROM FA_DEPRN_DETAIL SUB_DD
1268 WHERE SUB_DD.Book_Type_Code = Book AND
1269 SUB_DD.Distribution_ID = DH.Distribution_ID AND
1270 DH.Distribution_ID = DD.Distribution_ID AND
1271 SUB_DD.Period_Counter <= Period_PC)
1272 AND
1273 AH.Asset_ID = DH.Asset_ID AND
1274 AH.Asset_Type <> 'EXPENSED'
1275 AND
1276 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1277 A_Date) BETWEEN
1278 AH.Date_Effective AND
1279 NVL(AH.Date_Ineffective, SYSDATE)
1280 AND
1281 CB.Category_ID = AH.Category_ID AND
1282 CB.Book_Type_Code = DD.book_type_code -- changed from book var to column
1283 AND
1284 BK.Book_Type_Code = CB.book_type_code AND -- changed from book var to column
1285 BK.Asset_ID = DD.Asset_ID AND
1286 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1287 A_Date) BETWEEN
1288 BK.Date_Effective AND
1289 NVL(BK.Date_Ineffective, SYSDATE) AND
1290 NVL(BK.Period_Counter_Fully_Retired, Period_PC+1)
1291 > Earliest_PC
1292 AND
1293 DECODE (Report_Type,
1294 'COST', DECODE (AH.Asset_Type,
1295 'CAPITALIZED', CB.Asset_Cost_Acct,
1296 null),
1297 'CIP COST',
1298 DECODE (AH.Asset_Type,
1299 'CIP', CB.CIP_Cost_Acct,
1300 null),
1301 'RESERVE', CB.Deprn_Reserve_Acct,
1302 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null;
1303 else -- report_type in ('RESERVE','REVAL RESERVE')
1304
1305 INSERT INTO FA_BALANCES_REPORT_GT
1306 (Asset_ID,
1307 Distribution_CCID,
1308 Adjustment_CCID,
1309 Category_Books_Account,
1310 Source_Type_Code,
1311 Amount)
1312 SELECT
1313 DH.Asset_ID,
1314 DH.Code_Combination_ID,
1315 null,
1316 DECODE (Report_Type,
1317 'COST', CB.Asset_Cost_Acct,
1318 'CIP COST', CB.CIP_Cost_Acct,
1319 'RESERVE', CB.Deprn_Reserve_Acct,
1320 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1321 DECODE(Report_Type,
1322 'RESERVE', DECODE(DD.Deprn_Source_Code,
1323 'D', Begin_or_End, 'ADDITION'),
1324 'REVAL RESERVE',
1325 DECODE(DD.Deprn_Source_Code,
1326 'D', Begin_or_End, 'ADDITION'),
1327 Begin_or_End),
1328 DECODE (Report_Type,
1329 'COST', DD.Cost,
1330 'CIP COST', DD.Cost,
1331 'RESERVE', DD.Deprn_Reserve,
1332 'REVAL RESERVE', DD.Reval_Reserve)
1333 FROM
1334 FA_DISTRIBUTION_HISTORY DH,
1335 FA_DEPRN_DETAIL DD,
1336 FA_ASSET_HISTORY AH,
1337 FA_CATEGORY_BOOKS CB,
1338 FA_BOOKS BK
1339 WHERE
1340 DH.Book_Type_Code = Distribution_Source_Book AND
1341 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1342 A_Date) BETWEEN
1343 DH.Date_Effective AND
1344 NVL(DH.Date_Ineffective, SYSDATE)
1345 AND
1346 DD.Asset_ID = DH.Asset_ID AND
1347 DD.Book_Type_Code = Book AND
1348 DD.Distribution_ID = DH.Distribution_ID AND
1349 DD.Period_Counter <= Period_PC AND
1350 DECODE(Begin_or_End,
1351 'BEGIN', DD.Deprn_Source_Code, 'D') =
1352 DD.Deprn_Source_Code AND
1353 DD.Period_Counter =
1354 (SELECT MAX (SUB_DD.Period_Counter)
1355 FROM FA_DEPRN_DETAIL SUB_DD
1356 WHERE SUB_DD.Book_Type_Code = Book AND
1357 SUB_DD.Distribution_ID = DH.Distribution_ID AND
1358 DH.Distribution_ID = DD.Distribution_ID AND
1359 SUB_DD.Period_Counter <= Period_PC)
1360 AND
1361 AH.Asset_ID = DH.Asset_ID AND
1362 AH.Asset_Type in ( 'CAPITALIZED' ,'CIP') AND
1363 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1364 A_Date) BETWEEN
1365 AH.Date_Effective AND
1366 NVL(AH.Date_Ineffective, SYSDATE)
1367 AND
1368 CB.Category_ID = AH.Category_ID AND
1369 CB.Book_Type_Code = DD.book_type_code -- changed from book var to column
1370 AND
1371 BK.Book_Type_Code = CB.book_type_code AND -- changed from book var to column
1372 BK.Asset_ID = DD.Asset_ID AND
1373 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1374 A_Date) BETWEEN
1375 BK.Date_Effective AND
1376 NVL(BK.Date_Ineffective, SYSDATE) AND
1377 NVL(BK.Period_Counter_Fully_Retired, Period_PC+1)
1378 > Earliest_PC
1379 AND
1380 DECODE (Report_Type,
1381 'COST', DECODE (AH.Asset_Type,
1382 'CAPITALIZED', CB.Asset_Cost_Acct,
1383 null),
1384 'CIP COST',
1385 DECODE (AH.Asset_Type,
1386 'CIP', CB.CIP_Cost_Acct,
1387 null),
1388 'RESERVE', CB.Deprn_Reserve_Acct,
1389 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null;
1390
1391 end if;
1392
1393 end if;
1394
1395 -- Fix for Bug #1892406. Run only if CRL installed.
1396 elsif (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y' ) then
1397
1398 if (h_reporting_flag = 'R') then
1399 INSERT INTO FA_BALANCES_REPORT_GT
1400 (Asset_ID,
1401 Distribution_CCID,
1402 Adjustment_CCID,
1403 Category_Books_Account,
1404 Source_Type_Code,
1405 Amount)
1406 SELECT
1407 DH.Asset_ID,
1408 DH.Code_Combination_ID,
1409 null,
1410 DECODE (Report_Type,
1411 'COST', CB.Asset_Cost_Acct,
1412 'CIP COST', CB.CIP_Cost_Acct,
1413 'RESERVE', CB.Deprn_Reserve_Acct,
1414 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1415 DECODE(Report_Type,
1416 'RESERVE', DECODE(DD.Deprn_Source_Code,
1417 'D', Begin_or_End, 'ADDITION'),
1418 'REVAL RESERVE',
1419 DECODE(DD.Deprn_Source_Code,
1420 'D', Begin_or_End, 'ADDITION'),
1421 Begin_or_End),
1422 DECODE (Report_Type,
1423 'COST', DD.Cost,
1424 'CIP COST', DD.Cost,
1425 'RESERVE', DD.Deprn_Reserve,
1426 'REVAL RESERVE', DD.Reval_Reserve)
1427 FROM
1428 FA_DISTRIBUTION_HISTORY DH,
1429 FA_DEPRN_DETAIL_MRC_V DD,
1430 FA_ASSET_HISTORY AH,
1431 FA_CATEGORY_BOOKS CB,
1432 FA_BOOKS_MRC_V BK
1433 WHERE
1434 DH.Book_Type_Code = Distribution_Source_Book AND
1435 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1436 A_Date) BETWEEN
1437 DH.Date_Effective AND
1438 NVL(DH.Date_Ineffective, SYSDATE)
1439 AND
1440 DD.Asset_ID = DH.Asset_ID AND
1441 DD.Book_Type_Code = Book AND
1442 DD.Distribution_ID = DH.Distribution_ID AND
1443 DD.Period_Counter <= Period_PC AND
1444 -- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
1445 DECODE(Report_Type, 'CIP COST', DD.Deprn_Source_Code,
1446 DECODE(Begin_or_End,
1447 'BEGIN', DD.Deprn_Source_Code, 'D')) =
1448 DD.Deprn_Source_Code AND
1449 /* DECODE(Begin_or_End,
1450 'BEGIN', DD.Deprn_Source_Code, 'D') =
1451 DD.Deprn_Source_Code AND */
1452 -- end bug fix 5076193
1453 DD.Period_Counter =
1454 (SELECT MAX (SUB_DD.Period_Counter)
1455 FROM FA_DEPRN_DETAIL_MRC_V SUB_DD
1456 WHERE SUB_DD.Book_Type_Code = Book AND
1457 SUB_DD.Distribution_ID = DH.Distribution_ID AND
1458 DH.Distribution_ID = DD.Distribution_ID AND
1459 SUB_DD.Period_Counter <= Period_PC)
1460 AND
1461 AH.Asset_ID = DH.Asset_ID AND
1462 ((AH.Asset_Type <> 'EXPENSED' AND
1463 Report_Type IN ('COST', 'CIP COST')) OR
1464 (AH.Asset_Type in ('CAPITALIZED','CIP') AND
1465 Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
1466 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1467 A_Date) BETWEEN
1468 AH.Date_Effective AND
1469 NVL(AH.Date_Ineffective, SYSDATE)
1470 AND
1471 CB.Category_ID = AH.Category_ID AND
1472 CB.Book_Type_Code = DD.book_type_code -- changed from book var to column
1473 AND
1474 BK.Book_Type_Code = CB.book_type_code AND -- changed from book var to column
1475 BK.Asset_ID = DD.Asset_ID AND
1476 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1477 A_Date) BETWEEN
1478 BK.Date_Effective AND
1479 NVL(BK.Date_Ineffective, SYSDATE) AND
1480 NVL(BK.Period_Counter_Fully_Retired, Period_PC+1)
1481 > Earliest_PC
1482 AND
1483 DECODE (Report_Type,
1484 'COST', DECODE (AH.Asset_Type,
1485 'CAPITALIZED', CB.Asset_Cost_Acct,
1486 null),
1487 'CIP COST',
1488 DECODE (AH.Asset_Type,
1489 'CIP', CB.CIP_Cost_Acct,
1490 null),
1491 'RESERVE', CB.Deprn_Reserve_Acct,
1492 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
1493 -- start of CUA - This is to exclude the Group Asset Members
1494 and bk.GROUP_ASSET_ID IS NULL;
1495 else
1496 INSERT INTO FA_BALANCES_REPORT_GT
1497 (Asset_ID,
1498 Distribution_CCID,
1499 Adjustment_CCID,
1500 Category_Books_Account,
1501 Source_Type_Code,
1502 Amount)
1503 SELECT
1504 DH.Asset_ID,
1505 DH.Code_Combination_ID,
1506 null,
1507 DECODE (Report_Type,
1508 'COST', CB.Asset_Cost_Acct,
1509 'CIP COST', CB.CIP_Cost_Acct,
1510 'RESERVE', CB.Deprn_Reserve_Acct,
1511 'REVAL RESERVE', CB.Reval_Reserve_Acct),
1512 DECODE(Report_Type,
1513 'RESERVE', DECODE(DD.Deprn_Source_Code,
1514 'D', Begin_or_End, 'ADDITION'),
1515 'REVAL RESERVE',
1516 DECODE(DD.Deprn_Source_Code,
1517 'D', Begin_or_End, 'ADDITION'),
1518 Begin_or_End),
1519 DECODE (Report_Type,
1520 'COST', DD.Cost,
1521 'CIP COST', DD.Cost,
1522 'RESERVE', DD.Deprn_Reserve,
1523 'REVAL RESERVE', DD.Reval_Reserve)
1524 FROM
1525 FA_DISTRIBUTION_HISTORY DH,
1526 FA_DEPRN_DETAIL DD,
1527 FA_ASSET_HISTORY AH,
1528 FA_CATEGORY_BOOKS CB,
1529 FA_BOOKS BK
1530 WHERE
1531 DH.Book_Type_Code = Distribution_Source_Book AND
1532 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1533 A_Date) BETWEEN
1534 DH.Date_Effective AND
1535 NVL(DH.Date_Ineffective, SYSDATE)
1536 AND
1537 DD.Asset_ID = DH.Asset_ID AND
1538 DD.Book_Type_Code = Book AND
1539 DD.Distribution_ID = DH.Distribution_ID AND
1540 DD.Period_Counter <= Period_PC AND
1541 -- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
1542 DECODE(Report_Type, 'CIP COST', DD.Deprn_Source_Code,
1543 DECODE(Begin_or_End,
1544 'BEGIN', DD.Deprn_Source_Code, 'D')) =
1545 DD.Deprn_Source_Code AND
1546 /* DECODE(Begin_or_End,
1547 'BEGIN', DD.Deprn_Source_Code, 'D') =
1548 DD.Deprn_Source_Code AND */
1549 -- End bug fix 5076193
1550 DD.Period_Counter =
1551 (SELECT MAX (SUB_DD.Period_Counter)
1552 FROM FA_DEPRN_DETAIL SUB_DD
1553 WHERE SUB_DD.Book_Type_Code = Book AND
1554 SUB_DD.Distribution_ID = DH.Distribution_ID AND
1555 DH.Distribution_ID = DD.Distribution_ID AND
1556 SUB_DD.Period_Counter <= Period_PC)
1557 AND
1558 AH.Asset_ID = DH.Asset_ID AND
1559 ((AH.Asset_Type <> 'EXPENSED' AND
1560 Report_Type IN ('COST', 'CIP COST')) OR
1561 (AH.Asset_Type in ('CAPITALIZED','CIP') AND
1562 Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
1563 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1564 A_Date) BETWEEN
1565 AH.Date_Effective AND
1566 NVL(AH.Date_Ineffective, SYSDATE)
1567 AND
1568 CB.Category_ID = AH.Category_ID AND
1569 CB.Book_Type_Code = DD.book_type_code -- changed from book var to column
1570 AND
1571 BK.Book_Type_Code = CB.book_type_code AND -- changed from book var to column
1572 BK.Asset_ID = DD.Asset_ID AND
1573 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1574 A_Date) BETWEEN
1575 BK.Date_Effective AND
1576 NVL(BK.Date_Ineffective, SYSDATE) AND
1577 NVL(BK.Period_Counter_Fully_Retired, Period_PC+1)
1578 > Earliest_PC
1579 AND
1580 DECODE (Report_Type,
1581 'COST', DECODE (AH.Asset_Type,
1582 'CAPITALIZED', CB.Asset_Cost_Acct,
1583 null),
1584 'CIP COST',
1585 DECODE (AH.Asset_Type,
1586 'CIP', CB.CIP_Cost_Acct,
1587 null),
1588 'RESERVE', CB.Deprn_Reserve_Acct,
1589 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
1590 -- start of CUA - This is to exclude the Group Asset Members
1591 and bk.GROUP_ASSET_ID IS NULL;
1592 end if;
1593 -- end of cua
1594
1595 end if;
1596 --Added during DT Fix
1597 commit;
1598 --End of DT Fix
1599 end Get_Balance;
1600
1601
1602 procedure get_balance_group_begin
1603 (Book in varchar2,
1604 Distribution_Source_Book in varchar2,
1605 Period_PC in number,
1606 Earliest_PC in number,
1607 Period_Date in date,
1608 Additions_Date in date,
1609 Report_Type in varchar2,
1610 Balance_Type in varchar2,
1611 Begin_or_End in varchar2)
1612 is
1613
1614 --Added during DT Fix
1615 PRAGMA AUTONOMOUS_TRANSACTION;
1616 --End of DT Fix
1617 P_Date date := Period_Date;
1618 A_Date date := Additions_Date;
1619 h_set_of_books_id number;
1620 h_reporting_flag varchar2(1);
1621 begin
1622
1623 -- get mrc related info
1624 begin
1625 --h_set_of_books_id := to_number(substrb(userenv('CLIENT_INFO'),45,10));
1626 select to_number(substrb(userenv('CLIENT_INFO'),45,10))
1627 into h_set_of_books_id from dual;
1628
1629 exception
1630 when others then
1631 h_set_of_books_id := null;
1632 end;
1633
1634 if (h_set_of_books_id is not null) then
1635 if not fa_cache_pkg.fazcsob
1636 (X_set_of_books_id => h_set_of_books_id,
1637 X_mrc_sob_type_code => h_reporting_flag) then
1638 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1639 end if;
1640 else
1641 h_reporting_flag := 'P';
1642 end if;
1643
1644
1645 -- run only if CRL installed
1646 if ( nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') then
1647
1648 if ( report_type not in ('RESERVE') ) THEN
1649 if (h_reporting_flag = 'R') then
1650 INSERT INTO FA_BALANCES_REPORT_GT
1651 (Asset_ID,
1652 Distribution_CCID,
1653 Adjustment_CCID,
1654 Category_Books_Account,
1655 Source_Type_Code,
1656 Amount)
1657 SELECT
1658 DH.Asset_ID,
1659 --DH.Code_Combination_ID,
1660 nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, DH.Code_Combination_ID),
1661 -- Changed for BMA1
1662 -- nvl(gad.asset_cost_acct_ccid,1127),
1663 gad.asset_cost_acct_ccid,
1664 null,
1665 DECODE(Report_Type,
1666 'RESERVE', DECODE(DD.Deprn_Source_Code,
1667 'D', Begin_or_End, 'ADDITION'),
1668 'REVAL RESERVE',
1669 DECODE(DD.Deprn_Source_Code,
1670 'D', Begin_or_End, 'ADDITION'),
1671 Begin_or_End),
1672 DECODE (Report_Type,
1673 -- Commented by Prabakar
1674 'COST', decode(nvl(bk.group_asset_id,-2),-2,DD.Cost,bk.cost),
1675 -- 'COST', DD.Cost,
1676 'CIP COST', DD.Cost,
1677 'RESERVE', DD.Deprn_Reserve,
1678 'REVAL RESERVE', DD.Reval_Reserve)
1679 FROM
1680 FA_BOOKS_MRC_V BK,
1681 FA_CATEGORY_BOOKS CB,
1682 FA_ASSET_HISTORY AH,
1683 FA_DEPRN_DETAIL_MRC_V DD,
1684 FA_DISTRIBUTION_HISTORY DH,
1685 -- Commented by Prabakar
1686 fa_GROUP_ASSET_DEFAULT GAD
1687 WHERE
1688 -- Commented by Prabakar
1689 GAD.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
1690 AND GAD.GROUP_ASSET_ID = BK.GROUP_ASSET_ID
1691 and
1692 -- This is to include only the Group Asset Members
1693 bk.GROUP_ASSET_ID IS not NULL AND
1694 DH.Book_Type_Code = Distribution_Source_Book AND
1695 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1696 A_Date) BETWEEN
1697 DH.Date_Effective AND
1698 NVL(DH.Date_Ineffective, SYSDATE)
1699 AND
1700 DD.Asset_ID = DH.Asset_ID AND
1701 DD.Book_Type_Code = Book AND
1702 DD.Distribution_ID = DH.Distribution_ID AND
1703 DD.Period_Counter <= Period_PC AND
1704 DECODE(Begin_or_End,
1705 'BEGIN', DD.Deprn_Source_Code, 'D') =
1706 DD.Deprn_Source_Code AND
1707 DD.Period_Counter =
1708 (SELECT MAX (SUB_DD.Period_Counter)
1709 FROM FA_DEPRN_DETAIL_MRC_V SUB_DD
1710 WHERE SUB_DD.Book_Type_Code = Book AND
1711 SUB_DD.Distribution_ID = DH.Distribution_ID AND
1712 SUB_DD.Period_Counter <= Period_PC)
1713 AND
1714 AH.Asset_ID = DH.Asset_ID AND
1715 ((AH.Asset_Type <> 'EXPENSED' AND
1716 Report_Type IN ('COST', 'CIP COST')) OR
1717 (AH.Asset_Type in ('CAPITALIZED','CIP') AND
1718 Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
1719 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1720 A_Date) BETWEEN
1721 AH.Date_Effective AND
1722 NVL(AH.Date_Ineffective, SYSDATE)
1723 AND
1724 CB.Category_ID = AH.Category_ID AND
1725 CB.Book_Type_Code = Book
1726 AND
1727 BK.Book_Type_Code = Book AND
1728 BK.Asset_ID = DD.Asset_ID AND
1729 -- Commented by Prabakar
1730 ( bk.transaction_header_id_in
1731 = ( select min(fab.transaction_header_id_in) from fa_books_groups_mrc_v bg, fa_books_mrc_v fab
1732 where bg.group_asset_id = nvl(bk.group_asset_id,-2)
1733 and bg.book_type_code = fab.book_type_code
1734 and fab.transaction_header_id_in <= bg.transaction_header_id_in
1735 and nvl(fab.transaction_header_id_out,bg.transaction_header_id_in) >= bg.transaction_header_id_in
1736 and bg.period_counter = Period_pc + 1
1737 and fab.asset_id = bk.asset_id
1738 and fab.book_type_code = bk.book_type_code
1739 and bg.BEGINNING_BALANCE_FLAG is not null )
1740 )
1741 AND
1742 DECODE (Report_Type,
1743 'COST', DECODE (AH.Asset_Type,
1744 'CAPITALIZED', CB.Asset_Cost_Acct,
1745 null),
1746 'CIP COST',
1747 DECODE (AH.Asset_Type,
1748 'CIP', CB.CIP_Cost_Acct,
1749 null),
1750 'RESERVE', CB.Deprn_Reserve_Acct,
1751 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null;
1752 else
1753 INSERT INTO FA_BALANCES_REPORT_GT
1754 (Asset_ID,
1755 Distribution_CCID,
1756 Adjustment_CCID,
1757 Category_Books_Account,
1758 Source_Type_Code,
1759 Amount)
1760 SELECT
1761 DH.Asset_ID,
1762 --DH.Code_Combination_ID,
1763 nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, DH.Code_Combination_ID),
1764 -- Changed for BMA1
1765 -- nvl(gad.asset_cost_acct_ccid,1127),
1766 gad.asset_cost_acct_ccid,
1767 null,
1768 DECODE(Report_Type,
1769 'RESERVE', DECODE(DD.Deprn_Source_Code,
1770 'D', Begin_or_End, 'ADDITION'),
1771 'REVAL RESERVE',
1772 DECODE(DD.Deprn_Source_Code,
1773 'D', Begin_or_End, 'ADDITION'),
1774 Begin_or_End),
1775 DECODE (Report_Type,
1776 -- Commented by Prabakar
1777 'COST', decode(nvl(bk.group_asset_id,-2),-2,DD.Cost,bk.cost),
1778 -- 'COST', DD.Cost,
1779 'CIP COST', DD.Cost,
1780 'RESERVE', DD.Deprn_Reserve,
1781 'REVAL RESERVE', DD.Reval_Reserve)
1782 FROM
1783 FA_BOOKS BK,
1784 FA_CATEGORY_BOOKS CB,
1785 FA_ASSET_HISTORY AH,
1786 FA_DEPRN_DETAIL DD,
1787 FA_DISTRIBUTION_HISTORY DH,
1788 -- Commented by Prabakar
1789 fa_GROUP_ASSET_DEFAULT GAD
1790 WHERE
1791 -- Commented by Prabakar
1792 GAD.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
1793 AND GAD.GROUP_ASSET_ID = BK.GROUP_ASSET_ID
1794 and
1795 -- This is to include only the Group Asset Members
1796 bk.GROUP_ASSET_ID IS not NULL AND
1797 DH.Book_Type_Code = Distribution_Source_Book AND
1798 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1799 A_Date) BETWEEN
1800 DH.Date_Effective AND
1801 NVL(DH.Date_Ineffective, SYSDATE)
1802 AND
1803 DD.Asset_ID = DH.Asset_ID AND
1804 DD.Book_Type_Code = Book AND
1805 DD.Distribution_ID = DH.Distribution_ID AND
1806 DD.Period_Counter <= Period_PC AND
1807 DECODE(Begin_or_End,
1808 'BEGIN', DD.Deprn_Source_Code, 'D') =
1809 DD.Deprn_Source_Code AND
1810 DD.Period_Counter =
1811 (SELECT MAX (SUB_DD.Period_Counter)
1812 FROM FA_DEPRN_DETAIL SUB_DD
1813 WHERE SUB_DD.Book_Type_Code = Book AND
1814 SUB_DD.Distribution_ID = DH.Distribution_ID AND
1815 SUB_DD.Period_Counter <= Period_PC)
1816 AND
1817 AH.Asset_ID = DH.Asset_ID AND
1818 ((AH.Asset_Type <> 'EXPENSED' AND
1819 Report_Type IN ('COST', 'CIP COST')) OR
1820 (AH.Asset_Type in ('CAPITALIZED','CIP') AND
1821 Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
1822 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
1823 A_Date) BETWEEN
1824 AH.Date_Effective AND
1825 NVL(AH.Date_Ineffective, SYSDATE)
1826 AND
1827 CB.Category_ID = AH.Category_ID AND
1828 CB.Book_Type_Code = Book
1829 AND
1830 BK.Book_Type_Code = Book AND
1831 BK.Asset_ID = DD.Asset_ID AND
1832 -- Commented by Prabakar
1833 ( bk.transaction_header_id_in
1834 = ( select min(fab.transaction_header_id_in) from fa_books_groups bg, fa_books fab
1835 where bg.group_asset_id = nvl(bk.group_asset_id,-2)
1836 and bg.book_type_code = fab.book_type_code
1837 and fab.transaction_header_id_in <= bg.transaction_header_id_in
1838 and nvl(fab.transaction_header_id_out,bg.transaction_header_id_in) >= bg.transaction_header_id_in
1839 and bg.period_counter = Period_pc + 1
1840 and fab.asset_id = bk.asset_id
1841 and fab.book_type_code = bk.book_type_code
1842 and bg.BEGINNING_BALANCE_FLAG is not null )
1843 )
1844 AND
1845 DECODE (Report_Type,
1846 'COST', DECODE (AH.Asset_Type,
1847 'CAPITALIZED', CB.Asset_Cost_Acct,
1848 null),
1849 'CIP COST',
1850 DECODE (AH.Asset_Type,
1851 'CIP', CB.CIP_Cost_Acct,
1852 null),
1853 'RESERVE', CB.Deprn_Reserve_Acct,
1854 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null;
1855 end if;
1856 ELSE
1857
1858 -- Get the Depreciation reserve begin balance
1859
1860 if (h_reporting_flag = 'R') then
1861 INSERT INTO FA_BALANCES_REPORT_GT
1862 (Asset_ID,
1863 Distribution_CCID,
1864 Adjustment_CCID,
1865 Category_Books_Account,
1866 Source_Type_Code,
1867 Amount)
1868 SELECT
1869 GAR.GROUP_ASSET_ID ASSET_ID,
1870 GAD.DEPRN_EXPENSE_ACCT_CCID ,
1871 GAD.DEPRN_RESERVE_ACCT_CCID ,
1872 null,
1873 /* DECODE(Report_Type,
1874 'RESERVE', DECODE(DD.Deprn_Source_Code,
1875 'D', Begin_or_End, 'ADDITION'),
1876 'REVAL RESERVE',
1877 DECODE(DD.Deprn_Source_Code,
1878 'D', Begin_or_End, 'ADDITION'),
1879 Begin_or_End),
1880 */
1881 'BEGIN',
1882 DD.DEPRN_RESERVE
1883 FROM
1884 FA_DEPRN_SUMMARY_MRC_V DD,
1885 fa_GROUP_ASSET_RULES GAR,
1886 fa_GROUP_ASSET_DEFAULT GAD
1887 WHERE
1888 DD.BOOK_TYPE_CODE = book
1889 AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
1890 AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
1891 AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
1892 AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
1893 AND DD.PERIOD_COUNTER =
1894 (SELECT max (DD_SUB.PERIOD_COUNTER)
1895 FROM FA_DEPRN_DETAIL_MRC_V DD_SUB
1896 WHERE DD_SUB.BOOK_TYPE_CODE = book
1897 AND DD_SUB.ASSET_ID = GAR.GROUP_ASSET_ID
1898 AND DD_SUB.PERIOD_COUNTER <= PERIOD_PC);
1899 else
1900 INSERT INTO FA_BALANCES_REPORT_GT
1901 (Asset_ID,
1902 Distribution_CCID,
1903 Adjustment_CCID,
1904 Category_Books_Account,
1905 Source_Type_Code,
1906 Amount)
1907 SELECT
1908 GAR.GROUP_ASSET_ID ASSET_ID,
1909 GAD.DEPRN_EXPENSE_ACCT_CCID ,
1910 GAD.DEPRN_RESERVE_ACCT_CCID ,
1911 null,
1912 /* DECODE(Report_Type,
1913 'RESERVE', DECODE(DD.Deprn_Source_Code,
1914 'D', Begin_or_End, 'ADDITION'),
1915 'REVAL RESERVE',
1916 DECODE(DD.Deprn_Source_Code,
1917 'D', Begin_or_End, 'ADDITION'),
1918 Begin_or_End),
1919 */
1920 'BEGIN',
1921 DD.DEPRN_RESERVE
1922 FROM
1923 FA_DEPRN_SUMMARY DD,
1924 fa_GROUP_ASSET_RULES GAR,
1925 fa_GROUP_ASSET_DEFAULT GAD
1926 WHERE
1927 DD.BOOK_TYPE_CODE = book
1928 AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
1929 AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
1930 AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
1931 AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
1932 AND DD.PERIOD_COUNTER =
1933 (SELECT max (DD_SUB.PERIOD_COUNTER)
1934 FROM FA_DEPRN_DETAIL DD_SUB
1935 WHERE DD_SUB.BOOK_TYPE_CODE = book
1936 AND DD_SUB.ASSET_ID = GAR.GROUP_ASSET_ID
1937 AND DD_SUB.PERIOD_COUNTER <= PERIOD_PC);
1938 end if;
1939 --NULL;
1940 END IF;
1941
1942 end if; --end of CRL check
1943
1944 --Added during DT Fix
1945 commit;
1946 --End of DT Fix
1947 end get_balance_group_begin;
1948
1949
1950 procedure get_balance_group_end
1951 (Book in varchar2,
1952 Distribution_Source_Book in varchar2,
1953 Period_PC in number,
1954 Earliest_PC in number,
1955 Period_Date in date,
1956 Additions_Date in date,
1957 Report_Type in varchar2,
1958 Balance_Type in varchar2,
1959 Begin_or_End in varchar2)
1960 is
1961
1962
1963 --Added during DT Fix
1964 PRAGMA AUTONOMOUS_TRANSACTION;
1965 --End of DT Fix
1966 P_Date date := Period_Date;
1967 A_Date date := Additions_Date;
1968 h_set_of_books_id number;
1969 h_reporting_flag varchar2(1);
1970 begin
1971
1972 -- get mrc related info
1973 begin
1974 --h_set_of_books_id := to_number(substrb(userenv('CLIENT_INFO'),45,10));
1975 select to_number(substrb(userenv('CLIENT_INFO'),45,10))
1976 into h_set_of_books_id from dual;
1977
1978 exception
1979 when others then
1980 h_set_of_books_id := null;
1981 end;
1982
1983 if (h_set_of_books_id is not null) then
1984 if not fa_cache_pkg.fazcsob
1985 (X_set_of_books_id => h_set_of_books_id,
1986 X_mrc_sob_type_code => h_reporting_flag) then
1987 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1988 end if;
1989 else
1990 h_reporting_flag := 'P';
1991 end if;
1992
1993 -- run only if CRL installed
1994 if ( nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') then
1995
1996 IF REPORT_TYPE NOT IN ('RESERVE') THEN
1997 if (h_reporting_flag = 'R') then
1998 INSERT INTO FA_BALANCES_REPORT_GT
1999 (Asset_ID,
2000 Distribution_CCID,
2001 Adjustment_CCID,
2002 Category_Books_Account,
2003 Source_Type_Code,
2004 Amount)
2005 SELECT
2006 DH.Asset_ID,
2007 -- DH.Code_Combination_ID,
2008 nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, DH.Code_Combination_ID),
2009 -- Changed for BMA1
2010 -- nvl(gad.asset_cost_acct_ccid,1127),
2011 gad.asset_cost_acct_ccid,
2012 null,
2013 DECODE(Report_Type,
2014 'RESERVE', DECODE(DD.Deprn_Source_Code,
2015 'D', Begin_or_End, 'ADDITION'),
2016 'REVAL RESERVE',
2017 DECODE(DD.Deprn_Source_Code,
2018 'D', Begin_or_End, 'ADDITION'),
2019 Begin_or_End),
2020 DECODE (Report_Type,
2021 'COST', decode(nvl(bk.group_asset_id,-2),-2,DD.Cost,bk.cost),
2022 'CIP COST', DD.Cost,
2023 'RESERVE', DD.Deprn_Reserve,
2024 'REVAL RESERVE', DD.Reval_Reserve)
2025 FROM
2026 FA_BOOKS_MRC_V BK,
2027 FA_CATEGORY_BOOKS CB,
2028 FA_ASSET_HISTORY AH,
2029 FA_DEPRN_DETAIL_MRC_V DD,
2030 FA_DISTRIBUTION_HISTORY DH,
2031 -- Commented by Prabakar
2032 fa_GROUP_ASSET_DEFAULT GAD
2033 WHERE
2034 -- Commented by Prabakar
2035 GAD.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
2036 AND GAD.GROUP_ASSET_ID = BK.GROUP_ASSET_ID
2037 -- This is to include only the Group Asset Members
2038 and bk.GROUP_ASSET_ID IS not NULL AND
2039 DH.Book_Type_Code = Distribution_Source_Book AND
2040 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
2041 A_Date) BETWEEN
2042 DH.Date_Effective AND
2043 NVL(DH.Date_Ineffective, SYSDATE)
2044 AND
2045 DD.Asset_ID = DH.Asset_ID AND
2046 DD.Book_Type_Code = Book AND
2047 DD.Distribution_ID = DH.Distribution_ID AND
2048 DD.Period_Counter <= Period_PC AND
2049 DECODE(Begin_or_End,
2050 'BEGIN', DD.Deprn_Source_Code, 'D') =
2051 DD.Deprn_Source_Code AND
2052 DD.Period_Counter =
2053 (SELECT MAX (SUB_DD.Period_Counter)
2054 FROM FA_DEPRN_DETAIL_MRC_V SUB_DD
2055 WHERE SUB_DD.Book_Type_Code = Book AND
2056 SUB_DD.Distribution_ID = DH.Distribution_ID AND
2057 SUB_DD.Period_Counter <= Period_PC)
2058 AND
2059 AH.Asset_ID = DH.Asset_ID AND
2060 ((AH.Asset_Type <> 'EXPENSED' AND
2061 Report_Type IN ('COST', 'CIP COST')) OR
2062 (AH.Asset_Type in ('CAPITALIZED','CIP') AND
2063 Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
2064 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
2065 A_Date) BETWEEN
2066 AH.Date_Effective AND
2067 NVL(AH.Date_Ineffective, SYSDATE)
2068 AND
2069 CB.Category_ID = AH.Category_ID AND
2070 CB.Book_Type_Code = Book
2071 AND
2072 BK.Book_Type_Code = Book AND
2073 BK.Asset_ID = DD.Asset_ID AND
2074 -- Commented by Prabakar
2075 ( bk.transaction_header_id_in
2076 = ( select min(fab.transaction_header_id_in) from fa_books_groups_mrc_v bg, fa_books_mrc_v fab
2077 where bg.group_asset_id = nvl(bk.group_asset_id,-2)
2078 and bg.book_type_code = fab.book_type_code
2079 and fab.transaction_header_id_in <= bg.transaction_header_id_in
2080 and nvl(fab.transaction_header_id_out,bg.transaction_header_id_in) >= bg.transaction_header_id_in
2081 and bg.period_counter = Period_pc + 1
2082 and fab.asset_id = bk.asset_id
2083 and fab.book_type_code = bk.book_type_code
2084 and bg.BEGINNING_BALANCE_FLAG is not null )
2085 )
2086 AND
2087 DECODE (Report_Type,
2088 'COST', DECODE (AH.Asset_Type,
2089 'CAPITALIZED', CB.Asset_Cost_Acct,
2090 null),
2091 'CIP COST',
2092 DECODE (AH.Asset_Type,
2093 'CIP', CB.CIP_Cost_Acct,
2094 null),
2095 'RESERVE', CB.Deprn_Reserve_Acct,
2096 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null;
2097 else
2098 INSERT INTO FA_BALANCES_REPORT_GT
2099 (Asset_ID,
2100 Distribution_CCID,
2101 Adjustment_CCID,
2102 Category_Books_Account,
2103 Source_Type_Code,
2104 Amount)
2105 SELECT
2106 DH.Asset_ID,
2107 -- DH.Code_Combination_ID,
2108 nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, DH.Code_Combination_ID),
2109 -- Changed for BMA1
2110 -- nvl(gad.asset_cost_acct_ccid,1127),
2111 gad.asset_cost_acct_ccid,
2112 null,
2113 DECODE(Report_Type,
2114 'RESERVE', DECODE(DD.Deprn_Source_Code,
2115 'D', Begin_or_End, 'ADDITION'),
2116 'REVAL RESERVE',
2117 DECODE(DD.Deprn_Source_Code,
2118 'D', Begin_or_End, 'ADDITION'),
2119 Begin_or_End),
2120 DECODE (Report_Type,
2121 'COST', decode(nvl(bk.group_asset_id,-2),-2,DD.Cost,bk.cost),
2122 'CIP COST', DD.Cost,
2123 'RESERVE', DD.Deprn_Reserve,
2124 'REVAL RESERVE', DD.Reval_Reserve)
2125 FROM
2126 FA_BOOKS BK,
2127 FA_CATEGORY_BOOKS CB,
2128 FA_ASSET_HISTORY AH,
2129 FA_DEPRN_DETAIL DD,
2130 FA_DISTRIBUTION_HISTORY DH,
2131 -- Commented by Prabakar
2132 fa_GROUP_ASSET_DEFAULT GAD
2133 WHERE
2134 -- Commented by Prabakar
2135 GAD.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
2136 AND GAD.GROUP_ASSET_ID = BK.GROUP_ASSET_ID
2137 -- This is to include only the Group Asset Members
2138 and bk.GROUP_ASSET_ID IS not NULL AND
2139 DH.Book_Type_Code = Distribution_Source_Book AND
2140 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
2141 A_Date) BETWEEN
2142 DH.Date_Effective AND
2143 NVL(DH.Date_Ineffective, SYSDATE)
2144 AND
2145 DD.Asset_ID = DH.Asset_ID AND
2146 DD.Book_Type_Code = Book AND
2147 DD.Distribution_ID = DH.Distribution_ID AND
2148 DD.Period_Counter <= Period_PC AND
2149 DECODE(Begin_or_End,
2150 'BEGIN', DD.Deprn_Source_Code, 'D') =
2151 DD.Deprn_Source_Code AND
2152 DD.Period_Counter =
2153 (SELECT MAX (SUB_DD.Period_Counter)
2154 FROM FA_DEPRN_DETAIL SUB_DD
2155 WHERE SUB_DD.Book_Type_Code = Book AND
2156 SUB_DD.Distribution_ID = DH.Distribution_ID AND
2157 SUB_DD.Period_Counter <= Period_PC)
2158 AND
2159 AH.Asset_ID = DH.Asset_ID AND
2160 ((AH.Asset_Type <> 'EXPENSED' AND
2161 Report_Type IN ('COST', 'CIP COST')) OR
2162 (AH.Asset_Type in ('CAPITALIZED','CIP') AND
2163 Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
2164 DECODE(DD.Deprn_Source_Code, 'D', P_Date,
2165 A_Date) BETWEEN
2166 AH.Date_Effective AND
2167 NVL(AH.Date_Ineffective, SYSDATE)
2168 AND
2169 CB.Category_ID = AH.Category_ID AND
2170 CB.Book_Type_Code = Book
2171 AND
2172 BK.Book_Type_Code = Book AND
2173 BK.Asset_ID = DD.Asset_ID AND
2174 -- Commented by Prabakar
2175 ( bk.transaction_header_id_in
2176 = ( select min(fab.transaction_header_id_in) from fa_books_groups bg, fa_books fab
2177 where bg.group_asset_id = nvl(bk.group_asset_id,-2)
2178 and bg.book_type_code = fab.book_type_code
2179 and fab.transaction_header_id_in <= bg.transaction_header_id_in
2180 and nvl(fab.transaction_header_id_out,bg.transaction_header_id_in) >= bg.transaction_header_id_in
2181 and bg.period_counter = Period_pc + 1
2182 and fab.asset_id = bk.asset_id
2183 and fab.book_type_code = bk.book_type_code
2184 and bg.BEGINNING_BALANCE_FLAG is not null )
2185 )
2186 AND
2187 DECODE (Report_Type,
2188 'COST', DECODE (AH.Asset_Type,
2189 'CAPITALIZED', CB.Asset_Cost_Acct,
2190 null),
2191 'CIP COST',
2192 DECODE (AH.Asset_Type,
2193 'CIP', CB.CIP_Cost_Acct,
2194 null),
2195 'RESERVE', CB.Deprn_Reserve_Acct,
2196 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null;
2197 end if;
2198
2199 ELSE
2200
2201 if (h_reporting_flag = 'R') then
2202 INSERT INTO FA_BALANCES_REPORT_GT
2203 (Asset_ID,
2204 Distribution_CCID,
2205 Adjustment_CCID,
2206 Category_Books_Account,
2207 Source_Type_Code,
2208 Amount)
2209 SELECT
2210 GAR.GROUP_ASSET_ID ASSET_ID,
2211 GAD.DEPRN_EXPENSE_ACCT_CCID ,
2212 GAD.DEPRN_RESERVE_ACCT_CCID ,
2213 null,
2214 /* DECODE(Report_Type,
2215 'RESERVE', DECODE(DD.Deprn_Source_Code,
2216 'D', Begin_or_End, 'ADDITION'),
2217 'REVAL RESERVE',
2218 DECODE(DD.Deprn_Source_Code,
2219 'D', Begin_or_End, 'ADDITION'),
2220 Begin_or_End),*/
2221 'END',
2222 DD.DEPRN_RESERVE
2223 FROM
2224 FA_DEPRN_SUMMARY_MRC_V DD,
2225 fa_GROUP_ASSET_RULES GAR,
2226 fa_GROUP_ASSET_DEFAULT GAD
2227 WHERE
2228 DD.BOOK_TYPE_CODE = book
2229 AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
2230 AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
2231 AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
2232 AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
2233 AND DD.PERIOD_COUNTER =
2234 (SELECT max (DD_SUB.PERIOD_COUNTER)
2235 FROM FA_DEPRN_DETAIL_MRC_V DD_SUB
2236 WHERE DD_SUB.BOOK_TYPE_CODE = book
2237 AND DD_SUB.ASSET_ID = GAR.GROUP_ASSET_ID
2238 AND DD_SUB.PERIOD_COUNTER <= PERIOD_PC);
2239 else
2240 INSERT INTO FA_BALANCES_REPORT_GT
2241 (Asset_ID,
2242 Distribution_CCID,
2243 Adjustment_CCID,
2244 Category_Books_Account,
2245 Source_Type_Code,
2246 Amount)
2247 SELECT
2248 GAR.GROUP_ASSET_ID ASSET_ID,
2249 GAD.DEPRN_EXPENSE_ACCT_CCID ,
2250 GAD.DEPRN_RESERVE_ACCT_CCID ,
2251 null,
2252 /* DECODE(Report_Type,
2253 'RESERVE', DECODE(DD.Deprn_Source_Code,
2254 'D', Begin_or_End, 'ADDITION'),
2255 'REVAL RESERVE',
2256 DECODE(DD.Deprn_Source_Code,
2257 'D', Begin_or_End, 'ADDITION'),
2258 Begin_or_End),*/
2259 'END',
2260 DD.DEPRN_RESERVE
2261 FROM
2262 FA_DEPRN_SUMMARY DD,
2263 fa_GROUP_ASSET_RULES GAR,
2264 fa_GROUP_ASSET_DEFAULT GAD
2265 WHERE
2266 DD.BOOK_TYPE_CODE = book
2267 AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
2268 AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
2269 AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
2270 AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
2271 AND DD.PERIOD_COUNTER =
2272 (SELECT max (DD_SUB.PERIOD_COUNTER)
2273 FROM FA_DEPRN_DETAIL DD_SUB
2274 WHERE DD_SUB.BOOK_TYPE_CODE = book
2275 AND DD_SUB.ASSET_ID = GAR.GROUP_ASSET_ID
2276 AND DD_SUB.PERIOD_COUNTER <= PERIOD_PC);
2277 end if;
2278 END IF;
2279
2280 end if; -- end of CRL check
2281
2282 --Added during DT Fix
2283 commit;
2284 --End of DT Fix
2285 end get_balance_group_end;
2286
2287
2288 procedure Get_Deprn_Effects
2289 (Book in varchar2,
2290 Distribution_Source_Book in varchar2,
2291 Period1_PC in number,
2292 Period2_PC in number,
2293 Report_Type in varchar2)
2294 is
2295
2296 --Added during DT Fix
2297 PRAGMA AUTONOMOUS_TRANSACTION;
2298 --End of DT Fix
2299 h_set_of_books_id number;
2300 h_reporting_flag varchar2(1);
2301 begin
2302
2303 -- get mrc related info
2304 begin
2305 -- h_set_of_books_id := to_number(substrb(userenv('CLIENT_INFO'),45,10));
2306 select to_number(substrb(userenv('CLIENT_INFO'),45,10))
2307 into h_set_of_books_id from dual;
2308
2309 exception
2310 when others then
2311 h_set_of_books_id := null;
2312 end;
2313
2314 if (h_set_of_books_id is not null) then
2315 if not fa_cache_pkg.fazcsob
2316 (X_set_of_books_id => h_set_of_books_id,
2317 X_mrc_sob_type_code => h_reporting_flag) then
2318 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2319 end if;
2320 else
2321 h_reporting_flag := 'P';
2322 end if;
2323
2324 if (h_reporting_flag = 'R') then
2325 INSERT INTO FA_BALANCES_REPORT_GT
2326 (Asset_ID,
2327 Distribution_CCID,
2328 Adjustment_CCID,
2329 Category_Books_Account,
2330 Source_Type_Code,
2331 Amount)
2332 SELECT
2333 DH.Asset_ID,
2334 DH.Code_Combination_ID,
2335 null,
2336 DECODE (RT.Lookup_Code,
2337 'RESERVE', CB.Deprn_Reserve_Acct,
2338 'REVAL RESERVE', CB.Reval_Reserve_Acct),
2339 DECODE(DD.Deprn_Source_Code,
2340 'D', 'DEPRECIATION', 'ADDITION'),
2341 SUM (DECODE (RT.Lookup_Code,
2342 'RESERVE', DD.Deprn_Amount - decode(ADJ.debit_credit_flag,'DR',1,-1)
2343 * nvl(ADJ.adjustment_amount,0),
2344 'REVAL RESERVE', -DD.Reval_Amortization))
2345 FROM
2346 FA_LOOKUPS_B RT,
2347 FA_CATEGORY_BOOKS CB,
2348 FA_DISTRIBUTION_HISTORY DH,
2349 FA_ASSET_HISTORY AH,
2350 FA_DEPRN_DETAIL_MRC_V DD,
2351 FA_DEPRN_PERIODS_MRC_V DP,
2352 FA_ADJUSTMENTS_MRC_V ADJ
2353 WHERE
2354 DH.Book_Type_Code = Distribution_Source_Book
2355 AND
2356 AH.Asset_ID = DH.Asset_ID AND
2357 AH.Asset_Type in ( 'CAPITALIZED' ,'CIP') AND
2358 AH.Date_Effective <
2359 nvl(DH.date_ineffective, sysdate) AND
2360 nvl(DH.date_ineffective, sysdate) <=
2361 NVL(AH.Date_Ineffective, SYSDATE)
2362 AND
2363 CB.Category_ID = AH.Category_ID AND
2364 CB.Book_Type_Code = Book
2365 AND
2366 ((DD.Deprn_Source_Code = 'B'
2367 AND (DD.Period_Counter+1) < Period2_PC) OR
2368 (DD.Deprn_Source_Code = 'D')) AND
2369 DD.Book_Type_Code||'' = Book AND
2370 DD.Asset_ID = DH.Asset_ID AND
2371 DD.Distribution_ID = DH.Distribution_ID AND
2372 DD.Period_Counter between
2373 Period1_PC and Period2_PC
2374 AND
2375 DP.Book_Type_Code = DD.Book_Type_Code AND
2376 DP.Period_Counter = DD.Period_Counter
2377 AND
2378 RT.Lookup_Type = 'REPORT TYPE' AND
2379 DECODE (RT.Lookup_Code,
2380 'RESERVE', CB.Deprn_Reserve_Acct,
2381 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
2382 AND
2383 (DECODE (RT.Lookup_Code,
2384 'RESERVE', DD.Deprn_Amount,
2385 'REVAL RESERVE', NVL(DD.Reval_Amortization,0)) <> 0 OR
2386 DECODE (RT.Lookup_Code,
2387 'RESERVE', DD.Deprn_Amount - nvl(DD.deprn_adjustment_amount,0),
2388 'REVAL RESERVE', NVL(DD.Reval_Amortization,0)) <> 0)
2389 AND ADJ.asset_id(+) = DD.asset_id AND
2390 ADJ.book_type_code(+) = DD.book_type_code AND
2391 ADJ.period_counter_created(+) = DD.period_counter AND
2392 ADJ.distribution_id(+) = DD.distribution_id AND
2393 ADJ.source_type_code(+) = 'REVALUATION' AND
2394 ADJ.adjustment_type(+) = 'EXPENSE' AND
2395 ADJ.adjustment_amount(+) <> 0
2396 GROUP BY
2397 DH.Asset_ID,
2398 DH.Code_Combination_ID,
2399 DECODE (RT.Lookup_Code,
2400 'RESERVE', CB.Deprn_Reserve_Acct,
2401 'REVAL RESERVE', CB.Reval_Reserve_Acct),
2402 DD.Deprn_Source_Code;
2403 else
2404 INSERT INTO FA_BALANCES_REPORT_GT
2405 (Asset_ID,
2406 Distribution_CCID,
2407 Adjustment_CCID,
2408 Category_Books_Account,
2409 Source_Type_Code,
2410 Amount)
2411 SELECT
2412 DH.Asset_ID,
2413 DH.Code_Combination_ID,
2414 null,
2415 DECODE (RT.Lookup_Code,
2416 'RESERVE', CB.Deprn_Reserve_Acct,
2417 'REVAL RESERVE', CB.Reval_Reserve_Acct),
2418 DECODE(DD.Deprn_Source_Code,
2419 'D', 'DEPRECIATION', 'ADDITION'),
2420 SUM (DECODE (RT.Lookup_Code,
2421 'RESERVE', DD.Deprn_Amount - decode(ADJ.debit_credit_flag,'DR',1,-1)
2422 * nvl(ADJ.adjustment_amount,0),
2423 'REVAL RESERVE', -DD.Reval_Amortization))
2424 FROM
2425 FA_LOOKUPS_B RT,
2426 FA_CATEGORY_BOOKS CB,
2427 FA_DISTRIBUTION_HISTORY DH,
2428 FA_ASSET_HISTORY AH,
2429 FA_DEPRN_DETAIL DD,
2430 FA_DEPRN_PERIODS DP,
2431 FA_ADJUSTMENTS ADJ
2432 WHERE
2433 DH.Book_Type_Code = Distribution_Source_Book
2434 AND
2435 AH.Asset_ID = DH.Asset_ID AND
2436 AH.Asset_Type in ( 'CAPITALIZED','CIP') AND
2437 AH.Date_Effective <
2438 nvl(DH.date_ineffective, sysdate) AND
2439 nvl(DH.date_ineffective, sysdate) <=
2440 NVL(AH.Date_Ineffective, SYSDATE)
2441 AND
2442 CB.Category_ID = AH.Category_ID AND
2443 CB.Book_Type_Code = Book
2444 AND
2445 ((DD.Deprn_Source_Code = 'B'
2446 AND (DD.Period_Counter+1) < Period2_PC) OR
2447 (DD.Deprn_Source_Code = 'D')) AND
2448 DD.Book_Type_Code||'' = Book AND
2449 DD.Asset_ID = DH.Asset_ID AND
2450 DD.Distribution_ID = DH.Distribution_ID AND
2451 DD.Period_Counter between
2452 Period1_PC and Period2_PC
2453 AND
2454 DP.Book_Type_Code = DD.Book_Type_Code AND
2455 DP.Period_Counter = DD.Period_Counter
2456 AND
2457 RT.Lookup_Type = 'REPORT TYPE' AND
2458 DECODE (RT.Lookup_Code,
2459 'RESERVE', CB.Deprn_Reserve_Acct,
2460 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
2461 AND
2462 (DECODE (RT.Lookup_Code,
2463 'RESERVE', DD.Deprn_Amount,
2464 'REVAL RESERVE', NVL(DD.Reval_Amortization,0)) <> 0 OR
2465 DECODE (RT.Lookup_Code,
2466 'RESERVE', DD.Deprn_Amount - nvl(DD.deprn_adjustment_amount,0),
2467 'REVAL RESERVE', NVL(DD.Reval_Amortization,0)) <> 0)
2468 AND ADJ.asset_id(+) = DD.asset_id AND
2469 ADJ.book_type_code(+) = DD.book_type_code AND
2470 ADJ.period_counter_created(+) = DD.period_counter AND
2471 ADJ.distribution_id(+) = DD.distribution_id AND
2472 ADJ.source_type_code(+) = 'REVALUATION' AND
2473 ADJ.adjustment_type(+) = 'EXPENSE' AND
2474 ADJ.adjustment_amount(+) <> 0
2475 GROUP BY
2476 DH.Asset_ID,
2477 DH.Code_Combination_ID,
2478 DECODE (RT.Lookup_Code,
2479 'RESERVE', CB.Deprn_Reserve_Acct,
2480 'REVAL RESERVE', CB.Reval_Reserve_Acct),
2481 DD.Deprn_Source_Code;
2482 end if;
2483
2484 -- run only if CRL installed
2485 if ( nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') then
2486
2487 -- Get the Group Depreciation Effects
2488
2489 if (h_reporting_flag = 'R') then
2490 INSERT INTO FA_BALANCES_REPORT_GT
2491 (Asset_ID,
2492 Distribution_CCID,
2493 Adjustment_CCID,
2494 Category_Books_Account,
2495 Source_Type_Code,
2496 Amount)
2497 SELECT
2498 DD.Asset_ID,
2499 GAD.DEPRN_EXPENSE_ACCT_CCID ,
2500 GAD.DEPRN_RESERVE_ACCT_CCID,
2501 null,
2502 'DEPRECIATION',
2503 SUM ( DD.Deprn_Amount)
2504 FROM
2505 FA_DEPRN_SUMMARY_MRC_V DD,
2506 fa_GROUP_ASSET_RULES GAR,
2507 fa_GROUP_ASSET_DEFAULT GAD
2508 WHERE
2509 DD.BOOK_TYPE_CODE = book
2510 AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
2511 AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
2512 AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
2513 AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
2514 AND DD.PERIOD_COUNTER between
2515 Period1_PC and Period2_PC
2516 GROUP BY
2517 DD.Asset_ID,
2518 GAD.DEPRN_EXPENSE_ACCT_CCID ,
2519 GAD.DEPRN_RESERVE_ACCT_CCID ,
2520 null,
2521 'DEPRECIATION' ;
2522 else
2523 INSERT INTO FA_BALANCES_REPORT_GT
2524 (Asset_ID,
2525 Distribution_CCID,
2526 Adjustment_CCID,
2527 Category_Books_Account,
2528 Source_Type_Code,
2529 Amount)
2530 SELECT
2531 DD.Asset_ID,
2532 GAD.DEPRN_EXPENSE_ACCT_CCID ,
2533 GAD.DEPRN_RESERVE_ACCT_CCID,
2534 null,
2535 'DEPRECIATION',
2536 SUM ( DD.Deprn_Amount)
2537 FROM
2538 FA_DEPRN_SUMMARY DD,
2539 fa_GROUP_ASSET_RULES GAR,
2540 fa_GROUP_ASSET_DEFAULT GAD
2541 WHERE
2542 DD.BOOK_TYPE_CODE = book
2543 AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
2544 AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
2545 AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
2546 AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
2547 AND DD.PERIOD_COUNTER between
2548 Period1_PC and Period2_PC
2549 GROUP BY
2550 DD.Asset_ID,
2551 GAD.DEPRN_EXPENSE_ACCT_CCID ,
2552 GAD.DEPRN_RESERVE_ACCT_CCID ,
2553 null,
2554 'DEPRECIATION' ;
2555 end if;
2556 end if; -- end of CRL check
2557 --Added during DT Fix
2558 commit;
2559 --End of DT Fix
2560 end Get_Deprn_Effects;
2561
2562
2563 procedure Insert_Info
2564 (Book in varchar2,
2565 Start_Period_Name in varchar2,
2566 End_Period_Name in varchar2,
2567 Report_Type in varchar2,
2568 Adj_Mode in varchar2)
2569 is
2570 --Added during DT Fix
2571 PRAGMA AUTONOMOUS_TRANSACTION;
2572 --End of DT Fix
2573
2574 Period1_PC number;
2575 Period1_POD date;
2576 Period1_PCD date;
2577 Period2_PC number;
2578 Period2_PCD date;
2579 Distribution_Source_Book varchar2(15);
2580 Balance_Type varchar2(2);
2581
2582 h_set_of_books_id number;
2583 h_reporting_flag varchar2(1);
2584
2585 begin
2586
2587 -- get mrc related info
2588 begin
2589 --h_set_of_books_id := to_number(substrb(userenv('CLIENT_INFO'),45,10));
2590 select to_number(substrb(userenv('CLIENT_INFO'),45,10))
2591 into h_set_of_books_id from dual;
2592
2593 exception
2594 when others then
2595 h_set_of_books_id := null;
2596 end;
2597
2598 if (h_set_of_books_id is not null) then
2599 if not fa_cache_pkg.fazcsob
2600 (X_set_of_books_id => h_set_of_books_id,
2601 X_mrc_sob_type_code => h_reporting_flag) then
2602 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2603 end if;
2604 else
2605 h_reporting_flag := 'P';
2606 end if;
2607
2608
2609 if (h_reporting_flag = 'R') then
2610 SELECT
2611 P1.Period_Counter,
2612 P1.Period_Open_Date,
2613 NVL(P1.Period_Close_Date, SYSDATE),
2614 P2.Period_Counter,
2615 NVL(P2.Period_Close_Date, SYSDATE),
2616 BC.Distribution_Source_Book
2617 INTO
2618 Period1_PC,
2619 Period1_POD,
2620 Period1_PCD,
2621 Period2_PC,
2622 Period2_PCD,
2623 Distribution_Source_Book
2624 FROM
2625 FA_DEPRN_PERIODS_MRC_V P1,
2626 FA_DEPRN_PERIODS_MRC_V P2,
2627 FA_BOOK_CONTROLS_MRC_V BC
2628 WHERE
2629 BC.Book_Type_Code = Book
2630 AND
2631 P1.Book_Type_Code = Book AND
2632 P1.Period_Name = Start_Period_Name
2633 AND
2634 P2.Book_Type_Code = Book AND
2635 P2.Period_Name = End_Period_Name;
2636 else
2637 SELECT
2638 P1.Period_Counter,
2639 P1.Period_Open_Date,
2640 NVL(P1.Period_Close_Date, SYSDATE),
2641 P2.Period_Counter,
2642 NVL(P2.Period_Close_Date, SYSDATE),
2643 BC.Distribution_Source_Book
2644 INTO
2645 Period1_PC,
2646 Period1_POD,
2647 Period1_PCD,
2648 Period2_PC,
2649 Period2_PCD,
2650 Distribution_Source_Book
2651 FROM
2652 FA_DEPRN_PERIODS P1,
2653 FA_DEPRN_PERIODS P2,
2654 FA_BOOK_CONTROLS BC
2655 WHERE
2656 BC.Book_Type_Code = Book
2657 AND
2658 P1.Book_Type_Code = Book AND
2659 P1.Period_Name = Start_Period_Name
2660 AND
2661 P2.Book_Type_Code = Book AND
2662 P2.Period_Name = End_Period_Name;
2663 end if;
2664
2665 if (Report_Type = 'RESERVE' or Report_Type = 'REVAL RESERVE') then
2666 Balance_Type := 'CR';
2667 else
2668 Balance_Type := 'DR';
2669 end if;
2670
2671 /* DELETE FROM FA_BALANCES_REPORT_GT; */
2672
2673 /*This section of code needs to be replaced due to the fact that in 11.5 the
2674 FA_LOOKUPS table has been split into two tables: FA_LOOKUPS_B and
2675 FA_LOOKUPS_TL . FA_LOOKUPS is a synonym for a view of a join of these two
2676 tables. So Inserts and Deletes wont work on FA_LOOKUPS, and instead must be
2677 performed on both tables. Changes made by cbachand, 5/25/99
2678 DELETE FROM FA_LOOKUPS
2679 WHERE LOOKUP_TYPE = 'REPORT TYPE';
2680
2681 INSERT INTO FA_LOOKUPS
2682 (lookup_type,
2683 lookup_code,
2684 last_updated_by,
2685 last_update_date,
2686 meaning,
2687 enabled_flag)
2688 VALUES
2689 ('REPORT TYPE',
2690 Report_Type,
2691 1,
2692 SYSDATE,
2693 Report_Type,
2694 'Y'); */
2695
2696
2697 DELETE FROM FA_LOOKUPS_B
2698 WHERE LOOKUP_TYPE = 'REPORT TYPE'
2699 AND LOOKUP_CODE = Report_Type;
2700
2701
2702 DELETE FROM FA_LOOKUPS_TL
2703 WHERE LOOKUP_TYPE = 'REPORT TYPE'
2704 AND LOOKUP_CODE = Report_Type;
2705
2706 INSERT INTO FA_LOOKUPS_B
2707 (LOOKUP_TYPE,
2708 LOOKUP_CODE,
2709 LAST_UPDATED_BY,
2710 LAST_UPDATE_DATE,
2711 ENABLED_FLAG)
2712 VALUES
2713 ('REPORT TYPE',
2714 Report_Type,
2715 1,
2716 SYSDATE,
2717 'Y');
2718
2719 INSERT INTO FA_LOOKUPS_TL
2720 (LOOKUP_TYPE,
2721 LOOKUP_CODE,
2722 MEANING,
2723 LAST_UPDATE_DATE,
2724 LAST_UPDATED_BY,
2725 LANGUAGE,
2726 SOURCE_LANG)
2727 SELECT
2728 'REPORT TYPE',
2729 Report_Type,
2730 Report_Type,
2731 SYSDATE,
2732 1,
2733 L.LANGUAGE_CODE,
2734 userenv('LANG')
2735 FROM FND_LANGUAGES L
2736 WHERE L.INSTALLED_FLAG in ('I', 'B')
2737 AND NOT EXISTS
2738 (SELECT NULL
2739 FROM FA_LOOKUPS_TL T
2740 WHERE T.LOOKUP_TYPE = 'REPORT TYPE'
2741 AND T.LOOKUP_CODE = Report_Type
2742 AND T.LANGUAGE = L.LANGUAGE_CODE);
2743
2744 /* Get Beginning Balance */
2745 /* Use Period1_PC-1, to get balance as of end of period immediately
2746 preceding Period1_PC */
2747 Get_Balance (Book, Distribution_Source_Book,
2748 Period1_PC-1, Period1_PC-1, Period1_POD, Period1_PCD,
2749 Report_Type, Balance_Type,
2750 'BEGIN');
2751
2752 -- run only if CRL installed
2753 if ( nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') then
2754 Get_Balance_group_begin (Book, Distribution_Source_Book,
2755 Period1_PC - 1, Period1_PC-1, Period1_POD, Period1_PCD,
2756 Report_Type, Balance_Type,
2757 'BEGIN');
2758 end if;
2759
2760 /* Get Ending Balance */
2761 Get_Balance (Book, Distribution_Source_Book,
2762 Period2_PC, Period1_PC-1, Period2_PCD, Period2_PCD,
2763 Report_Type, Balance_Type,
2764 'END');
2765
2766 -- run only if CRL installed
2767 if ( nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') then
2768 Get_Balance_group_end (Book, Distribution_Source_Book,
2769 Period2_PC, Period1_PC-1, Period2_PCD, Period2_PCD,
2770 Report_Type, Balance_Type,
2771 'END');
2772 end if;
2773
2774 Get_Adjustments (Book, Distribution_Source_Book,
2775 Period1_PC, Period2_PC,
2776 Report_Type, Balance_Type);
2777
2778 -- run only if CRL installed
2779 if ( nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') then
2780 Get_Adjustments_for_group (Book, Distribution_Source_Book,
2781 Period1_PC, Period2_PC,
2782 Report_Type, Balance_Type);
2783 end if;
2784
2785 if (Report_Type = 'RESERVE' or Report_Type = 'REVAL RESERVE') then
2786 Get_Deprn_Effects (Book, Distribution_Source_Book,
2787 Period1_PC, Period2_PC,
2788 Report_Type);
2789 end if;
2790 --Added during DT Fix
2791 commit;
2792 --End of DT Fix
2793
2794 end Insert_Info;
2795
2796
2797 END FA_FASRSVES_XMLP_PKG ;
2798