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