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