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