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