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