[Home] [Help]
PACKAGE BODY: APPS.JL_JLMXFIMP_XMLP_PKG
Source
1 PACKAGE BODY JL_JLMXFIMP_XMLP_PKG AS
2 /* $Header: JLMXFIMPB.pls 120.1 2007/12/25 16:53:01 dwkrishn noship $ */
3 PROCEDURE GET_BASE_CURR_DATA IS
4 BASE_CURR FND_CURRENCIES_VL.CURRENCY_CODE%TYPE;
5 PREC FND_CURRENCIES_VL.PRECISION%TYPE;
6 MIN_AU FND_CURRENCIES_VL.MINIMUM_ACCOUNTABLE_UNIT%TYPE;
7 DESCR FND_CURRENCIES_VL.DESCRIPTION%TYPE;
8 ERRNO NUMBER;
9 ERRMSG VARCHAR2(1000);
10 BEGIN
11 BASE_CURR := '';
12 PREC := 0;
13 MIN_AU := 0;
14 DESCR := '';
15 BEGIN
16 SELECT
17 COMPANY_NAME,
18 CATEGORY_FLEX_STRUCTURE
19 INTO C_ORGANIZATION_NAME,CAT_FLEX_STRUCT
20 FROM
21 FA_SYSTEM_CONTROLS;
22 EXCEPTION
23 WHEN OTHERS THEN
24 NULL;
25 END;
26 IF UPPER(P_MRCSOBTYPE) = 'R' THEN
27 BEGIN
28 SELECT
29 FCURR.CURRENCY_CODE,
30 FCURR.PRECISION,
31 FCURR.MINIMUM_ACCOUNTABLE_UNIT,
32 FCURR.DESCRIPTION
33 INTO BASE_CURR,PREC,MIN_AU,DESCR
34 FROM
35 FA_BOOK_CONTROLS_MRC_V BKCTRL,
36 FND_CURRENCIES_VL FCURR,
37 GL_SETS_OF_BOOKS GSBKS
38 WHERE BKCTRL.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
39 AND BKCTRL.SET_OF_BOOKS_ID = GSBKS.SET_OF_BOOKS_ID
40 AND GSBKS.CURRENCY_CODE = FCURR.CURRENCY_CODE;
41 EXCEPTION
42 WHEN NO_DATA_FOUND THEN
43 RAISE_ERR('JL_AR_FA_CURR_DET_NOT_DEFINED'
44 ,'N');
45 WHEN OTHERS THEN
46 RAISE_ORA_ERR;
47 END;
48 ELSE
49 BEGIN
50 SELECT
51 FCURR.CURRENCY_CODE,
52 FCURR.PRECISION,
53 FCURR.MINIMUM_ACCOUNTABLE_UNIT,
54 FCURR.DESCRIPTION
55 INTO BASE_CURR,PREC,MIN_AU,DESCR
56 FROM
57 FA_BOOK_CONTROLS BKCTRL,
58 FND_CURRENCIES_VL FCURR,
59 GL_SETS_OF_BOOKS GSBKS
60 WHERE BKCTRL.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
61 AND BKCTRL.SET_OF_BOOKS_ID = GSBKS.SET_OF_BOOKS_ID
62 AND GSBKS.CURRENCY_CODE = FCURR.CURRENCY_CODE;
63 EXCEPTION
64 WHEN NO_DATA_FOUND THEN
65 RAISE_ERR('JL_AR_FA_CURR_DET_NOT_DEFINED'
66 ,'N');
67 WHEN OTHERS THEN
68 RAISE_ORA_ERR;
69 END;
70 END IF;
71 C_BASE_CURRENCY_CODE := BASE_CURR;
72 C_BASE_PRECISION := PREC;
73 C_BASE_MIN_ACCT_UNIT := MIN_AU;
74 C_BASE_DESCRIPTION := DESCR;
75 END GET_BASE_CURR_DATA;
76
77 FUNCTION AFTERREPORT RETURN BOOLEAN IS
78 BEGIN
79 BEGIN
80 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
81 EXCEPTION
82 WHEN OTHERS THEN
83 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
84 END;
85 RETURN (TRUE);
86 END AFTERREPORT;
87
88 FUNCTION C_PRICE_INDEXFORMULA(ASSET_CATEGORY IN NUMBER
89 ,ACQDATE IN DATE) RETURN NUMBER IS
90 X NUMBER(15);
91 CATEGORY_DESC VARCHAR2(40);
92 ERRMSG VARCHAR2(1000);
93 BEGIN
94 SELECT
95 FPI.PRICE_INDEX_ID
96 INTO X
97 FROM
98 FA_PRICE_INDEXES FPI,
99 FA_CATEGORY_BOOK_DEFAULTS FCBD
100 WHERE FCBD.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
101 AND FCBD.CATEGORY_ID = ASSET_CATEGORY
102 AND ACQDATE >= FCBD.START_DPIS
103 AND ACQDATE <= NVL(FCBD.END_DPIS
104 ,ACQDATE)
105 AND FCBD.PRICE_INDEX_NAME = FPI.PRICE_INDEX_NAME;
106 IF X IS NULL THEN
107 RAISE NO_DATA_FOUND;
108 END IF;
109 RETURN (X);
110 EXCEPTION
111 WHEN NO_DATA_FOUND THEN
112 SELECT
113 DESCRIPTION
114 INTO CATEGORY_DESC
115 FROM
116 FA_CATEGORIES
117 WHERE CATEGORY_ID = ASSET_CATEGORY;
118 SET_NAME('JL'
119 ,'JL_ZZ_FA_INDX_NOT_DEF_FOR_CATG');
120 SET_TOKEN('ASSET_CATEGORY'
121 ,CATEGORY_DESC
122 ,FALSE);
123 ERRMSG := GET;
124 /*SRW.MESSAGE(JL_ZZ_FA_UTILITIES_PKG.GET_APP_ERRNUM('JL'
125 ,'JL_ZZ_FA_INDX_NOT_DEF_FOR_CATG')
126 ,ERRMSG)*/NULL;
127 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
128 RETURN NULL;
129 WHEN OTHERS THEN
130 RAISE_ORA_ERR;
131 RETURN NULL;
132 END C_PRICE_INDEXFORMULA;
133
134 FUNCTION C_INDEX_VALUE_JUNEFORMULA(C_PRICE_INDEX IN NUMBER) RETURN NUMBER IS
135 X NUMBER;
136 JUNE_PERIOD_DATE DATE;
137 ERRMSG VARCHAR2(1000);
138 INDEX_NAME VARCHAR2(40);
139 BEGIN
140 JUNE_PERIOD_DATE := TO_DATE('30-06-' || TO_CHAR(P_CURR_FY)
141 ,'DD-MM-YYYY');
142 SELECT
143 PRICE_INDEX_VALUE
144 INTO X
145 FROM
146 FA_PRICE_INDEX_VALUES
147 WHERE PRICE_INDEX_ID = C_PRICE_INDEX
148 AND JUNE_PERIOD_DATE BETWEEN FROM_DATE
149 AND TO_DATE;
150 RETURN (NVL(X
151 ,0));
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 SELECT
155 PRICE_INDEX_NAME
156 INTO INDEX_NAME
157 FROM
158 FA_PRICE_INDEXES
159 WHERE PRICE_INDEX_ID = C_PRICE_INDEX;
160 SET_NAME('JL'
161 ,'JL_ZZ_FA_INDX_VAL_NOT_FOUND');
162 SET_TOKEN('PRICE_INDEX'
163 ,INDEX_NAME
164 ,FALSE);
165 SET_TOKEN('MISSING_DATE'
166 ,TO_CHAR(JUNE_PERIOD_DATE)
167 ,FALSE);
168 ERRMSG := GET;
169 /*SRW.MESSAGE(JL_ZZ_FA_UTILITIES_PKG.GET_APP_ERRNUM('JL'
170 ,'JL_ZZ_FA_INDX_VAL_NOT_FOUND')
171 ,ERRMSG)*/NULL;
172 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
173 RETURN NULL;
174 WHEN OTHERS THEN
175 RAISE_ORA_ERR;
176 RETURN NULL;
177 END C_INDEX_VALUE_JUNEFORMULA;
178
179 FUNCTION C_CORR_FACTORFORMULA(ACQDATE IN DATE
180 ,C_INDEX_VALUE_JUNE IN NUMBER
181 ,INPC IN NUMBER) RETURN NUMBER IS
182 V_FACTOR NUMBER;
183 BEGIN
184 IF TRUNC(ACQDATE) > NVL(TRUNC(C_END_MID_YR)
185 ,TRUNC(ACQDATE) - 1) THEN
186 V_FACTOR := 1;
187 ELSE
188 V_FACTOR := TRUNC(C_INDEX_VALUE_JUNE / nvl(INPC,1)
189 ,C_RATIO_PRECISION);
190 END IF;
191 RETURN (V_FACTOR);
192 END C_CORR_FACTORFORMULA;
193
194 FUNCTION C_ADJ_NBV_LESS_50PERFORMULA(C_ADJ_NBV_END_PREV_FY IN NUMBER
195 ,C_50PER_ADJ_DEPRN_CURR_FY IN NUMBER) RETURN NUMBER IS
196 BEGIN
197 RETURN (C_ADJ_NBV_END_PREV_FY - C_50PER_ADJ_DEPRN_CURR_FY);
198 END C_ADJ_NBV_LESS_50PERFORMULA;
199
200 FUNCTION C_ADJ_NBV_END_PREV_FYFORMULA(C_NBV_END_PREV_FY IN NUMBER
201 ,C_CORR_FACTOR IN NUMBER) RETURN NUMBER IS
202 BEGIN
203 RETURN (C_NBV_END_PREV_FY * C_CORR_FACTOR);
204 END C_ADJ_NBV_END_PREV_FYFORMULA;
205
206 FUNCTION C_50PER_ADJ_DEPRN_CURR_FYFORMU(ADJUSTED_DEPRN_CURRENT_FY IN NUMBER) RETURN NUMBER IS
207 BEGIN
208 RETURN (ADJUSTED_DEPRN_CURRENT_FY * 0.5);
209 END C_50PER_ADJ_DEPRN_CURR_FYFORMU;
210
211 FUNCTION C_IMPAC_BASE_VALUEFORMULA(ACQDATE IN DATE
212 ,RETIREMENT_ID_1 IN NUMBER
213 ,CF_RETIREMENT_DATE IN DATE
214 ,PERIOD_COUNTER_FULLY_RESERVED IN NUMBER
215 ,C_ADJ_NBV_LESS_50PER IN NUMBER) RETURN NUMBER IS
216 L_PERIODS_OF_USE NUMBER;
217 L_ADDITION_MONTH NUMBER;
218 L_RETIREMENT_MONTH NUMBER;
219 L_INCLUDE_RET VARCHAR2(1);
220 BEGIN
221 L_INCLUDE_RET := P_INCLUDE_RET;
222 IF ACQDATE BETWEEN C_FISCAL_START_DATE AND C_FISCAL_END_DATE THEN
223 L_ADDITION_MONTH := TO_NUMBER(TO_CHAR(ACQDATE
224 ,'MM'));
225 ELSE
226 L_ADDITION_MONTH := 0;
227 END IF;
228 IF RETIREMENT_ID_1 IS NOT NULL THEN
229 L_RETIREMENT_MONTH := TO_NUMBER(TO_CHAR(CF_RETIREMENT_DATE
230 ,'MM'));
231 ELSE
232 IF PERIOD_COUNTER_FULLY_RESERVED IS NOT NULL AND (PERIOD_COUNTER_FULLY_RESERVED BETWEEN MIN_PERIOD_COUNTER AND MAX_PERIOD_COUNTER) THEN
233 IF P_FULLY_DEPRN_ASSETS = 'IN USE' THEN
234 L_RETIREMENT_MONTH := 13;
235 END IF;
236 IF P_FULLY_DEPRN_ASSETS = 'RETIRED' THEN
237 IF UPPER(P_MRCSOBTYPE) = 'R' THEN
238 BEGIN
239 SELECT
240 PERIOD_NUM
241 INTO L_RETIREMENT_MONTH
242 FROM
243 FA_DEPRN_PERIODS_MRC_V
244 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
245 AND PERIOD_COUNTER = PERIOD_COUNTER_FULLY_RESERVED;
246 END;
247 ELSE
248 BEGIN
249 SELECT
250 PERIOD_NUM
251 INTO L_RETIREMENT_MONTH
252 FROM
253 FA_DEPRN_PERIODS
254 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
255 AND PERIOD_COUNTER = PERIOD_COUNTER_FULLY_RESERVED;
256 END;
257 END IF;
258 L_INCLUDE_RET := 'Y';
259 END IF;
260 ELSE
261 L_RETIREMENT_MONTH := 13;
262 END IF;
263 END IF;
264 L_PERIODS_OF_USE := JL_ZZ_FA_FUNCTIONS_PKG.PERIODS_OF_USE(L_ADDITION_MONTH
265 ,L_RETIREMENT_MONTH
266 ,P_INCLUDE_DPIS
267 ,L_INCLUDE_RET);
268 RETURN ((C_ADJ_NBV_LESS_50PER / PERIODS_PER_FY) * L_PERIODS_OF_USE);
269 END C_IMPAC_BASE_VALUEFORMULA;
270
271 FUNCTION C_NBV_END_PREV_FYFORMULA(ORIGINAL_COST IN NUMBER
272 ,ACCUM_DEPRN_PREVIOUS_FY IN NUMBER) RETURN NUMBER IS
273 BEGIN
274 RETURN (ORIGINAL_COST - ACCUM_DEPRN_PREVIOUS_FY);
275 END C_NBV_END_PREV_FYFORMULA;
276
277 PROCEDURE CUSTOM_INIT IS
278 FIS_YR NUMBER(4);
279 V_RATIO_PRECISION VARCHAR2(10);
280 L_STAGE_REQUEST_ID NUMBER(15);
281 L_SET_REQUEST_ID NUMBER(15);
282 V_PERIODS_PER_FY NUMBER;
283 V_CALENDAR_TYPE VARCHAR2(15);
284 V_MIN_PERIOD_COUNTER NUMBER;
285 V_MAX_PERIOD_COUNTER NUMBER;
286 V_MIDDLE_YEAR NUMBER;
287 V_START_DATE DATE;
288 V_END_DATE DATE;
289 V_START_DATE_FISCAL_YEAR DATE;
290 V_END_DATE_FISCAL_YEAR DATE;
291 BEGIN
292 /*SRW.USER_EXIT('FND GETPROFILE NAME="CONC_REQUEST_ID", FIELD=":P_CONC_REQUEST_ID"')*/NULL;
293 FND_PROFILE.GET('JLZZ_INF_RATIO_PRECISION'
294 ,V_RATIO_PRECISION);
295 IF V_RATIO_PRECISION IS NULL THEN
296 C_RATIO_PRECISION := 38;
297 ELSE
298 C_RATIO_PRECISION := TO_NUMBER(V_RATIO_PRECISION);
299 END IF;
300 IF UPPER(P_MRCSOBTYPE) = 'R' THEN
301 BEGIN
302 SELECT
303 MIN(PERIOD_COUNTER),
304 MAX(PERIOD_COUNTER)
305 INTO V_MIN_PERIOD_COUNTER,V_MAX_PERIOD_COUNTER
306 FROM
307 FA_DEPRN_PERIODS_MRC_V
308 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
309 AND FISCAL_YEAR = P_CURR_FY;
310 EXCEPTION
311 WHEN NO_DATA_FOUND THEN
312 RAISE_ERR('JL_AR_FA_CUR_FY_DEP_PER_NOTDEF'
313 ,'N');
314 WHEN OTHERS THEN
315 RAISE_ORA_ERR;
316 END;
317 BEGIN
318 SELECT
319 CT.CALENDAR_TYPE,
320 CT.NUMBER_PER_FISCAL_YEAR,
321 CEIL(CT.NUMBER_PER_FISCAL_YEAR / 2)
322 INTO V_CALENDAR_TYPE,V_PERIODS_PER_FY,V_MIDDLE_YEAR
323 FROM
324 FA_BOOK_CONTROLS_MRC_V BK,
325 FA_CALENDAR_TYPES CT
326 WHERE BK.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
327 AND CT.CALENDAR_TYPE = BK.DEPRN_CALENDAR;
328 EXCEPTION
329 WHEN NO_DATA_FOUND THEN
330 RAISE_ERR('JL_AR_FA_CUR_FY_DEP_PER_NOTDEF'
331 ,'N');
332 WHEN OTHERS THEN
333 RAISE_ORA_ERR;
334 END;
335 ELSE
336 BEGIN
337 SELECT
338 MIN(PERIOD_COUNTER),
339 MAX(PERIOD_COUNTER)
340 INTO V_MIN_PERIOD_COUNTER,V_MAX_PERIOD_COUNTER
341 FROM
342 FA_DEPRN_PERIODS
343 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
344 AND FISCAL_YEAR = P_CURR_FY;
345 EXCEPTION
346 WHEN NO_DATA_FOUND THEN
347 RAISE_ERR('JL_AR_FA_CUR_FY_DEP_PER_NOTDEF'
348 ,'N');
349 WHEN OTHERS THEN
350 RAISE_ORA_ERR;
351 END;
352 BEGIN
353 SELECT
354 CT.CALENDAR_TYPE,
355 CT.NUMBER_PER_FISCAL_YEAR,
356 CEIL(CT.NUMBER_PER_FISCAL_YEAR / 2)
357 INTO V_CALENDAR_TYPE,V_PERIODS_PER_FY,V_MIDDLE_YEAR
358 FROM
359 FA_BOOK_CONTROLS BK,
360 FA_CALENDAR_TYPES CT
361 WHERE BK.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
362 AND CT.CALENDAR_TYPE = BK.DEPRN_CALENDAR;
363 EXCEPTION
364 WHEN NO_DATA_FOUND THEN
365 RAISE_ERR('JL_AR_FA_CUR_FY_DEP_PER_NOTDEF'
366 ,'N');
367 WHEN OTHERS THEN
368 RAISE_ORA_ERR;
369 END;
370 END IF;
371 IF UPPER(P_MRCSOBTYPE) = 'R' THEN
372 BEGIN
373 SELECT
374 FY.START_DATE,
375 FY.END_DATE,
376 CP.START_DATE,
377 CP.END_DATE
378 INTO V_START_DATE_FISCAL_YEAR,V_END_DATE_FISCAL_YEAR,V_START_DATE,V_END_DATE
379 FROM
380 FA_BOOK_CONTROLS_MRC_V BC,
381 FA_FISCAL_YEAR FY,
382 FA_CALENDAR_PERIODS CP
383 WHERE FY.FISCAL_YEAR = P_CURR_FY
384 AND BC.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
385 AND BC.FISCAL_YEAR_NAME = FY.FISCAL_YEAR_NAME
386 AND CP.CALENDAR_TYPE = V_CALENDAR_TYPE
387 AND CP.START_DATE >= FY.START_DATE
388 AND CP.END_DATE <= FY.END_DATE
389 AND CP.PERIOD_NUM = V_MIDDLE_YEAR;
390 EXCEPTION
391 WHEN NO_DATA_FOUND THEN
392 RAISE_ERR('JL_AR_FA_FY_DETAIL_NOT_DEFINED'
393 ,'Y');
394 WHEN OTHERS THEN
395 RAISE_ORA_ERR;
396 END;
397 ELSE
398 BEGIN
399 SELECT
400 FY.START_DATE,
401 FY.END_DATE,
402 CP.START_DATE,
403 CP.END_DATE
404 INTO V_START_DATE_FISCAL_YEAR,V_END_DATE_FISCAL_YEAR,V_START_DATE,V_END_DATE
405 FROM
406 FA_BOOK_CONTROLS BC,
407 FA_FISCAL_YEAR FY,
408 FA_CALENDAR_PERIODS CP
409 WHERE FY.FISCAL_YEAR = P_CURR_FY
410 AND BC.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
411 AND BC.FISCAL_YEAR_NAME = FY.FISCAL_YEAR_NAME
412 AND CP.CALENDAR_TYPE = V_CALENDAR_TYPE
413 AND CP.START_DATE >= FY.START_DATE
414 AND CP.END_DATE <= FY.END_DATE
415 AND CP.PERIOD_NUM = V_MIDDLE_YEAR;
416 EXCEPTION
417 WHEN NO_DATA_FOUND THEN
418 RAISE_ERR('JL_AR_FA_FY_DETAIL_NOT_DEFINED'
419 ,'Y');
420 WHEN OTHERS THEN
421 RAISE_ORA_ERR;
422 END;
423 END IF;
424 BEGIN
425 SELECT
426 A.REQUEST_ID
427 INTO L_SET_REQUEST_ID
428 FROM
429 FND_CONCURRENT_REQUESTS A
430 WHERE A.PARENT_REQUEST_ID = (
431 SELECT
432 B.REQUEST_ID
433 FROM
434 FND_CONCURRENT_REQUESTS B
435 WHERE B.PARENT_REQUEST_ID = (
436 SELECT
437 C.PARENT_REQUEST_ID
438 FROM
439 FND_CONCURRENT_REQUESTS C
440 WHERE C.ROWID <> B.ROWID
441 AND C.REQUEST_ID = (
442 SELECT
443 D.PARENT_REQUEST_ID
444 FROM
445 FND_CONCURRENT_REQUESTS D
446 WHERE D.REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID ) ) );
447 EXCEPTION
448 WHEN OTHERS THEN
449 RAISE_ORA_ERR;
450 END;
451 P_PROCESS_ID := L_SET_REQUEST_ID;
452 PERIODS_PER_FY := V_PERIODS_PER_FY;
453 MIN_PERIOD_COUNTER := V_MIN_PERIOD_COUNTER;
454 MAX_PERIOD_COUNTER := V_MAX_PERIOD_COUNTER;
455 C_INI_MID_YR := V_START_DATE;
456 C_END_MID_YR := V_END_DATE;
457 C_FISCAL_START_DATE := V_START_DATE_FISCAL_YEAR;
458 C_FISCAL_END_DATE := V_END_DATE_FISCAL_YEAR;
459 END CUSTOM_INIT;
460
461 PROCEDURE RAISE_ORA_ERR IS
462 ERRMSG VARCHAR2(1000);
463 ERRNUM NUMBER;
464 BEGIN
465 ERRNUM := SQLCODE;
466 /*SRW.MESSAGE(ERRNUM
467 ,ERRMSG)*/NULL;
468 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
469 END RAISE_ORA_ERR;
470
471 PROCEDURE RAISE_ERR(MSGNAME IN VARCHAR2
472 ,ABORT_FLAG IN VARCHAR2) IS
473 ERRMSG VARCHAR2(1000);
474 BEGIN
475 SET_NAME('JL'
476 ,MSGNAME);
477 ERRMSG := GET;
478 /*SRW.MESSAGE(JL_ZZ_FA_UTILITIES_PKG.GET_APP_ERRNUM('JL'
479 ,MSGNAME)
480 ,ERRMSG)*/NULL;
481 IF ABORT_FLAG = 'Y' THEN
482 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
483 END IF;
484 END RAISE_ERR;
485
486 FUNCTION C_INDEX_VALUE_ACQFORMULA(C_PRICE_INDEX IN NUMBER
487 ,ACQDATE IN DATE) RETURN NUMBER IS
488 X NUMBER;
489 ERRMSG VARCHAR2(1000);
490 INDEX_NAME VARCHAR2(40);
491 BEGIN
492 SELECT
493 PRICE_INDEX_VALUE
494 INTO X
495 FROM
496 FA_PRICE_INDEX_VALUES
497 WHERE PRICE_INDEX_ID = C_PRICE_INDEX
498 AND ACQDATE BETWEEN FROM_DATE
499 AND TO_DATE;
500 RETURN (NVL(X
501 ,0));
502 EXCEPTION
503 WHEN NO_DATA_FOUND THEN
504 SELECT
505 PRICE_INDEX_NAME
506 INTO INDEX_NAME
507 FROM
508 FA_PRICE_INDEXES
509 WHERE PRICE_INDEX_ID = C_PRICE_INDEX;
510 SET_NAME('JL'
511 ,'JL_ZZ_FA_INDX_VAL_NOT_FOUND');
512 SET_TOKEN('PRICE_INDEX'
513 ,INDEX_NAME
514 ,FALSE);
515 SET_TOKEN('MISSING_DATE'
516 ,TO_CHAR(ACQDATE)
517 ,FALSE);
518 ERRMSG := GET;
519 /*SRW.MESSAGE(JL_ZZ_FA_UTILITIES_PKG.GET_APP_ERRNUM('JL'
520 ,'JL_ZZ_FA_INDX_VAL_NOT_FOUND')
521 ,ERRMSG)*/NULL;
522 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
523 RETURN NULL;
524 WHEN OTHERS THEN
525 RAISE_ORA_ERR;
526 RETURN NULL;
527 END C_INDEX_VALUE_ACQFORMULA;
528
529 FUNCTION CF_RETIREMENT_DATE(RETIREMENT_ID_1 IN NUMBER) RETURN DATE IS
530 L_FA_RETIREMENT DATE;
531 BEGIN
532 IF RETIREMENT_ID_1 IS NOT NULL THEN
533 IF UPPER(P_MRCSOBTYPE) = 'R' THEN
534 SELECT
535 DATE_RETIRED
536 INTO L_FA_RETIREMENT
537 FROM
538 FA_RETIREMENTS_MRC_V
539 WHERE RETIREMENT_ID = RETIREMENT_ID_1;
540 ELSE
541 SELECT
542 DATE_RETIRED
543 INTO L_FA_RETIREMENT
544 FROM
545 FA_RETIREMENTS
546 WHERE RETIREMENT_ID = RETIREMENT_ID_1;
547 END IF;
548 ELSE
549 L_FA_RETIREMENT := NULL;
550 END IF;
551 RETURN (L_FA_RETIREMENT);
552 END CF_RETIREMENT_DATE;
553
554 FUNCTION CF_INCLUDE_DPISFORMULA RETURN CHAR IS
555 T_MEANING VARCHAR2(30);
556 BEGIN
557 SELECT
558 MEANING
559 INTO T_MEANING
560 FROM
561 FND_LOOKUPS
562 WHERE LOOKUP_TYPE = 'YES_NO'
563 AND LOOKUP_CODE = P_INCLUDE_DPIS;
564 RETURN (T_MEANING);
565 EXCEPTION
566 WHEN OTHERS THEN
567 RETURN (P_INCLUDE_DPIS);
568 END CF_INCLUDE_DPISFORMULA;
569
570 FUNCTION CF_INCLUDE_RETFORMULA RETURN CHAR IS
571 T_MEANING VARCHAR2(30);
572 BEGIN
573 SELECT
574 MEANING
575 INTO T_MEANING
576 FROM
577 FND_LOOKUPS
578 WHERE LOOKUP_TYPE = 'YES_NO'
579 AND LOOKUP_CODE = P_INCLUDE_RET;
580 RETURN (T_MEANING);
581 EXCEPTION
582 WHEN OTHERS THEN
583 RETURN (P_INCLUDE_RET);
584 END CF_INCLUDE_RETFORMULA;
585
586 FUNCTION CF_FULLY_DEPRN_ASSETFORMULA RETURN CHAR IS
587 T_MEANING VARCHAR2(2000);
588 BEGIN
589 SELECT
590 MEANING
591 INTO T_MEANING
592 FROM
593 FND_LOOKUPS
594 WHERE LOOKUP_TYPE = 'JLMX_FA_FULLY_DEPRN_ASSET_OPT'
595 AND LOOKUP_CODE = P_FULLY_DEPRN_ASSETS
596 AND sysdate between NVL(START_DATE_ACTIVE
597 ,SYSDATE - 1)
598 AND NVL(END_DATE_ACTIVE
599 ,SYSDATE + 1)
600 AND ENABLED_FLAG = 'Y';
601 RETURN (T_MEANING);
602 EXCEPTION
603 WHEN OTHERS THEN
604 RETURN (P_FULLY_DEPRN_ASSETS);
605 END CF_FULLY_DEPRN_ASSETFORMULA;
606
607 FUNCTION CF_INCLUDE_ZERO_NBV_ASSETSFORM RETURN CHAR IS
608 T_MEANING VARCHAR2(30);
609 BEGIN
610 SELECT
611 MEANING
612 INTO T_MEANING
613 FROM
614 FND_LOOKUPS
615 WHERE LOOKUP_TYPE = 'YES_NO'
616 AND LOOKUP_CODE = P_INCLUDE_ZERO_NBV_ASSETS;
617 RETURN (T_MEANING);
618 EXCEPTION
619 WHEN OTHERS THEN
620 RETURN (P_INCLUDE_ZERO_NBV_ASSETS);
621 END CF_INCLUDE_ZERO_NBV_ASSETSFORM;
622
623 FUNCTION AFTERPFORM RETURN BOOLEAN IS
624 BEGIN
625 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
626 IF P_CA_SET_OF_BOOKS_ID <> -1999 THEN
627 BEGIN
628 SELECT
629 MRC_SOB_TYPE_CODE,
630 CURRENCY_CODE
631 INTO P_MRCSOBTYPE,LP_CURRENCY_CODE
632 FROM
633 GL_SETS_OF_BOOKS
634 WHERE SET_OF_BOOKS_ID = P_CA_SET_OF_BOOKS_ID;
635 EXCEPTION
636 WHEN OTHERS THEN
637 P_MRCSOBTYPE := 'P';
638 END;
639 ELSE
640 P_MRCSOBTYPE := 'P';
641 END IF;
642 /*SRW.MESSAGE(1
643 ,'New:lp_currency_code' || LP_CURRENCY_CODE)*/NULL;
644 IF UPPER(P_MRCSOBTYPE) = 'R' THEN
645 LP_FA_BOOK_CONTROLS := 'FA_BOOK_CONTROLS_MRC_V';
646 LP_FA_BOOKS := 'FA_BOOKS_MRC_V';
647 LP_FA_ADJUSTMENTS := 'FA_ADJUSTMENTS_MRC_V';
648 LP_FA_DEPRN_PERIODS := 'FA_DEPRN_PERIODS_MRC_V';
649 LP_FA_DEPRN_DETAIL := 'FA_DEPRN_DETAIL_MRC_V';
650 LP_FA_DEPRN_SUMMARY := 'FA_DEPRN_SUMMARY_MRC_V';
651 ELSE
652 LP_FA_BOOK_CONTROLS := 'FA_BOOK_CONTROLS';
653 LP_FA_BOOKS := 'FA_BOOKS';
654 LP_FA_ADJUSTMENTS := 'FA_ADJUSTMENTS';
655 LP_FA_DEPRN_PERIODS := 'FA_DEPRN_PERIODS';
656 LP_FA_DEPRN_DETAIL := 'FA_DEPRN_DETAL';
657 LP_FA_DEPRN_SUMMARY := 'FA_DEPRN_SUMMARY';
658 END IF;
659 RETURN (TRUE);
660 END AFTERPFORM;
661
662 FUNCTION C_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
663 BEGIN
664 RETURN C_BASE_CURRENCY_CODE;
665 END C_BASE_CURRENCY_CODE_P;
666
667 FUNCTION C_BASE_PRECISION_P RETURN NUMBER IS
668 BEGIN
669 RETURN C_BASE_PRECISION;
670 END C_BASE_PRECISION_P;
671
672 FUNCTION C_BASE_MIN_ACCT_UNIT_P RETURN NUMBER IS
673 BEGIN
674 RETURN C_BASE_MIN_ACCT_UNIT;
675 END C_BASE_MIN_ACCT_UNIT_P;
676
677 FUNCTION C_BASE_DESCRIPTION_P RETURN VARCHAR2 IS
678 BEGIN
679 RETURN C_BASE_DESCRIPTION;
680 END C_BASE_DESCRIPTION_P;
681
682 FUNCTION C_ORGANIZATION_NAME_P RETURN VARCHAR2 IS
683 BEGIN
684 RETURN C_ORGANIZATION_NAME;
685 END C_ORGANIZATION_NAME_P;
686
687 FUNCTION C_CURR_FISCAL_YRX_P RETURN NUMBER IS
688 BEGIN
689 RETURN C_CURR_FISCAL_YRX;
690 END C_CURR_FISCAL_YRX_P;
691
692 FUNCTION CAT_FLEX_STRUCT_P RETURN NUMBER IS
693 BEGIN
694 RETURN CAT_FLEX_STRUCT;
695 END CAT_FLEX_STRUCT_P;
696
697 FUNCTION C_RATIO_PRECISION_P RETURN NUMBER IS
698 BEGIN
699 RETURN C_RATIO_PRECISION;
700 END C_RATIO_PRECISION_P;
701
702 FUNCTION C_ALL_SEGS_P RETURN VARCHAR2 IS
703 BEGIN
704 RETURN C_ALL_SEGS;
705 END C_ALL_SEGS_P;
706
707 FUNCTION PERIODS_PER_FY_P RETURN NUMBER IS
708 BEGIN
709 RETURN PERIODS_PER_FY;
710 END PERIODS_PER_FY_P;
711
712 FUNCTION MIN_PERIOD_COUNTER_P RETURN NUMBER IS
713 BEGIN
714 RETURN MIN_PERIOD_COUNTER;
715 END MIN_PERIOD_COUNTER_P;
716
717 FUNCTION MAX_PERIOD_COUNTER_P RETURN NUMBER IS
718 BEGIN
719 RETURN MAX_PERIOD_COUNTER;
720 END MAX_PERIOD_COUNTER_P;
721
722 FUNCTION C_INI_MID_YR_P RETURN DATE IS
723 BEGIN
724 RETURN C_INI_MID_YR;
725 END C_INI_MID_YR_P;
726
727 FUNCTION C_END_MID_YR_P RETURN DATE IS
728 BEGIN
729 RETURN C_END_MID_YR;
730 END C_END_MID_YR_P;
731
732 FUNCTION C_FISCAL_START_DATE_P RETURN DATE IS
733 BEGIN
734 RETURN C_FISCAL_START_DATE;
735 END C_FISCAL_START_DATE_P;
736
737 FUNCTION C_FISCAL_END_DATE_P RETURN DATE IS
738 BEGIN
739 RETURN C_FISCAL_END_DATE;
740 END C_FISCAL_END_DATE_P;
741
742 PROCEDURE SET_NAME(APPLICATION IN VARCHAR2
743 ,NAME IN VARCHAR2) IS
744 BEGIN
745 /* STPROC.INIT('begin FND_MESSAGE.SET_NAME(:APPLICATION, :NAME); end;');
746 STPROC.BIND_I(APPLICATION);
747 STPROC.BIND_I(NAME);
748 STPROC.EXECUTE;*/
749
750 FND_MESSAGE.SET_NAME(APPLICATION,NAME);
751 END SET_NAME;
752
753 PROCEDURE SET_TOKEN(TOKEN IN VARCHAR2
754 ,VALUE IN VARCHAR2
755 ,TRANSLATE IN BOOLEAN) IS
756 TRANSLATE1 BOOLEAN;
757 BEGIN
758 /* STPROC.INIT('declare TRANSLATE BOOLEAN; begin TRANSLATE := sys.diutil.int_to_bool(:TRANSLATE); FND_MESSAGE.SET_TOKEN(:TOKEN, :VALUE, TRANSLATE); end;');
759 STPROC.BIND_I(TRANSLATE);
760 STPROC.BIND_I(TOKEN);
761 STPROC.BIND_I(VALUE);
762 STPROC.EXECUTE;*/
763
764 -- TRANSLATE1 := sys.diutil.int_to_bool(TRANSLATE);
765 FND_MESSAGE.SET_TOKEN(TOKEN,VALUE, TRANSLATE1);
766 END SET_TOKEN;
767
768 PROCEDURE RETRIEVE(MSGOUT OUT NOCOPY VARCHAR2) IS
769 BEGIN
770 /* STPROC.INIT('begin FND_MESSAGE.RETRIEVE(:MSGOUT); end;');
771 STPROC.BIND_O(MSGOUT);
772 STPROC.EXECUTE;
773 STPROC.RETRIEVE(1
774 ,MSGOUT);*/null;
775 END RETRIEVE;
776
777 PROCEDURE CLEAR IS
778 BEGIN
779 /* STPROC.INIT('begin FND_MESSAGE.CLEAR; end;');
780 STPROC.EXECUTE;*/null;
781 END CLEAR;
782
783 FUNCTION GET_STRING(APPIN IN VARCHAR2
784 ,NAMEIN IN VARCHAR2) RETURN VARCHAR2 IS
785 X0 VARCHAR2(2000);
786 BEGIN
787 /* STPROC.INIT('begin :X0 := FND_MESSAGE.GET_STRING(:APPIN, :NAMEIN); end;');
788 STPROC.BIND_O(X0);
789 STPROC.BIND_I(APPIN);
790 STPROC.BIND_I(NAMEIN);
791 STPROC.EXECUTE;
792 STPROC.RETRIEVE(1
793 ,X0);*/
794 RETURN X0;
795 END GET_STRING;
796
797 FUNCTION GET_NUMBER(APPIN IN VARCHAR2
798 ,NAMEIN IN VARCHAR2) RETURN NUMBER IS
799 X0 NUMBER;
800 BEGIN
801 /* STPROC.INIT('begin :X0 := FND_MESSAGE.GET_NUMBER(:APPIN, :NAMEIN); end;');
802 STPROC.BIND_O(X0);
803 STPROC.BIND_I(APPIN);
804 STPROC.BIND_I(NAMEIN);
805 STPROC.EXECUTE;
806 STPROC.RETRIEVE(1
807 ,X0);*/
808 RETURN X0;
809 END GET_NUMBER;
810
811 FUNCTION GET RETURN VARCHAR2 IS
812 X0 VARCHAR2(2000);
813 BEGIN
814 /* STPROC.INIT('begin :X0 := FND_MESSAGE.GET; end;');
815 STPROC.BIND_O(X0);
816 STPROC.EXECUTE;
817 STPROC.RETRIEVE(1
818 ,X0);*/
819 X0 := FND_MESSAGE.GET;
820 RETURN X0;
821 END GET;
822
823 FUNCTION GET_ENCODED RETURN VARCHAR2 IS
824 X0 VARCHAR2(2000);
825 BEGIN
826 /* STPROC.INIT('begin :X0 := FND_MESSAGE.GET_ENCODED; end;');
827 STPROC.BIND_O(X0);
828 STPROC.EXECUTE;
829 STPROC.RETRIEVE(1
830 ,X0);*/
831 RETURN X0;
832 END GET_ENCODED;
833
834 PROCEDURE PARSE_ENCODED(ENCODED_MESSAGE IN VARCHAR2
835 ,APP_SHORT_NAME OUT NOCOPY VARCHAR2
836 ,MESSAGE_NAME OUT NOCOPY VARCHAR2) IS
837 BEGIN
838 /*STPROC.INIT('begin FND_MESSAGE.PARSE_ENCODED(:ENCODED_MESSAGE, :APP_SHORT_NAME, :MESSAGE_NAME); end;');
839 STPROC.BIND_I(ENCODED_MESSAGE);
840 STPROC.BIND_O(APP_SHORT_NAME);
841 STPROC.BIND_O(MESSAGE_NAME);
842 STPROC.EXECUTE;
843 STPROC.RETRIEVE(2
844 ,APP_SHORT_NAME);
845 STPROC.RETRIEVE(3
846 ,MESSAGE_NAME);*/null;
847 END PARSE_ENCODED;
848
849 PROCEDURE SET_ENCODED(ENCODED_MESSAGE IN VARCHAR2) IS
850 BEGIN
851 /* STPROC.INIT('begin FND_MESSAGE.SET_ENCODED(:ENCODED_MESSAGE); end;');
852 STPROC.BIND_I(ENCODED_MESSAGE);
853 STPROC.EXECUTE;*/null;
854 END SET_ENCODED;
855
856 PROCEDURE RAISE_ERROR IS
857 BEGIN
858 /* STPROC.INIT('begin FND_MESSAGE.RAISE_ERROR; end;');
859 STPROC.EXECUTE;*/null;
860 END RAISE_ERROR;
861 function BeforeReport return boolean is
862 begin
863
864
865 --SRW.USER_EXIT('FND SRWINIT');
866
867 IF upper(p_mrcsobtype) = 'R'
868 THEN
869 fnd_client_info.set_currency_context(p_ca_set_of_books_id);
870 END IF;
871 -- Call report level PL/SQL function
872
873 get_base_curr_data;
874
875 -- Call report level PL/SQL
876
877 custom_init;
878
879 --SRW.REFERENCE(:cat_flex_struct);
880 /*SRW.USER_EXIT('FND FLEXSQL CODE="CAT#"
881 NUM=":cat_flex_struct"
882 APPL_SHORT_NAME="OFA"
883 TABLEALIAS="ct"
884 OUTPUT=":c_all_segs"
885 MODE="SELECT"
886 DISPLAY="ALL"');*/
887
888 return (TRUE);
889 end;
890
891 END JL_JLMXFIMP_XMLP_PKG;
892
893