4 BEGIN
1 PACKAGE BODY IGI_IGIIARPS_XMLP_PKG AS
2 /* $Header: IGIIARPSB.pls 120.0.12010000.2 2008/08/14 13:24:26 sharoy ship $ */
3 FUNCTION CF_PERIODNAMEFORMULA RETURN NUMBER IS
5 SELECT
6 PERIOD_NAME
7 INTO CP_PERIOD_NAME
8 FROM
9 FA_DEPRN_PERIODS
10 WHERE PERIOD_COUNTER = P_PERIOD_COUNTER
11 AND BOOK_TYPE_CODE = P_BOOK_TYPE_CODE;
12 RETURN (1);
13 END CF_PERIODNAMEFORMULA;
14
15 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
16 BEGIN
17 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
18 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
19 INSERT_INFO_VAR := do_insertformula();
20
21 select SUBSTR(argument1,INSTR(argument1,'=',1)+1,LENGTH(argument1)),
25 into P_BOOK_TYPE_CODE,P_REVALUATION_ID,P_PERIOD_COUNTER
22 SUBSTR(argument2,INSTR(argument2,'=',1)+1,LENGTH(argument2)),
23 SUBSTR(argument3,INSTR(argument3,'=',1)+1,LENGTH(argument3))
24
26 from FND_CONCURRENT_REQUESTS
27 where request_id=P_CONC_REQUEST_ID;
28
29
30 SELECT accounting_flex_structure
31 INTO accounting_Flex_structure
32 FROM fa_book_controls
33 WHERE book_Type_code = p_book_type_code;
34
35 RETURN (TRUE);
36 END BEFOREREPORT;
37
38 FUNCTION MINOR_CATEGORYFORMULA RETURN VARCHAR2 IS
39 MINOR_CAT VARCHAR2(15);
40 BEGIN
41 RETURN ('abcd');
42 END MINOR_CATEGORYFORMULA;
43
44 FUNCTION AFTERREPORT RETURN BOOLEAN IS
45 BEGIN
46 ROLLBACK;
47 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
48 RETURN (TRUE);
49 END AFTERREPORT;
50
51 FUNCTION CF_CALC_COMPANY_NAMEFORMULA(COMPANY_NAME IN VARCHAR2) RETURN NUMBER IS
52 BEGIN
53 P_BOOK := P_BOOK_TYPE_CODE;
54 RP_COMPANY_NAME := COMPANY_NAME;
55 RETURN 1;
56 END CF_CALC_COMPANY_NAMEFORMULA;
57
58 FUNCTION CF_CURR_CODEFORMULA RETURN NUMBER IS
59 L_CURR_CODE VARCHAR2(20);
60 BEGIN
61 SELECT
62 A.CURRENCY_CODE
63 INTO L_CURR_CODE
64 FROM
65 GL_SETS_OF_BOOKS A,
66 FA_BOOK_CONTROLS B
67 WHERE A.SET_OF_BOOKS_ID = B.SET_OF_BOOKS_ID
68 AND B.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE;
69 CP_CURR_CODE := L_CURR_CODE;
70 RETURN (1);
71 EXCEPTION
72 WHEN OTHERS THEN
73 /*SRW.MESSAGE(100
74 ,'Failed. Currency code not defined')*/NULL;
75 /*SRW.MESSAGE(100
76 ,' No is ' || SQLERRM)*/NULL;
77 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
78 RETURN (1);
79 END CF_CURR_CODEFORMULA;
80
81 FUNCTION CF_REPORT_NAMEFORMULA RETURN NUMBER IS
82 L_REPORT_NAME VARCHAR2(240);
83 BEGIN
84 SELECT
85 CP.USER_CONCURRENT_PROGRAM_NAME
86 INTO L_REPORT_NAME
87 FROM
88 FND_CONCURRENT_PROGRAMS_TL CP,
89 FND_CONCURRENT_REQUESTS CR
90 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
91 AND CP.LANGUAGE = USERENV('LANG')
92 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
93 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
94 l_report_name:= substr(l_report_name,1,instr(l_report_name,' (XML)'));
95 RP_REPORT_NAME := L_REPORT_NAME;
96 RETURN (1);
97 EXCEPTION
98 WHEN OTHERS THEN
99 RP_REPORT_NAME := 'Inflation Accounting Asset Revaluation Preview Summary Report:';
100 /*SRW.MESSAGE(98
101 ,'report name is ' || RP_REPORT_NAME)*/NULL;
102 RETURN (1);
103 END CF_REPORT_NAMEFORMULA;
104
105 FUNCTION CF_PERIOD_NAMEFORMULA RETURN NUMBER IS
106 BEGIN
107 SELECT
108 PERIOD_NAME
109 INTO CP_PERIOD_NAME
110 FROM
111 FA_DEPRN_PERIODS
112 WHERE PERIOD_COUNTER = P_PERIOD_COUNTER
113 AND BOOK_TYPE_CODE = P_BOOK_TYPE_CODE;
114 SELECT
115 CT.FISCAL_YEAR_NAME FISCAL_YEAR_NAME
116 INTO P_FISCAL_YEAR
117 FROM
118 FA_BOOK_CONTROLS BC,
119 FA_CALENDAR_TYPES CT
120 WHERE BC.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
121 AND BC.DEPRN_CALENDAR = CT.CALENDAR_TYPE;
122 RETURN (1);
123 END CF_PERIOD_NAMEFORMULA;
124
125 FUNCTION CF_REP_TOLERANCEFORMULA(S_REP_B_REVAL_COST IN NUMBER
126 ,S_REP_A_REVAL_COST IN NUMBER) RETURN VARCHAR2 IS
127 TOL_VAL VARCHAR2(4);
128 GAP NUMBER;
129 BEGIN
130 IF CF_TOLERANCE_LEVEL IS NOT NULL AND S_REP_B_REVAL_COST <> 0 THEN
131 GAP := ABS(((NVL(S_REP_A_REVAL_COST
132 ,0) - NVL(S_REP_B_REVAL_COST
133 ,0)) / NVL(S_REP_B_REVAL_COST
134 ,0)) * 100);
135 IF GAP >= CF_TOLERANCE_LEVEL THEN
136 TOL_VAL := '***';
137 ELSE
138 TOL_VAL := ' ';
139 END IF;
140 END IF;
141 RETURN (TOL_VAL);
142 END CF_REP_TOLERANCEFORMULA;
143
144 FUNCTION CALC_TOLERANCE_LEVELFORMULA RETURN NUMBER IS
145 BEGIN
146 FND_PROFILE.GET('IGI_IAC_REVAL_TOLERANCE',CF_TOLERANCE_LEVEL);
147 RETURN 1;
148 END CALC_TOLERANCE_LEVELFORMULA;
149
150 FUNCTION CF_CO_TOLERANCEFORMULA(S_CO_B_REVAL_COST IN NUMBER
151 ,S_CO_A_REVAL_COST IN NUMBER) RETURN CHAR IS
152 TOL_VAL VARCHAR2(4);
153 GAP NUMBER;
154 BEGIN
155 IF CF_TOLERANCE_LEVEL IS NOT NULL AND S_CO_B_REVAL_COST <> 0 THEN
156 GAP := ABS(((NVL(S_CO_A_REVAL_COST
157 ,0) - NVL(S_CO_B_REVAL_COST
158 ,0)) / NVL(S_CO_B_REVAL_COST
159 ,0)) * 100);
160 IF GAP >= CF_TOLERANCE_LEVEL THEN
161 TOL_VAL := '***';
162 ELSE
163 TOL_VAL := ' ';
164 END IF;
165 END IF;
166 RETURN (TOL_VAL);
167 END CF_CO_TOLERANCEFORMULA;
168
169 FUNCTION CF_MC_TOLERANCEFORMULA(S_MC_B_REVAL_COST IN NUMBER
170 ,S_MC_A_REVAL_COST IN NUMBER) RETURN CHAR IS
171 TOL_VAL VARCHAR2(4);
172 GAP NUMBER;
173 BEGIN
174 IF CF_TOLERANCE_LEVEL IS NOT NULL AND S_MC_B_REVAL_COST <> 0 THEN
175 GAP := ABS(((NVL(S_MC_A_REVAL_COST
176 ,0) - NVL(S_MC_B_REVAL_COST
177 ,0)) / NVL(S_MC_B_REVAL_COST
178 ,0)) * 100);
179 IF GAP >= CF_TOLERANCE_LEVEL THEN
183 END IF;
180 TOL_VAL := '***';
181 ELSE
182 TOL_VAL := ' ';
184 END IF;
185 RETURN (TOL_VAL);
186 END CF_MC_TOLERANCEFORMULA;
187
188 FUNCTION CF_CAT_TOLERANCEFORMULA(S_CAT_B_REVAL_COST IN NUMBER
189 ,S_CAT_A_REVAL_COST IN NUMBER) RETURN CHAR IS
190 TOL_VAL VARCHAR2(4);
191 GAP NUMBER;
192 BEGIN
193 IF CF_TOLERANCE_LEVEL IS NOT NULL AND S_CAT_B_REVAL_COST <> 0 THEN
194 GAP := ABS(((NVL(S_CAT_A_REVAL_COST
195 ,0) - NVL(S_CAT_B_REVAL_COST
196 ,0)) / NVL(S_CAT_B_REVAL_COST
197 ,0)) * 100);
198 IF GAP >= CF_TOLERANCE_LEVEL THEN
199 TOL_VAL := '***';
200 ELSE
201 TOL_VAL := ' ';
202 END IF;
203 END IF;
204 RETURN (TOL_VAL);
205 END CF_CAT_TOLERANCEFORMULA;
206
207 FUNCTION CF_CC_TOLERANCEFORMULA(S_CC_B_REVAL_COST IN NUMBER
208 ,S_CC_A_REVAL_COST IN NUMBER) RETURN CHAR IS
209 TOL_VAL VARCHAR2(4);
210 GAP NUMBER;
211 BEGIN
212 CF_B_REVAL_COST := S_CC_B_REVAL_COST;
213 CF_A_REVAL_COST := S_CC_A_REVAL_COST;
214 IF CF_TOLERANCE_LEVEL IS NOT NULL AND CF_B_REVAL_COST <> 0 THEN
215 GAP := ABS(((NVL(CF_A_REVAL_COST
216 ,0) - NVL(CF_B_REVAL_COST
217 ,0)) / NVL(CF_B_REVAL_COST
218 ,0)) * 100);
219 IF GAP >= CF_TOLERANCE_LEVEL THEN
220 TOL_VAL := '***';
221 ELSE
222 TOL_VAL := ' ';
223 END IF;
224 END IF;
225 RETURN (TOL_VAL);
226 END CF_CC_TOLERANCEFORMULA;
227
228 FUNCTION CF_CALC_PLACEHOLDERSFORMULA RETURN NUMBER IS
229 TOL_VAL VARCHAR2(4);
230 GAP NUMBER;
231 BEGIN
232 RETURN 1;
233 END CF_CALC_PLACEHOLDERSFORMULA;
234
235 FUNCTION CF_MAJOR_CATFORMULA(MAJOR_CATEGORY IN VARCHAR2) RETURN NUMBER IS
236 BEGIN
237 CP_MAJOR_CATEGORY := MAJOR_CATEGORY;
238 RETURN 1;
239 END CF_MAJOR_CATFORMULA;
240
241 PROCEDURE INSERT_INFO IS
242 L_SOURCE_TYPE_CODE VARCHAR2(50);
243 L_AMOUNT1 VARCHAR2(200);
244 L_AMOUNT2 VARCHAR2(200);
245 L_AMOUNT3 VARCHAR2(200);
246 L_SQL VARCHAR2(15000);
247 IDX NUMBER;
248 L_ASSET_ID NUMBER;
249 L_DIST_CCID NUMBER;
250 L_SOURCE VARCHAR2(30);
251 L_AMOUNT NUMBER;
252 L_DATA_SOURCE VARCHAR2(30);
253 L_DISPLAY_ORDER NUMBER;
254 BEGIN
255 -- delete from igi_iac_balances_report;
256
257 FOR idx IN 1..5 LOOP
258 IF (idx = 1) THEN
259 l_source_type_code := 'COST';
260 l_amount1 := 'nvl(dd.cost,0)';
261 l_amount2 := 'nvl(idb.adjustment_cost,0)+nvl(dd.cost,0)';
262 l_amount3 := 'nvl(idb.adjustment_cost,0)+nvl(dd.cost,0)';
263 ELSIF (idx = 2) THEN
264 l_source_type_code := 'REVAL_RSV';
265 l_amount1 := 0;
266 l_amount2 := 'nvl(idb.reval_reserve_net,0)';
267 l_amount3 := 'nvl(idb.reval_reserve_net,0)';
268 ELSIF (idx = 3) THEN
269 l_source_type_code := 'OP_EXPENSE';
270 l_amount1 := 0;
271 l_amount2 := '(-1)*nvl(idb.operating_acct_net,0)';
272 l_amount3 := '(-1)*nvl(idb.operating_acct_net,0)';
273 ELSIF (idx = 4) THEN
274 l_source_type_code := 'DEP_RSV';
275 l_amount1 := 'dd.deprn_reserve';
276 l_Amount2 := 'nvl(idb.deprn_reserve+deprn_reserve_backlog,0)+ nvl(dd.deprn_reserve,0)';
277 l_Amount3 := 'nvl(idb.deprn_reserve+deprn_reserve_backlog,0)+ nvl(dd.deprn_reserve,0)';
278 ELSE
279 l_source_type_code := 'BACKLOG';
280 l_amount1 := 0;
281 l_amount2 := 'nvl(idb.deprn_reserve_backlog,0)';
282 l_amount3 := 'nvl(idb.deprn_reserve_backlog,0)';
283 END IF;
284
285 -- define the insert statement
286
287 l_sql := 'INSERT INTO igi_iac_balances_report(
288 asset_id,
289 distribution_ccid,
290 source_type_code,
291 amount,
292 data_source,
293 display_order )
294 SELECT ad.asset_id
295 ,dh.code_combination_id distribution_ccid
296 ,'''||l_source_type_code||''' source_type_code
297 ,'||l_amount1||' amount
298 ,''Before'' data_source
299 ,''1'' display_order
300 FROM fa_deprn_detail dd,
301 fa_books bk,
302 gl_code_combinations cc,
303 fa_additions ad,
304 fa_distribution_history dh,
305 fa_categories fc,
306 igi_iac_reval_asset_rules irar,
307 igi_iac_reval_categories irc
308 WHERE irc.revaluation_id = '||p_revaluation_id||
309 ' AND irar.asset_id = bk.asset_id
310 AND irar.revaluation_id = irc.revaluation_id
311 AND irar.category_id = irc.category_id
312 AND irc.select_category = ''Y''
313 AND irar.selected_for_reval_flag = ''Y''
314 AND irar.book_type_code = '''||p_book_type_code||'''
315 AND bk.book_type_code = irar.book_type_code
316 AND bk.asset_id = ad.asset_id
317 AND dd.period_counter = (SELECT MAX(period_counter)
318 FROM fa_deprn_summary ds
319 WHERE ds.asset_id =bk.asset_id
320 AND ds.book_type_code= '''||p_book_type_code||'''
321 AND ds.period_counter<= '||p_period_counter||')
322 AND EXISTS (SELECT MAX(ith.adjustment_id)
323 FROM igi_iac_transaction_headers ith
324 WHERE ith.asset_id = bk.asset_id
328 AND dh.book_type_code = bk.book_type_code
325 AND ith.book_type_code = '''||p_book_type_code||'''
326 AND ith.adjustment_status NOT IN (''PREVIEW'',''OBSOLETE''))
327 AND bk.date_ineffective IS NULL
329 AND dd.asset_id= bk.asset_id
330 AND dh.asset_id = dd.asset_id
331 AND dh.distribution_id = dd.distribution_id
332 AND dh.transaction_header_id_out IS NULL
333 AND dh.code_combination_id = cc.code_combination_id
334 AND fc.category_id=ad.asset_category_id
335 AND bk.asset_id NOT IN
336 (SELECT asset_id
337 FROM igi_iac_asset_balances
338 WHERE book_type_code = bk.book_type_code
339 AND asset_id = bk.asset_id)
340 AND bk.asset_id NOT IN (SELECT asset_id
341 FROM igi_iac_exceptions
342 WHERE revaluation_id = irc.revaluation_id)
343 UNION
344 SELECT ad.asset_id
345 ,dh.code_combination_id distribution_ccid
346 ,'''||l_source_type_code||''' source_type_code
347 ,'||l_amount2||' amount
348 ,''Before'' data_source
349 ,''1'' display_order
350 FROM igi_iac_det_balances idb,
351 fa_deprn_detail dd,
352 fa_books bk,
353 gl_code_combinations cc,
354 fa_additions ad,
355 fa_categories fc,
356 fa_distribution_history dh ,
357 igi_iac_reval_asset_rules irar,
358 igi_iac_reval_categories irc
359 WHERE irc.revaluation_id = '||p_revaluation_id||'
360 AND irar.asset_id = bk.asset_id
361 AND irar.selected_for_reval_flag = ''Y''
362 AND irar.revaluation_id = irc.revaluation_id
363 AND irar.category_id = irc.category_id
364 AND irc.select_category = ''Y''
365 AND irar.book_type_code = '''||p_book_type_code||'''
366 AND bk.book_type_code = irar.book_type_code
367 AND bk.asset_id = ad.asset_id
368 AND dd.asset_id=bk.asset_id
369 AND dd.period_counter =(SELECT max(period_counter)
370 FROM fa_deprn_summary ds
371 WHERE ds.asset_id =bk.asset_id
372 AND ds.book_type_code='''||p_book_type_code||'''
373 AND ds.period_counter<='||p_period_counter||')
374 AND idb.adjustment_id =(SELECT max(ith.adjustment_id)
375 FROM igi_iac_transaction_headers ith
376 WHERE ith.asset_id = bk.asset_id
377 AND ith.book_type_code ='''||p_book_type_code||'''
378 AND ith.adjustment_status NOT IN (''PREVIEW'', ''OBSOLETE''))
379 AND bk.date_ineffective IS NULL
380 AND dh.book_type_code = bk.book_type_code
381 AND dh.asset_id = dd.asset_id
382 AND dh.distribution_id = dd.distribution_id
383 AND dh.transaction_header_id_out is NULL
384 AND dh.distribution_id = idb.distribution_id
385 AND dh.asset_id = idb.asset_id
386 AND dh.book_type_code = idb.book_type_code
387 AND dh.code_combination_id = cc.code_combination_id
388 AND fc.category_id= ad.asset_category_id
389 AND bk.asset_id NOT IN (SELECT asset_id
390 FROM igi_iac_exceptions
391 WHERE revaluation_id = irc.revaluation_id)
392 UNION
393 SELECT ad.asset_id
394 ,dh.code_combination_id distribution_ccid
395 ,'''||l_source_type_code||''' source_type_code
396 ,'||l_amount3||' amount
397 ,''After'' data_source
398 ,''2'' display_order
399 FROM igi_iac_det_balances idb,
400 fa_deprn_detail dd,
401 fa_books bk,
402 gl_code_combinations cc,
403 fa_additions ad,
404 fa_categories fc,
405 fa_distribution_history dh,
406 igi_iac_reval_asset_rules irar,
407 igi_iac_reval_categories irc
408 WHERE irc.revaluation_id = '||p_revaluation_id||'
409 and irar.asset_id = bk.asset_id
410 and irar.selected_for_reval_flag = ''Y''
411 AND irar.revaluation_id = irc.revaluation_id
412 AND irar.category_id = irc.category_id
413 AND irc.select_category = ''Y''
414 and irar.book_type_code = '''||p_book_type_code||'''
415 and bk.book_type_code= irar.book_type_code
416 and bk.asset_id = ad.asset_id
417 and idb.adjustment_id=(select max(ith.adjustment_id)
418 from igi_iac_transaction_headers ith
419 where ith.asset_id = bk.asset_id
420 and ith.book_type_code ='''||p_book_type_code||'''
421 and ith.period_counter <='||p_period_counter||'
422 and ith.adjustment_status=''PREVIEW'')
423 and dd.period_counter=(select max(period_counter)
424 from fa_deprn_summary ds
425 where ds.asset_id =bk.asset_id
426 and ds.book_type_code='''||p_book_type_code||'''
427 and ds.period_counter<='||p_period_counter||')
428 and bk.date_ineffective is NULL
429 and dh.book_type_code = bk.book_type_code
430 and dh.asset_id = dd.asset_id
431 and dd.asset_id=bk.asset_id
432 and dh.distribution_id = dd.distribution_id
433 and dh.transaction_header_id_out is NULL
434 and dh.distribution_id = idb.distribution_id
435 and dh.code_combination_id = cc.code_combination_id
436 and fc.category_id=ad.asset_category_id
437 AND bk.asset_id NOT IN (SELECT asset_id
438 FROM igi_iac_exceptions
439 WHERE revaluation_id = irc.revaluation_id)
440 ';
441
442 -- srw.message(999,l_sql);
443
444 execute immediate l_sql;
445 END LOOP;
446
447 END INSERT_INFO;
448
452 RETURN (1);
449 FUNCTION DO_INSERTFORMULA RETURN NUMBER IS
450 BEGIN
451 INSERT_INFO;
453 EXCEPTION
454 WHEN OTHERS THEN
455 RETURN (0);
456 END DO_INSERTFORMULA;
457
458 FUNCTION B_REVALUED_COSTFORMULA(BALANCE_TYPE IN VARCHAR2
459 ,REVALUED_COST IN NUMBER) RETURN NUMBER IS
460 L_NUM NUMBER;
461 BEGIN
462 IF (BALANCE_TYPE = 'Before') THEN
463 L_NUM := REVALUED_COST;
464 END IF;
465 RETURN (L_NUM);
466 EXCEPTION
467 WHEN OTHERS THEN
468 RETURN (0);
469 END B_REVALUED_COSTFORMULA;
470
471 FUNCTION A_REVALUED_COSTFORMULA(BALANCE_TYPE IN VARCHAR2
472 ,REVALUED_COST IN NUMBER) RETURN NUMBER IS
473 L_NUM NUMBER;
474 BEGIN
475 IF (BALANCE_TYPE = 'After') THEN
476 L_NUM := REVALUED_COST;
477 END IF;
478 RETURN (L_NUM);
479 EXCEPTION
480 WHEN OTHERS THEN
481 RETURN (0);
482 END A_REVALUED_COSTFORMULA;
483
484 FUNCTION B_REVAL_RESERVEFORMULA(BALANCE_TYPE IN VARCHAR2
485 ,REVALUATION_RESERVE IN NUMBER) RETURN NUMBER IS
486 L_NUM NUMBER;
487 BEGIN
488 IF (BALANCE_TYPE = 'Before') THEN
489 L_NUM := REVALUATION_RESERVE;
490 END IF;
491 RETURN (L_NUM);
492 EXCEPTION
493 WHEN OTHERS THEN
494 RETURN (0);
495 END B_REVAL_RESERVEFORMULA;
496
497 FUNCTION A_REVAL_RESERVEFORMULA(BALANCE_TYPE IN VARCHAR2
498 ,REVALUATION_RESERVE IN NUMBER) RETURN NUMBER IS
499 L_NUM NUMBER;
500 BEGIN
501 IF (BALANCE_TYPE = 'After') THEN
502 L_NUM := REVALUATION_RESERVE;
503 END IF;
504 RETURN (L_NUM);
505 EXCEPTION
506 WHEN OTHERS THEN
507 RETURN (0);
508 END A_REVAL_RESERVEFORMULA;
509
510 FUNCTION B_OPERATING_ACCTFORMULA(BALANCE_TYPE IN VARCHAR2
511 ,OPERATING_ACCT IN NUMBER) RETURN NUMBER IS
512 L_NUM NUMBER;
513 BEGIN
514 IF (BALANCE_TYPE = 'Before') THEN
515 L_NUM := OPERATING_ACCT;
516 END IF;
517 RETURN (L_NUM);
518 EXCEPTION
519 WHEN OTHERS THEN
520 RETURN (0);
521 END B_OPERATING_ACCTFORMULA;
522
523 FUNCTION A_OPERATING_ACCTFORMULA(BALANCE_TYPE IN VARCHAR2
524 ,OPERATING_ACCT IN NUMBER) RETURN NUMBER IS
525 L_NUM NUMBER;
526 BEGIN
527 IF (BALANCE_TYPE = 'After') THEN
528 L_NUM := OPERATING_ACCT;
529 END IF;
530 RETURN (L_NUM);
531 EXCEPTION
532 WHEN OTHERS THEN
533 RETURN (0);
534 END A_OPERATING_ACCTFORMULA;
535
536 FUNCTION B_ACC_DEPRNFORMULA(BALANCE_TYPE IN VARCHAR2
537 ,ACCUMULATED_DEPRECIATION IN NUMBER) RETURN NUMBER IS
538 L_NUM NUMBER;
539 BEGIN
540 IF (BALANCE_TYPE = 'Before') THEN
541 L_NUM := ACCUMULATED_DEPRECIATION;
542 END IF;
543 RETURN (L_NUM);
544 EXCEPTION
545 WHEN OTHERS THEN
546 RETURN (0);
547 END B_ACC_DEPRNFORMULA;
548
549 FUNCTION A_ACC_DEPRNFORMULA(BALANCE_TYPE IN VARCHAR2
550 ,ACCUMULATED_DEPRECIATION IN NUMBER) RETURN NUMBER IS
551 L_NUM NUMBER;
552 BEGIN
553 IF (BALANCE_TYPE = 'After') THEN
554 L_NUM := ACCUMULATED_DEPRECIATION;
555 END IF;
556 RETURN (L_NUM);
557 EXCEPTION
558 WHEN OTHERS THEN
559 RETURN (0);
560 END A_ACC_DEPRNFORMULA;
561
562 FUNCTION B_ACC_BLOGFORMULA(BALANCE_TYPE IN VARCHAR2
563 ,ACCUMULATED_BACKLOG IN NUMBER) RETURN NUMBER IS
564 L_NUM NUMBER;
565 BEGIN
566 IF (BALANCE_TYPE = 'Before') THEN
567 L_NUM := ACCUMULATED_BACKLOG;
568 END IF;
569 RETURN (L_NUM);
570 EXCEPTION
571 WHEN OTHERS THEN
572 RETURN (0);
573 END B_ACC_BLOGFORMULA;
574
575 FUNCTION A_ACC_BLOGFORMULA(BALANCE_TYPE IN VARCHAR2
576 ,ACCUMULATED_BACKLOG IN NUMBER) RETURN NUMBER IS
577 L_NUM NUMBER;
578 BEGIN
579 IF (BALANCE_TYPE = 'After') THEN
580 L_NUM := ACCUMULATED_BACKLOG;
581 END IF;
582 RETURN (L_NUM);
583 EXCEPTION
584 WHEN OTHERS THEN
585 RETURN (0);
586 END A_ACC_BLOGFORMULA;
587
588 FUNCTION ACCOUNTING_FLEX_STRUCTURE_P RETURN NUMBER IS
589 BEGIN
590 RETURN ACCOUNTING_FLEX_STRUCTURE;
591 END ACCOUNTING_FLEX_STRUCTURE_P;
592
593 FUNCTION CP_REP_FORMAT_DIFF_P RETURN NUMBER IS
594 BEGIN
595 RETURN CP_REP_FORMAT_DIFF;
596 END CP_REP_FORMAT_DIFF_P;
597
598 FUNCTION CP_CO_FORMAT_DIFF_P RETURN NUMBER IS
599 BEGIN
600 RETURN CP_CO_FORMAT_DIFF;
601 END CP_CO_FORMAT_DIFF_P;
602
603 FUNCTION CP_MC_FORMAT_DIFF_P RETURN NUMBER IS
604 BEGIN
605 RETURN CP_MC_FORMAT_DIFF;
606 END CP_MC_FORMAT_DIFF_P;
607
608 FUNCTION F_MINOR_CATEGORY_P RETURN VARCHAR2 IS
609 BEGIN
610 RETURN F_MINOR_CATEGORY;
611 END F_MINOR_CATEGORY_P;
612
613 FUNCTION CP_MAJOR_CATEGORY_P RETURN VARCHAR2 IS
614 BEGIN
615 RETURN CP_MAJOR_CATEGORY;
616 END CP_MAJOR_CATEGORY_P;
617
618 FUNCTION CP_CAT_FORMAT_DIFF_P RETURN NUMBER IS
619 BEGIN
620 RETURN CP_CAT_FORMAT_DIFF;
621 END CP_CAT_FORMAT_DIFF_P;
622
623 FUNCTION CP_COST_ACCT_P RETURN VARCHAR2 IS
624 BEGIN
625 RETURN CP_COST_ACCT;
626 END CP_COST_ACCT_P;
627
628 FUNCTION CP_REVAL_RES_ACCT_P RETURN VARCHAR2 IS
629 BEGIN
630 RETURN CP_REVAL_RES_ACCT;
631 END CP_REVAL_RES_ACCT_P;
632
633 FUNCTION CP_ACC_DEPRN_ACCT_P RETURN VARCHAR2 IS
634 BEGIN
635 RETURN CP_ACC_DEPRN_ACCT;
636 END CP_ACC_DEPRN_ACCT_P;
637
638 FUNCTION CP_OPERATING_ACCT_P RETURN VARCHAR2 IS
639 BEGIN
640 RETURN CP_OPERATING_ACCT;
641 END CP_OPERATING_ACCT_P;
642
643 FUNCTION CP_BACKLOG_ACCT_P RETURN VARCHAR2 IS
644 BEGIN
645 RETURN CP_BACKLOG_ACCT;
646 END CP_BACKLOG_ACCT_P;
647
648 FUNCTION CP_CC_FORMAT_DIFF_P RETURN NUMBER IS
649 BEGIN
650 RETURN CP_CC_FORMAT_DIFF;
651 END CP_CC_FORMAT_DIFF_P;
652
653 FUNCTION CP_A_FORMAT_DIFF_P RETURN NUMBER IS
654 BEGIN
655 RETURN CP_A_FORMAT_DIFF;
656 END CP_A_FORMAT_DIFF_P;
657
658 FUNCTION F_REVAL_RESERVE_DIFF_P RETURN NUMBER IS
659 BEGIN
660 RETURN F_REVAL_RESERVE_DIFF;
661 END F_REVAL_RESERVE_DIFF_P;
662
663 FUNCTION F_REVAL_COST_DIFF_P RETURN NUMBER IS
664 BEGIN
665 RETURN F_REVAL_COST_DIFF;
666 END F_REVAL_COST_DIFF_P;
667
668 FUNCTION F_OPERATING_ACCT_DIFF_P RETURN NUMBER IS
669 BEGIN
670 RETURN F_OPERATING_ACCT_DIFF;
671 END F_OPERATING_ACCT_DIFF_P;
672
673 FUNCTION F_ACC_DEPRN_DIFF_P RETURN NUMBER IS
674 BEGIN
675 RETURN F_ACC_DEPRN_DIFF;
676 END F_ACC_DEPRN_DIFF_P;
677
678 FUNCTION F_ACC_BACKLOG_DIFF_P RETURN NUMBER IS
679 BEGIN
680 RETURN F_ACC_BACKLOG_DIFF;
681 END F_ACC_BACKLOG_DIFF_P;
682
683 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
684 BEGIN
685 RETURN RP_REPORT_NAME;
686 END RP_REPORT_NAME_P;
687
688 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
689 BEGIN
690 RETURN RP_COMPANY_NAME;
691 END RP_COMPANY_NAME_P;
692
693 FUNCTION CP_PERIOD_NAME_P RETURN VARCHAR2 IS
694 BEGIN
695 RETURN CP_PERIOD_NAME;
696 END CP_PERIOD_NAME_P;
697
698 FUNCTION P_FISCAL_YEAR_P RETURN VARCHAR2 IS
699 BEGIN
700 RETURN P_FISCAL_YEAR;
701 END P_FISCAL_YEAR_P;
702
703 FUNCTION P_BOOK_P RETURN VARCHAR2 IS
704 BEGIN
705 RETURN P_BOOK;
706 END P_BOOK_P;
707
708 FUNCTION CP_CURR_CODE_P RETURN VARCHAR2 IS
709 BEGIN
710 RETURN CP_CURR_CODE;
711 END CP_CURR_CODE_P;
712
713 FUNCTION CP_ACCOUNT_SEGMENT_P RETURN VARCHAR2 IS
714 BEGIN
715 RETURN CP_ACCOUNT_SEGMENT;
716 END CP_ACCOUNT_SEGMENT_P;
717
718
719 function CF_calc_acct_valueFormula( asset_category_id in number,cp_account_segment in VARCHAR2) return Number is
720
721 oper_exp number(15);
722 bk_rsv number(15);
723 asset_cost number(15);
724 deprn_rsv number(15);
725 reval_rsv number(15);
726 sql_stmt varchar2(500);
727 begin
728
729
730 Select asset_cost_acct,deprn_reserve_acct into
731 cp_cost_acct,cp_acc_deprn_acct
732 from fa_category_books
733 where book_type_code = p_book_type_code and
734 category_id = asset_category_id;
735
736 cp_cost_acct := '('||cp_cost_acct||')';
737
738
739
740 select operating_expense_ccid,backlog_deprn_rsv_ccid,reval_rsv_ccid
741 into oper_exp, bk_rsv,reval_rsv
742 from igi_iac_category_books where
743 book_type_code = p_book_type_code and
744 category_id = asset_category_id;
745
746
747 execute immediate('select ' || cp_account_segment ||' from gl_code_combinations cc where
748 chart_of_accounts_id= ' || accounting_flex_structure || ' and
749 code_combination_id = '|| oper_exp) into
750 cp_operating_acct;
751
752 execute immediate('select ' || cp_account_segment ||' from gl_code_combinations cc where
753 chart_of_accounts_id= ' || accounting_flex_structure || ' and
754 code_combination_id = '|| bk_rsv) into
755 cp_backlog_acct;
756
757
758 execute immediate('select ' || cp_account_segment ||' from gl_code_combinations cc where
759 chart_of_accounts_id= ' || accounting_flex_structure || ' and
760 code_combination_id = '|| reval_rsv) into
761 cp_reval_res_acct;
762
763 cp_reval_res_acct := '('||cp_reval_res_acct||')';
764 cp_operating_acct := '(' || cp_operating_acct ||')';
765 cp_acc_deprn_acct := '(' || cp_acc_deprn_acct || '+' || cp_backlog_acct || ')';
766 cp_backlog_acct := '('||cp_backlog_acct||')';
767
768
769 return 1;
770 end;
771 END IGI_IGIIARPS_XMLP_PKG;
[Home] [Help]