[Home] [Help]
PACKAGE BODY: APPS.IGI_IGIIARRD_XMLP_PKG
Source
1 PACKAGE BODY IGI_IGIIARRD_XMLP_PKG AS
2 /* $Header: IGIIARRDB.pls 120.0.12010000.1 2008/07/29 08:58:44 appldev ship $ */
3
4 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
5 BEGIN
6 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
7 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
8 DO_INSERT:=DO_INSERTFORMULA();
9 RETURN (TRUE);
10 END BEFOREREPORT;
11
12 FUNCTION AFTERREPORT RETURN BOOLEAN IS
13 BEGIN
14 ROLLBACK;
15 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
16 RETURN (TRUE);
17 END AFTERREPORT;
18 procedure Debug_print(p_char varchar2) IS
19 l_log varchar2(30);
20 l_out varchar2(30);
21 begin
22 --fnd_file.get_names(l_log,l_out);
23 fnd_file.put_line(1,p_char);
24 --srw.message(1000,p_char);
25 null;
26 end;
27
28
29 procedure FDRCSID(idstring in varchar2) is
30 BEGIN
31 IF (idstring is NULL) THEN
32 NULL;
33 END IF;
34 -- FDRCSID('$Header: IGIIARRDB.pls 120.0.12010000.1 2008/07/29 08:58:44 appldev ship $');
35 END;
36
37
38 PROCEDURE Get_Adjustments (
39 p_book varchar2,
40 p_distribution_source_book varchar2,
41 p_period1_pc number,
42 p_period2_pc number,
43 p_report_type varchar2,
44 p_balance_type varchar2)
45 IS
46 l_account_type varchar2(25);
47 l_balance_type varchar2(3);
48 l_data_source varchar2(30);
49 l_display_order number(5);
50 h_set_of_books_id number;
51 BEGIN
52
53 BEGIN
54 select set_of_books_id
55 into h_set_of_books_id
56 from fa_book_controls
57 where book_type_code = p_book;
58 EXCEPTION
59 WHEN OTHERS THEN
60 h_set_of_books_id := NULL;
61 Debug_Print('Error in fetching set_of_books_id :'||sqlerrm);
62 END;
63
64 debug_print('Parameters to Get_Adjustments');
65 debug_print('Book :'|| p_book);
66 debug_print('Distribution Source Book :'|| p_distribution_source_book);
67 debug_print('p_period1_pc :'||p_period1_pc);
68 debug_print('p_period2_pc :'||p_period2_pc);
69 debug_print('p_report_type :'||p_report_type);
70 debug_print('p_balance_type :'||p_balance_type);
71
72 IF (p_report_type = 'COST' OR p_report_type = 'RESERVE') THEN
73
74 INSERT INTO IGI_IAC_BALANCES_REPORT
75 (Asset_ID,
76 Distribution_CCID,
77 Adjustment_CCID,
78 Category_Books_Account,
79 Source_Type_Code,
80 Amount,
81 Data_Source,
82 Display_order)
83 SELECT
84 DH.Asset_ID,
85 DH.Code_Combination_ID,
86 lines.code_combination_id, --AJ.Code_Combination_ID,
87 null,
88 AJ.Source_Type_Code,
89 SUM (DECODE (AJ.Debit_Credit_Flag, p_Balance_Type, 1, -1) *
90 AJ.Adjustment_Amount),
91 'FA',
92 1
93 FROM
94 FA_DISTRIBUTION_HISTORY DH,
95 FA_TRANSACTION_HEADERS TH,
96 FA_ASSET_HISTORY AH,
97 FA_ADJUSTMENTS AJ
98
99 /* SLA Changes */
100 ,xla_ae_headers headers
101 ,xla_ae_lines lines
102 ,xla_distribution_links links
103 WHERE
104 DH.Book_Type_Code = p_Distribution_Source_Book AND
105 AJ.Asset_ID = DH.Asset_ID AND
106 AJ.Book_Type_Code = p_Book AND
107 AJ.Distribution_ID = DH.Distribution_ID AND
108 AJ.Adjustment_Type in
109 (p_Report_Type, DECODE(p_Report_Type,
110 'REVAL RESERVE', 'REVAL AMORT')) AND
111 AJ.Period_Counter_Created BETWEEN
112 p_Period1_PC AND p_Period2_PC AND
113 TH.Transaction_Header_ID = AJ.Transaction_Header_ID AND
114 AH.Asset_ID = DH.Asset_ID AND
115 ((AH.Asset_Type <> 'EXPENSED' AND
116 p_Report_Type IN ('COST', 'OP EXPENSE')) OR
117 (AH.Asset_Type = 'CAPITALIZED' AND
118 p_Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
119 TH.Transaction_Header_ID BETWEEN
120 AH.Transaction_Header_ID_In AND
121 NVL (AH.Transaction_Header_ID_Out - 1,
122 TH.Transaction_Header_ID) AND
123 (DECODE (p_report_type, AJ.Adjustment_Type, 1, 0) *
124 AJ.Adjustment_Amount) <> 0
125
126 /* SLA Changes */
127 and links.Source_distribution_id_num_1 = aj.transaction_header_id
128 and links.Source_distribution_id_num_2 = aj.adjustment_line_id
129 and links.application_id = 140
130 and links.source_distribution_type = 'TRX'
131 and headers.application_id = 140
132 and headers.ae_header_id = links.ae_header_id
133 and headers.ledger_id = h_set_of_books_id
134 and lines.ae_header_id = links.ae_header_id
135 and lines.ae_line_num = links.ae_line_num
136 and lines.application_id = 140
137
138 GROUP BY
139 DH.Asset_ID,
140 DH.Code_Combination_ID,
141 lines.code_combination_id, --AJ.Code_Combination_ID,
142 AJ.Source_Type_Code;
143
144 END IF;
145
146 IF (p_report_type = 'COST') THEN
147
148 INSERT INTO IGI_IAC_BALANCES_REPORT
149 (Asset_ID,
150 Distribution_CCID,
151 Adjustment_CCID,
152 Category_Books_Account,
153 Source_Type_Code,
154 Amount,
155 Data_Source,
156 Display_order)
157 SELECT
158 DH.Asset_ID,
159 DH.Code_Combination_ID,
160 AJ.Code_Combination_ID,
161 null,
162 DECODE(TH.Transaction_Type_Code,
163 'FULL RETIREMENT','RETIREMENT',
164 'PARTIAL RETIRE','RETIREMENT',
165 'REINSTATEMENT','RETIREMENT',
166 'REVALUATION',DECODE(TH.Transaction_sub_Type,
167 'IMPLEMENTATION','ADDITION',
168 TH.Transaction_Type_Code),
169 TH.Transaction_Type_Code),
170 SUM (DECODE (AJ.Dr_Cr_Flag, p_Balance_Type, 1, -1) *
171 AJ.Amount),
172 'IAC',
173 2
174 FROM
175 FA_DISTRIBUTION_HISTORY DH,
176 IGI_IAC_TRANSACTION_HEADERS TH,
177 IGI_IAC_ADJUSTMENTS AJ
178 WHERE
179 DH.Book_Type_Code = p_Distribution_Source_Book AND
180 AJ.Asset_ID = DH.Asset_ID AND
181 AJ.Book_Type_Code = p_Book AND
182 AJ.Distribution_ID = DH.Distribution_ID AND
183 AJ.Adjustment_Type = p_Report_Type AND
184 AJ.Period_Counter BETWEEN
185 p_Period1_PC AND p_Period2_PC AND
186 AJ.transfer_to_gl_flag <> 'N' AND
187 TH.adjustment_ID = AJ.adjustment_ID
188 GROUP BY
189 DH.Asset_ID,
190 DH.Code_Combination_ID,
191 AJ.Code_Combination_ID,
192 TH.Transaction_Type_Code,
193 TH.Transaction_sub_Type;
194
195 END IF;
196
197 IF (p_report_type = 'BL RESERVE' OR p_report_type = 'RESERVE') THEN
198
199 INSERT INTO IGI_IAC_BALANCES_REPORT
200 (Asset_ID,
201 Distribution_CCID,
202 Adjustment_CCID,
203 Category_Books_Account,
204 Source_Type_Code,
205 Amount,
206 Data_Source,
207 Display_order)
208 SELECT
209 DH.Asset_ID,
210 DH.Code_Combination_ID,
211 AJ.Code_Combination_ID,
212 null,
213 DECODE(TH.Transaction_Type_Code,
214 'FULL RETIREMENT','RETIREMENT',
215 'PARTIAL RETIRE','RETIREMENT',
216 'REINSTATEMENT','RETIREMENT',
217 'REVALUATION',DECODE(TH.Transaction_sub_Type,
218 'IMPLEMENTATION','DEPRECIATION',
219 TH.Transaction_Type_Code),
220 TH.Transaction_Type_Code),
221 SUM (DECODE (AJ.Dr_Cr_Flag, p_Balance_Type, 1, -1) *
222 AJ.Amount),
223 'IAC',
224 2
225 FROM
226 FA_DISTRIBUTION_HISTORY DH,
227 IGI_IAC_TRANSACTION_HEADERS TH,
228 IGI_IAC_ADJUSTMENTS AJ
229 WHERE
230 DH.Book_Type_Code = p_Distribution_Source_Book AND
231 AJ.Asset_ID = DH.Asset_ID AND
232 AJ.Book_Type_Code = p_Book AND
233 AJ.Distribution_ID = DH.Distribution_ID AND
234 AJ.Adjustment_Type = p_Report_Type AND
235 AJ.Period_Counter BETWEEN
236 p_Period1_PC AND p_Period2_PC AND
237 AJ.transfer_to_gl_flag <> 'N' AND
238 TH.adjustment_ID = AJ.adjustment_ID
239 GROUP BY
240 DH.Asset_ID,
241 DH.Code_Combination_ID,
242 AJ.Code_Combination_ID,
243 TH.Transaction_Type_Code,
244 TH.Transaction_sub_type;
245
246 END IF;
247
248 IF (p_report_type = 'REVAL RESERVE' OR p_report_type = 'OP EXPENSE') THEN
249
250 debug_print('INSIDE COST for reval reserve');
251
252 FOR counter IN 1..4 LOOP
253
254 IF counter = 1 THEN
255 IF p_report_type = 'REVAL RESERVE' THEN
256 l_account_type := 'COST';
257 l_balance_type := 'DR';
258 l_data_source := 'Cost';
259 l_display_order := 1;
260 ELSIF p_report_type = 'OP EXPENSE' THEN
261 l_account_type := 'COST' ;
262 l_balance_type := 'CR' ;
263 l_data_source := 'Cost';
264 l_display_order := 1;
265 END IF ;
266 ELSIF counter = 2 THEN
267 l_account_type := 'BL RESERVE';
268 l_balance_type := p_balance_type;
269 l_data_source := 'Backlog';
270 IF p_report_type = 'REVAL RESERVE' THEN
271 l_display_order := 3;
272 ELSIF p_report_type = 'OP EXPENSE' THEN
273 l_display_order := 2;
274 END IF;
275 ELSIF counter = 3 THEN
276 l_account_type := 'GENERAL FUND';
277 l_balance_type := p_balance_type;
278 l_data_source := 'General Fund';
279 l_display_order := 2;
280 ELSE
281 l_account_type := p_report_type;
282 l_balance_type := p_balance_type;
283 l_data_source := 'Net';
284 IF p_report_type = 'REVAL RESERVE' THEN
285 l_display_order := 4;
286 ELSIF p_report_type = 'OP EXPENSE' THEN
287 l_display_order := 3;
288 END IF;
289
290 END IF;
291
292
293 IF (l_account_type IN ('COST','GENERAL FUND','BL RESERVE') AND
294 NOT(p_report_type = 'OP EXPENSE' AND l_account_type = 'GENERAL FUND')) THEN
295
296 INSERT INTO IGI_IAC_BALANCES_REPORT
297 (Asset_ID,
298 Distribution_CCID,
299 Adjustment_CCID,
300 Category_Books_Account,
301 Source_Type_Code,
302 Amount,
303 Data_Source,
304 Display_order)
305 SELECT
306 DH.Asset_ID,
307 DH.Code_Combination_ID,
308 AJ.Report_ccid,
309 null,
310 TH.Transaction_Type_Code,
311 SUM (DECODE (AJ.Dr_Cr_Flag, l_Balance_Type, 1, -1) * AJ.Amount),
312 l_data_source,
313 l_display_order
314 FROM
315 FA_DISTRIBUTION_HISTORY DH,
316 IGI_IAC_TRANSACTION_HEADERS TH,
317 IGI_IAC_ADJUSTMENTS AJ
318 WHERE
319 DH.Book_Type_Code = p_Distribution_Source_Book AND
320 AJ.Asset_ID = DH.Asset_ID AND
321 AJ.Book_Type_Code = p_Book AND
322 AJ.Distribution_ID = DH.Distribution_ID AND
323 AJ.Adjustment_Type = l_account_type AND
324 AJ.Period_Counter BETWEEN
325 p_Period1_PC AND p_Period2_PC AND
326 AJ.transfer_to_gl_flag <> 'N' AND
327 TH.adjustment_ID = AJ.adjustment_ID AND
328 TH.Transaction_type_code NOT IN ('PARTIAL RETIRE', 'FULL RETIREMENT', 'REINSTATEMENT') AND
329 AJ.adjustment_offset_type = p_report_type
330 GROUP BY
331 DH.Asset_ID,
332 DH.Code_Combination_ID,
333 AJ.report_ccid,
334 TH.Transaction_Type_Code;
335
336 END IF;
337
338 /* IF l_account_type = 'BL RESERVE' THEN
339
340 INSERT INTO IGI_IAC_BALANCES_REPORT
341 (Asset_ID,
342 Distribution_CCID,
343 Adjustment_CCID,
344 Category_Books_Account,
345 Source_Type_Code,
346 Amount,
347 Data_Source,
348 Display_order)
349 SELECT
350 DH.Asset_ID,
351 DH.Code_Combination_ID,
352 SUB_AJ.Code_Combination_ID,
353 null,
354 TH.Transaction_Type_Code,
355 SUM (DECODE (AJ.Dr_Cr_Flag, l_Balance_Type, 1, -1) * AJ.Amount),
356 l_data_source,
357 l_display_order
358 FROM
359 FA_DISTRIBUTION_HISTORY DH,
360 IGI_IAC_TRANSACTION_HEADERS TH,
361 IGI_IAC_ADJUSTMENTS AJ,
362 IGI_IAC_ADJUSTMENTS SUB_AJ
363 WHERE
364 DH.Book_Type_Code = p_Distribution_Source_Book AND
365 AJ.Asset_ID = DH.Asset_ID AND
366 AJ.Book_Type_Code = p_Book AND
367 AJ.Distribution_ID = DH.Distribution_ID AND
368 AJ.Adjustment_Type = l_account_type AND
369 AJ.Period_Counter BETWEEN
370 p_Period1_PC AND p_Period2_PC AND
371 AJ.transfer_to_gl_flag <> 'N' AND
372 TH.adjustment_ID = AJ.adjustment_ID AND
373 TH.Transaction_type_code NOT IN ('PARTIAL RETIRE', 'FULL RETIREMENT', 'REINSTATEMENT') AND
374 AJ.adjustment_id = SUB_AJ.adjustment_id AND
375 AJ.distribution_id = SUB_AJ.distribution_id AND
376 SUB_AJ.adjustment_type = p_report_type AND
377 SUB_AJ.rowid = (select min(x_aj.rowid)
378 FROM igi_iac_adjustments x_aj
379 WHERE x_aj.Book_Type_Code = p_book
380 AND x_aj.adjustment_id = sub_aj.adjustment_id
381 AND x_aj.distribution_id = sub_aj.distribution_id
382 AND x_aj.asset_id = sub_AJ.Asset_ID
383 AND x_aj.adjustment_type = p_report_type
384 AND x_aj.amount = AJ.amount
385 AND x_aj.adjustment_type <> AJ.adjustment_type)
386 GROUP BY
387 DH.Asset_ID,
388 DH.Code_Combination_ID,
389 SUB_AJ.Code_Combination_ID,
390 TH.Transaction_Type_Code;
391 END IF;*/
392
393 IF l_account_type = p_report_type THEN
394
395 INSERT INTO IGI_IAC_BALANCES_REPORT
396 (Asset_ID,
397 Distribution_CCID,
398 Adjustment_CCID,
399 Category_Books_Account,
400 Source_Type_Code,
401 Amount,
402 Data_Source,
403 Display_order)
404 SELECT
405 DH.Asset_ID,
406 DH.Code_Combination_ID,
407 AJ.Code_Combination_ID,
408 null,
409 DECODE(TH.Transaction_Type_Code,'FULL RETIREMENT','RETIREMENT',
410 'PARTIAL RETIRE','RETIREMENT',
411 'REINSTATEMENT','RETIREMENT',
412 TH.Transaction_Type_Code),
413 SUM (DECODE (AJ.Dr_Cr_Flag, l_Balance_Type, 1, -1) * AJ.Amount),
414 l_data_source,
415 l_display_order
416 FROM
417 FA_DISTRIBUTION_HISTORY DH,
418 IGI_IAC_TRANSACTION_HEADERS TH,
419 IGI_IAC_ADJUSTMENTS AJ
420 WHERE
421 DH.Book_Type_Code = p_Distribution_Source_Book AND
422 AJ.Asset_ID = DH.Asset_ID AND
423 AJ.Book_Type_Code = p_Book AND
424 AJ.Distribution_ID = DH.Distribution_ID AND
425 AJ.Adjustment_Type = l_account_type AND
426 AJ.Period_Counter BETWEEN
427 p_Period1_PC AND p_Period2_PC AND
428 AJ.transfer_to_gl_flag <> 'N' AND
429 TH.adjustment_ID = AJ.adjustment_ID
430 GROUP BY
431 DH.Asset_ID,
432 DH.Code_Combination_ID,
433 AJ.Code_Combination_ID,
434 TH.Transaction_Type_Code;
435 END IF;
436 END LOOP;
437
438 -- 02-Jun-2003, mh start, update source type to retirement for all non general fund
439 -- reinstatement trxs
440
441 /* UPDATE igi_iac_balances_report
442 SET source_type_code = 'RETIREMENT'
443 WHERE source_type_code = 'REINSTATEMENT'
444 AND data_source <> 'General Fund'; */
445
446 -- mh end
447
448
449
450 /* UPDATE igi_iac_balances_report BR
451 SET adjustment_ccid = (SELECT adjustment_ccid
452 FROM igi_iac_balances_report SUB_BR
453 WHERE SUB_BR.asset_id = BR.asset_id AND
454 SUB_BR.distribution_ccid = BR.distribution_ccid AND
455 SUB_BR.display_order = DECODE(p_report_type,
456 'REVAL RESERVE',4,
457 'OP EXPENSE',3) AND
458 SUB_BR.source_type_code NOT IN ('BEGIN','END'))
459 WHERE BR.data_source IN ('Cost','Backlog','General Fund');
460 */
461
462 END IF;
463
464 IF (p_report_type = 'RESERVE') THEN
465
466 INSERT INTO IGI_IAC_BALANCES_REPORT
467 (Asset_id,
468 Distribution_CCID,
469 Adjustment_CCID,
470 Category_books_account,
471 Source_type_code,
472 Amount,
473 Data_source,
474 Display_order)
475 SELECT
476 dh.asset_id,
477 dh.code_combination_id,
478 null,
479 CB.Deprn_Reserve_Acct,
480 'ADDITION',
481 sum(DD.DEPRN_RESERVE),
482 'FA',
483 1
484 FROM
485 FA_DISTRIBUTION_HISTORY DH,
486 FA_CATEGORY_BOOKS CB,
487 FA_ASSET_HISTORY AH,
488 FA_DEPRN_DETAIL DD
489 WHERE
490 NOT EXISTS (SELECT ASSET_ID
491 FROM IGI_IAC_BALANCES_REPORT
492 WHERE ASSET_ID = DH.ASSET_ID
493 AND DISTRIBUTION_CCID = DH.CODE_COMBINATION_ID
494 AND SOURCE_TYPE_CODE = 'ADDITION'
495 AND DATA_SOURCE = 'FA')
496 AND DD.BOOK_TYPE_CODE = p_book
497 AND (DD.PERIOD_COUNTER+1) BETWEEN
498 p_period1_pc AND p_period2_pc
499 AND DD.DEPRN_SOURCE_CODE = 'B'
500 AND DD.ASSET_ID = DH.ASSET_ID
501 AND DD.DEPRN_RESERVE <> 0
502 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
503 AND DH.ASSET_ID = AH.ASSET_ID
504 AND AH.DATE_EFFECTIVE <
505 NVL(DH.DATE_INEFFECTIVE, SYSDATE)
506 AND NVL(DH.DATE_INEFFECTIVE,SYSDATE) <=
507 NVL(AH.DATE_INEFFECTIVE,SYSDATE)
508 AND DD.BOOK_TYPE_CODE = CB.BOOK_TYPE_CODE
509 AND AH.CATEGORY_ID = CB.CATEGORY_ID
510 GROUP BY
511 Dh.ASSET_ID,
512 DH.CODE_COMBINATION_ID,
513 CB.DEPRN_RESERVE_ACCT;
514 END IF;
515
516 EXCEPTION
517 WHEN others THEN
518 debug_print('Error in Get_Adjustments :'||sqlerrm);
519 NULL ;
520 END;
521
522
523 PROCEDURE Get_Balance ( p_book varchar2,
524 p_distribution_source_book varchar2,
525 p_period_pc number,
526 p_earliest_pc number,
527 p_period_date date,
528 p_additions_date date,
529 p_earliest_date date,
530 p_report_type varchar2,
531 p_balance_type varchar2,
532 p_begin_or_end varchar2)
533 IS
534 l_account_type varchar2(15);
535 BEGIN
536 debug_print('Parameters to Get_Balance');
537 debug_print('Book :'|| p_book);
538 debug_print('Distribution Source Book :'|| p_distribution_source_book);
539 debug_print('p_period_pc :'||p_period_pc);
540 debug_print('p_earliest_pc :'||p_earliest_pc);
541 debug_print('p_period_date :'||p_period_date);
542 debug_print('p_additions_date :'||p_additions_date);
543 debug_print('p_earliest_date :'||p_earliest_date);
544 debug_print('p_report_type :'||p_report_type);
545 debug_print('p_balance_type :'||p_balance_type);
546 debug_print('p_begin_or_end :'||p_begin_or_end);
547
548 IF (p_report_type = 'COST' OR p_report_type = 'RESERVE') THEN
549 INSERT INTO IGI_IAC_BALANCES_REPORT
550 (Asset_ID,
551 Distribution_CCID,
552 Adjustment_CCID,
553 Category_Books_Account,
554 Source_Type_Code,
555 Amount,
556 Data_source,
557 Display_order)
558 SELECT /*+ index(dd FA_DEPRN_DETAIL_U1) */
559 DH.Asset_ID,
560 DH.Code_Combination_ID,
561 null,
562 DECODE (p_Report_Type,
563 'COST', CB.Asset_Cost_Acct,
564 'RESERVE', CB.Deprn_Reserve_Acct,
565 'REVAL RESERVE', CB.Reval_Reserve_Acct),
566 DECODE(p_Report_Type,
567 'RESERVE', DECODE(DD.Deprn_Source_Code,
568 'D', p_Begin_or_End, 'ADDITION'),
569 'REVAL RESERVE',
570 DECODE(DD.Deprn_Source_Code,
571 'D', p_Begin_or_End, 'ADDITION'),
572 p_Begin_or_End),
573 DECODE (p_Report_Type,
574 'COST', DD.Cost,
575 'OP EXPENSE', 0,
576 'RESERVE', DD.Deprn_Reserve,
577 'REVAL RESERVE', DD.Reval_Reserve),
578 'FA',
579 1
580 FROM
581 FA_DISTRIBUTION_HISTORY DH,
582 FA_DEPRN_DETAIL DD,
583 FA_ASSET_HISTORY AH,
584 FA_CATEGORY_BOOKS CB,
585 FA_BOOKS BK
586 WHERE
587 DH.Book_Type_Code = p_Distribution_Source_Book AND
588 DECODE(DD.Deprn_Source_Code, 'D', P_Period_Date,
589 p_Additions_Date) BETWEEN
590 DH.Date_Effective AND
591 NVL(DH.Date_Ineffective, SYSDATE) AND
592 DD.Asset_ID = DH.Asset_ID AND
593 DD.Book_Type_Code = p_Book AND
594 DD.Distribution_ID = DH.Distribution_ID AND
595 DD.Period_Counter <= p_Period_PC AND
596 DECODE(p_Begin_or_End,
597 'BEGIN', DD.Deprn_Source_Code, 'D') =
598 DD.Deprn_Source_Code AND
599 DD.Period_Counter =
600 (SELECT MAX (SUB_DD.Period_Counter)
601 FROM FA_DEPRN_DETAIL SUB_DD
602 WHERE SUB_DD.Book_Type_Code = p_Book AND
603 SUB_DD.Distribution_ID = DH.Distribution_ID AND
604 DH.Distribution_ID = DD.Distribution_ID AND
605 SUB_DD.Period_Counter <= p_Period_PC) AND
606 AH.Asset_ID = DH.Asset_ID AND
607 ((AH.Asset_Type <> 'EXPENSED' AND
608 p_Report_Type IN ('COST', 'CIP COST')) OR
609 (AH.Asset_Type = 'CAPITALIZED' AND
610 p_Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
611 DECODE(DD.Deprn_Source_Code, 'D', P_Period_Date,
612 p_Additions_Date) BETWEEN
613 AH.Date_Effective AND
614 NVL(AH.Date_Ineffective, SYSDATE) AND
615 CB.Category_ID = AH.Category_ID AND
616 CB.Book_Type_Code = DD.book_type_code AND
617 BK.Book_Type_Code = CB.book_type_code AND
618 BK.Asset_ID = DD.Asset_ID AND
619 DECODE(DD.Deprn_Source_Code, 'D', P_period_Date,
620 p_Additions_Date) BETWEEN
621 BK.Date_Effective AND
622 NVL(BK.Date_Ineffective, SYSDATE) AND
623 NVL(BK.Period_Counter_Fully_Retired, p_Period_PC+1)
624 > p_Earliest_PC AND
625 DECODE (p_Report_Type,
626 'COST', DECODE (AH.Asset_Type,
627 'CAPITALIZED', CB.Asset_Cost_Acct,
628 null),
629 'CIP COST',
630 DECODE (AH.Asset_Type,
631 'CIP', CB.CIP_Cost_Acct,
632 null),
633 'RESERVE', CB.Deprn_Reserve_Acct,
634 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null;
635 END IF;
636
637 IF (p_report_type = 'COST' OR p_report_type = 'RESERVE' OR p_report_type = 'BL RESERVE') THEN
638
639 INSERT INTO IGI_IAC_BALANCES_REPORT
640 (Asset_ID,
641 Distribution_CCID,
642 Adjustment_CCID,
643 Category_Books_Account,
644 Source_Type_Code,
645 Amount,
646 Data_source,
647 Display_order)
648 SELECT
649 DH.Asset_ID,
650 DH.Code_Combination_ID,
651 null,
652 DECODE (p_Report_Type,
653 'COST', CB.Asset_Cost_Acct,
654 'RESERVE', CB.Deprn_Reserve_Acct,
655 'BL RESERVE', ICB.Backlog_Deprn_Rsv_ccid,
656 'REVAL RESERVE', ICB.Reval_Rsv_ccid,
657 'OP EXPENSE',ICB.Operating_Expense_ccid),
658 p_Begin_or_End,
659 DECODE (p_Report_Type,
660 'COST', DD.adjustment_Cost,
661 'OP EXPENSE', DD.Operating_Acct_Net,
662 'RESERVE', DD.Deprn_Reserve,
663 'BL RESERVE', DD.Deprn_Reserve_Backlog,
664 'REVAL RESERVE', DD.Reval_Reserve_Net),
665 'IAC',
666 2
667 FROM
668 FA_DISTRIBUTION_HISTORY DH,
669 IGI_IAC_DET_BALANCES DD,
670 FA_ASSET_HISTORY AH,
671 FA_CATEGORY_BOOKS CB,
672 IGI_IAC_CATEGORY_BOOKS ICB,
673 FA_BOOKS BK,
674 IGI_IAC_TRANSACTION_HEADERS ITH
675 WHERE
676 DH.Book_Type_Code = p_Distribution_Source_Book AND
677 DD.Asset_ID = DH.Asset_ID AND
678 DD.Book_Type_Code = p_Book AND
679 DD.Distribution_ID = DH.Distribution_ID AND
680 DD.Period_Counter <= p_Period_PC AND
681 DD.adjustment_id =
682 (SELECT MAX (SUB_TH.adjustment_id)
683 FROM IGI_IAC_TRANSACTION_HEADERS SUB_TH
684 WHERE SUB_TH.Book_Type_Code = p_Book AND
685 SUB_TH.Asset_ID = DH.Asset_ID AND
686 SUB_TH.Period_Counter <= p_Period_PC AND
687 SUB_TH.adjustment_status NOT IN ('PREVIEW','OBSOLETE')) AND
688 ITH.adjustment_id = DD.adjustment_id AND
689 ITH.asset_id = DD.asset_id AND
690 ITH.book_type_code = DD.book_type_code AND
691 ITH.category_id = AH.category_id AND
692 nvl(DD.Active_Flag,'Y') <> 'N' AND
693 AH.Asset_ID = DH.Asset_ID AND
694 ((AH.Asset_Type <> 'EXPENSED' AND
695 p_Report_Type IN ('COST', 'OP EXPENSE')) OR
696 (AH.Asset_Type = 'CAPITALIZED' AND
697 p_Report_Type IN ('RESERVE', 'REVAL RESERVE', 'BL RESERVE'))) AND
698 CB.Category_ID = AH.Category_ID AND
699 ICB.Category_ID = AH.Category_ID AND
700 p_period_date BETWEEN
701 AH.Date_Effective AND
702 NVL(AH.Date_Ineffective, SYSDATE) AND
703 CB.Book_Type_Code = DD.book_type_code AND
704 ICB.Book_Type_Code = DD.book_type_code AND
705 BK.Book_Type_Code = CB.book_type_code AND
706 BK.Asset_ID = DD.Asset_ID AND
707 p_period_Date BETWEEN
708 BK.Date_Effective AND
709 NVL(BK.Date_Ineffective, SYSDATE) AND
710 NVL(BK.Period_Counter_Fully_Retired, p_Period_PC+1)
711 > p_Earliest_PC AND
712 DECODE (p_Report_Type,
713 'COST', DECODE (AH.Asset_Type,
714 'CAPITALIZED', CB.Asset_Cost_Acct,
715 null),
716 'RESERVE', CB.Deprn_Reserve_Acct,
717 'BL RESERVE', ICB.Backlog_deprn_Rsv_ccid,
718 'REVAL RESERVE', ICB.Reval_Rsv_ccid,
719 'OP EXPENSE', ICB.Operating_Expense_ccid) is not null;
720 END IF;
721
722 IF (p_report_type = 'REVAL RESERVE') THEN
723
724 -- mh, 16/06/2003, OR p_report_type = 'OP EXPENSE') THEN - coomented because BEGIN and END balances are
725 -- no longer required for Op Expense reports
726
727 FOR counter IN 1..4 LOOP
728 IF (counter = 1) THEN
729 l_account_type := 'COST';
730 ELSIF counter = 2 THEN
731 l_account_type := 'BACKLOG';
732 ELSIF counter = 3 THEN
733 l_account_type := 'GENERAL FUND';
734 ELSE
735 l_account_type := 'NET';
736 END IF;
737
738 -- mh,16/06/2003 IF NOT (p_report_type = 'OP EXPENSE' AND l_account_type = 'GENERAL FUND') THEN
739
740 -- mh, 21/01/2004 bug 3377806 IF NOT l_account_type = 'GENERAL FUND' THEN
741
742 INSERT INTO IGI_IAC_BALANCES_REPORT
743 (Asset_ID,
744 Distribution_CCID,
745 Adjustment_CCID,
746 Category_Books_Account,
747 Source_Type_Code,
748 Amount,
749 Data_source,
750 Display_order)
751 SELECT
752 DH.Asset_ID,
753 DH.Code_Combination_ID,
754 null,
755 DECODE (p_Report_Type,
756 'REVAL RESERVE', ICB.Reval_Rsv_ccid, /* Here ccid should be replaced by account */
757 'OP EXPENSE',ICB.Operating_Expense_ccid), /* Here ccid should be replaced by account */
758 p_Begin_or_End,
759 DECODE (p_Report_Type,
760 'OP EXPENSE',DECODE(l_account_type,
761 'COST',DD.Operating_Acct_Cost,
762 'BACKLOG',DD.Operating_Acct_Backlog,
763 'NET',DD.Operating_Acct_Net) ,
764 'REVAL RESERVE', DECODE(l_account_type,
765 'COST',DD.Reval_Reserve_Cost,
766 'BACKLOG',DD.Reval_Reserve_Backlog,
767 'GENERAL FUND',DD.Reval_Reserve_Gen_Fund,
768 'NET',DD.Reval_Reserve_Net) ),
769 DECODE (p_Report_Type,
770 'OP EXPENSE',DECODE(l_account_type,
771 'COST','Cost',
772 'BACKLOG','Backlog',
773 'NET','Net') ,
774 'REVAL RESERVE', DECODE(l_account_type,
775 'COST','Cost',
776 'BACKLOG','Backlog',
777 'GENERAL FUND','General Fund',
778 'NET','Net') ),
779 DECODE (p_Report_Type,
780 'OP EXPENSE',DECODE(l_account_type,
781 'COST',1,
782 'BACKLOG',2,
783 'NET',3) ,
784 'REVAL RESERVE', DECODE(l_account_type,
785 'COST',1,
786 'BACKLOG',3,
787 'GENERAL FUND',2,
788 'NET',4) )
789 FROM
790 FA_DISTRIBUTION_HISTORY DH,
791 IGI_IAC_DET_BALANCES DD,
792 FA_ASSET_HISTORY AH,
793 FA_CATEGORY_BOOKS CB,
794 IGI_IAC_CATEGORY_BOOKS ICB,
795 FA_BOOKS BK,
796 IGI_IAC_TRANSACTION_HEADERS ITH
797 WHERE
798 DH.Book_Type_Code = p_Distribution_Source_Book AND
799 DD.Asset_ID = DH.Asset_ID AND
800 DD.Book_Type_Code = p_Book AND
801 DD.Distribution_ID = DH.Distribution_ID AND
802 DD.Period_Counter <= p_Period_PC AND
803 DD.adjustment_id =
804 (SELECT MAX (SUB_TH.adjustment_id)
805 FROM IGI_IAC_TRANSACTION_HEADERS SUB_TH
806 WHERE SUB_TH.Book_Type_Code = p_Book AND
807 SUB_TH.Asset_ID = DH.Asset_ID AND
808 SUB_TH.Period_Counter <= p_Period_PC AND
809 SUB_TH.adjustment_status NOT IN ('PREVIEW','OBSOLETE')) AND
810 ITH.adjustment_id = DD.adjustment_id AND
811 ITH.asset_id = DD.asset_id AND
812 ITH.book_type_code = DD.book_type_code AND
813 ITH.category_id = AH.category_id AND
814 nvl(DD.Active_Flag,'Y') <> 'N' AND
815 AH.Asset_ID = DH.Asset_ID AND
816 ((AH.Asset_Type <> 'EXPENSED' AND
817 p_Report_Type IN ('COST', 'OP EXPENSE')) OR
818 (AH.Asset_Type = 'CAPITALIZED' AND
819 p_Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
820 CB.Category_ID = AH.Category_ID AND
821 ICB.Category_ID = AH.Category_ID AND
822 p_Period_date BETWEEN
823 AH.Date_Effective AND
824 NVL(AH.Date_Ineffective, SYSDATE) AND
825 CB.Book_Type_Code = DD.book_type_code AND
826 ICB.Book_Type_Code = DD.book_type_code AND
827 BK.Book_Type_Code = CB.book_type_code AND
828 BK.Asset_ID = DD.Asset_ID AND
829 p_Period_Date BETWEEN
830 BK.Date_Effective AND
831 NVL(BK.Date_Ineffective, SYSDATE) AND
832 NVL(BK.Period_Counter_Fully_Retired, p_Period_PC+1)
833 > p_Earliest_PC AND
834 DECODE (p_Report_Type,
835 'REVAL RESERVE', ICB.Reval_Rsv_ccid,
836 'OP EXPENSE', ICB.Operating_Expense_ccid) is not null;
837
838 /* mh, 16/06/2003 IF (p_report_type = 'OP EXPENSE') THEN
839 -- This is required for getting the ending balance of inactive distributions
840 INSERT INTO IGI_IAC_BALANCES_REPORT
841 (Asset_ID,
842 Distribution_CCID,
843 Adjustment_CCID,
844 Category_Books_Account,
845 Source_Type_Code,
846 Amount,
847 Data_source,
848 Display_order)
849 SELECT
850 db.asset_id,
851 dh.code_combination_id,
852 NULL,
853 icb.operating_expense_ccid,
854 p_Begin_or_End,
855 DECODE(l_account_type,
856 'COST',DB.Operating_Acct_Cost,
857 'BACKLOG',DB.Operating_Acct_Backlog,
858 'NET',DB.Operating_Acct_Net) ,
859 DECODE(l_account_type,
860 'COST','Cost',
861 'BACKLOG','Backlog',
862 'NET','Net'),
863 DECODE(l_account_type,
864 'COST',1,
865 'BACKLOG',2,
866 'NET',3)
867 FROM
868 igi_iac_det_balances db,
869 fa_distribution_history dh,
870 fa_asset_history ah,
871 igi_iac_category_books icb
872 WHERE
873 dh.book_type_code = p_book AND
874 nvl(dh.date_ineffective, p_earliest_date-1) > p_earliest_date AND
875 dh.asset_id = ah.asset_id AND
876 nvl(dh.date_ineffective, SYSDATE) > ah.date_effective AND
877 nvl(dh.date_ineffective, SYSDATE) <= nvl(ah.date_ineffective, SYSDATE) AND
878 icb.book_type_code = p_book AND
879 icb.category_id = ah.category_id AND
880 db.distribution_id = dh.distribution_id AND
881 db.adjustment_id = (SELECT max(idb.adjustment_id)
882 FROM igi_iac_det_balances idb
883 WHERE idb.book_type_code = p_book AND
884 idb.asset_id = dh.asset_id AND
885 idb.distribution_id = db.distribution_id AND
886 idb.period_counter <= p_period_pc);
887 END IF; */
888
889 -- END IF;
890
891 END LOOP;
892 END IF;
893
894 EXCEPTION
895 WHEN others THEN
896 debug_print('Error in Get_Balance :'||sqlerrm);
897 NULL ;
898 END;
899
900
901 PROCEDURE Get_Deprn_Effects (
902 p_book varchar2,
903 p_distribution_source_book varchar2,
904 p_period1_pc number,
905 p_period2_pc number,
906 p_report_type varchar2,
907 p_balance_type varchar2)
908 IS
909 BEGIN
910
911 INSERT INTO IGI_IAC_BALANCES_REPORT
912 (Asset_ID,
913 Distribution_CCID,
914 Adjustment_CCID,
915 Category_Books_Account,
916 Source_Type_Code,
917 Amount,
918 Data_Source,
919 Display_Order)
920 SELECT
921 DH.Asset_ID,
922 DH.Code_Combination_ID,
923 null,
924 DECODE (p_report_type,
925 'RESERVE', CB.Deprn_Reserve_Acct,
926 'REVAL RESERVE', CB.Reval_Reserve_Acct),
927 DECODE(DD.Deprn_Source_Code,
928 'D', 'DEPRECIATION', 'ADDITION'),
929 SUM (DECODE (p_report_type,
930 'RESERVE', DD.Deprn_Amount,
931 'REVAL RESERVE', -DD.Reval_Amortization)),
932 'FA',
933 1
934 FROM
935 FA_CATEGORY_BOOKS CB,
936 FA_DISTRIBUTION_HISTORY DH,
937 FA_ASSET_HISTORY AH,
938 FA_DEPRN_DETAIL DD,
939 FA_DEPRN_PERIODS DP
940 WHERE
941 DH.Book_Type_Code = p_Distribution_Source_Book AND
942 AH.Asset_ID = DH.Asset_ID AND
943 AH.Asset_Type = 'CAPITALIZED' AND
944 AH.Date_Effective <
945 nvl(DH.date_ineffective, sysdate) AND
946 nvl(DH.date_ineffective, sysdate) <=
947 NVL(AH.Date_Ineffective, SYSDATE) AND
948 CB.Category_ID = AH.Category_ID AND
949 CB.Book_Type_Code = p_Book AND
950 ((DD.Deprn_Source_Code = 'B'
951 AND (DD.Period_Counter+1) < p_Period2_PC) OR
952 (DD.Deprn_Source_Code = 'D')) AND
953 DD.Book_Type_Code||'' = p_Book AND
954 DD.Asset_ID = DH.Asset_ID AND
955 DD.Distribution_ID = DH.Distribution_ID AND
956 DD.Period_Counter between
957 p_Period1_PC and p_Period2_PC AND
958 DP.Book_Type_Code = DD.Book_Type_Code AND
959 DP.Period_Counter = DD.Period_Counter AND
960 DECODE (p_report_type,
961 'RESERVE', CB.Deprn_Reserve_Acct,
962 'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null AND
963 DECODE (p_report_type,
964 'RESERVE', DD.Deprn_Amount,
965 'REVAL RESERVE', NVL(DD.Reval_Amortization,0)) <> 0
966 GROUP BY
967 DH.Asset_ID,
968 DH.Code_Combination_ID,
969 DECODE (p_report_type,
970 'RESERVE', CB.Deprn_Reserve_Acct,
971 'REVAL RESERVE', CB.Reval_Reserve_Acct),
972 DD.Deprn_Source_Code;
973
974 EXCEPTION
975 WHEN others THEN
976 debug_print('Error in Get_Deprn_Effects :'||sqlerrm);
977 NULL ;
978 END;
979
980
981 PROCEDURE GET_GENERAL_FUND(
982 p_book varchar2,
983 p_period1_pc number,
984 p_period2_pc number,
985 p_report_type varchar2,
986 p_balance_type varchar2)
987 IS
988 l_prev_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE;
989 l_distribution_ccid fa_distribution_history.code_combination_id%TYPE;
990
991 /* Cursor for fetching all retirement, reinstatement transactions */
992 CURSOR c_get_transactions(cp_book fa_books.book_type_code%TYPE) IS
993 SELECT *
994 FROM igi_iac_transaction_headers
995 WHERE book_type_code = cp_book
996 AND period_counter BETWEEN p_period1_pc AND p_period2_pc
997 AND transaction_type_code IN ('PARTIAL RETIRE', 'FULL RETIREMENT', 'REINSTATEMENT');
998
999 /* Cursor for fetching the transaction previous to retirement or reinstatement */
1000 CURSOR c_get_Prev_transaction(cp_book fa_books.book_type_code%TYPE,
1001 cp_asset_id igi_iac_transaction_headers.asset_id%TYPE,
1002 cp_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE) IS
1003 SELECT max(adjustment_id)
1004 FROM igi_iac_transaction_headers
1005 WHERE book_type_code = cp_book
1006 AND asset_id = cp_asset_id
1007 AND adjustment_id < cp_adjustment_id
1008 AND adjustment_status NOT IN ('PREVIEW','OBSOLETE');
1009
1010 /* Cursor to fetch the general fund movement from previous transaction
1011 to current transaction for the distributions which exist in both
1012 transactions */
1013 CURSOR c_get_dists(cp_book fa_books.book_type_code%TYPE,
1014 cp_asset_id igi_iac_transaction_headers.asset_id%TYPE,
1015 cp_curr_adj_id igi_iac_transaction_headers.adjustment_id%TYPE,
1016 cp_prev_adj_id igi_iac_transaction_headers.adjustment_id%TYPE) IS
1017 SELECT curr_adj.distribution_id distribution_id,
1018 (curr_adj.reval_reserve_cost - prev_adj.reval_reserve_cost) reval_reserve_cost,
1019 (curr_adj.reval_reserve_backlog - prev_adj.reval_reserve_backlog) reval_reserve_backlog,
1020 (curr_adj.general_fund_acc - prev_adj.general_fund_acc) general_fund,
1021 (curr_adj.Operating_acct_cost - prev_adj.Operating_acct_cost) operating_acct_cost,
1022 (curr_adj.Operating_acct_backlog - prev_adj.Operating_acct_backlog) operating_acct_backlog
1023 FROM igi_iac_det_balances curr_adj,
1024 igi_iac_det_balances prev_adj
1025 WHERE curr_adj.book_type_code = cp_book
1026 AND curr_adj.asset_id = cp_asset_id
1027 AND prev_adj.book_type_code = cp_book
1028 AND prev_adj.asset_id = cp_asset_id
1029 AND curr_adj.adjustment_id = cp_curr_adj_id
1030 AND prev_adj.adjustment_id = cp_prev_adj_id
1031 AND curr_adj.distribution_id = prev_adj.distribution_id;
1032
1033 /* Cursor to fetch the general fund movement from previous transaction
1034 to current transaction for the distributions which exist only in the
1035 latest transaction */
1036 CURSOR c_get_new_dists(cp_book fa_books.book_type_code%TYPE,
1037 cp_asset_id igi_iac_transaction_headers.asset_id%TYPE,
1038 cp_curr_adj_id igi_iac_transaction_headers.adjustment_id%TYPE,
1039 cp_prev_adj_id igi_iac_transaction_headers.adjustment_id%TYPE) IS
1040 SELECT adj.distribution_id,
1041 adj.reval_reserve_cost reval_reserve_cost,
1042 adj.reval_reserve_backlog reval_reserve_backlog,
1043 adj.general_fund_acc general_fund,
1044 adj.operating_acct_cost operating_acct_cost,
1045 adj.operating_acct_backlog operating_acct_backlog
1046 FROM igi_iac_det_balances adj
1047 WHERE book_type_code = cp_book
1048 AND asset_id = cp_asset_id
1049 AND adjustment_id = cp_curr_adj_id
1050 AND NOT EXISTS (SELECT 'X'
1051 FROM igi_iac_det_balances sub_adj
1052 WHERE sub_adj.book_type_code = cp_book
1053 AND sub_adj.asset_id = cp_asset_id
1054 AND sub_adj.adjustment_id = cp_prev_adj_id
1055 AND sub_adj.distribution_id = adj.distribution_id);
1056
1057 /* Cursor to fetch the general fund movement from previous transaction
1058 to current transaction for the distributions which exist only in the
1059 previous transaction */
1060 CURSOR c_get_old_dists(cp_book fa_books.book_type_code%TYPE,
1061 cp_asset_id igi_iac_transaction_headers.asset_id%TYPE,
1062 cp_curr_adj_id igi_iac_transaction_headers.adjustment_id%TYPE,
1063 cp_prev_adj_id igi_iac_transaction_headers.adjustment_id%TYPE) IS
1064 SELECT adj.distribution_id,
1065 (adj.reval_reserve_cost * -1) reval_reserve_cost,
1066 (adj.reval_reserve_backlog * -1) reval_reserve_backlog,
1067 (adj.general_fund_acc * -1) general_fund,
1068 (adj.operating_acct_cost * -1) operating_acct_cost,
1069 (adj.operating_acct_backlog * -1) operating_acct_backlog
1070 FROM igi_iac_det_balances adj
1071 WHERE book_type_code = cp_book
1072 AND asset_id = cp_asset_id
1073 AND adjustment_id = cp_prev_adj_id
1074 AND NOT EXISTS (SELECT 'X'
1075 FROM igi_iac_det_balances sub_adj
1076 WHERE sub_adj.book_type_code = cp_book
1077 AND sub_adj.asset_id = cp_asset_id
1078 AND sub_adj.adjustment_id = cp_curr_adj_id
1079 AND sub_adj.distribution_id = adj.distribution_id);
1080
1081 /* Cursor to fetch the category for the asset */
1082 CURSOR c_get_category(cp_book fa_books.book_type_code%TYPE,
1083 cp_asset_id fa_additions.asset_id%TYPE,
1084 cp_transaction_id igi_iac_transaction_headers.transaction_header_id%TYPE) IS
1085 SELECT ah.category_id
1086 FROM fa_asset_history ah,
1087 fa_transaction_headers th
1088 WHERE ah.asset_id = cp_asset_id
1089 AND th.transaction_header_id = cp_transaction_id
1090 AND th.book_type_code = cp_book
1091 AND th.asset_id = cp_asset_id
1092 AND th.transaction_header_id BETWEEN
1093 ah.transaction_header_id_in AND
1094 NVL (ah.transaction_header_id_out - 1,
1095 th.transaction_header_id);
1096
1097 /* Cursor to fetch the revaluation reserve account for the book
1098 and the category */
1099 CURSOR c_get_account(cp_book fa_books.book_type_code%TYPE,
1100 cp_category_id igi_iac_category_books.category_id%TYPE,
1101 cp_report_type varchar2) IS
1102 SELECT DECODE(cp_report_type,'REVAL RESERVE', reval_rsv_ccid,
1103 'OP EXPENSE', operating_expense_ccid) adjustment_ccid
1104 FROM igi_iac_category_books
1105 WHERE book_type_code = cp_book
1106 AND category_id = cp_category_id;
1107
1108 /* Cursor to fetch the distribution ccid for a distribution */
1109 CURSOR c_get_dist_ccid( cp_book fa_distribution_history.book_type_code%TYPE,
1110 cp_asset_id fa_distribution_history.asset_id%TYPE,
1111 cp_distribution_id fa_distribution_history.distribution_id%TYPE) IS
1112 SELECT code_combination_id
1113 FROM fa_distribution_history
1114 WHERE book_type_code = cp_book
1115 AND asset_id = cp_asset_id
1116 AND distribution_id = cp_distribution_id;
1117
1118 BEGIN
1119 Debug_Print('Starting of processing for retirements');
1120 FOR l_transaction IN c_get_transactions(p_book) LOOP
1121 Debug_print('Adjustment id :'||l_transaction.adjustment_id);
1122 OPEN c_get_prev_transaction(l_transaction.book_type_code,
1123 l_transaction.asset_id,
1124 l_transaction.adjustment_id);
1125 FETCH c_get_prev_transaction INTO l_prev_adjustment_id;
1126 CLOSE c_get_prev_transaction;
1127 Debug_Print('Previous Adjustment_id :'||l_prev_adjustment_id);
1128 /* Processing distributions existing in both transactions */
1129 Debug_Print('Before start of distributions in both transactions');
1130 FOR l_dist IN c_get_dists(l_transaction.book_type_code,
1131 l_transaction.asset_id,
1132 l_transaction.adjustment_id,
1133 l_prev_adjustment_id) LOOP
1134 Debug_Print('Distribution_id :'||l_dist.distribution_id);
1135 Debug_Print('Reval Reserve Cost :'||l_dist.reval_reserve_cost);
1136 Debug_Print('Reval Reserve General Fund :'||l_dist.general_fund);
1137 Debug_Print('Reval Reserve Backlog :'||l_dist.reval_reserve_backlog);
1138 Debug_Print('Operating Acct Cost :'||l_dist.operating_acct_cost);
1139 Debug_Print('Operating Acct Backlog :'||l_dist.operating_acct_backlog);
1140 FOR l_category IN c_get_category(l_transaction.book_type_code,
1141 l_transaction.asset_id,
1142 l_transaction.transaction_header_id) LOOP
1143 Debug_Print('Category_id :'||l_category.category_id);
1144 FOR l_account IN c_get_account(l_transaction.book_type_code,
1145 l_category.category_id,
1146 p_report_type) LOOP
1147 Debug_Print('Account CCID: '|| l_account.adjustment_ccid);
1148 OPEN c_get_dist_ccid(l_transaction.book_type_code,
1149 l_transaction.asset_id,
1150 l_dist.distribution_id);
1151 FETCH c_get_dist_ccid INTO l_distribution_ccid;
1152 CLOSE c_get_dist_ccid;
1153
1154
1155 IF (p_report_type = 'REVAL RESERVE') THEN
1156 Debug_Print('Inserting Reval Reserve records');
1157 INSERT INTO IGI_IAC_BALANCES_REPORT
1158 (Asset_ID,
1159 Distribution_CCID,
1160 Adjustment_CCID,
1161 Category_Books_Account,
1162 Source_Type_Code,
1163 Amount,
1164 Data_Source,
1165 Display_order)
1166 VALUES
1167 (l_transaction.asset_id,
1168 l_distribution_ccid,
1169 l_account.adjustment_ccid,
1170 NULL,
1171 'RETIREMENT',
1172 l_dist.reval_reserve_cost,
1173 'Cost',
1174 1);
1175
1176 INSERT INTO IGI_IAC_BALANCES_REPORT
1177 (Asset_ID,
1178 Distribution_CCID,
1179 Adjustment_CCID,
1180 Category_Books_Account,
1181 Source_Type_Code,
1182 Amount,
1183 Data_Source,
1184 Display_order)
1185 VALUES
1186 (l_transaction.asset_id,
1187 l_distribution_ccid,
1188 l_account.adjustment_ccid,
1189 NULL,
1190 'RETIREMENT',
1191 l_dist.general_fund,
1192 'General Fund',
1193 2);
1194
1195 INSERT INTO IGI_IAC_BALANCES_REPORT
1196 (Asset_ID,
1197 Distribution_CCID,
1198 Adjustment_CCID,
1199 Category_Books_Account,
1200 Source_Type_Code,
1201 Amount,
1202 Data_Source,
1203 Display_order)
1204 VALUES
1205 (l_transaction.asset_id,
1206 l_distribution_ccid,
1207 l_account.adjustment_ccid,
1208 NULL,
1209 'RETIREMENT',
1210 l_dist.reval_reserve_backlog,
1211 'Backlog',
1212 3);
1213 END IF;
1214
1215 IF (p_report_type = 'OP EXPENSE') THEN
1216 INSERT INTO IGI_IAC_BALANCES_REPORT
1217 (Asset_ID,
1218 Distribution_CCID,
1219 Adjustment_CCID,
1220 Category_Books_Account,
1221 Source_Type_Code,
1222 Amount,
1223 Data_Source,
1224 Display_order)
1225 VALUES
1226 (l_transaction.asset_id,
1227 l_distribution_ccid,
1228 l_account.adjustment_ccid,
1229 NULL,
1230 'RETIREMENT',
1231 l_dist.operating_acct_cost,
1232 'Cost',
1233 1);
1234
1235 INSERT INTO IGI_IAC_BALANCES_REPORT
1236 (Asset_ID,
1237 Distribution_CCID,
1238 Adjustment_CCID,
1239 Category_Books_Account,
1240 Source_Type_Code,
1241 Amount,
1242 Data_Source,
1243 Display_order)
1244 VALUES
1245 (l_transaction.asset_id,
1246 l_distribution_ccid,
1247 l_account.adjustment_ccid,
1248 NULL,
1249 'RETIREMENT',
1250 l_dist.operating_acct_backlog,
1251 'Backlog',
1252 2);
1253 END IF;
1254 END LOOP;
1255 END LOOP;
1256 END LOOP;
1257
1258 /* Processing the distributions existing only in latest transaction */
1259 debug_Print('Before start of distributions only in new transaction');
1260 FOR l_dist IN c_get_new_dists(l_transaction.book_type_code,
1261 l_transaction.asset_id,
1262 l_transaction.adjustment_id,
1263 l_prev_adjustment_id) LOOP
1264 Debug_Print('Distribution_id :'||l_dist.distribution_id);
1265 Debug_Print('Reval Reserve Cost :'||l_dist.reval_reserve_cost);
1266 Debug_Print('Reval Reserve General Fund :'||l_dist.general_fund);
1267 Debug_Print('Reval Reserve Backlog :'||l_dist.reval_reserve_backlog);
1268 Debug_Print('Operating Acct Cost :'||l_dist.operating_acct_cost);
1269 Debug_Print('Operating Acct Backlog :'||l_dist.operating_acct_backlog);
1270
1271 FOR l_category IN c_get_category(l_transaction.book_type_code,
1272 l_transaction.asset_id,
1273 l_transaction.transaction_header_id) LOOP
1274
1275 FOR l_account IN c_get_account(l_transaction.book_type_code,
1276 l_category.category_id,
1277 p_report_type) LOOP
1278 OPEN c_get_dist_ccid(l_transaction.book_type_code,
1279 l_transaction.asset_id,
1280 l_dist.distribution_id);
1281 FETCH c_get_dist_ccid INTO l_distribution_ccid;
1282 CLOSE c_get_dist_ccid;
1283
1284 IF (p_report_type = 'REVAL RESERVE') THEN
1285 Debug_Print('Inserting Reval Reserve records');
1286 INSERT INTO IGI_IAC_BALANCES_REPORT
1287 (Asset_ID,
1288 Distribution_CCID,
1289 Adjustment_CCID,
1290 Category_Books_Account,
1291 Source_Type_Code,
1292 Amount,
1293 Data_Source,
1294 Display_order)
1295 VALUES
1296 (l_transaction.asset_id,
1297 l_distribution_ccid,
1298 l_account.adjustment_ccid,
1299 NULL,
1300 'RETIREMENT',
1301 l_dist.reval_reserve_cost,
1302 'Cost',
1303 1);
1304
1305 INSERT INTO IGI_IAC_BALANCES_REPORT
1306 (Asset_ID,
1307 Distribution_CCID,
1308 Adjustment_CCID,
1309 Category_Books_Account,
1310 Source_Type_Code,
1311 Amount,
1312 Data_Source,
1313 Display_order)
1314 VALUES
1315 (l_transaction.asset_id,
1316 l_distribution_ccid,
1317 l_account.adjustment_ccid,
1318 NULL,
1319 'RETIREMENT',
1320 l_dist.general_fund,
1321 'General Fund',
1322 2);
1323
1324 INSERT INTO IGI_IAC_BALANCES_REPORT
1325 (Asset_ID,
1326 Distribution_CCID,
1327 Adjustment_CCID,
1328 Category_Books_Account,
1329 Source_Type_Code,
1330 Amount,
1331 Data_Source,
1332 Display_order)
1333 VALUES
1334 (l_transaction.asset_id,
1335 l_distribution_ccid,
1336 l_account.adjustment_ccid,
1337 NULL,
1338 'RETIREMENT',
1339 l_dist.reval_reserve_backlog,
1340 'Backlog',
1341 3);
1342 END IF;
1343
1344 IF (p_report_type = 'OP EXPENSE') THEN
1345 INSERT INTO IGI_IAC_BALANCES_REPORT
1346 (Asset_ID,
1347 Distribution_CCID,
1348 Adjustment_CCID,
1349 Category_Books_Account,
1350 Source_Type_Code,
1351 Amount,
1352 Data_Source,
1353 Display_order)
1354 VALUES
1355 (l_transaction.asset_id,
1356 l_distribution_ccid,
1357 l_account.adjustment_ccid,
1358 NULL,
1359 'RETIREMENT',
1360 l_dist.operating_acct_cost,
1361 'Cost',
1362 1);
1363
1364 INSERT INTO IGI_IAC_BALANCES_REPORT
1365 (Asset_ID,
1366 Distribution_CCID,
1367 Adjustment_CCID,
1368 Category_Books_Account,
1369 Source_Type_Code,
1370 Amount,
1371 Data_Source,
1372 Display_order)
1373 VALUES
1374 (l_transaction.asset_id,
1375 l_distribution_ccid,
1376 l_account.adjustment_ccid,
1377 NULL,
1378 'RETIREMENT',
1379 l_dist.operating_acct_backlog,
1380 'Backlog',
1381 2);
1382 END IF;
1383
1384 END LOOP;
1385 END LOOP;
1386 END LOOP;
1387
1388 Debug_Print('Before start of distributions only in previous transaction');
1389 /* Processing the distributions existing only in previous transaction */
1390 FOR l_dist IN c_get_old_dists(l_transaction.book_type_code,
1391 l_transaction.asset_id,
1392 l_transaction.adjustment_id,
1393 l_prev_adjustment_id) LOOP
1394 Debug_Print('Distribution_id :'||l_dist.distribution_id);
1395 Debug_Print('Reval Reserve Cost :'||l_dist.reval_reserve_cost);
1396 Debug_Print('Reval Reserve General Fund :'||l_dist.general_fund);
1397 Debug_Print('Reval Reserve Backlog :'||l_dist.reval_reserve_backlog);
1398 Debug_Print('Operating Acct Cost :'||l_dist.operating_acct_cost);
1399 Debug_Print('Operating Acct Backlog :'||l_dist.operating_acct_backlog);
1400
1401 FOR l_category IN c_get_category(l_transaction.book_type_code,
1402 l_transaction.asset_id,
1403 l_transaction.transaction_header_id) LOOP
1404
1405 FOR l_account IN c_get_account(l_transaction.book_type_code,
1406 l_category.category_id,
1407 p_report_type) LOOP
1408 OPEN c_get_dist_ccid(l_transaction.book_type_code,
1409 l_transaction.asset_id,
1410 l_dist.distribution_id);
1411 FETCH c_get_dist_ccid INTO l_distribution_ccid;
1412 CLOSE c_get_dist_ccid;
1413
1414 IF (p_report_type = 'REVAL RESERVE') THEN
1415 Debug_Print('Inserting Reval Reserve records');
1416 INSERT INTO IGI_IAC_BALANCES_REPORT
1417 (Asset_ID,
1418 Distribution_CCID,
1419 Adjustment_CCID,
1420 Category_Books_Account,
1421 Source_Type_Code,
1422 Amount,
1423 Data_Source,
1424 Display_order)
1425 VALUES
1426 (l_transaction.asset_id,
1427 l_distribution_ccid,
1428 l_account.adjustment_ccid,
1429 NULL,
1430 'RETIREMENT',
1431 l_dist.reval_reserve_cost,
1432 'Cost',
1433 1);
1434
1435 INSERT INTO IGI_IAC_BALANCES_REPORT
1436 (Asset_ID,
1437 Distribution_CCID,
1438 Adjustment_CCID,
1439 Category_Books_Account,
1440 Source_Type_Code,
1441 Amount,
1442 Data_Source,
1443 Display_order)
1444 VALUES
1445 (l_transaction.asset_id,
1446 l_distribution_ccid,
1447 l_account.adjustment_ccid,
1448 NULL,
1449 'RETIREMENT',
1450 l_dist.general_fund,
1451 'General Fund',
1452 2);
1453
1454 INSERT INTO IGI_IAC_BALANCES_REPORT
1455 (Asset_ID,
1456 Distribution_CCID,
1457 Adjustment_CCID,
1458 Category_Books_Account,
1459 Source_Type_Code,
1460 Amount,
1461 Data_Source,
1462 Display_order)
1463 VALUES
1464 (l_transaction.asset_id,
1465 l_distribution_ccid,
1466 l_account.adjustment_ccid,
1467 NULL,
1468 'RETIREMENT',
1469 l_dist.reval_reserve_backlog,
1470 'Backlog',
1471 3);
1472 END IF;
1473
1474 IF (p_report_type = 'OP EXPENSE') THEN
1475 INSERT INTO IGI_IAC_BALANCES_REPORT
1476 (Asset_ID,
1477 Distribution_CCID,
1478 Adjustment_CCID,
1479 Category_Books_Account,
1480 Source_Type_Code,
1481 Amount,
1482 Data_Source,
1483 Display_order)
1484 VALUES
1485 (l_transaction.asset_id,
1486 l_distribution_ccid,
1487 l_account.adjustment_ccid,
1488 NULL,
1489 'RETIREMENT',
1490 l_dist.operating_acct_cost,
1491 'Cost',
1492 1);
1493
1494 INSERT INTO IGI_IAC_BALANCES_REPORT
1495 (Asset_ID,
1496 Distribution_CCID,
1497 Adjustment_CCID,
1498 Category_Books_Account,
1499 Source_Type_Code,
1500 Amount,
1501 Data_Source,
1502 Display_order)
1503 VALUES
1504 (l_transaction.asset_id,
1505 l_distribution_ccid,
1506 l_account.adjustment_ccid,
1507 NULL,
1508 'RETIREMENT',
1509 l_dist.operating_acct_backlog,
1510 'Backlog',
1511 2);
1512 END IF;
1513
1514 END LOOP;
1515 END LOOP;
1516 END LOOP;
1517
1518 END LOOP;
1519
1520 END get_general_fund;
1521
1522
1523 PROCEDURE Insert_info (
1524 p_book varchar2,
1525 p_start_period_name varchar2,
1526 p_end_period_name varchar2,
1527 p_report_type varchar2) IS
1528
1529 l_Period1_PC number(15);
1530 l_Period1_POD date;
1531 l_Period1_PCD date;
1532 l_Period2_PC number(15);
1533 l_Period2_PCD date;
1534 l_Distribution_Source_Book varchar2(15);
1535 l_balance_type varchar2(3);
1536 l_rowid rowid;
1537 l_chart_of_accounts_id number;
1538 l_company varchar2(30);
1539 l_cost_ctr varchar2(30);
1540 l_account varchar2(30);
1541 l_appl_id number;
1542 l_company_segment number;
1543 l_account_segment number;
1544 l_cc_segment number;
1545
1546 CURSOR c_get_balances IS
1547 SELECT rowid,ibr.*
1548 FROM igi_iac_balances_report ibr;
1549
1550 PROCEDURE Get_Company_CostCtr(
1551 appl_short_name IN VARCHAR2,
1552 key_flex_code IN VARCHAR2,
1553 structure_number IN NUMBER,
1554 combination_id IN NUMBER,
1555 company_segment IN NUMBER,
1556 cc_segment IN NUMBER,
1557 company_value OUT NOCOPY VARCHAR2,
1558 cc_value OUT NOCOPY VARCHAR2) IS
1559
1560 segment_count NUMBER;
1561 segments FND_FLEX_EXT.SegmentArray;
1562 segment_value VARCHAR2(30);
1563
1564 BEGIN
1565
1566 IF FND_FLEX_EXT.get_segments(appl_short_name, key_flex_code,
1567 structure_number, combination_id, segment_count, segments)
1568 then
1569 company_value := segments(company_segment);
1570 cc_value := segments(cc_segment);
1571 END IF;
1572
1573
1574 EXCEPTION
1575 when NO_DATA_FOUND then
1576 --Debug_Print('Application short name not found.');
1577 company_value := NULL;
1578 cc_value := NULL;
1579
1580 when OTHERS then
1581 --Debug_Print('Error in procedure get_qulaified_segment');
1582 company_value := NULL;
1583 cc_value := NULL;
1584
1585 END get_company_costctr;
1586
1587
1588 FUNCTION Get_account(
1589 appl_short_name IN VARCHAR2,
1590 key_flex_code IN VARCHAR2,
1591 structure_number IN NUMBER,
1592 combination_id IN NUMBER,
1593 account_segment IN NUMBER)
1594 RETURN VARCHAR2 IS
1595
1596 segment_count NUMBER;
1597 segments FND_FLEX_EXT.SegmentArray;
1598 segment_value VARCHAR2(30);
1599
1600 BEGIN
1601
1602 IF FND_FLEX_EXT.get_segments(appl_short_name, key_flex_code,
1603 structure_number, combination_id, segment_count, segments)
1604 then
1605 segment_value := segments(account_segment);
1606 return(segment_value);
1607 END IF;
1608 return null;
1609
1610 EXCEPTION
1611 when NO_DATA_FOUND then
1612 --Debug_Print('Application short name not found.');
1613 return NULL;
1614
1615 when OTHERS then
1616 --Debug_Print('Error in procedure get_qulaified_segment');
1617 return NULL;
1618
1619 END get_account;
1620
1621
1622 BEGIN
1623 Debug_Print('Inside Insert_Info');
1624 SELECT
1625 P1.Period_Counter,
1626 P1.Period_Open_Date,
1627 NVL(P1.Period_Close_Date, SYSDATE),
1628 P2.Period_Counter,
1629 NVL(P2.Period_Close_Date, SYSDATE),
1630 BC.Distribution_Source_Book
1631 INTO
1632 l_Period1_PC,
1633 l_Period1_POD,
1634 l_Period1_PCD,
1635 l_Period2_PC,
1636 l_Period2_PCD,
1637 l_Distribution_Source_Book
1638 FROM
1639 FA_DEPRN_PERIODS P1,
1640 FA_DEPRN_PERIODS P2,
1641 FA_BOOK_CONTROLS BC
1642 WHERE
1643 BC.Book_Type_Code = p_Book AND
1644 P1.Book_Type_Code = p_Book AND
1645 P1.Period_Name = p_Start_Period_Name AND
1646 P2.Book_Type_Code = p_Book AND
1647 P2.Period_Name = p_End_Period_Name;
1648
1649 Debug_Print('Before assigning balance type');
1650 -- 02-Jun-2003, mh, add "OR p_report_type = 'OP EXPENSE'" to the statement below as part of
1651 -- reporting enhancement project
1652
1653 IF (p_report_type = 'COST' OR p_report_type = 'OP EXPENSE') THEN
1654 l_balance_type := 'DR';
1655 ELSE
1656 l_balance_type := 'CR';
1657 END IF;
1658
1659 Debug_Print('Before processing for Delete');
1660 DELETE FROM igi_iac_balances_report;
1661
1662 Debug_Print('Before processing for beginning balances');
1663 Get_Balance(
1664 p_book => p_book,
1665 p_distribution_source_book => l_distribution_source_book,
1666 p_period_pc => l_period1_pc - 1,
1667 p_earliest_pc => l_period1_pc - 1,
1668 p_period_date => l_period1_POD,
1669 p_additions_date => l_period1_PCD,
1670 p_earliest_date => l_period1_POD,
1671 p_report_type => p_report_type,
1672 p_balance_type => l_balance_type,
1673 p_begin_or_end => 'BEGIN');
1674
1675 Debug_Print('Before processing for ending balances');
1676 Get_Balance(
1677 p_book => p_book,
1678 p_distribution_source_book => l_distribution_source_book,
1679 p_period_pc => l_period2_pc,
1680 p_earliest_pc => l_period1_pc - 1,
1681 p_period_date => l_period2_PCD,
1682 p_additions_date => l_period2_PCD,
1683 p_earliest_date => l_period1_POD,
1684 p_report_type => p_report_type,
1685 p_balance_type => l_balance_type,
1686 p_begin_or_end => 'END');
1687
1688 Debug_Print('Before processing for adjustments balances');
1689 Get_Adjustments(
1690 p_book => p_book,
1691 p_distribution_source_book => l_distribution_source_book,
1692 p_period1_pc => l_period1_pc,
1693 p_period2_pc => l_period2_pc,
1694 p_report_type => p_report_type,
1695 p_balance_type => l_balance_type);
1696
1697 Debug_Print('Before processing for retirements');
1698 IF (p_report_type = 'REVAL RESERVE' OR p_report_type = 'OP EXPENSE') THEN
1699 Get_General_Fund(
1700 p_book => p_book,
1701 p_period1_pc => l_period1_pc,
1702 p_period2_pc => l_period2_pc,
1703 p_report_type => p_report_type,
1704 p_balance_type => l_balance_type);
1705 END IF;
1706
1707 IF (p_report_type = 'RESERVE') THEN
1708
1709 Debug_Print('Before processing for depreciation balances');
1710 Get_Deprn_Effects(
1711 p_book => p_book,
1712 p_distribution_source_book => l_distribution_source_book,
1713 p_period1_pc => l_period1_pc,
1714 p_period2_pc => l_period2_pc,
1715 p_report_type => p_report_type,
1716 p_balance_type => l_balance_type);
1717
1718 END IF;
1719
1720 DELETE FROM igi_iac_balances_report
1721 WHERE amount = 0;
1722
1723 SELECT SOB.Chart_of_Accounts_ID
1724 INTO l_chart_of_accounts_id
1725 FROM fa_book_controls BC,
1726 gl_sets_of_books SOB
1727 WHERE BC.Book_Type_Code = p_book AND
1728 SOB.Set_Of_Books_ID = BC.Set_Of_Books_ID;
1729
1730 SELECT application_id
1731 INTO l_appl_id FROM fnd_application
1732 WHERE application_short_name = 'SQLGL';
1733
1734 IF (FND_FLEX_APIS.get_qualifier_segnum(l_appl_id, 'GL#',
1735 l_chart_of_accounts_id, 'GL_BALANCING', l_company_segment))
1736 AND (FND_FLEX_APIS.get_qualifier_segnum(l_appl_id, 'GL#',
1737 l_chart_of_accounts_id, 'GL_ACCOUNT', l_account_segment))
1738 AND (FND_FLEX_APIS.get_qualifier_segnum(l_appl_id, 'GL#',
1739 l_chart_of_accounts_id, 'FA_COST_CTR', l_cc_segment)) THEN
1740 NULL;
1741 END IF;
1742
1743 FOR l_balance IN c_get_balances LOOP
1744
1745 get_company_costctr(
1746 'SQLGL',
1747 'GL#',
1748 l_chart_of_accounts_id,
1749 l_balance.distribution_ccid,
1750 l_company_segment,
1751 l_cc_segment,
1752 l_company,
1753 l_cost_ctr);
1754
1755 IF p_report_type in ('COST','RESERVE') THEN
1756
1757 if l_balance.category_books_account is not null then
1758 l_account := l_balance.category_books_account;
1759 else
1760 l_account := get_account(
1761 'SQLGL',
1762 'GL#',
1763 l_chart_of_accounts_id,
1764 l_balance.adjustment_ccid,
1765 l_account_segment);
1766 end if;
1767
1768 END IF;
1769
1770 IF p_report_type in ('REVAL RESERVE', 'OP EXPENSE', 'BL RESERVE') THEN
1771
1772 l_account := get_account(
1773 'SQLGL',
1774 'GL#',
1775 l_chart_of_accounts_id,
1776 nvl(l_balance.adjustment_ccid,l_balance.category_books_account),
1777 l_account_segment);
1778
1779 END IF;
1780
1781 UPDATE igi_iac_balances_report
1782 SET company = l_company,
1783 cost_center = l_cost_ctr,
1784 account = l_account
1785 WHERE rowid = l_balance.rowid;
1786
1787 END LOOP;
1788
1789 EXCEPTION
1790 WHEN others THEN
1791 Debug_Print('Error in Insert Info :'||sqlerrm);
1792 NULL ;
1793 END Insert_Info;
1794
1795 FUNCTION DO_INSERTFORMULA RETURN NUMBER IS
1796 BEGIN
1797 IF (P_REPORT_TYPE = 'REVAL RESERVE') THEN
1798 INSERT_INFO(P_BOOK
1799 ,P_PERIOD1
1800 ,P_PERIOD2
1801 ,P_REPORT_TYPE);
1802 RETURN (1);
1803 ELSE
1804 RETURN (0);
1805 END IF;
1806 RETURN NULL;
1807 END DO_INSERTFORMULA;
1808
1809 FUNCTION REPORT_NAMEFORMULA(COMPANY_NAME IN VARCHAR2
1810 ,CURRENCY_CODE IN VARCHAR2) RETURN CHAR IS
1811 BEGIN
1812 DECLARE
1813 L_REPORT_NAME VARCHAR2(80);
1814 BEGIN
1815 RP_COMPANY_NAME := COMPANY_NAME;
1816 SELECT
1817 CP.USER_CONCURRENT_PROGRAM_NAME
1818 INTO L_REPORT_NAME
1819 FROM
1820 FND_CONCURRENT_PROGRAMS_VL CP,
1821 FND_CONCURRENT_REQUESTS CR
1822 WHERE CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID
1823 AND CR.REQUEST_ID = P_CONC_REQUEST_ID
1824 AND CP.APPLICATION_ID = 8400;
1825 l_report_name := substr(l_report_name,1,instr(l_report_name,' (XML)'));
1826 RP_REPORT_NAME := L_REPORT_NAME || ' (' || CURRENCY_CODE || ')';
1827 RETURN (L_REPORT_NAME);
1828 EXCEPTION
1829 WHEN OTHERS THEN
1830 RP_REPORT_NAME := 'Inflation Accounting : Revaluation Reserve Summary Report';
1831 RETURN (RP_REPORT_NAME);
1832 END;
1833 RETURN NULL;
1834 END REPORT_NAMEFORMULA;
1835
1836 FUNCTION COST_BEGINNINGFORMULA(BALANCE_TYPE IN VARCHAR2
1837 ,BEGINNING IN NUMBER) RETURN NUMBER IS
1838 L_NUM NUMBER := 0;
1839 BEGIN
1840 IF (BALANCE_TYPE = 'Cost') THEN
1841 L_NUM := BEGINNING;
1842 END IF;
1843 RETURN (L_NUM);
1844 END COST_BEGINNINGFORMULA;
1845
1846 FUNCTION COST_ADDITIONFORMULA(BALANCE_TYPE IN VARCHAR2
1847 ,ADDITION IN NUMBER) RETURN NUMBER IS
1848 L_NUM NUMBER := 0;
1849 BEGIN
1850 IF (BALANCE_TYPE = 'Cost') THEN
1851 L_NUM := ADDITION;
1852 END IF;
1853 RETURN (L_NUM);
1854 END COST_ADDITIONFORMULA;
1855
1856 FUNCTION COST_DEPRECIATIONFORMULA(BALANCE_TYPE IN VARCHAR2
1857 ,DEPRECIATION IN NUMBER) RETURN NUMBER IS
1858 L_NUM NUMBER := 0;
1859 BEGIN
1860 IF (BALANCE_TYPE = 'Cost') THEN
1861 L_NUM := DEPRECIATION;
1862 END IF;
1863 RETURN (L_NUM);
1864 END COST_DEPRECIATIONFORMULA;
1865
1866 FUNCTION COST_ADJUSTMENTFORMULA(BALANCE_TYPE IN VARCHAR2
1867 ,ADJUSTMENT IN NUMBER) RETURN NUMBER IS
1868 L_NUM NUMBER := 0;
1869 BEGIN
1870 IF (BALANCE_TYPE = 'Cost') THEN
1871 L_NUM := ADJUSTMENT;
1872 END IF;
1873 RETURN (L_NUM);
1874 END COST_ADJUSTMENTFORMULA;
1875
1876 FUNCTION COST_RETIREMENTFORMULA(BALANCE_TYPE IN VARCHAR2
1877 ,RETIREMENT IN NUMBER) RETURN NUMBER IS
1878 L_NUM NUMBER := 0;
1879 BEGIN
1880 IF (BALANCE_TYPE = 'Cost') THEN
1881 L_NUM := RETIREMENT;
1882 END IF;
1883 RETURN (L_NUM);
1884 END COST_RETIREMENTFORMULA;
1885
1886 FUNCTION COST_REVALUATIONFORMULA(BALANCE_TYPE IN VARCHAR2
1887 ,REVALUATION IN NUMBER) RETURN NUMBER IS
1888 L_NUM NUMBER := 0;
1889 BEGIN
1890 IF (BALANCE_TYPE = 'Cost') THEN
1891 L_NUM := REVALUATION;
1892 END IF;
1893 RETURN (L_NUM);
1894 END COST_REVALUATIONFORMULA;
1895
1896 FUNCTION COST_RECLASSFORMULA(BALANCE_TYPE IN VARCHAR2
1897 ,RECLASS IN NUMBER) RETURN NUMBER IS
1898 L_NUM NUMBER := 0;
1899 BEGIN
1900 IF (BALANCE_TYPE = 'Cost') THEN
1901 L_NUM := RECLASS;
1902 END IF;
1903 RETURN (L_NUM);
1904 END COST_RECLASSFORMULA;
1905
1906 FUNCTION COST_TRANSFERFORMULA(BALANCE_TYPE IN VARCHAR2
1907 ,TRANSFER IN NUMBER) RETURN NUMBER IS
1908 L_NUM NUMBER := 0;
1909 BEGIN
1910 IF (BALANCE_TYPE = 'Cost') THEN
1911 L_NUM := TRANSFER;
1912 END IF;
1913 RETURN (L_NUM);
1914 END COST_TRANSFERFORMULA;
1915
1916 FUNCTION COST_ENDINGFORMULA(BALANCE_TYPE IN VARCHAR2
1917 ,ENDING IN NUMBER) RETURN NUMBER IS
1918 L_NUM NUMBER := 0;
1919 BEGIN
1920 IF (BALANCE_TYPE = 'Cost') THEN
1921 L_NUM := ENDING;
1922 END IF;
1923 RETURN (L_NUM);
1924 END COST_ENDINGFORMULA;
1925
1926 FUNCTION GFUND_BEGINNINGFORMULA(BALANCE_TYPE IN VARCHAR2
1927 ,BEGINNING IN NUMBER) RETURN NUMBER IS
1928 L_NUM NUMBER := 0;
1929 BEGIN
1930 IF (BALANCE_TYPE = 'General Fund') THEN
1931 L_NUM := BEGINNING;
1932 END IF;
1933 RETURN (L_NUM);
1934 END GFUND_BEGINNINGFORMULA;
1935
1936 FUNCTION GFUND_ADDITIONFORMULA(BALANCE_TYPE IN VARCHAR2
1937 ,ADDITION IN NUMBER) RETURN NUMBER IS
1938 L_NUM NUMBER := 0;
1939 BEGIN
1940 IF (BALANCE_TYPE = 'General Fund') THEN
1941 L_NUM := ADDITION;
1942 END IF;
1943 RETURN (L_NUM);
1944 END GFUND_ADDITIONFORMULA;
1945
1946 FUNCTION GFUND_DEPRECIATIONFORMULA(BALANCE_TYPE IN VARCHAR2
1947 ,DEPRECIATION IN NUMBER) RETURN NUMBER IS
1948 L_NUM NUMBER := 0;
1949 BEGIN
1950 IF (BALANCE_TYPE = 'General Fund') THEN
1951 L_NUM := DEPRECIATION;
1952 END IF;
1953 RETURN (L_NUM);
1954 END GFUND_DEPRECIATIONFORMULA;
1955
1956 FUNCTION GFUND_ADJUSTMENTFORMULA(BALANCE_TYPE IN VARCHAR2
1957 ,ADJUSTMENT IN NUMBER) RETURN NUMBER IS
1958 L_NUM NUMBER := 0;
1959 BEGIN
1960 IF (BALANCE_TYPE = 'General Fund') THEN
1961 L_NUM := ADJUSTMENT;
1962 END IF;
1963 RETURN (L_NUM);
1964 END GFUND_ADJUSTMENTFORMULA;
1965
1966 FUNCTION GFUND_RETIREMENTFORMULA(BALANCE_TYPE IN VARCHAR2
1967 ,RETIREMENT IN NUMBER) RETURN NUMBER IS
1968 L_NUM NUMBER := 0;
1969 BEGIN
1970 IF (BALANCE_TYPE = 'General Fund') THEN
1971 L_NUM := RETIREMENT;
1972 END IF;
1973 RETURN (L_NUM);
1974 END GFUND_RETIREMENTFORMULA;
1975
1976 FUNCTION GFUND_REVALUATIONFORMULA(BALANCE_TYPE IN VARCHAR2
1977 ,REVALUATION IN NUMBER) RETURN NUMBER IS
1978 L_NUM NUMBER := 0;
1979 BEGIN
1980 IF (BALANCE_TYPE = 'General Fund') THEN
1981 L_NUM := REVALUATION;
1982 END IF;
1983 RETURN (L_NUM);
1984 END GFUND_REVALUATIONFORMULA;
1985
1986 FUNCTION GFUND_RECLASSFORMULA(BALANCE_TYPE IN VARCHAR2
1987 ,RECLASS IN NUMBER) RETURN NUMBER IS
1988 L_NUM NUMBER := 0;
1989 BEGIN
1990 IF (BALANCE_TYPE = 'General Fund') THEN
1991 L_NUM := RECLASS;
1992 END IF;
1993 RETURN (L_NUM);
1994 END GFUND_RECLASSFORMULA;
1995
1996 FUNCTION GFUND_TRANSFERFORMULA(BALANCE_TYPE IN VARCHAR2
1997 ,TRANSFER IN NUMBER) RETURN NUMBER IS
1998 L_NUM NUMBER := 0;
1999 BEGIN
2000 IF (BALANCE_TYPE = 'General Fund') THEN
2001 L_NUM := TRANSFER;
2002 END IF;
2003 RETURN (L_NUM);
2004 END GFUND_TRANSFERFORMULA;
2005
2006 FUNCTION GFUND_ENDINGFORMULA(BALANCE_TYPE IN VARCHAR2
2007 ,ENDING IN NUMBER) RETURN NUMBER IS
2008 L_NUM NUMBER := 0;
2009 BEGIN
2010 IF (BALANCE_TYPE = 'General Fund') THEN
2011 L_NUM := ENDING;
2012 END IF;
2013 RETURN (L_NUM);
2014 END GFUND_ENDINGFORMULA;
2015
2016 FUNCTION BLOG_BEGINNINGFORMULA(BALANCE_TYPE IN VARCHAR2
2017 ,BEGINNING IN NUMBER) RETURN NUMBER IS
2018 L_NUM NUMBER := 0;
2019 BEGIN
2020 IF (BALANCE_TYPE = 'Backlog') THEN
2021 L_NUM := BEGINNING;
2022 END IF;
2023 RETURN (L_NUM);
2024 END BLOG_BEGINNINGFORMULA;
2025
2026 FUNCTION BLOG_ADDITIONFORMULA(BALANCE_TYPE IN VARCHAR2
2027 ,ADDITION IN NUMBER) RETURN NUMBER IS
2028 L_NUM NUMBER := 0;
2029 BEGIN
2030 IF (BALANCE_TYPE = 'Backlog') THEN
2031 L_NUM := ADDITION;
2032 END IF;
2033 RETURN (L_NUM);
2034 END BLOG_ADDITIONFORMULA;
2035
2036 FUNCTION BLOG_DEPRECIATIONFORMULA(BALANCE_TYPE IN VARCHAR2
2037 ,DEPRECIATION IN NUMBER) RETURN NUMBER IS
2038 L_NUM NUMBER := 0;
2039 BEGIN
2040 IF (BALANCE_TYPE = 'Backlog') THEN
2041 L_NUM := DEPRECIATION;
2042 END IF;
2043 RETURN (L_NUM);
2044 END BLOG_DEPRECIATIONFORMULA;
2045
2046 FUNCTION BLOG_ADJUSTMENTFORMULA(BALANCE_TYPE IN VARCHAR2
2047 ,ADJUSTMENT IN NUMBER) RETURN NUMBER IS
2048 L_NUM NUMBER := 0;
2049 BEGIN
2050 IF (BALANCE_TYPE = 'Backlog') THEN
2051 L_NUM := ADJUSTMENT;
2052 END IF;
2053 RETURN (L_NUM);
2054 END BLOG_ADJUSTMENTFORMULA;
2055
2056 FUNCTION BLOG_RETIREMENTFORMULA(BALANCE_TYPE IN VARCHAR2
2057 ,RETIREMENT IN NUMBER) RETURN NUMBER IS
2058 L_NUM NUMBER := 0;
2059 BEGIN
2060 IF (BALANCE_TYPE = 'Backlog') THEN
2061 L_NUM := RETIREMENT;
2062 END IF;
2063 RETURN (L_NUM);
2064 END BLOG_RETIREMENTFORMULA;
2065
2066 FUNCTION BLOG_REVALUATIONFORMULA(BALANCE_TYPE IN VARCHAR2
2067 ,REVALUATION IN NUMBER) RETURN NUMBER IS
2068 L_NUM NUMBER := 0;
2069 BEGIN
2070 IF (BALANCE_TYPE = 'Backlog') THEN
2071 L_NUM := REVALUATION;
2072 END IF;
2073 RETURN (L_NUM);
2074 END BLOG_REVALUATIONFORMULA;
2075
2076 FUNCTION BLOG_RECLASSFORMULA(BALANCE_TYPE IN VARCHAR2
2077 ,RECLASS IN NUMBER) RETURN NUMBER IS
2078 L_NUM NUMBER := 0;
2079 BEGIN
2080 IF (BALANCE_TYPE = 'Backlog') THEN
2081 L_NUM := RECLASS;
2082 END IF;
2083 RETURN (L_NUM);
2084 END BLOG_RECLASSFORMULA;
2085
2086 FUNCTION BLOG_TRANSFERFORMULA(BALANCE_TYPE IN VARCHAR2
2087 ,TRANSFER IN NUMBER) RETURN NUMBER IS
2088 L_NUM NUMBER := 0;
2089 BEGIN
2090 IF (BALANCE_TYPE = 'Backlog') THEN
2091 L_NUM := TRANSFER;
2092 END IF;
2093 RETURN (L_NUM);
2094 END BLOG_TRANSFERFORMULA;
2095
2096 FUNCTION BLOG_ENDINGFORMULA(BALANCE_TYPE IN VARCHAR2
2097 ,ENDING IN NUMBER) RETURN NUMBER IS
2098 L_NUM NUMBER := 0;
2099 BEGIN
2100 IF (BALANCE_TYPE = 'Backlog') THEN
2101 L_NUM := ENDING;
2102 END IF;
2103 RETURN (L_NUM);
2104 END BLOG_ENDINGFORMULA;
2105
2106 FUNCTION NET_BEGINNINGFORMULA(BALANCE_TYPE IN VARCHAR2
2107 ,BEGINNING IN NUMBER) RETURN NUMBER IS
2108 L_NUM NUMBER := 0;
2109 BEGIN
2110 IF (BALANCE_TYPE = 'Net') THEN
2111 L_NUM := BEGINNING;
2112 END IF;
2113 RETURN (L_NUM);
2114 END NET_BEGINNINGFORMULA;
2115
2116 FUNCTION NET_ADDITIONFORMULA(BALANCE_TYPE IN VARCHAR2
2117 ,ADDITION IN NUMBER) RETURN NUMBER IS
2118 L_NUM NUMBER := 0;
2119 BEGIN
2120 IF (BALANCE_TYPE = 'Net') THEN
2121 L_NUM := ADDITION;
2122 END IF;
2123 RETURN (L_NUM);
2124 END NET_ADDITIONFORMULA;
2125
2126 FUNCTION NET_DEPRECIATIONFORMULA(BALANCE_TYPE IN VARCHAR2
2127 ,DEPRECIATION IN NUMBER) RETURN NUMBER IS
2128 L_NUM NUMBER := 0;
2129 BEGIN
2130 IF (BALANCE_TYPE = 'Net') THEN
2131 L_NUM := DEPRECIATION;
2132 END IF;
2133 RETURN (L_NUM);
2134 END NET_DEPRECIATIONFORMULA;
2135
2136 FUNCTION NET_ADJUSTMENTFORMULA(BALANCE_TYPE IN VARCHAR2
2137 ,ADJUSTMENT IN NUMBER) RETURN NUMBER IS
2138 L_NUM NUMBER := 0;
2139 BEGIN
2140 IF (BALANCE_TYPE = 'Net') THEN
2141 L_NUM := ADJUSTMENT;
2142 END IF;
2143 RETURN (L_NUM);
2144 END NET_ADJUSTMENTFORMULA;
2145
2146 FUNCTION NET_RETIREMENTFORMULA(BALANCE_TYPE IN VARCHAR2
2147 ,RETIREMENT IN NUMBER) RETURN NUMBER IS
2148 L_NUM NUMBER := 0;
2149 BEGIN
2150 IF (BALANCE_TYPE = 'Net') THEN
2151 L_NUM := RETIREMENT;
2152 END IF;
2153 RETURN (L_NUM);
2154 END NET_RETIREMENTFORMULA;
2155
2156 FUNCTION NET_REVALUATIONFORMULA(BALANCE_TYPE IN VARCHAR2
2157 ,REVALUATION IN NUMBER) RETURN NUMBER IS
2158 L_NUM NUMBER := 0;
2159 BEGIN
2160 IF (BALANCE_TYPE = 'Net') THEN
2161 L_NUM := REVALUATION;
2162 END IF;
2163 RETURN (L_NUM);
2164 END NET_REVALUATIONFORMULA;
2165
2166 FUNCTION NET_RECLASSFORMULA(BALANCE_TYPE IN VARCHAR2
2167 ,RECLASS IN NUMBER) RETURN NUMBER IS
2168 L_NUM NUMBER := 0;
2169 BEGIN
2170 IF (BALANCE_TYPE = 'Net') THEN
2171 L_NUM := RECLASS;
2172 END IF;
2173 RETURN (L_NUM);
2174 END NET_RECLASSFORMULA;
2175
2176 FUNCTION NET_TRANSFERFORMULA(BALANCE_TYPE IN VARCHAR2
2177 ,TRANSFER IN NUMBER) RETURN NUMBER IS
2178 L_NUM NUMBER := 0;
2179 BEGIN
2180 IF (BALANCE_TYPE = 'Net') THEN
2181 L_NUM := TRANSFER;
2182 END IF;
2183 RETURN (L_NUM);
2184 END NET_TRANSFERFORMULA;
2185
2186 FUNCTION NET_ENDINGFORMULA(BALANCE_TYPE IN VARCHAR2
2187 ,ENDING IN NUMBER) RETURN NUMBER IS
2188 L_NUM NUMBER := 0;
2189 BEGIN
2190 IF (BALANCE_TYPE = 'Net') THEN
2191 L_NUM := ENDING;
2192 END IF;
2193 RETURN (L_NUM);
2194 END NET_ENDINGFORMULA;
2195
2196
2197
2198 FUNCTION ACCT_BAL_APROMPT_P RETURN VARCHAR2 IS
2199 BEGIN
2200 RETURN ACCT_BAL_APROMPT;
2201 END ACCT_BAL_APROMPT_P;
2202
2203 FUNCTION ACCT_CC_APROMPT_P RETURN VARCHAR2 IS
2204 BEGIN
2205 RETURN ACCT_CC_APROMPT;
2206 END ACCT_CC_APROMPT_P;
2207
2208 FUNCTION RP_BAL_LPROMPT_P RETURN VARCHAR2 IS
2209 BEGIN
2210 RETURN RP_BAL_LPROMPT;
2211 END RP_BAL_LPROMPT_P;
2212
2213 FUNCTION RP_CC_LPROMPT_P RETURN VARCHAR2 IS
2214 BEGIN
2215 RETURN RP_CC_LPROMPT;
2216 END RP_CC_LPROMPT_P;
2217
2218 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
2219 BEGIN
2220 RETURN RP_COMPANY_NAME;
2221 END RP_COMPANY_NAME_P;
2222
2223 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
2224 BEGIN
2225 RETURN RP_REPORT_NAME;
2226 END RP_REPORT_NAME_P;
2227
2228 END IGI_IGIIARRD_XMLP_PKG;