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