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