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