[Home] [Help]
PACKAGE BODY: APPS.JL_JLMXFISR_XMLP_PKG
Source
1 PACKAGE BODY JL_JLMXFISR_XMLP_PKG AS
2 /* $Header: JLMXFISRB.pls 120.2 2008/01/11 05:07:34 abraghun 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 BEGIN
9 BASE_CURR := '';
10 PREC := 0;
11 MIN_AU := 0;
12 DESCR := '';
13 SELECT
14 COMPANY_NAME,
15 CATEGORY_FLEX_STRUCTURE
16 INTO C_ORGANIZATION_NAME,CAT_FLEX_STRUCT
17 FROM
18 FA_SYSTEM_CONTROLS;
19 IF UPPER(P_MRCSOBTYPE) = 'R' THEN
20 BEGIN
21 SELECT
22 FCURR.CURRENCY_CODE,
23 FCURR.PRECISION,
24 FCURR.MINIMUM_ACCOUNTABLE_UNIT,
25 FCURR.DESCRIPTION
26 INTO BASE_CURR,PREC,MIN_AU,DESCR
27 FROM
28 FA_BOOK_CONTROLS_MRC_V BKCTRL,
29 FND_CURRENCIES_VL FCURR,
30 GL_SETS_OF_BOOKS GSBKS
31 WHERE BKCTRL.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
32 AND BKCTRL.SET_OF_BOOKS_ID = GSBKS.SET_OF_BOOKS_ID
33 AND GSBKS.CURRENCY_CODE = FCURR.CURRENCY_CODE;
34 EXCEPTION
35 WHEN NO_DATA_FOUND THEN
36 RAISE_ERR('JL_AR_FA_CURR_DET_NOT_DEFINED'
37 ,'N');
38 WHEN OTHERS THEN
39 RAISE_ORA_ERR;
40 END;
41 ELSE
42 BEGIN
43 SELECT
44 FCURR.CURRENCY_CODE,
45 FCURR.PRECISION,
46 FCURR.MINIMUM_ACCOUNTABLE_UNIT,
47 FCURR.DESCRIPTION
48 INTO BASE_CURR,PREC,MIN_AU,DESCR
49 FROM
50 FA_BOOK_CONTROLS BKCTRL,
51 FND_CURRENCIES_VL FCURR,
52 GL_SETS_OF_BOOKS GSBKS
53 WHERE BKCTRL.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
54 AND BKCTRL.SET_OF_BOOKS_ID = GSBKS.SET_OF_BOOKS_ID
55 AND GSBKS.CURRENCY_CODE = FCURR.CURRENCY_CODE;
56 EXCEPTION
57 WHEN NO_DATA_FOUND THEN
58 RAISE_ERR('JL_AR_FA_CURR_DET_NOT_DEFINED'
59 ,'N');
60 WHEN OTHERS THEN
61 RAISE_ORA_ERR;
62 END;
63 END IF;
64 C_BASE_CURRENCY_CODE := BASE_CURR;
65 C_BASE_PRECISION := PREC;
66 C_BASE_MIN_ACCT_UNIT := MIN_AU;
67 C_BASE_DESCRIPTION := DESCR;
68 END GET_BASE_CURR_DATA;
69
70 FUNCTION AFTERREPORT RETURN BOOLEAN IS
71 BEGIN
72 BEGIN
73 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
74 COMMIT;
75 EXCEPTION
76 WHEN OTHERS THEN
77 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
78 END;
79 RETURN (TRUE);
80 END AFTERREPORT;
81
82 FUNCTION C_PRICE_INDEXFORMULA(ASSET_CAT_ID IN NUMBER
83 ,ACQDATE IN DATE) RETURN NUMBER IS
84 X NUMBER(15);
85 CATEGORY_DESC VARCHAR2(40);
86 ERRMSG VARCHAR2(1000);
87 BEGIN
88 SELECT
89 FPI.PRICE_INDEX_ID
90 INTO X
91 FROM
92 FA_PRICE_INDEXES FPI,
93 FA_CATEGORY_BOOK_DEFAULTS FCBD
94 WHERE FCBD.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
95 AND FCBD.CATEGORY_ID = ASSET_CAT_ID
96 AND ACQDATE >= FCBD.START_DPIS
97 AND ACQDATE <= NVL(FCBD.END_DPIS
98 ,ACQDATE)
99 AND FCBD.PRICE_INDEX_NAME = FPI.PRICE_INDEX_NAME;
100 IF X IS NULL THEN
101 RAISE NO_DATA_FOUND;
102 END IF;
103 RETURN (X);
104 EXCEPTION
105 WHEN NO_DATA_FOUND THEN
106 SELECT
107 DESCRIPTION
108 INTO CATEGORY_DESC
109 FROM
110 FA_CATEGORIES
111 WHERE CATEGORY_ID = ASSET_CAT_ID;
112 SET_NAME('JL'
113 ,'JL_ZZ_FA_INDX_NOT_DEF_FOR_CATG');
114 SET_TOKEN('ASSET_CATEGORY'
115 ,CATEGORY_DESC
116 ,FALSE);
117 ERRMSG := GET;
118 /*SRW.MESSAGE(JL_ZZ_FA_UTILITIES_PKG.GET_APP_ERRNUM('JL'
119 ,'JL_ZZ_FA_INDX_NOT_DEF_FOR_CATG')
120 ,ERRMSG)*/NULL;
121 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
122 RETURN NULL;
123 WHEN OTHERS THEN
124 RAISE_ORA_ERR;
125 RETURN NULL;
126 END C_PRICE_INDEXFORMULA;
127
128 FUNCTION C_INDEX_VALUEFORMULA(C_PRICE_INDEX IN NUMBER
129 ,ACQDATE IN DATE) RETURN NUMBER IS
130 X NUMBER;
131 ERRMSG VARCHAR2(1000);
132 INDEX_NAME VARCHAR2(40);
133 BEGIN
134 SELECT
135 PRICE_INDEX_VALUE
136 INTO X
137 FROM
138 FA_PRICE_INDEX_VALUES
139 WHERE PRICE_INDEX_ID = C_PRICE_INDEX
140 AND ACQDATE BETWEEN FROM_DATE
141 AND TO_DATE;
142 RETURN (NVL(X
143 ,0));
144 EXCEPTION
145 WHEN NO_DATA_FOUND THEN
146 SELECT
147 PRICE_INDEX_NAME
148 INTO INDEX_NAME
149 FROM
150 FA_PRICE_INDEXES
151 WHERE PRICE_INDEX_ID = C_PRICE_INDEX;
152 SET_NAME('JL'
153 ,'JL_ZZ_FA_INDX_VAL_NOT_FOUND');
154 SET_TOKEN('PRICE_INDEX'
155 ,INDEX_NAME
156 ,FALSE);
157 SET_TOKEN('MISSING_DATE'
158 ,TO_CHAR(ACQDATE)
159 ,FALSE);
160 ERRMSG := GET;
161 /*SRW.MESSAGE(JL_ZZ_FA_UTILITIES_PKG.GET_APP_ERRNUM('JL'
162 ,'JL_ZZ_FA_INDX_VAL_NOT_FOUND')
163 ,ERRMSG)*/NULL;
164 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
165 RETURN NULL;
166 WHEN OTHERS THEN
167 RAISE_ORA_ERR;
168 RETURN NULL;
169 END C_INDEX_VALUEFORMULA;
170
171 FUNCTION C_CORR_FACTORFORMULA(C_INDEX_VALUE_HALF_PERIOD IN NUMBER
172 ,C_INDEX_VALUE IN NUMBER) RETURN NUMBER IS
173 FACTOR NUMBER;
174 BEGIN
175 RETURN (TRUNC(C_INDEX_VALUE_HALF_PERIOD / C_INDEX_VALUE
176 ,C_RATIO_PRECISION));
177 END C_CORR_FACTORFORMULA;
178
179 FUNCTION C_ACCUM_DEPRN_PREV_YRFORMULA(ASSET_ID IN NUMBER
180 ,PERIOD_COUNTER IN NUMBER
181 ,ORIGINAL_COST IN NUMBER
182 ,RETIREMENT_ID IN NUMBER) RETURN NUMBER IS
183 T_ACC_DEPRN_PREV_FY NUMBER;
184 BEGIN
185 prc_prev_fiscal_year_deprn(asset_id,
186 p_book_type_code,
187 c_min_period_counter,
188 c_max_period_counter,
189 period_counter,
190 original_cost,
191 retirement_id,
192 t_acc_deprn_prev_fy);
193 /* PRC_PREV_FISCAL_YEAR_DEPRN(P_ACC_DEPRN_PREV_FY
194 ,P_RETIREMENT_ID
195 ,P_COST_RETIRED
196 ,P_PERIOD_COUNTER
197 ,P_PERIOD_END
198 ,P_PERIOD_START
199 ,P_BOOK_TYPE_CODE
200 ,P_ASSET_ID
201 ,RETIREMENT_ID);*/
202 RETURN (T_ACC_DEPRN_PREV_FY);
203 END C_ACCUM_DEPRN_PREV_YRFORMULA;
204
205 FUNCTION C_ADJ_ACCUM_DEPRNFORMULA(C_ACCUM_DEPRN_CURR_YR IN NUMBER
206 ,C_CORR_FACTOR IN NUMBER
207 ,ASSET_ID IN NUMBER
208 ,TRANSACTION_HEADER_ID_IN IN NUMBER
209 ,TRANSACTION_HEADER_ID_OUT IN NUMBER
210 ,DESCRIPTION IN VARCHAR2
211 ,ASSET_CAT_ID IN NUMBER
212 ,ASSET_NUMBER IN VARCHAR2
213 ,ACQDATE IN DATE
214 ,PRORATE_DATE IN DATE
215 ,RETIREMENT_ID IN NUMBER
216 ,C_INDEX_VALUE IN NUMBER
217 ,ORIGINAL_COST IN NUMBER
218 ,C_ACCUM_DEPRN_PREV_YR IN NUMBER
219 ,PERIOD_COUNTER_FULLY_RESERVED IN NUMBER) RETURN NUMBER IS
220 ACCUM_DEPRN NUMBER;
221 PRAGMA AUTONOMOUS_TRANSACTION;
222 BEGIN
223 ACCUM_DEPRN := NVL(C_ACCUM_DEPRN_CURR_YR
224 ,0) * NVL(C_CORR_FACTOR
225 ,0);
226 INSERT INTO JL_MX_FA_ISR_IMPAC
227 (PROCESS_ID
228 ,ASSET_ID
229 ,TRANSACTION_HEADER_ID_IN
230 ,TRANSACTION_HEADER_ID_OUT
231 ,DESCRIPTION
232 ,ASSET_CATEGORY_ID
233 ,ASSET_NUMBER
234 ,DATE_PLACED_IN_SERVICE
235 ,PRORATE_DATE
236 ,RETIREMENT_ID
237 ,INPC
238 ,ORIGINAL_COST
239 ,ACCUM_DEPRN_PREVIOUS_FY
240 ,ADJUSTED_DEPRN_CURRENT_FY
241 ,PERIOD_COUNTER_FULLY_RESERVED
242 ,LAST_UPDATE_DATE
243 ,LAST_UPDATED_BY
244 ,CREATION_DATE
245 ,CREATED_BY
246 ,LAST_UPDATE_LOGIN)
247 VALUES (P_CONC_REQUEST_ID
248 ,ASSET_ID
249 ,TRANSACTION_HEADER_ID_IN
250 ,TRANSACTION_HEADER_ID_OUT
251 ,DESCRIPTION
252 ,ASSET_CAT_ID
253 ,ASSET_NUMBER
254 ,ACQDATE
255 ,PRORATE_DATE
256 ,RETIREMENT_ID
257 ,C_INDEX_VALUE
258 ,ORIGINAL_COST
259 ,C_ACCUM_DEPRN_PREV_YR
260 ,ACCUM_DEPRN
261 ,PERIOD_COUNTER_FULLY_RESERVED
262 ,SYSDATE
263 ,-1
264 ,SYSDATE
265 ,C_USER_ID
266 ,C_USER_ID);
267 commit;
268 RETURN (ACCUM_DEPRN);
269 END C_ADJ_ACCUM_DEPRNFORMULA;
270
271 FUNCTION C_ACCUM_DEPRNFORMULA(STATUS IN VARCHAR2
272 ,TRANSACTION_HEADER_ID_OUT IN NUMBER
273 ,TRANSACTION_HEADER_ID_IN IN NUMBER
274 ,ASSET_ID IN NUMBER
275 ,PERIOD_COUNTER IN NUMBER
276 ,RETIREMENT_ID IN NUMBER
277 ,ORIGINAL_COST IN NUMBER) RETURN NUMBER IS
278 T_ACC_DEPRN_LIFE_TD NUMBER;
279 T_TRANSACTION_HEADER_ID_IN_OUT NUMBER;
280 BEGIN
281 IF STATUS = 'R' THEN
282 T_TRANSACTION_HEADER_ID_IN_OUT := TRANSACTION_HEADER_ID_OUT;
283 ELSE
284 T_TRANSACTION_HEADER_ID_IN_OUT := TRANSACTION_HEADER_ID_IN;
285 END IF;
286 PRC_LIFE_TO_DATE_DEPRN(ASSET_ID
287 ,P_BOOK_TYPE_CODE
288 ,T_TRANSACTION_HEADER_ID_IN_OUT
289 ,C_MIN_PERIOD_COUNTER
290 ,C_MAX_PERIOD_COUNTER
291 ,PERIOD_COUNTER
292 ,RETIREMENT_ID
293 ,ORIGINAL_COST
294 ,T_ACC_DEPRN_LIFE_TD);
295 RETURN (T_ACC_DEPRN_LIFE_TD);
296 END C_ACCUM_DEPRNFORMULA;
297
298 FUNCTION C_ACCUM_DEPRN_CURR_YRFORMULA(C_ACCUM_DEPRN IN NUMBER
299 ,C_ACCUM_DEPRN_PREV_YR IN NUMBER) RETURN NUMBER IS
300 T_ACC_DEPRN_CURR_FY NUMBER;
301 BEGIN
302 T_ACC_DEPRN_CURR_FY := C_ACCUM_DEPRN - C_ACCUM_DEPRN_PREV_YR;
303 RETURN (T_ACC_DEPRN_CURR_FY);
304 END C_ACCUM_DEPRN_CURR_YRFORMULA;
305
306 FUNCTION C_INDEX_VALUE_HALF_PERIODFORMU(RETIREMENT_ID_1 IN NUMBER
307 ,ACQDATE IN DATE
308 ,C_PRICE_INDEX IN NUMBER) RETURN NUMBER IS
309 MON_RETIRED VARCHAR2(2);
310 MON_ACQUIRED VARCHAR2(2);
311 MIDDLE_MONTH VARCHAR2(2);
315 ERRMSG VARCHAR2(1000);
312 INPC_HALF NUMBER;
313 HALF_PERIOD_DATE DATE;
314 YEAR VARCHAR2(4);
316 INDEX_NAME VARCHAR2(40);
317 BEGIN
318 IF RETIREMENT_ID_1 IS NOT NULL THEN
319 IF UPPER(P_MRCSOBTYPE) = 'R' THEN
320 BEGIN
321 SELECT
322 TO_CHAR(A.DATE_RETIRED
323 ,'MM')
324 INTO MON_RETIRED
325 FROM
326 FA_RETIREMENTS_MRC_V A
327 WHERE A.RETIREMENT_ID = RETIREMENT_ID_1;
328 EXCEPTION
329 WHEN OTHERS THEN
330 MON_RETIRED := 13;
331 END;
332 ELSE
333 BEGIN
334 SELECT
335 TO_CHAR(A.DATE_RETIRED
336 ,'MM')
337 INTO MON_RETIRED
338 FROM
339 FA_RETIREMENTS A
340 WHERE A.RETIREMENT_ID = RETIREMENT_ID_1;
341 EXCEPTION
342 WHEN OTHERS THEN
343 MON_RETIRED := 13;
344 END;
345 END IF;
346 ELSE
347 MON_RETIRED := 13;
348 END IF;
349 IF ACQDATE BETWEEN C_FISCAL_START_DATE AND C_FISCAL_END_DATE THEN
350 MON_ACQUIRED := TO_NUMBER(TO_CHAR(ACQDATE
351 ,'MM'));
352 ELSE
353 MON_ACQUIRED := 0;
354 END IF;
355 MIDDLE_MONTH := JL_ZZ_FA_FUNCTIONS_PKG.MIDDLE_MONTH(MON_ACQUIRED
356 ,MON_RETIRED
357 ,P_INCLUDE_DPIS
358 ,P_INCLUDE_RET);
359 IF MIDDLE_MONTH = '0' THEN
360 YEAR := TO_CHAR(C_FISCAL_START_DATE - 365
361 ,'YYYY');
362 MIDDLE_MONTH := '12';
363 ELSE
364 YEAR := TO_CHAR(C_FISCAL_START_DATE
365 ,'YYYY');
366 END IF;
367 HALF_PERIOD_DATE := LAST_DAY(TO_DATE('01-' || LPAD(MIDDLE_MONTH
368 ,2
369 ,'0') || '-' || YEAR
370 ,'DD-MM-YYYY'));
371 BEGIN
372 SELECT
373 PRICE_INDEX_VALUE
374 INTO INPC_HALF
375 FROM
376 FA_PRICE_INDEX_VALUES
377 WHERE PRICE_INDEX_ID = C_PRICE_INDEX
378 AND HALF_PERIOD_DATE BETWEEN FROM_DATE
379 AND TO_DATE;
380 RETURN (NVL(INPC_HALF
381 ,0));
382 EXCEPTION
383 WHEN NO_DATA_FOUND THEN
384 SELECT
385 PRICE_INDEX_NAME
386 INTO INDEX_NAME
387 FROM
388 FA_PRICE_INDEXES
389 WHERE PRICE_INDEX_ID = C_PRICE_INDEX;
390 SET_NAME('JL'
391 ,'JL_ZZ_FA_INDX_VAL_NOT_FOUND');
392 SET_TOKEN('PRICE_INDEX'
393 ,INDEX_NAME
394 ,FALSE);
395 SET_TOKEN('MISSING_DATE'
396 ,TO_CHAR(HALF_PERIOD_DATE)
397 ,FALSE);
398 ERRMSG := GET;
399 /*SRW.MESSAGE(JL_ZZ_FA_UTILITIES_PKG.GET_APP_ERRNUM('JL'
400 ,'JL_ZZ_FA_INDX_VAL_NOT_FOUND')
401 ,ERRMSG)*/NULL;
402 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
403 RETURN NULL;
404 WHEN OTHERS THEN
405 RAISE_ORA_ERR;
406 RETURN NULL;
407 END;
408 END C_INDEX_VALUE_HALF_PERIODFORMU;
409
410 PROCEDURE CUSTOM_INIT IS
411 FIS_YR NUMBER(4);
412 MAX_PER_COUNTER NUMBER(15);
413 MIN_PER_COUNTER NUMBER(15);
414 LAST_PER_COUNTER NUMBER(15);
415 V_RATIO_PRECISION VARCHAR2(10);
416 FROM_DATE DATE;
417 TO_DATE DATE;
418 BEGIN
419 /*SRW.USER_EXIT('FND GETPROFILE NAME="CONC_REQUEST_ID", FIELD=":P_PROCESS_ID"')*/NULL;
420 /*SRW.USER_EXIT('FND GETPROFILE NAME="USER_ID", FIELD=":C_USER_ID"')*/NULL;
421 FND_PROFILE.GET('JLZZ_INF_RATIO_PRECISION'
422 ,V_RATIO_PRECISION);
423 IF V_RATIO_PRECISION IS NULL THEN
424 C_RATIO_PRECISION := 38;
425 ELSE
426 C_RATIO_PRECISION := TO_NUMBER(V_RATIO_PRECISION);
427 END IF;
428 IF UPPER(P_MRCSOBTYPE) = 'R' THEN
429 BEGIN
430 SELECT
431 MIN(PERIOD_COUNTER),
432 MAX(PERIOD_COUNTER)
433 INTO MIN_PER_COUNTER,MAX_PER_COUNTER
434 FROM
435 FA_DEPRN_PERIODS_MRC_V
436 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
437 AND FISCAL_YEAR = P_CURR_FY;
438 EXCEPTION
439 WHEN NO_DATA_FOUND THEN
440 RAISE_ERR('JL_AR_FA_CUR_FY_DEP_PER_NOTDEF'
441 ,'N');
442 WHEN OTHERS THEN
443 RAISE_ORA_ERR;
444 END;
445 C_MIN_PERIOD_COUNTER := MIN_PER_COUNTER;
446 C_MAX_PERIOD_COUNTER := MAX_PER_COUNTER;
447 BEGIN
448 SELECT
449 MAX(PERIOD_COUNTER)
450 INTO LAST_PER_COUNTER
451 FROM
452 FA_DEPRN_PERIODS_MRC_V
453 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
454 AND FISCAL_YEAR = P_CURR_FY - 1;
455 EXCEPTION
456 WHEN NO_DATA_FOUND THEN
457 RAISE_ERR('JL_AR_FA_PRV_FY_DEP_PER_NOTDEF'
458 ,'N');
459 WHEN OTHERS THEN
460 RAISE_ORA_ERR;
461 END;
462 C_LAST_PERIOD_COUNTER := LAST_PER_COUNTER;
463 BEGIN
464 SELECT
465 A.START_DATE,
466 A.END_DATE
467 INTO FROM_DATE,TO_DATE
468 FROM
469 FA_BOOK_CONTROLS_MRC_V B,
470 FA_FISCAL_YEAR A
474 EXCEPTION
471 WHERE A.FISCAL_YEAR = P_CURR_FY
472 AND B.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
473 AND B.FISCAL_YEAR_NAME = A.FISCAL_YEAR_NAME;
475 WHEN NO_DATA_FOUND THEN
476 RAISE_ERR('JL_AR_FA_FY_DETAIL_NOT_DEFINED'
477 ,'Y');
478 WHEN OTHERS THEN
479 RAISE_ORA_ERR;
480 END;
481 C_FISCAL_START_DATE := FROM_DATE;
482 C_FISCAL_END_DATE := TO_DATE;
483 ELSE
484 BEGIN
485 SELECT
486 MIN(PERIOD_COUNTER),
487 MAX(PERIOD_COUNTER)
488 INTO MIN_PER_COUNTER,MAX_PER_COUNTER
489 FROM
490 FA_DEPRN_PERIODS
491 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
492 AND FISCAL_YEAR = P_CURR_FY;
493 EXCEPTION
494 WHEN NO_DATA_FOUND THEN
495 RAISE_ERR('JL_AR_FA_CUR_FY_DEP_PER_NOTDEF'
496 ,'N');
497 WHEN OTHERS THEN
498 RAISE_ORA_ERR;
499 END;
500 C_MIN_PERIOD_COUNTER := MIN_PER_COUNTER;
501 C_MAX_PERIOD_COUNTER := MAX_PER_COUNTER;
502 BEGIN
503 SELECT
504 MAX(PERIOD_COUNTER)
505 INTO LAST_PER_COUNTER
506 FROM
507 FA_DEPRN_PERIODS
508 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
509 AND FISCAL_YEAR = P_CURR_FY - 1;
510 EXCEPTION
511 WHEN NO_DATA_FOUND THEN
512 RAISE_ERR('JL_AR_FA_PRV_FY_DEP_PER_NOTDEF'
513 ,'N');
514 WHEN OTHERS THEN
515 RAISE_ORA_ERR;
516 END;
517 C_LAST_PERIOD_COUNTER := LAST_PER_COUNTER;
518 BEGIN
519 SELECT
520 A.START_DATE,
521 A.END_DATE
522 INTO FROM_DATE,TO_DATE
523 FROM
524 FA_BOOK_CONTROLS B,
525 FA_FISCAL_YEAR A
526 WHERE A.FISCAL_YEAR = P_CURR_FY
527 AND B.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
528 AND B.FISCAL_YEAR_NAME = A.FISCAL_YEAR_NAME;
529 EXCEPTION
530 WHEN NO_DATA_FOUND THEN
531 RAISE_ERR('JL_AR_FA_FY_DETAIL_NOT_DEFINED'
532 ,'Y');
533 WHEN OTHERS THEN
534 RAISE_ORA_ERR;
535 END;
536 C_FISCAL_START_DATE := FROM_DATE;
537 C_FISCAL_END_DATE := TO_DATE;
538 END IF;
539 END CUSTOM_INIT;
540
541 PROCEDURE RAISE_ERR(MSGNAME IN VARCHAR2
542 ,ABORT_FLAG IN VARCHAR2) IS
543 ERRMSG VARCHAR2(1000);
544 BEGIN
545 SET_NAME('JL'
546 ,MSGNAME);
547 ERRMSG := GET;
548 /*SRW.MESSAGE(JL_ZZ_FA_UTILITIES_PKG.GET_APP_ERRNUM('JL'
549 ,MSGNAME)
550 ,ERRMSG)*/NULL;
551 IF ABORT_FLAG = 'Y' THEN
552 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
553 END IF;
554 END RAISE_ERR;
555
556 PROCEDURE RAISE_ORA_ERR IS
557 ERRMSG VARCHAR2(1000);
558 ERRNUM NUMBER;
559 BEGIN
560 ERRMSG := SQLERRM;
561 ERRNUM := SQLCODE;
562 /*SRW.MESSAGE(ERRNUM
563 ,ERRMSG)*/NULL;
564 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
565 END RAISE_ORA_ERR;
566
567 PROCEDURE PRC_PREV_FISCAL_YEAR_DEPRN(P_ASSET_ID IN NUMBER
568 ,P_BOOK_TYPE_CODE IN VARCHAR
569 ,P_PERIOD_START IN NUMBER
570 ,P_PERIOD_END IN NUMBER
571 ,P_PERIOD_COUNTER IN NUMBER
572 ,P_COST_RETIRED IN NUMBER
573 ,P_RETIREMENT_ID IN NUMBER
574 ,P_ACC_DEPRN_PREV_FY OUT NOCOPY NUMBER) IS
575 L_NR_COST NUMBER;
576 L_HAS_RESERVE_ADJUSTMENT NUMBER := 0;
577 L_HAS_RETIREMENT NUMBER := 0;
578 L_TOTAL_DEPRN_ADJUSTMENT NUMBER := 0;
579 L_LAST_DEPRN_PERIOD_PREV_FY NUMBER := 0;
580 L_ACC_DEPRN_PREV_FY NUMBER := 0;
581 L_ADJUSTED_COST_PREV_FY NUMBER := 0;
582 L_PREV_ADJUSTED_COST NUMBER;
583 L_COST_RETIRED NUMBER;
584 L_ADJ_COST_LESS_PART_RET NUMBER;
585 L_COST_BEGIN_YEAR NUMBER;
586 CURSOR C_RET_DEPRN_SUMMARY IS
587 SELECT
588 FDS.ADJUSTED_COST,
589 FDS.PERIOD_COUNTER
590 FROM
591 FA_DEPRN_SUMMARY FDS
592 WHERE FDS.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
593 AND FDS.ASSET_ID = P_ASSET_ID
594 AND FDS.PERIOD_COUNTER between P_PERIOD_START
595 AND P_PERIOD_COUNTER;
596 CURSOR C_RET_DEPRN_SUMMARY_MRC IS
597 SELECT
598 FDS.ADJUSTED_COST,
599 FDS.PERIOD_COUNTER
600 FROM
601 FA_DEPRN_SUMMARY_MRC_V FDS
602 WHERE FDS.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
603 AND FDS.ASSET_ID = P_ASSET_ID
604 AND FDS.PERIOD_COUNTER between P_PERIOD_START
605 AND P_PERIOD_COUNTER;
606 BEGIN
607 P_ACC_DEPRN_PREV_FY := 0;
608 IF UPPER(P_MRCSOBTYPE) = 'R' THEN
609 BEGIN
610 SELECT
611 PERIOD_COUNTER,
612 DEPRN_RESERVE,
613 ADJUSTED_COST
614 INTO L_LAST_DEPRN_PERIOD_PREV_FY,L_ACC_DEPRN_PREV_FY,L_ADJUSTED_COST_PREV_FY
615 FROM
616 FA_DEPRN_SUMMARY_MRC_V
617 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
618 AND ASSET_ID = P_ASSET_ID
619 AND PERIOD_COUNTER = (
620 SELECT
621 MAX(FDS2.PERIOD_COUNTER)
622 FROM
623 FA_DEPRN_SUMMARY_MRC_V FDS2
624 WHERE FDS2.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
625 AND FDS2.ASSET_ID = P_ASSET_ID
626 AND FDS2.PERIOD_COUNTER <= P_PERIOD_START - 1 );
627 EXCEPTION
628 WHEN NO_DATA_FOUND THEN
629 L_ACC_DEPRN_PREV_FY := 0;
630 WHEN OTHERS THEN
631 L_ACC_DEPRN_PREV_FY := -1;
632 END;
633 IF L_ACC_DEPRN_PREV_FY = 0 THEN
634 P_ACC_DEPRN_PREV_FY := 0;
635 RETURN;
636 END IF;
637 BEGIN
638 SELECT
639 COUNT(RET.RETIREMENT_ID)
640 INTO L_HAS_RETIREMENT
641 FROM
642 FA_RETIREMENTS_MRC_V RET,
643 FA_DEPRN_PERIODS_MRC_V FDP
644 WHERE RET.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
645 AND RET.STATUS <> 'DELETED'
646 AND RET.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
647 AND RET.ASSET_ID = P_ASSET_ID
648 AND RET.DATE_RETIRED between FDP.CALENDAR_PERIOD_OPEN_DATE
649 AND FDP.CALENDAR_PERIOD_CLOSE_DATE
650 AND FDP.FISCAL_YEAR = P_CURR_FY;
651 END;
652 IF L_HAS_RETIREMENT = 0 THEN
653 P_ACC_DEPRN_PREV_FY := L_ACC_DEPRN_PREV_FY;
654 RETURN;
655 END IF;
656 BEGIN
657 SELECT
658 COUNT(FDD.DEPRN_ADJUSTMENT_AMOUNT),
659 SUM(FDD.DEPRN_ADJUSTMENT_AMOUNT)
660 INTO L_HAS_RESERVE_ADJUSTMENT,L_TOTAL_DEPRN_ADJUSTMENT
661 FROM
662 FA_DEPRN_DETAIL_MRC_V FDD
663 WHERE FDD.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
664 AND FDD.ASSET_ID = P_ASSET_ID
665 AND FDD.PERIOD_COUNTER between P_PERIOD_START
666 AND P_PERIOD_END
667 AND FDD.DEPRN_ADJUSTMENT_AMOUNT <> 0;
668 END;
669 BEGIN
670 SELECT
671 BKS.COST
672 INTO L_COST_BEGIN_YEAR
673 FROM
674 FA_BOOKS_MRC_V BKS,
675 FA_DEPRN_PERIODS_MRC_V DPP
676 WHERE DPP.PERIOD_COUNTER = P_PERIOD_START
677 AND BKS.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
678 AND DPP.BOOK_TYPE_CODE = BKS.BOOK_TYPE_CODE
679 AND BKS.ASSET_ID = P_ASSET_ID
680 AND DPP.PERIOD_OPEN_DATE between BKS.DATE_EFFECTIVE
681 AND NVL(BKS.DATE_INEFFECTIVE
682 ,DPP.PERIOD_OPEN_DATE);
683 EXCEPTION
684 WHEN NO_DATA_FOUND THEN
685 L_COST_BEGIN_YEAR := 0;
686 WHEN OTHERS THEN
687 /*SRW.MESSAGE(1
688 ,'Error Calculation Cost at begining of the year')*/NULL;
689 END;
690 IF L_HAS_RESERVE_ADJUSTMENT = 0 THEN
691 BEGIN
692 SELECT
693 DEPRN_RESERVE / L_COST_BEGIN_YEAR * P_COST_RETIRED
694 INTO P_ACC_DEPRN_PREV_FY
695 FROM
696 FA_DEPRN_SUMMARY_MRC_V
697 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
698 AND ASSET_ID = P_ASSET_ID
699 AND PERIOD_COUNTER = L_LAST_DEPRN_PERIOD_PREV_FY;
700 EXCEPTION
701 WHEN NO_DATA_FOUND THEN
702 P_ACC_DEPRN_PREV_FY := 0;
703 WHEN OTHERS THEN
704 P_ACC_DEPRN_PREV_FY := -1;
705 END;
706 ELSE
707 L_PREV_ADJUSTED_COST := L_ADJUSTED_COST_PREV_FY;
708 L_ADJ_COST_LESS_PART_RET := L_ADJUSTED_COST_PREV_FY;
709 FOR c_ret_rec IN C_RET_DEPRN_SUMMARY_MRC LOOP
710 IF L_ADJ_COST_LESS_PART_RET = 0 THEN
711 L_COST_RETIRED := 0;
712 EXIT;
713 END IF;
714 IF C_RET_REC.ADJUSTED_COST <> L_PREV_ADJUSTED_COST THEN
715 BEGIN
716 SELECT
717 RET.COST_RETIRED
718 INTO L_COST_RETIRED
719 FROM
720 FA_RETIREMENTS_MRC_V RET,
721 FA_DEPRN_PERIODS_MRC_V FDP
722 WHERE RET.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
723 AND RET.STATUS <> 'DELETED'
724 AND RET.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
725 AND RET.ASSET_ID = P_ASSET_ID
726 AND RET.DATE_RETIRED between FDP.CALENDAR_PERIOD_OPEN_DATE
727 AND FDP.CALENDAR_PERIOD_CLOSE_DATE
728 AND FDP.PERIOD_COUNTER = C_RET_REC.PERIOD_COUNTER;
729 EXCEPTION
730 WHEN NO_DATA_FOUND THEN
731 L_COST_RETIRED := 0;
732 WHEN OTHERS THEN
733 L_COST_RETIRED := 0;
734 END;
735 IF L_COST_RETIRED <> 0 THEN
736 IF L_COST_RETIRED <= L_ADJ_COST_LESS_PART_RET THEN
737 L_ADJ_COST_LESS_PART_RET := L_ADJ_COST_LESS_PART_RET - L_COST_RETIRED;
738 ELSE
739 L_COST_RETIRED := L_ADJ_COST_LESS_PART_RET;
740 L_ADJ_COST_LESS_PART_RET := 0;
741 END IF;
742 END IF;
743 L_PREV_ADJUSTED_COST := C_RET_REC.ADJUSTED_COST;
744 END IF;
745 END LOOP;
746 IF P_RETIREMENT_ID IS NULL THEN
747 P_ACC_DEPRN_PREV_FY := (L_ACC_DEPRN_PREV_FY / L_ADJUSTED_COST_PREV_FY) * L_ADJ_COST_LESS_PART_RET;
748 ELSE
749 P_ACC_DEPRN_PREV_FY := (L_ACC_DEPRN_PREV_FY / L_ADJUSTED_COST_PREV_FY) * L_COST_RETIRED;
750 END IF;
751 END IF;
752 ELSE
753 BEGIN
754 SELECT
755 PERIOD_COUNTER,
756 DEPRN_RESERVE,
757 ADJUSTED_COST
758 INTO L_LAST_DEPRN_PERIOD_PREV_FY,L_ACC_DEPRN_PREV_FY,L_ADJUSTED_COST_PREV_FY
759 FROM
760 FA_DEPRN_SUMMARY
761 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
762 AND ASSET_ID = P_ASSET_ID
763 AND PERIOD_COUNTER = (
764 SELECT
765 MAX(FDS2.PERIOD_COUNTER)
766 FROM
767 FA_DEPRN_SUMMARY FDS2
768 WHERE FDS2.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
769 AND FDS2.ASSET_ID = P_ASSET_ID
770 AND FDS2.PERIOD_COUNTER <= P_PERIOD_START - 1 );
771 EXCEPTION
772 WHEN NO_DATA_FOUND THEN
773 L_ACC_DEPRN_PREV_FY := 0;
774 WHEN OTHERS THEN
775 L_ACC_DEPRN_PREV_FY := -1;
776 END;
777 IF L_ACC_DEPRN_PREV_FY = 0 THEN
778 P_ACC_DEPRN_PREV_FY := 0;
779 RETURN;
780 END IF;
781 BEGIN
782 SELECT
783 COUNT(RET.RETIREMENT_ID)
784 INTO L_HAS_RETIREMENT
785 FROM
786 FA_RETIREMENTS RET,
787 FA_DEPRN_PERIODS FDP
788 WHERE RET.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
789 AND RET.STATUS <> 'DELETED'
790 AND RET.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
791 AND RET.ASSET_ID = P_ASSET_ID
792 AND RET.DATE_RETIRED between FDP.CALENDAR_PERIOD_OPEN_DATE
793 AND FDP.CALENDAR_PERIOD_CLOSE_DATE
794 AND FDP.FISCAL_YEAR = P_CURR_FY;
795 END;
796 IF L_HAS_RETIREMENT = 0 THEN
797 P_ACC_DEPRN_PREV_FY := L_ACC_DEPRN_PREV_FY;
798 RETURN;
799 END IF;
800 BEGIN
801 SELECT
802 BKS.COST
803 INTO L_COST_BEGIN_YEAR
804 FROM
805 FA_BOOKS BKS,
806 FA_DEPRN_PERIODS DPP
807 WHERE DPP.PERIOD_COUNTER = P_PERIOD_START
808 AND BKS.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
809 AND DPP.BOOK_TYPE_CODE = BKS.BOOK_TYPE_CODE
810 AND BKS.ASSET_ID = P_ASSET_ID
811 AND DPP.PERIOD_OPEN_DATE between BKS.DATE_EFFECTIVE
812 AND NVL(BKS.DATE_INEFFECTIVE
813 ,DPP.PERIOD_OPEN_DATE);
814 EXCEPTION
815 WHEN NO_DATA_FOUND THEN
816 L_COST_BEGIN_YEAR := 0;
817 WHEN OTHERS THEN
818 /*SRW.MESSAGE(1
819 ,'Error Calculation Cost at begining of the year')*/NULL;
820 END;
821 BEGIN
822 SELECT
823 COUNT(FDD.DEPRN_ADJUSTMENT_AMOUNT),
824 SUM(FDD.DEPRN_ADJUSTMENT_AMOUNT)
825 INTO L_HAS_RESERVE_ADJUSTMENT,L_TOTAL_DEPRN_ADJUSTMENT
826 FROM
827 FA_DEPRN_DETAIL FDD
828 WHERE FDD.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
829 AND FDD.ASSET_ID = P_ASSET_ID
830 AND FDD.PERIOD_COUNTER between P_PERIOD_START
831 AND P_PERIOD_END
832 AND FDD.DEPRN_ADJUSTMENT_AMOUNT <> 0;
833 END;
834 IF L_HAS_RESERVE_ADJUSTMENT = 0 THEN
835 BEGIN
836 SELECT
837 DEPRN_RESERVE / L_COST_BEGIN_YEAR * P_COST_RETIRED
838 INTO P_ACC_DEPRN_PREV_FY
839 FROM
840 FA_DEPRN_SUMMARY
841 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
842 AND ASSET_ID = P_ASSET_ID
843 AND PERIOD_COUNTER = L_LAST_DEPRN_PERIOD_PREV_FY;
844 EXCEPTION
845 WHEN NO_DATA_FOUND THEN
846 P_ACC_DEPRN_PREV_FY := 0;
847 WHEN OTHERS THEN
848 P_ACC_DEPRN_PREV_FY := -1;
849 END;
850 ELSE
851 L_PREV_ADJUSTED_COST := L_ADJUSTED_COST_PREV_FY;
852 L_ADJ_COST_LESS_PART_RET := L_ADJUSTED_COST_PREV_FY;
853 FOR c_ret_rec IN C_RET_DEPRN_SUMMARY LOOP
854 IF L_ADJ_COST_LESS_PART_RET = 0 THEN
855 L_COST_RETIRED := 0;
856 EXIT;
857 END IF;
858 IF C_RET_REC.ADJUSTED_COST <> L_PREV_ADJUSTED_COST THEN
859 BEGIN
860 SELECT
861 RET.COST_RETIRED
862 INTO L_COST_RETIRED
863 FROM
864 FA_RETIREMENTS RET,
865 FA_DEPRN_PERIODS FDP
866 WHERE RET.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
867 AND RET.STATUS <> 'DELETED'
868 AND RET.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
869 AND RET.ASSET_ID = P_ASSET_ID
870 AND RET.DATE_RETIRED between FDP.CALENDAR_PERIOD_OPEN_DATE
871 AND FDP.CALENDAR_PERIOD_CLOSE_DATE
872 AND FDP.PERIOD_COUNTER = C_RET_REC.PERIOD_COUNTER;
873 EXCEPTION
874 WHEN NO_DATA_FOUND THEN
875 L_COST_RETIRED := 0;
876 WHEN OTHERS THEN
877 L_COST_RETIRED := 0;
878 END;
879 IF L_COST_RETIRED <> 0 THEN
880 IF L_COST_RETIRED <= L_ADJ_COST_LESS_PART_RET THEN
881 L_ADJ_COST_LESS_PART_RET := L_ADJ_COST_LESS_PART_RET - L_COST_RETIRED;
882 ELSE
883 L_COST_RETIRED := L_ADJ_COST_LESS_PART_RET;
884 L_ADJ_COST_LESS_PART_RET := 0;
885 END IF;
886 END IF;
887 L_PREV_ADJUSTED_COST := C_RET_REC.ADJUSTED_COST;
888 END IF;
889 END LOOP;
890 IF P_RETIREMENT_ID IS NULL THEN
891 P_ACC_DEPRN_PREV_FY := (L_ACC_DEPRN_PREV_FY / L_ADJUSTED_COST_PREV_FY) * L_ADJ_COST_LESS_PART_RET;
892 ELSE
893 P_ACC_DEPRN_PREV_FY := (L_ACC_DEPRN_PREV_FY / L_ADJUSTED_COST_PREV_FY) * L_COST_RETIRED;
894 END IF;
895 END IF;
896 END IF;
897 END PRC_PREV_FISCAL_YEAR_DEPRN;
898
899 PROCEDURE PRC_LIFE_TO_DATE_DEPRN(P_ASSET_ID IN NUMBER
900 ,P_BOOK_TYPE_CODE IN VARCHAR
901 ,P_TRANSACTION_HEADER_ID IN NUMBER
902 ,P_PERIOD_START IN NUMBER
903 ,P_PERIOD_END IN NUMBER
904 ,P_PERIOD_COUNTER IN NUMBER
905 ,P_RETIREMENT_ID IN NUMBER
906 ,P_COST_RETIRED IN NUMBER
907 ,P_ACC_DEPRN_LIFE_TD OUT NOCOPY NUMBER) IS
908 BEGIN
909 P_ACC_DEPRN_LIFE_TD := 0;
910 IF UPPER(P_MRCSOBTYPE) = 'R' THEN
911 IF P_RETIREMENT_ID IS NOT NULL THEN
912 BEGIN
913 SELECT
914 SUM(FAD.ADJUSTMENT_AMOUNT)
915 INTO P_ACC_DEPRN_LIFE_TD
916 FROM
917 FA_ADJUSTMENTS_MRC_V FAD
918 WHERE FAD.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
919 AND FAD.ASSET_ID = P_ASSET_ID
920 AND FAD.SOURCE_TYPE_CODE = 'RETIREMENT'
921 AND FAD.ADJUSTMENT_TYPE = 'RESERVE'
922 AND FAD.TRANSACTION_HEADER_ID = P_TRANSACTION_HEADER_ID;
923 EXCEPTION
924 WHEN NO_DATA_FOUND THEN
925 P_ACC_DEPRN_LIFE_TD := 0;
926 WHEN OTHERS THEN
927 P_ACC_DEPRN_LIFE_TD := -1;
928 END;
929 ELSE
930 BEGIN
931 SELECT
932 DEPRN_RESERVE
933 INTO P_ACC_DEPRN_LIFE_TD
934 FROM
935 FA_DEPRN_SUMMARY_MRC_V
936 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
937 AND ASSET_ID = P_ASSET_ID
938 AND PERIOD_COUNTER = P_PERIOD_COUNTER;
939 EXCEPTION
940 WHEN NO_DATA_FOUND THEN
941 P_ACC_DEPRN_LIFE_TD := 0;
942 WHEN OTHERS THEN
943 P_ACC_DEPRN_LIFE_TD := -1;
944 END;
945 END IF;
946 ELSE
947 IF P_RETIREMENT_ID IS NOT NULL THEN
948 BEGIN
949 SELECT
950 SUM(FAD.ADJUSTMENT_AMOUNT)
951 INTO P_ACC_DEPRN_LIFE_TD
952 FROM
953 FA_ADJUSTMENTS FAD
954 WHERE FAD.BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
955 AND FAD.ASSET_ID = P_ASSET_ID
956 AND FAD.SOURCE_TYPE_CODE = 'RETIREMENT'
957 AND FAD.ADJUSTMENT_TYPE = 'RESERVE'
958 AND FAD.TRANSACTION_HEADER_ID = P_TRANSACTION_HEADER_ID;
959 EXCEPTION
960 WHEN NO_DATA_FOUND THEN
961 P_ACC_DEPRN_LIFE_TD := 0;
962 WHEN OTHERS THEN
963 P_ACC_DEPRN_LIFE_TD := -1;
964 END;
965 ELSE
966 BEGIN
967 SELECT
968 DEPRN_RESERVE
969 INTO P_ACC_DEPRN_LIFE_TD
970 FROM
971 FA_DEPRN_SUMMARY
972 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
973 AND ASSET_ID = P_ASSET_ID
974 AND PERIOD_COUNTER = P_PERIOD_COUNTER;
975 EXCEPTION
976 WHEN NO_DATA_FOUND THEN
977 P_ACC_DEPRN_LIFE_TD := 0;
978 WHEN OTHERS THEN
979 P_ACC_DEPRN_LIFE_TD := -1;
980 END;
981 END IF;
982 END IF;
983 END PRC_LIFE_TO_DATE_DEPRN;
984
985 FUNCTION CF_RETIREMENT_DATE(RETIREMENT_ID_1 IN NUMBER) RETURN DATE IS
986 L_FA_RETIREMENT DATE;
987 BEGIN
988 IF RETIREMENT_ID_1 IS NOT NULL THEN
989 IF UPPER(P_MRCSOBTYPE) = 'R' THEN
990 SELECT
991 DATE_RETIRED
992 INTO L_FA_RETIREMENT
993 FROM
994 FA_RETIREMENTS_MRC_V
995 WHERE RETIREMENT_ID = RETIREMENT_ID_1;
996 ELSE
997 SELECT
998 DATE_RETIRED
999 INTO L_FA_RETIREMENT
1000 FROM
1001 FA_RETIREMENTS
1002 WHERE RETIREMENT_ID = RETIREMENT_ID_1;
1003 END IF;
1004 ELSE
1005 L_FA_RETIREMENT := NULL;
1006 END IF;
1007 RETURN (L_FA_RETIREMENT);
1008 END CF_RETIREMENT_DATE;
1009
1010 FUNCTION CF_INCLUDE_DPISFORMULA RETURN CHAR IS
1011 T_MEANING VARCHAR2(30);
1012 BEGIN
1013 SELECT
1014 MEANING
1015 INTO T_MEANING
1016 FROM
1017 FND_LOOKUPS
1018 WHERE LOOKUP_TYPE = 'YES_NO'
1019 AND LOOKUP_CODE = P_INCLUDE_DPIS;
1020 RETURN (T_MEANING);
1021 EXCEPTION
1022 WHEN OTHERS THEN
1023 RETURN (P_INCLUDE_DPIS);
1024 END CF_INCLUDE_DPISFORMULA;
1025
1026 FUNCTION CF_INCLUDE_RETFORMULA RETURN CHAR IS
1027 T_MEANING VARCHAR2(30);
1028 BEGIN
1029 SELECT
1030 MEANING
1031 INTO T_MEANING
1032 FROM
1033 FND_LOOKUPS
1034 WHERE LOOKUP_TYPE = 'YES_NO'
1035 AND LOOKUP_CODE = P_INCLUDE_RET;
1036 RETURN (T_MEANING);
1037 EXCEPTION
1038 WHEN OTHERS THEN
1039 RETURN (P_INCLUDE_RET);
1040 END CF_INCLUDE_RETFORMULA;
1041
1042 FUNCTION CF_INCLUDE_ZERO_NVB_ASSETSFORM RETURN CHAR IS
1043 T_MEANING VARCHAR2(30);
1044 BEGIN
1045 SELECT
1046 MEANING
1047 INTO T_MEANING
1048 FROM
1049 FND_LOOKUPS
1050 WHERE LOOKUP_TYPE = 'YES_NO'
1051 AND LOOKUP_CODE = P_INCLUDE_ZERO_NBV_ASSETS;
1052 RETURN (T_MEANING);
1053 EXCEPTION
1054 WHEN OTHERS THEN
1055 RETURN (P_INCLUDE_ZERO_NBV_ASSETS);
1056 END CF_INCLUDE_ZERO_NVB_ASSETSFORM;
1057
1058 FUNCTION AFTERPFORM RETURN BOOLEAN IS
1059 BEGIN
1060 IF P_CA_SET_OF_BOOKS_ID <> -1999 THEN
1061 BEGIN
1062 SELECT
1063 MRC_SOB_TYPE_CODE,
1064 CURRENCY_CODE
1065 INTO P_MRCSOBTYPE,LP_CURRENCY_CODE
1066 FROM
1067 GL_SETS_OF_BOOKS
1068 WHERE SET_OF_BOOKS_ID = P_CA_SET_OF_BOOKS_ID;
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 P_MRCSOBTYPE := 'P';
1072 END;
1073 ELSE
1074 P_MRCSOBTYPE := 'P';
1075 END IF;
1076 IF UPPER(P_MRCSOBTYPE) = 'R' THEN
1077 LP_FA_BOOK_CONTROLS := 'FA_BOOK_CONTROLS_MRC_V';
1078 LP_FA_BOOKS := 'FA_BOOKS_MRC_V';
1079 LP_FA_ADJUSTMENTS := 'FA_ADJUSTMENTS_MRC_V';
1080 LP_FA_DEPRN_PERIODS := 'FA_DEPRN_PERIODS_MRC_V';
1081 LP_FA_DEPRN_SUMMARY := 'FA_DEPRN_SUMMARY_MRC_V';
1082 LP_FA_DEPRN_DETAIL := 'FA_DEPRN_DETAIL_MRC_V';
1083 ELSE
1084 LP_FA_BOOK_CONTROLS := 'FA_BOOK_CONTROLS';
1085 LP_FA_BOOKS := 'FA_BOOKS';
1086 LP_FA_ADJUSTMENTS := 'FA_ADJUSTMENTS';
1087 LP_FA_DEPRN_PERIODS := 'FA_DEPRN_PERIODS';
1088 LP_FA_DEPRN_SUMMARY := 'FA_DEPRN_SUMMARY';
1089 LP_FA_DEPRN_DETAIL := 'FA_DEPRN_DETAIL';
1090 END IF;
1091 RETURN (TRUE);
1092 END AFTERPFORM;
1093
1094 FUNCTION C_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
1095 BEGIN
1096 RETURN C_BASE_CURRENCY_CODE;
1097 END C_BASE_CURRENCY_CODE_P;
1098
1099 FUNCTION C_BASE_PRECISION_P RETURN NUMBER IS
1100 BEGIN
1101 RETURN C_BASE_PRECISION;
1102 END C_BASE_PRECISION_P;
1106 RETURN C_BASE_MIN_ACCT_UNIT;
1103
1104 FUNCTION C_BASE_MIN_ACCT_UNIT_P RETURN NUMBER IS
1105 BEGIN
1107 END C_BASE_MIN_ACCT_UNIT_P;
1108
1109 FUNCTION C_BASE_DESCRIPTION_P RETURN VARCHAR2 IS
1110 BEGIN
1111 RETURN C_BASE_DESCRIPTION;
1112 END C_BASE_DESCRIPTION_P;
1113
1114 FUNCTION C_ORGANIZATION_NAME_P RETURN VARCHAR2 IS
1115 BEGIN
1116 RETURN C_ORGANIZATION_NAME;
1117 END C_ORGANIZATION_NAME_P;
1118
1119 FUNCTION C_MAX_PERIOD_COUNTER_P RETURN NUMBER IS
1120 BEGIN
1121 RETURN C_MAX_PERIOD_COUNTER;
1122 END C_MAX_PERIOD_COUNTER_P;
1123
1124 FUNCTION C_LAST_PERIOD_COUNTER_P RETURN NUMBER IS
1125 BEGIN
1126 RETURN C_LAST_PERIOD_COUNTER;
1127 END C_LAST_PERIOD_COUNTER_P;
1128
1129 FUNCTION C_FISCAL_START_DATE_P RETURN DATE IS
1130 BEGIN
1131 RETURN C_FISCAL_START_DATE;
1132 END C_FISCAL_START_DATE_P;
1133
1134 FUNCTION C_FISCAL_END_DATE_P RETURN DATE IS
1135 BEGIN
1136 RETURN C_FISCAL_END_DATE;
1137 END C_FISCAL_END_DATE_P;
1138
1139 FUNCTION C_RATIO_PRECISION_P RETURN NUMBER IS
1140 BEGIN
1141 RETURN C_RATIO_PRECISION;
1142 END C_RATIO_PRECISION_P;
1143
1144 FUNCTION C_MIN_PERIOD_COUNTER_P RETURN NUMBER IS
1145 BEGIN
1146 RETURN C_MIN_PERIOD_COUNTER;
1147 END C_MIN_PERIOD_COUNTER_P;
1148
1149 FUNCTION CAT_FLEX_STRUCT_P RETURN NUMBER IS
1150 BEGIN
1151 RETURN CAT_FLEX_STRUCT;
1152 END CAT_FLEX_STRUCT_P;
1153
1154 FUNCTION C_USER_ID_P RETURN NUMBER IS
1155 BEGIN
1156 RETURN C_USER_ID;
1157 END C_USER_ID_P;
1158
1159 FUNCTION C_ALL_SEGS_P RETURN VARCHAR2 IS
1160 BEGIN
1161 RETURN C_ALL_SEGS;
1162 END C_ALL_SEGS_P;
1163
1164 PROCEDURE SET_NAME(APPLICATION IN VARCHAR2
1165 ,NAME IN VARCHAR2) IS
1166 BEGIN
1167 /* STPROC.INIT('begin FND_MESSAGE.SET_NAME(:APPLICATION, :NAME); end;');
1168 STPROC.BIND_I(APPLICATION);
1169 STPROC.BIND_I(NAME);
1170 STPROC.EXECUTE;*/
1171 FND_MESSAGE.SET_NAME(APPLICATION,NAME);
1172 END SET_NAME;
1173
1174 PROCEDURE SET_TOKEN(TOKEN IN VARCHAR2
1175 ,VALUE IN VARCHAR2
1176 ,TRANSLATE IN BOOLEAN) IS
1177 TRANSLATE1 BOOLEAN;
1178 BEGIN
1179 /* STPROC.INIT('declare TRANSLATE BOOLEAN; begin TRANSLATE := sys.diutil.int_to_bool(:TRANSLATE); FND_MESSAGE.SET_TOKEN(:TOKEN, :VALUE, TRANSLATE); end;');
1180 STPROC.BIND_I(TRANSLATE);
1181 STPROC.BIND_I(TOKEN);
1182 STPROC.BIND_I(VALUE);
1183 STPROC.EXECUTE;*/
1184 --TRANSLATE1 := sys.diutil.int_to_bool(TRANSLATE);
1185 FND_MESSAGE.SET_TOKEN(TOKEN,VALUE,TRANSLATE1);
1186 END SET_TOKEN;
1187
1188 PROCEDURE RETRIEVE(MSGOUT OUT NOCOPY VARCHAR2) IS
1189 BEGIN
1190 /* STPROC.INIT('begin FND_MESSAGE.RETRIEVE(:MSGOUT); end;');
1191 STPROC.BIND_O(MSGOUT);
1192 STPROC.EXECUTE;
1193 STPROC.RETRIEVE(1
1194 ,MSGOUT);*/null;
1195 END RETRIEVE;
1196
1197 PROCEDURE CLEAR IS
1198 BEGIN
1199 /* STPROC.INIT('begin FND_MESSAGE.CLEAR; end;');
1200 STPROC.EXECUTE;*/null;
1201 END CLEAR;
1202
1203 FUNCTION GET_STRING(APPIN IN VARCHAR2
1204 ,NAMEIN IN VARCHAR2) RETURN VARCHAR2 IS
1205 X0 VARCHAR2(2000);
1206 BEGIN
1207 /* STPROC.INIT('begin :X0 := FND_MESSAGE.GET_STRING(:APPIN, :NAMEIN); end;');
1208 STPROC.BIND_O(X0);
1209 STPROC.BIND_I(APPIN);
1210 STPROC.BIND_I(NAMEIN);
1211 STPROC.EXECUTE;
1212 STPROC.RETRIEVE(1
1213 ,X0);*/
1214 X0 := FND_MESSAGE.GET_STRING(APPIN,NAMEIN);
1215 RETURN X0;
1216 END GET_STRING;
1217
1218 FUNCTION GET_NUMBER(APPIN IN VARCHAR2
1219 ,NAMEIN IN VARCHAR2) RETURN NUMBER IS
1220 X0 NUMBER;
1221 BEGIN
1222 /* STPROC.INIT('begin :X0 := FND_MESSAGE.GET_NUMBER(:APPIN, :NAMEIN); end;');
1223 STPROC.BIND_O(X0);
1224 STPROC.BIND_I(APPIN);
1225 STPROC.BIND_I(NAMEIN);
1226 STPROC.EXECUTE;
1227 STPROC.RETRIEVE(1
1228 ,X0);*/
1229 X0 := FND_MESSAGE.GET_NUMBER(APPIN,NAMEIN);
1230 RETURN X0;
1231 END GET_NUMBER;
1232
1233 FUNCTION GET RETURN VARCHAR2 IS
1234 X0 VARCHAR2(2000);
1235 BEGIN
1236 /* STPROC.INIT('begin :X0 := FND_MESSAGE.GET; end;');
1237 STPROC.BIND_O(X0);
1238 STPROC.EXECUTE;
1239 STPROC.RETRIEVE(1
1240 ,X0);*/
1241 X0 := FND_MESSAGE.GET;
1242 RETURN X0;
1243 END GET;
1244
1245 FUNCTION GET_ENCODED RETURN VARCHAR2 IS
1246 X0 VARCHAR2(2000);
1247 BEGIN
1248 /* STPROC.INIT('begin :X0 := FND_MESSAGE.GET_ENCODED; end;');
1249 STPROC.BIND_O(X0);
1250 STPROC.EXECUTE;
1251 STPROC.RETRIEVE(1
1252 ,X0);*/
1253 X0 := FND_MESSAGE.GET_ENCODED;
1254 RETURN X0;
1255 END GET_ENCODED;
1256
1257 PROCEDURE PARSE_ENCODED(ENCODED_MESSAGE IN VARCHAR2
1258 ,APP_SHORT_NAME OUT NOCOPY VARCHAR2
1259 ,MESSAGE_NAME OUT NOCOPY VARCHAR2) IS
1260 BEGIN
1261 /* STPROC.INIT('begin FND_MESSAGE.PARSE_ENCODED(:ENCODED_MESSAGE, :APP_SHORT_NAME, :MESSAGE_NAME); end;');
1262 STPROC.BIND_I(ENCODED_MESSAGE);
1263 STPROC.BIND_O(APP_SHORT_NAME);
1264 STPROC.BIND_O(MESSAGE_NAME);
1265 STPROC.EXECUTE;
1266 STPROC.RETRIEVE(2
1267 ,APP_SHORT_NAME);
1268 STPROC.RETRIEVE(3
1269 ,MESSAGE_NAME);*/
1270 FND_MESSAGE.PARSE_ENCODED(ENCODED_MESSAGE,APP_SHORT_NAME,MESSAGE_NAME);
1271 END PARSE_ENCODED;
1272
1273 PROCEDURE SET_ENCODED(ENCODED_MESSAGE IN VARCHAR2) IS
1274 BEGIN
1275 /* STPROC.INIT('begin FND_MESSAGE.SET_ENCODED(:ENCODED_MESSAGE); end;');
1276 STPROC.BIND_I(ENCODED_MESSAGE);
1277 STPROC.EXECUTE;*/null;
1278 END SET_ENCODED;
1279
1280 PROCEDURE RAISE_ERROR IS
1281 BEGIN
1282 /* STPROC.INIT('begin FND_MESSAGE.RAISE_ERROR; end;');
1283 STPROC.EXECUTE;*/null;
1284 END RAISE_ERROR;
1285
1286 function BeforeReport return boolean is
1287 begin
1288
1289 --SRW.USER_EXIT('FND SRWINIT');
1290 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1291 IF upper(p_mrcsobtype) = 'R'
1292 THEN
1293 fnd_client_info.set_currency_context(p_ca_set_of_books_id);
1294 END IF;
1295 -- Call report level PL/SQL function
1296
1297 get_base_curr_data;
1298
1299 -- Call report level PL/SQL
1300
1301 custom_init;
1302
1303
1304 /*SRW.REFERENCE(:cat_flex_struct);
1305 SRW.USER_EXIT('FND FLEXSQL CODE="CAT#"
1306 NUM=":cat_flex_struct"
1307 APPL_SHORT_NAME="OFA"
1308 TABLEALIAS="ct"
1309 OUTPUT=":c_all_segs"
1310 MODE="SELECT"
1311 DISPLAY="ALL"');*/
1312
1313 return (TRUE);
1314 end;
1315
1316 END JL_JLMXFISR_XMLP_PKG;
1317
1318
1319