1 PACKAGE BODY IGI_IGIIARPR_XMLP_PKG AS
2 /* $Header: IGIIARPRB.pls 120.0.12010000.3 2008/08/21 11:49:16 sharoy ship $ */
3 FUNCTION CF_PERIODNAMEFORMULA RETURN NUMBER IS
4 BEGIN
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
20 INSERT_INFO_VAR := IGI_IGIIARPR_XMLP_PKG.do_insertformula;
21
22 select SUBSTR(argument1,INSTR(argument1,'=',1)+1,LENGTH(argument1)),
23 SUBSTR(argument2,INSTR(argument2,'=',1)+1,LENGTH(argument2)),
24 SUBSTR(argument3,INSTR(argument3,'=',1)+1,LENGTH(argument3))
25
26 into P_BOOK_TYPE_CODE,P_REVALUATION_ID,P_PERIOD_COUNTER
27 from FND_CONCURRENT_REQUESTS
28 where request_id=P_CONC_REQUEST_ID;
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 Detail 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'
147 ,CF_TOLERANCE_LEVEL);
148 RETURN 1;
149 END CALC_TOLERANCE_LEVELFORMULA;
150
151 FUNCTION CF_CO_TOLERANCEFORMULA(S_CO_B_REVAL_COST IN NUMBER
152 ,S_CO_A_REVAL_COST IN NUMBER) RETURN CHAR IS
153 TOL_VAL VARCHAR2(4);
154 GAP NUMBER;
155 BEGIN
156 IF CF_TOLERANCE_LEVEL IS NOT NULL AND S_CO_B_REVAL_COST <> 0 THEN
157 GAP := ABS(((NVL(S_CO_A_REVAL_COST
158 ,0) - NVL(S_CO_B_REVAL_COST
159 ,0)) / NVL(S_CO_B_REVAL_COST
160 ,0)) * 100);
161 IF GAP >= CF_TOLERANCE_LEVEL THEN
162 TOL_VAL := '***';
163 ELSE
164 TOL_VAL := ' ';
165 END IF;
166 END IF;
167 RETURN (TOL_VAL);
168 END CF_CO_TOLERANCEFORMULA;
169
170 FUNCTION CF_MC_TOLERANCEFORMULA(S_MC_B_REVAL_COST IN NUMBER
171 ,S_MC_A_REVAL_COST IN NUMBER) RETURN CHAR IS
172 TOL_VAL VARCHAR2(4);
173 GAP NUMBER;
174 BEGIN
175 IF CF_TOLERANCE_LEVEL IS NOT NULL AND S_MC_B_REVAL_COST <> 0 THEN
176 GAP := ABS(((NVL(S_MC_A_REVAL_COST
177 ,0) - NVL(S_MC_B_REVAL_COST
178 ,0)) / NVL(S_MC_B_REVAL_COST
179 ,0)) * 100);
180 IF GAP >= CF_TOLERANCE_LEVEL THEN
181 TOL_VAL := '***';
182 ELSE
183 TOL_VAL := ' ';
184 END IF;
185 END IF;
186 RETURN (TOL_VAL);
187 END CF_MC_TOLERANCEFORMULA;
188
189 FUNCTION CF_CAT_TOLERANCEFORMULA(S_CAT_B_REVAL_COST IN NUMBER
190 ,S_CAT_A_REVAL_COST IN NUMBER) RETURN CHAR IS
191 TOL_VAL VARCHAR2(4);
192 GAP NUMBER;
193 BEGIN
194 IF CF_TOLERANCE_LEVEL IS NOT NULL AND S_CAT_B_REVAL_COST <> 0 THEN
195 GAP := ABS(((NVL(S_CAT_A_REVAL_COST
196 ,0) - NVL(S_CAT_B_REVAL_COST
197 ,0)) / NVL(S_CAT_B_REVAL_COST
198 ,0)) * 100);
199 IF GAP >= CF_TOLERANCE_LEVEL THEN
200 TOL_VAL := '***';
201 ELSE
202 TOL_VAL := ' ';
203 END IF;
204 END IF;
205 RETURN (TOL_VAL);
206 END CF_CAT_TOLERANCEFORMULA;
207
208 FUNCTION CF_CC_TOLERANCEFORMULA(S_CC_B_REVAL_COST IN NUMBER
209 ,S_CC_A_REVAL_COST IN NUMBER) RETURN CHAR IS
210 TOL_VAL VARCHAR2(4);
211 GAP NUMBER;
212 BEGIN
213 CF_B_REVAL_COST := S_CC_B_REVAL_COST;
214 CF_A_REVAL_COST := S_CC_A_REVAL_COST;
215 IF CF_TOLERANCE_LEVEL IS NOT NULL AND CF_B_REVAL_COST <> 0 THEN
216 GAP := ABS(((NVL(CF_A_REVAL_COST
217 ,0) - NVL(CF_B_REVAL_COST
218 ,0)) / NVL(CF_B_REVAL_COST
219 ,0)) * 100);
220 IF GAP >= CF_TOLERANCE_LEVEL THEN
221 TOL_VAL := '***';
222 ELSE
223 TOL_VAL := ' ';
224 END IF;
225 END IF;
226 RETURN (TOL_VAL);
227 END CF_CC_TOLERANCEFORMULA;
228
229 FUNCTION CF_CALC_PLACEHOLDERSFORMULA RETURN NUMBER IS
230 TOL_VAL VARCHAR2(4);
231 GAP NUMBER;
232 BEGIN
233 RETURN 1;
234 END CF_CALC_PLACEHOLDERSFORMULA;
235
236 FUNCTION CF_CALC_TOLERANCEFORMULA(S_A_B_REVAL_COST IN NUMBER
237 ,S_A_A_REVAL_COST IN NUMBER) RETURN NUMBER IS
238 GAP NUMBER;
239 BEGIN
240 CF_B_REVAL_COST := S_A_B_REVAL_COST;
241 CF_A_REVAL_COST := S_A_A_REVAL_COST;
242 IF CF_TOLERANCE_LEVEL IS NOT NULL AND CF_B_REVAL_COST <> 0 THEN
243 GAP := ABS(((NVL(CF_A_REVAL_COST
244 ,0) - NVL(CF_B_REVAL_COST
245 ,0)) / NVL(CF_B_REVAL_COST
246 ,0)) * 100);
247 IF GAP >= CF_TOLERANCE_LEVEL THEN
248 CF_ASSET_TOLERANCE := '***';
249 ELSE
250 CF_ASSET_TOLERANCE := ' ';
251 END IF;
252 END IF;
253 RETURN 1;
254 END CF_CALC_TOLERANCEFORMULA;
255
256 FUNCTION CF_MAJOR_CATFORMULA(MAJOR_CATEGORY IN VARCHAR2) RETURN NUMBER IS
257 BEGIN
258 CP_MAJOR_CATEGORY := MAJOR_CATEGORY;
259 RETURN 1;
260 END CF_MAJOR_CATFORMULA;
261
262 PROCEDURE INSERT_INFO IS
263 L_SOURCE_TYPE_CODE VARCHAR2(50);
264 L_AMOUNT1 VARCHAR2(200);
265 L_AMOUNT2 VARCHAR2(200);
266 L_AMOUNT3 VARCHAR2(200);
267 L_SQL VARCHAR2(15000);
268 IDX NUMBER;
269 L_ASSET_ID NUMBER;
270 L_DIST_CCID NUMBER;
271 L_SOURCE VARCHAR2(30);
272 L_AMOUNT NUMBER;
273 L_DATA_SOURCE VARCHAR2(30);
274 L_DISPLAY_ORDER NUMBER;
275 BEGIN
276 -- delete from igi_iac_balances_report;
277
278 FOR idx IN 1..5 LOOP
279 IF (idx = 1) THEN
280 l_source_type_code := 'COST';
281 l_amount1 := 'nvl(dd.cost,0)';
282 l_amount2 := 'nvl(idb.adjustment_cost,0)+nvl(dd.cost,0)';
283 l_amount3 := 'nvl(idb.adjustment_cost,0)+nvl(dd.cost,0)';
284 ELSIF (idx = 2) THEN
285 l_source_type_code := 'REVAL_RSV';
286 l_amount1 := 0;
287 l_amount2 := 'nvl(idb.reval_reserve_net,0)';
288 l_amount3 := 'nvl(idb.reval_reserve_net,0)';
289 ELSIF (idx = 3) THEN
290 l_source_type_code := 'OP_EXPENSE';
291 l_amount1 := 0;
292 l_amount2 := '(-1)*nvl(idb.operating_acct_net,0)';
293 l_amount3 := '(-1)*nvl(idb.operating_acct_net,0)';
294 ELSIF (idx = 4) THEN
295 l_source_type_code := 'DEP_RSV';
296 l_amount1 := 'dd.deprn_reserve';
297 l_Amount2 := 'nvl(idb.deprn_reserve+deprn_reserve_backlog,0)+ nvl(dd.deprn_reserve,0)';
298 l_Amount3 := 'nvl(idb.deprn_reserve+deprn_reserve_backlog,0)+ nvl(dd.deprn_reserve,0)';
299 ELSE
300 l_source_type_code := 'BACKLOG';
301 l_amount1 := 0;
302 l_amount2 := 'nvl(idb.deprn_reserve_backlog,0)';
303 l_amount3 := 'nvl(idb.deprn_reserve_backlog,0)';
304 END IF;
305
306 -- define the insert statement
307
308 l_sql := 'INSERT INTO igi_iac_balances_report(
309 asset_id,
310 distribution_ccid,
311 source_type_code,
312 amount,
313 data_source,
314 display_order )
315 SELECT ad.asset_id
316 ,dh.code_combination_id distribution_ccid
317 ,'''||l_source_type_code||''' source_type_code
318 ,'||l_amount1||' amount
319 ,''Before'' data_source
320 ,''1'' display_order
321 FROM fa_deprn_detail dd,
322 fa_books bk,
323 gl_code_combinations cc,
324 fa_additions ad,
325 fa_distribution_history dh,
326 fa_categories fc,
327 igi_iac_reval_asset_rules irar,
328 igi_iac_reval_categories irc
329 WHERE irc.revaluation_id = '||p_revaluation_id||
330 ' AND irar.asset_id = bk.asset_id
331 AND irar.revaluation_id = irc.revaluation_id
332 AND irar.category_id = irc.category_id
333 AND irc.select_category = ''Y''
334 AND irar.selected_for_reval_flag = ''Y''
335 AND irar.book_type_code = '''||p_book_type_code||'''
336 AND bk.book_type_code = irar.book_type_code
337 AND bk.asset_id = ad.asset_id
338 AND dd.period_counter = (SELECT MAX(period_counter)
339 FROM fa_deprn_summary ds
340 WHERE ds.asset_id =bk.asset_id
341 AND ds.book_type_code= '''||p_book_type_code||'''
342 AND ds.period_counter<= '||p_period_counter||')
343 AND EXISTS (SELECT MAX(ith.adjustment_id)
344 FROM igi_iac_transaction_headers ith
345 WHERE ith.asset_id = bk.asset_id
346 AND ith.book_type_code = '''||p_book_type_code||'''
347 AND ith.adjustment_status NOT IN (''PREVIEW'',''OBSOLETE''))
348 AND bk.date_ineffective IS NULL
349 AND dh.book_type_code = bk.book_type_code
350 AND dd.asset_id= bk.asset_id
351 AND dh.asset_id = dd.asset_id
352 AND dh.distribution_id = dd.distribution_id
353 AND dh.transaction_header_id_out IS NULL
354 AND dh.code_combination_id = cc.code_combination_id
355 AND fc.category_id=ad.asset_category_id
356 AND bk.asset_id NOT IN
357 (SELECT asset_id
358 FROM igi_iac_asset_balances
359 WHERE book_type_code = bk.book_type_code
360 AND asset_id = bk.asset_id)
361 AND bk.asset_id NOT IN (SELECT asset_id
362 FROM igi_iac_exceptions
363 WHERE revaluation_id = irc.revaluation_id)
364 UNION
365 SELECT ad.asset_id
366 ,dh.code_combination_id distribution_ccid
367 ,'''||l_source_type_code||''' source_type_code
368 ,'||l_amount2||' amount
369 ,''Before'' data_source
370 ,''1'' display_order
371 FROM igi_iac_det_balances idb,
372 fa_deprn_detail dd,
373 fa_books bk,
374 gl_code_combinations cc,
375 fa_additions ad,
376 fa_categories fc,
377 fa_distribution_history dh ,
378 igi_iac_reval_asset_rules irar,
379 igi_iac_reval_categories irc
380 WHERE irc.revaluation_id = '||p_revaluation_id||'
381 AND irar.asset_id = bk.asset_id
382 AND irar.selected_for_reval_flag = ''Y''
383 AND irar.revaluation_id = irc.revaluation_id
384 AND irar.category_id = irc.category_id
385 AND irc.select_category = ''Y''
386 AND irar.book_type_code = '''||p_book_type_code||'''
387 AND bk.book_type_code = irar.book_type_code
388 AND bk.asset_id = ad.asset_id
389 AND dd.asset_id=bk.asset_id
390 AND dd.period_counter =(SELECT max(period_counter)
391 FROM fa_deprn_summary ds
392 WHERE ds.asset_id =bk.asset_id
393 AND ds.book_type_code='''||p_book_type_code||'''
394 AND ds.period_counter<='||p_period_counter||')
395 AND idb.adjustment_id =(SELECT max(ith.adjustment_id)
396 FROM igi_iac_transaction_headers ith
397 WHERE ith.asset_id = bk.asset_id
398 AND ith.book_type_code ='''||p_book_type_code||'''
399 AND ith.adjustment_status NOT IN (''PREVIEW'', ''OBSOLETE''))
400 AND bk.date_ineffective IS NULL
401 AND dh.book_type_code = bk.book_type_code
402 AND dh.asset_id = dd.asset_id
403 AND dh.distribution_id = dd.distribution_id
404 AND dh.transaction_header_id_out is NULL
405 AND dh.distribution_id = idb.distribution_id
406 AND dh.asset_id = idb.asset_id
407 AND dh.book_type_code = idb.book_type_code
408 AND dh.code_combination_id = cc.code_combination_id
409 AND fc.category_id= ad.asset_category_id
410 AND bk.asset_id NOT IN (SELECT asset_id
411 FROM igi_iac_exceptions
412 WHERE revaluation_id = irc.revaluation_id)
413 UNION
414 SELECT ad.asset_id
415 ,dh.code_combination_id distribution_ccid
416 ,'''||l_source_type_code||''' source_type_code
417 ,'||l_amount3||' amount
418 ,''After'' data_source
419 ,''2'' display_order
420 FROM igi_iac_det_balances idb,
421 fa_deprn_detail dd,
422 fa_books bk,
423 gl_code_combinations cc,
424 fa_additions ad,
425 fa_categories fc,
426 fa_distribution_history dh,
427 igi_iac_reval_asset_rules irar,
428 igi_iac_reval_categories irc
429 WHERE irc.revaluation_id = '||p_revaluation_id||'
430 and irar.asset_id = bk.asset_id
431 and irar.selected_for_reval_flag = ''Y''
432 AND irar.revaluation_id = irc.revaluation_id
433 AND irar.category_id = irc.category_id
434 AND irc.select_category = ''Y''
435 and irar.book_type_code = '''||p_book_type_code||'''
436 and bk.book_type_code= irar.book_type_code
437 and bk.asset_id = ad.asset_id
438 and idb.adjustment_id=(select max(ith.adjustment_id)
439 from igi_iac_transaction_headers ith
440 where ith.asset_id = bk.asset_id
441 and ith.book_type_code ='''||p_book_type_code||'''
442 and ith.period_counter <='||p_period_counter||'
443 and ith.adjustment_status=''PREVIEW'')
444 and dd.period_counter=(select max(period_counter)
445 from fa_deprn_summary ds
446 where ds.asset_id =bk.asset_id
447 and ds.book_type_code='''||p_book_type_code||'''
448 and ds.period_counter<='||p_period_counter||')
449 and bk.date_ineffective is NULL
450 and dh.book_type_code = bk.book_type_code
451 and dh.asset_id = dd.asset_id
452 and dd.asset_id=bk.asset_id
453 and dh.distribution_id = dd.distribution_id
454 and dh.transaction_header_id_out is NULL
455 and dh.distribution_id = idb.distribution_id
456 and dh.code_combination_id = cc.code_combination_id
457 and fc.category_id=ad.asset_category_id
458 AND bk.asset_id NOT IN (SELECT asset_id
459 FROM igi_iac_exceptions
460 WHERE revaluation_id = irc.revaluation_id)
461 ';
462
463 -- srw.message(999,l_sql);
464
465 execute immediate l_sql;
466 END LOOP;
467
468 END INSERT_INFO;
469
470 FUNCTION DO_INSERTFORMULA RETURN NUMBER IS
471 BEGIN
472 INSERT_INFO;
473 RETURN (1);
474 EXCEPTION
475 WHEN OTHERS THEN
476 RETURN (0);
477 END DO_INSERTFORMULA;
478
479 FUNCTION B_REVALUED_COSTFORMULA(BALANCE_TYPE IN VARCHAR2
480 ,REVALUED_COST IN NUMBER) RETURN NUMBER IS
481 L_NUM NUMBER;
482 BEGIN
483 IF (BALANCE_TYPE = 'Before') THEN
484 L_NUM := REVALUED_COST;
485 END IF;
486 RETURN (L_NUM);
487 EXCEPTION
488 WHEN OTHERS THEN
489 RETURN (0);
490 END B_REVALUED_COSTFORMULA;
491
492 FUNCTION A_REVALUED_COSTFORMULA(BALANCE_TYPE IN VARCHAR2
493 ,REVALUED_COST IN NUMBER) RETURN NUMBER IS
494 L_NUM NUMBER;
495 BEGIN
496 IF (BALANCE_TYPE = 'After') THEN
497 L_NUM := REVALUED_COST;
498 END IF;
499 RETURN (L_NUM);
500 EXCEPTION
501 WHEN OTHERS THEN
502 RETURN (0);
503 END A_REVALUED_COSTFORMULA;
504
505 FUNCTION B_REVAL_RESERVEFORMULA(BALANCE_TYPE IN VARCHAR2
506 ,REVALUATION_RESERVE IN NUMBER) RETURN NUMBER IS
507 L_NUM NUMBER;
508 BEGIN
509 IF (BALANCE_TYPE = 'Before') THEN
510 L_NUM := REVALUATION_RESERVE;
511 END IF;
512 RETURN (L_NUM);
513 EXCEPTION
514 WHEN OTHERS THEN
515 RETURN (0);
516 END B_REVAL_RESERVEFORMULA;
517
518 FUNCTION A_REVAL_RESERVEFORMULA(BALANCE_TYPE IN VARCHAR2
519 ,REVALUATION_RESERVE IN NUMBER) RETURN NUMBER IS
520 L_NUM NUMBER;
521 BEGIN
522 IF (BALANCE_TYPE = 'After') THEN
523 L_NUM := REVALUATION_RESERVE;
524 END IF;
525 RETURN (L_NUM);
526 EXCEPTION
527 WHEN OTHERS THEN
528 RETURN (0);
529 END A_REVAL_RESERVEFORMULA;
530
531 FUNCTION B_OPERATING_ACCTFORMULA(BALANCE_TYPE IN VARCHAR2
532 ,OPERATING_ACCT IN NUMBER) RETURN NUMBER IS
533 L_NUM NUMBER;
534 BEGIN
535 IF (BALANCE_TYPE = 'Before') THEN
536 L_NUM := OPERATING_ACCT;
537 END IF;
538 RETURN (L_NUM);
539 EXCEPTION
540 WHEN OTHERS THEN
541 RETURN (0);
542 END B_OPERATING_ACCTFORMULA;
543
544 FUNCTION A_OPERATING_ACCTFORMULA(BALANCE_TYPE IN VARCHAR2
545 ,OPERATING_ACCT IN NUMBER) RETURN NUMBER IS
546 L_NUM NUMBER;
547 BEGIN
548 IF (BALANCE_TYPE = 'After') THEN
549 L_NUM := OPERATING_ACCT;
550 END IF;
551 RETURN (L_NUM);
552 EXCEPTION
553 WHEN OTHERS THEN
554 RETURN (0);
555 END A_OPERATING_ACCTFORMULA;
556
557 FUNCTION B_ACC_DEPRNFORMULA(BALANCE_TYPE IN VARCHAR2
558 ,ACCUMULATED_DEPRECIATION IN NUMBER) RETURN NUMBER IS
559 L_NUM NUMBER;
560 BEGIN
561 IF (BALANCE_TYPE = 'Before') THEN
562 L_NUM := ACCUMULATED_DEPRECIATION;
563 END IF;
564 RETURN (L_NUM);
565 EXCEPTION
566 WHEN OTHERS THEN
567 RETURN (0);
568 END B_ACC_DEPRNFORMULA;
569
570 FUNCTION A_ACC_DEPRNFORMULA(BALANCE_TYPE IN VARCHAR2
571 ,ACCUMULATED_DEPRECIATION IN NUMBER) RETURN NUMBER IS
572 L_NUM NUMBER;
573 BEGIN
574 IF (BALANCE_TYPE = 'After') THEN
575 L_NUM := ACCUMULATED_DEPRECIATION;
576 END IF;
577 RETURN (L_NUM);
578 EXCEPTION
579 WHEN OTHERS THEN
580 RETURN (0);
581 END A_ACC_DEPRNFORMULA;
582
583 FUNCTION B_ACC_BACKLOGFORMULA(BALANCE_TYPE IN VARCHAR2
584 ,ACCUMULATED_BACKLOG IN NUMBER) RETURN NUMBER IS
585 L_NUM NUMBER;
586 BEGIN
587 IF (BALANCE_TYPE = 'Before') THEN
588 L_NUM := ACCUMULATED_BACKLOG;
589 END IF;
590 RETURN (L_NUM);
591 EXCEPTION
592 WHEN OTHERS THEN
593 RETURN (0);
594 END B_ACC_BACKLOGFORMULA;
595
596 FUNCTION A_ACC_BACKLOGFORMULA(BALANCE_TYPE IN VARCHAR2
597 ,ACCUMULATED_BACKLOG IN NUMBER) RETURN NUMBER IS
598 L_NUM NUMBER;
599 BEGIN
600 IF (BALANCE_TYPE = 'After') THEN
601 L_NUM := ACCUMULATED_BACKLOG;
602 END IF;
603 RETURN (L_NUM);
604 EXCEPTION
605 WHEN OTHERS THEN
606 RETURN (0);
607 END A_ACC_BACKLOGFORMULA;
608
609 FUNCTION ACCOUNTING_FLEX_STRUCTURE_P RETURN NUMBER IS
610 BEGIN
611 RETURN ACCOUNTING_FLEX_STRUCTURE;
612 END ACCOUNTING_FLEX_STRUCTURE_P;
613
614 FUNCTION CP_REP_FORMAT_DIFF_P RETURN NUMBER IS
615 BEGIN
616 RETURN CP_REP_FORMAT_DIFF;
617 END CP_REP_FORMAT_DIFF_P;
618
619 FUNCTION CP_CO_FORMAT_DIFF_P RETURN NUMBER IS
620 BEGIN
621 RETURN CP_CO_FORMAT_DIFF;
622 END CP_CO_FORMAT_DIFF_P;
623
624 FUNCTION CP_MC_FORMAT_DIFF_P RETURN NUMBER IS
625 BEGIN
626 RETURN CP_MC_FORMAT_DIFF;
627 END CP_MC_FORMAT_DIFF_P;
628
629 FUNCTION F_MINOR_CATEGORY_P RETURN VARCHAR2 IS
630 BEGIN
631 RETURN F_MINOR_CATEGORY;
632 END F_MINOR_CATEGORY_P;
633
634 FUNCTION CP_MAJOR_CATEGORY_P RETURN VARCHAR2 IS
635 BEGIN
636 RETURN CP_MAJOR_CATEGORY;
637 END CP_MAJOR_CATEGORY_P;
638
639 FUNCTION CP_CAT_FORMAT_DIFF_P RETURN NUMBER IS
640 BEGIN
641 RETURN CP_CAT_FORMAT_DIFF;
642 END CP_CAT_FORMAT_DIFF_P;
643
644 FUNCTION CP_CC_FORMAT_DIFF_P RETURN NUMBER IS
645 BEGIN
646 RETURN CP_CC_FORMAT_DIFF;
647 END CP_CC_FORMAT_DIFF_P;
648
649 FUNCTION CP_COST_ACCT_P RETURN VARCHAR2 IS
650 BEGIN
651 RETURN CP_COST_ACCT;
652 END CP_COST_ACCT_P;
653
654 FUNCTION CP_REVAL_RES_ACCT_P RETURN VARCHAR2 IS
655 BEGIN
656 RETURN CP_REVAL_RES_ACCT;
657 END CP_REVAL_RES_ACCT_P;
658
659 FUNCTION CP_BACKLOG_ACCT_P RETURN VARCHAR2 IS
660 BEGIN
661 RETURN CP_BACKLOG_ACCT;
662 END CP_BACKLOG_ACCT_P;
663
664 FUNCTION CP_OPERATING_ACCT_P RETURN VARCHAR2 IS
665 BEGIN
666 RETURN CP_OPERATING_ACCT;
667 END CP_OPERATING_ACCT_P;
668
669 FUNCTION CP_ACC_DEPRN_ACCT_P RETURN VARCHAR2 IS
670 BEGIN
671 RETURN CP_ACC_DEPRN_ACCT;
672 END CP_ACC_DEPRN_ACCT_P;
673
674 FUNCTION CP_A_FORMAT_DIFF_P RETURN NUMBER IS
675 BEGIN
676 RETURN CP_A_FORMAT_DIFF;
677 END CP_A_FORMAT_DIFF_P;
678
679 FUNCTION F_REVAL_RESERVE_DIFF_P RETURN NUMBER IS
680 BEGIN
681 RETURN F_REVAL_RESERVE_DIFF;
682 END F_REVAL_RESERVE_DIFF_P;
683
684 FUNCTION F_REVAL_COST_DIFF_P RETURN NUMBER IS
685 BEGIN
686 RETURN F_REVAL_COST_DIFF;
687 END F_REVAL_COST_DIFF_P;
688
689 FUNCTION F_OPERATING_ACCT_DIFF_P RETURN NUMBER IS
690 BEGIN
691 RETURN F_OPERATING_ACCT_DIFF;
692 END F_OPERATING_ACCT_DIFF_P;
693
694 FUNCTION F_ACC_DEPRN_DIFF_P RETURN NUMBER IS
695 BEGIN
696 RETURN F_ACC_DEPRN_DIFF;
697 END F_ACC_DEPRN_DIFF_P;
698
699 FUNCTION F_ACC_BACKLOG_DIFF_P RETURN NUMBER IS
700 BEGIN
701 RETURN F_ACC_BACKLOG_DIFF;
702 END F_ACC_BACKLOG_DIFF_P;
703
704 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
705 BEGIN
706 RETURN RP_REPORT_NAME;
707 END RP_REPORT_NAME_P;
708
709 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
710 BEGIN
711 RETURN RP_COMPANY_NAME;
712 END RP_COMPANY_NAME_P;
713
714 FUNCTION CP_PERIOD_NAME_P RETURN VARCHAR2 IS
715 BEGIN
716 RETURN CP_PERIOD_NAME;
717 END CP_PERIOD_NAME_P;
718
719 FUNCTION P_FISCAL_YEAR_P RETURN VARCHAR2 IS
720 BEGIN
721 RETURN P_FISCAL_YEAR;
722 END P_FISCAL_YEAR_P;
723
724 FUNCTION P_BOOK_P RETURN VARCHAR2 IS
725 BEGIN
726 RETURN P_BOOK;
727 END P_BOOK_P;
728
729 FUNCTION CP_CURR_CODE_P RETURN VARCHAR2 IS
730 BEGIN
731 RETURN CP_CURR_CODE;
732 END CP_CURR_CODE_P;
733
734 FUNCTION CP_ACCOUNT_SEGMENT_P RETURN VARCHAR2 IS
735 BEGIN
736 RETURN CP_ACCOUNT_SEGMENT;
737 END CP_ACCOUNT_SEGMENT_P;
738
739
740 function CF_calc_acct_valueFormula( asset_category_id in number,cp_account_segment in VARCHAR2) return Number is
741
742 oper_exp number(15);
743 bk_rsv number(15);
744 asset_cost number(15);
745 deprn_rsv number(15);
746 reval_rsv number(15);
747 sql_stmt varchar2(500);
748 begin
749
750
751 Select asset_cost_acct,deprn_reserve_acct into
752 cp_cost_acct,cp_acc_deprn_acct
753 from fa_category_books
754 where book_type_code = p_book_type_code and
755 category_id = asset_category_id;
756
757 cp_cost_acct := '('||cp_cost_acct||')';
758
759
760
761 select operating_expense_ccid,backlog_deprn_rsv_ccid,reval_rsv_ccid
762 into oper_exp, bk_rsv,reval_rsv
763 from igi_iac_category_books where
764 book_type_code = p_book_type_code and
765 category_id = asset_category_id;
766
767
768 execute immediate('select ' || cp_account_segment ||' from gl_code_combinations cc where
769 chart_of_accounts_id= ' || accounting_flex_structure || ' and
770 code_combination_id = '|| oper_exp) into
771 cp_operating_acct;
772
773 execute immediate('select ' || cp_account_segment ||' from gl_code_combinations cc where
774 chart_of_accounts_id= ' || accounting_flex_structure || ' and
775 code_combination_id = '|| bk_rsv) into
776 cp_backlog_acct;
777
778
779 execute immediate('select ' || cp_account_segment ||' from gl_code_combinations cc where
780 chart_of_accounts_id= ' || accounting_flex_structure || ' and
781 code_combination_id = '|| reval_rsv) into
782 cp_reval_res_acct;
783
784 cp_reval_res_acct := '('||cp_reval_res_acct||')';
785 cp_operating_acct := '(' || cp_operating_acct ||')';
786 cp_acc_deprn_acct := '(' || cp_acc_deprn_acct || '+' || cp_backlog_acct || ')';
787 cp_backlog_acct := '('||cp_backlog_acct||')';
788
789
790 return 1;
791 end;
792 END IGI_IGIIARPR_XMLP_PKG;
[Home] [Help]