1 PACKAGE BODY XTR_XTRCCYGL_XMLP_PKG AS
2 /* $Header: XTRCCYGLB.pls 120.1 2007/12/28 12:42:59 npannamp noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 L_FACTOR NUMBER(15) := 1;
5 BEGIN
6 BEGIN
7 P_DATE_FROM_T:=P_DATE_FROM;
8 P_DATE_TO_T := P_DATE_TO;
9 P_REALIZED_FLAG_T:=P_REALIZED_FLAG;
10 IF P_FACTOR IS NOT NULL THEN
11 SELECT
12 DECODE(SUBSTR(P_FACTOR
13 ,1
14 ,1)
15 ,'U'
16 ,1
17 ,'T'
18 ,1000
19 ,'M'
20 ,1000000
21 ,'B'
22 ,100000000),
23 MEANING
24 INTO
25 L_FACTOR
26 ,P_USER_FACTOR
27 FROM
28 FND_LOOKUPS
29 WHERE LOOKUP_TYPE = 'XTR_FACTOR'
30 AND LOOKUP_CODE = SUBSTR(P_FACTOR
31 ,1
32 ,1);
33 ELSE
34 L_FACTOR := 1000;
35 END IF;
36 P_UNIT := L_FACTOR;
37 EXCEPTION
38 WHEN OTHERS THEN
39 NULL;
40 END;
41 BEGIN
42 --IF P_REALIZED_FLAG IS NOT NULL THEN
43 IF P_REALIZED_FLAG_T IS NOT NULL THEN
44 SELECT
45 MEANING
46 INTO
47 Z2REALIZED_FLAG
48 FROM
49 FND_LOOKUPS
50 WHERE LOOKUP_TYPE = 'XTR_MISC'
51 --AND LOOKUP_CODE = P_REALIZED_FLAG;
52 AND LOOKUP_CODE = P_REALIZED_FLAG_T;
53 IF P_REALIZED_FLAG_T = 'REAL' THEN
54 P_REALIZED_FLAG_T := 'Y';
55 ELSE
56 P_REALIZED_FLAG_T := 'N';
57 END IF;
58 ELSE
59 P_REALIZED_FLAG_T := 'N';
60 END IF;
61 EXCEPTION
62 WHEN OTHERS THEN
63 NULL;
64 END;
65 BEGIN
66 IF P_DEAL_TYPE IS NOT NULL AND P_DEAL_TYPE < 'HEDGE' THEN
67 SELECT
68 USER_DEAL_TYPE
69 INTO
70 P_USER_DEAL_TYPE
71 FROM
72 XTR_DEAL_TYPES
73 WHERE DEAL_TYPE = P_DEAL_TYPE;
74 ELSIF P_DEAL_TYPE IS NOT NULL AND P_DEAL_TYPE = 'HEDGE' THEN
75 SELECT
76 MEANING
77 INTO
78 P_USER_DEAL_TYPE
79 FROM
80 FND_LOOKUPS
81 WHERE LOOKUP_TYPE = 'XTR_HEDGE_MISC'
82 AND LOOKUP_CODE = 'HEDGE';
83 END IF;
84 EXCEPTION
85 WHEN OTHERS THEN
86 NULL;
87 END;
88 BEGIN
89 IF P_GROUPBY IS NOT NULL THEN
90 SELECT
91 MEANING
92 INTO
93 P_USER_GROUPBY
94 FROM
95 FND_LOOKUPS
96 WHERE LOOKUP_TYPE = 'XTR_MISC'
97 AND LOOKUP_CODE = P_GROUPBY;
98 END IF;
99 EXCEPTION
100 WHEN OTHERS THEN
101 NULL;
102 END;
103 BEGIN
104 IF P_COMPANY IS NOT NULL THEN
105 SELECT
106 SUBSTR(SHORT_NAME
107 ,1
108 ,30)
109 INTO
110 P_USER_COMPANY
111 FROM
112 XTR_PARTY_INFO
113 WHERE PARTY_CODE = P_COMPANY
114 AND PARTY_TYPE = 'C';
115 END IF;
116 EXCEPTION
117 WHEN OTHERS THEN
118 NULL;
119 END;
120 BEGIN
121 IF P_BATCH_ID_FROM IS NOT NULL THEN
122 SELECT
123 TO_CHAR(MIN(PERIOD_START)
124 ,'YYYY/MM/DD HH24:MI:SS')
125 INTO
126 P_DATE_FROM_T
127 FROM
128 XTR_BATCHES B,
129 XTR_BATCH_EVENTS E
130 WHERE B.BATCH_ID = E.BATCH_ID
131 AND E.EVENT_CODE = 'REVAL'
132 AND B.BATCH_ID >= P_BATCH_ID_FROM;
133 P_USER_DATE_FROM := TO_DATE(P_DATE_FROM_T
134 ,'YYYY/MM/DD HH24:MI:SS');
135 END IF;
136 IF P_BATCH_ID_TO IS NOT NULL THEN
137 SELECT
138 TO_CHAR(MAX(PERIOD_END)
139 ,'YYYY/MM/DD HH24:MI:SS')
140 INTO
141 P_DATE_TO_T
142 FROM
143 XTR_BATCHES B,
144 XTR_BATCH_EVENTS E
145 WHERE B.BATCH_ID = E.BATCH_ID
146 AND E.EVENT_CODE = 'REVAL'
147 AND B.BATCH_ID <= P_BATCH_ID_TO;
148 P_USER_DATE_TO := TO_DATE(P_DATE_TO_T
149 ,'YYYY/MM/DD HH24:MI:SS');
150 END IF;
151 EXCEPTION
152 WHEN OTHERS THEN
153 NULL;
154 END;
155 RETURN (TRUE);
156 END AFTERPFORM;
157
158 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
159 L_DMMY_NUM NUMBER;
160 L_MESSAGE FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
161 CURSOR GET_LANGUAGE_DESC IS
162 SELECT
163 ITEM_NAME,
164 SUBSTR(TEXT
165 ,1
166 ,100) LANG_NAME
167 FROM
168 XTR_SYS_LANGUAGES_VL
169 WHERE MODULE_NAME = 'XTRCCYGL';
170 BEGIN
171 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
172 FOR c IN GET_LANGUAGE_DESC LOOP
173 IF C.ITEM_NAME = 'Z2COMPANY' THEN
174 Z2COMPANY := C.LANG_NAME;
175 ELSIF C.ITEM_NAME = 'Z2REPORT_PRD' THEN
176 Z2REPORT_PRD := C.LANG_NAME;
177 ELSIF C.ITEM_NAME = 'Z2REVAL_CCY' THEN
178 Z2REVAL_CCY := C.LANG_NAME;
179 ELSIF C.ITEM_NAME = 'Z2SOB_CCY' THEN
180 Z2SOB_CCY := C.LANG_NAME;
181 ELSIF C.ITEM_NAME = 'Z2PORTFOLIO' THEN
182 Z2PORTFOLIO := C.LANG_NAME;
183 ELSIF C.ITEM_NAME = 'Z2DEAL_TYPE' THEN
184 Z2DEAL_TYPE := C.LANG_NAME;
185 ELSIF C.ITEM_NAME = 'Z2DEAL_SUBTYPE' THEN
186 Z2DEAL_SUBTYPE := C.LANG_NAME;
187 ELSIF C.ITEM_NAME = 'Z2PRODUCT_TYPE' THEN
188 Z2PRODUCT_TYPE := C.LANG_NAME;
189 ELSIF C.ITEM_NAME = 'Z2REFERENCE' THEN
190 Z2REFERENCE := C.LANG_NAME;
191 ELSIF C.ITEM_NAME = 'Z2START' THEN
192 Z2START := C.LANG_NAME;
193 ELSIF C.ITEM_NAME = 'Z2PRDEND' THEN
194 Z2PRDEND := C.LANG_NAME;
195 ELSIF C.ITEM_NAME = 'Z2PERIOD' THEN
196 Z2PERIOD := C.LANG_NAME;
197 ELSIF C.ITEM_NAME = 'Z2BUY' THEN
198 Z2BUY := C.LANG_NAME;
199 ELSIF C.ITEM_NAME = 'Z2SELL' THEN
200 Z2SELL := C.LANG_NAME;
201 ELSIF C.ITEM_NAME = 'Z2CCY' THEN
202 Z2CCY := C.LANG_NAME;
203 ELSIF C.ITEM_NAME = 'Z2CCY_SHT' THEN
204 Z2CCY_SHT := C.LANG_NAME;
205 ELSIF C.ITEM_NAME = 'Z2AMOUNT' THEN
206 Z2AMOUNT := C.LANG_NAME;
207 ELSIF C.ITEM_NAME = 'Z2TRANS' THEN
208 Z2TRANS := C.LANG_NAME;
209 ELSIF C.ITEM_NAME = 'Z2BEGIN' THEN
210 Z2BEGIN := C.LANG_NAME;
211 ELSIF C.ITEM_NAME = 'Z2FAIR_VALUE' THEN
212 Z2FAIR_VALUE := C.LANG_NAME;
213 ELSIF C.ITEM_NAME = 'Z2END' THEN
214 Z2END := C.LANG_NAME;
215 ELSIF C.ITEM_NAME = 'Z2GL_RATE' THEN
216 Z2GL_RATE := C.LANG_NAME;
217 ELSIF C.ITEM_NAME = 'Z2GAIN_LOSS' THEN
218 Z2GAIN_LOSS := C.LANG_NAME;
219 ELSIF C.ITEM_NAME = 'Z2TOTAL' THEN
220 Z2TOTAL := C.LANG_NAME;
221 ELSIF C.ITEM_NAME = 'Z2END_OF_REPORT' THEN
222 Z2END_OF_REPORT := C.LANG_NAME;
223 ELSIF C.ITEM_NAME = 'Z2NO_DATA_FOUND' THEN
224 Z2NO_DATA_FOUND := C.LANG_NAME;
225 ELSIF C.ITEM_NAME = 'Z1BATCH_ID_FROM' THEN
226 Z1BATCH_ID_FROM := C.LANG_NAME;
227 ELSIF C.ITEM_NAME = 'Z1BATCH_ID_TO' THEN
228 Z1BATCH_ID_TO := C.LANG_NAME;
229 ELSIF C.ITEM_NAME = 'Z1DATE_FROM' THEN
230 Z1DATE_FROM := C.LANG_NAME;
231 ELSIF C.ITEM_NAME = 'Z1DATE_TO' THEN
232 Z1DATE_TO := C.LANG_NAME;
233 ELSIF C.ITEM_NAME = 'Z1PARA_GROUPING' THEN
234 Z1PARA_GROUPING := C.LANG_NAME;
235 ELSIF C.ITEM_NAME = 'Z1REAL_UNREAL' THEN
236 Z1REAL_UNREAL := C.LANG_NAME;
237 ELSIF C.ITEM_NAME = 'Z1FACTOR' THEN
238 Z1FACTOR := C.LANG_NAME;
239 ELSIF C.ITEM_NAME = 'Z1REPHEAD_REAL' THEN
240 Z1REPHEAD_REAL := C.LANG_NAME;
241 ELSIF C.ITEM_NAME = 'Z1REPHEAD_UNREAL' THEN
242 Z1REPHEAD_UNREAL := C.LANG_NAME;
243 END IF;
244 END LOOP;
245 RETURN (TRUE);
246 END BEFOREREPORT;
247
248 FUNCTION AFTERREPORT RETURN BOOLEAN IS
249 BEGIN
250 RETURN (TRUE);
251 END AFTERREPORT;
252
253 FUNCTION C_DATEFORMATFORMULA(C_DATEFORMAT IN VARCHAR2) RETURN CHAR IS
254 BEGIN
255 RETURN (fnd_global.nls_date_format);
256 END C_DATEFORMATFORMULA;
257
258 FUNCTION CO_SHT_NAMEFORMULA(COMPANY IN VARCHAR2) RETURN CHAR IS
259 L_SHT_NAME VARCHAR2(30);
260 BEGIN
261 IF COMPANY IS NOT NULL THEN
262 SELECT
263 SHORT_NAME
264 INTO
265 L_SHT_NAME
266 FROM
267 XTR_PARTY_INFO
268 WHERE PARTY_CODE = COMPANY
269 AND PARTY_TYPE = 'C';
270 END IF;
271 RETURN (L_SHT_NAME);
272 EXCEPTION
273 WHEN OTHERS THEN
274 RETURN (NULL);
275 END CO_SHT_NAMEFORMULA;
276
277 FUNCTION C_REPORT_NAMEFORMULA RETURN CHAR IS
278 L_REPORT_NAME VARCHAR2(240);
279 BEGIN
280 IF P_REALIZED_FLAG_T in ('Y','REAL') THEN
281 L_REPORT_NAME := Z1REPHEAD_REAL;
282 ELSE
283 L_REPORT_NAME := Z1REPHEAD_UNREAL;
284 END IF;
285 RETURN (L_REPORT_NAME);
286 EXCEPTION
287 WHEN OTHERS THEN
288 SELECT
289 SUBSTR(CP.USER_CONCURRENT_PROGRAM_NAME
290 ,INSTR(CP.USER_CONCURRENT_PROGRAM_NAME
291 ,'-') + 2)
292 INTO
293 L_REPORT_NAME
294 FROM
295 FND_CONCURRENT_PROGRAMS_VL CP,
296 FND_CONCURRENT_REQUESTS CR
297 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
298 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
299 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
300 RETURN (L_REPORT_NAME);
301 END C_REPORT_NAMEFORMULA;
302
303 FUNCTION USER_DEAL_SUBTYPEFORMULA(DEAL_SUBTYPE_P IN VARCHAR2
304 ,DEAL_TYPE_P IN VARCHAR2) RETURN CHAR IS
305 L_USER_DEAL_SUBTYPE VARCHAR2(30);
306 BEGIN
307 IF DEAL_SUBTYPE_P IS NOT NULL AND DEAL_TYPE_P < 'HEDGE' THEN
308 SELECT
309 SUBSTR(USER_DEAL_SUBTYPE
310 ,1
311 ,30)
312 INTO
313 L_USER_DEAL_SUBTYPE
314 FROM
315 XTR_DEAL_SUBTYPES
316 WHERE DEAL_SUBTYPE = DEAL_SUBTYPE_P
317 AND DEAL_TYPE = DEAL_TYPE_P;
318 ELSIF DEAL_SUBTYPE_P IS NOT NULL AND DEAL_TYPE_P = 'HEDGE' THEN
319 SELECT
320 SUBSTR(MEANING
321 ,1
322 ,30)
323 INTO
324 L_USER_DEAL_SUBTYPE
325 FROM
326 FND_LOOKUPS
327 WHERE LOOKUP_TYPE = 'XTR_HEDGE_TYPES'
328 AND LOOKUP_CODE = DEAL_SUBTYPE_p;
329 END IF;
330 RETURN (L_USER_DEAL_SUBTYPE);
331 EXCEPTION
332 WHEN OTHERS THEN
333 RETURN (NULL);
334 END USER_DEAL_SUBTYPEFORMULA;
335
336 FUNCTION USER_DEAL_TYPEFORMULA(DEAL_TYPE_p IN VARCHAR2) RETURN CHAR IS
337 L_USER_DEAL_TYPE VARCHAR2(30);
338 BEGIN
339 IF DEAL_TYPE_p IS NOT NULL AND DEAL_TYPE_p < 'HEDGE' THEN
340 SELECT
341 SUBSTR(USER_DEAL_TYPE
342 ,1
343 ,30)
344 INTO
345 L_USER_DEAL_TYPE
346 FROM
347 XTR_DEAL_TYPES
348 WHERE DEAL_TYPE = DEAL_TYPE_p;
349 ELSIF DEAL_TYPE_p IS NOT NULL AND DEAL_TYPE_p = 'HEDGE' THEN
350 SELECT
351 SUBSTR(MEANING
352 ,1
353 ,30)
354 INTO
355 L_USER_DEAL_TYPE
356 FROM
357 FND_LOOKUPS
358 WHERE LOOKUP_TYPE = 'XTR_HEDGE_MISC'
359 AND LOOKUP_CODE = 'HEDGE';
360 END IF;
361 RETURN (L_USER_DEAL_TYPE);
362 EXCEPTION
363 WHEN OTHERS THEN
364 RETURN (NULL);
365 END USER_DEAL_TYPEFORMULA;
366
367 FUNCTION FAIR_VALUEFORMULA(COMPANY_P IN VARCHAR2
368 ,REF_NUMBER_P IN VARCHAR2
369 ,PERIOD_END_p IN DATE
370 ,BATCH_ID_P IN NUMBER
371 ,DEAL_TYPE IN VARCHAR2) RETURN NUMBER IS
372 L_FAIR_VALUE NUMBER;
373 CURSOR C_UNREAL IS
374 SELECT
375 A.FAIR_VALUE
376 FROM
377 XTR_REVALUATION_DETAILS_SUM_V A,
378 XTR_BATCHES B
379 WHERE A.COMPANY_CODE = COMPANY_p
380 AND A.COMPANY_CODE = B.COMPANY_CODE
381 AND A.BATCH_ID = B.BATCH_ID
382 AND A.REF_NUMBER = REF_NUMBER_P
383 AND B.PERIOD_END <= PERIOD_END_p
384 AND A.REALIZED_FLAG = P_REALIZED_FLAG_T
385 ORDER BY
386 B.PERIOD_END DESC;
387 CURSOR C_UNREAL_FIRST IS
388 SELECT
389 INITIAL_FAIR_VALUE
390 FROM
391 XTR_DEALS
392 WHERE DEAL_NO = REF_NUMBER_p;
393 CURSOR C_FIRST_TRANS IS
394 SELECT
395 SUM(INITIAL_FAIR_VALUE)
396 FROM
397 XTR_ROLLOVER_TRANSACTIONS
398 WHERE DEAL_NUMBER = REF_NUMBER_p;
399 CURSOR C_IRS IS
400 SELECT
401 SUM(INITIAL_FAIR_VALUE)
402 FROM
403 XTR_DEALS
404 WHERE INT_SWAP_REF = REF_NUMBER_p;
405 CURSOR C_CA_FV IS
406 SELECT
407 FAIR_VALUE
408 FROM
409 XTR_REVALUATION_DETAILS
410 WHERE ACCOUNT_NO = REF_NUMBER_P
411 AND DEAL_TYPE = 'CA'
412 AND BATCH_ID = BATCH_ID_P
413 AND EFFECTIVE_DATE = (
414 SELECT
415 MAX(EFFECTIVE_DATE)
416 FROM
417 XTR_REVALUATION_DETAILS
418 WHERE ACCOUNT_NO = REF_NUMBER_p
419 AND DEAL_TYPE = 'CA'
420 AND BATCH_ID = BATCH_ID_p );
421 CURSOR C_IG_FV IS
422 SELECT
423 FAIR_VALUE
424 FROM
425 XTR_REVALUATION_DETAILS
426 WHERE DEAL_NO = TO_NUMBER(REF_NUMBER_p)
427 AND DEAL_TYPE = 'IG'
428 AND BATCH_ID = BATCH_ID_p
429 AND EFFECTIVE_DATE = (
430 SELECT
431 MAX(EFFECTIVE_DATE)
432 FROM
433 XTR_REVALUATION_DETAILS
434 WHERE DEAL_NO = REF_NUMBER_p
435 AND DEAL_TYPE = 'IG'
436 AND BATCH_ID = BATCH_ID_p );
437 CURSOR C_ONC_FV IS
438 SELECT
439 SUM(FACE_VALUE)
440 FROM
441 XTR_REVALUATION_DETAILS
442 WHERE DEAL_NO = TO_NUMBER(REF_NUMBER_p)
443 AND DEAL_TYPE = 'ONC'
444 AND BATCH_ID = BATCH_ID_p
445 AND NVL(REALIZED_FLAG
446 ,'N') = 'N'
447 AND ( ( COMPLETE_FLAG = 'Y'
448 AND TRANSACTION_NO in (
449 SELECT
450 TRANSACTION_NUMBER
451 FROM
452 XTR_ROLLOVER_TRANSACTIONS
453 WHERE DEAL_NUMBER = TO_NUMBER(REF_NUMBER_p)
454 AND START_DATE <= PERIOD_END_p
455 AND ( CROSS_REF_TO_TRANS is null
456 OR CROSS_REF_TO_TRANS not in (
457 SELECT
458 TRANSACTION_NO
459 FROM
460 XTR_REVALUATION_DETAILS
461 WHERE DEAL_NO = TO_NUMBER(REF_NUMBER_p)
462 AND BATCH_ID = BATCH_ID_p ) ) ) )
463 OR ( COMPLETE_FLAG = 'N'
464 AND TRANSACTION_NO = 1 ) );
465 BEGIN
466 IF DEAL_TYPE = 'CA' THEN
467 OPEN C_CA_FV;
468 FETCH C_CA_FV
469 INTO
470 L_FAIR_VALUE;
471 CLOSE C_CA_FV;
472 ELSIF DEAL_TYPE = 'IG' THEN
473 OPEN C_IG_FV;
474 FETCH C_IG_FV
475 INTO
476 L_FAIR_VALUE;
477 CLOSE C_IG_FV;
478 ELSIF DEAL_TYPE = 'ONC' THEN
479 OPEN C_ONC_FV;
480 FETCH C_ONC_FV
481 INTO
482 L_FAIR_VALUE;
483 CLOSE C_ONC_FV;
484 END IF;
485 IF P_REALIZED_FLAG_T = 'Y' AND DEAL_TYPE not in ('FRA','IG','ONC','CA') THEN
486 IF DEAL_TYPE in ('IRS') THEN
487 OPEN C_IRS;
488 FETCH C_IRS
489 INTO
490 L_FAIR_VALUE;
491 CLOSE C_IRS;
492 ELSE
493 SELECT
494 INITIAL_FAIR_VALUE
495 INTO
496 L_FAIR_VALUE
497 FROM
498 XTR_DEALS
499 WHERE DEAL_NO = REF_NUMBER_p;
500 END IF;
501 IF L_FAIR_VALUE IS NULL AND DEAL_TYPE in ('NI') THEN
502 OPEN C_FIRST_TRANS;
503 FETCH C_FIRST_TRANS
504 INTO
505 L_FAIR_VALUE;
506 CLOSE C_FIRST_TRANS;
507 END IF;
508 ELSIF P_REALIZED_FLAG_T = 'N' AND DEAL_TYPE not in ('IG','ONC','CA') THEN
509 OPEN C_UNREAL;
510 FETCH C_UNREAL
511 INTO
512 L_FAIR_VALUE;
513 CLOSE C_UNREAL;
514 IF L_FAIR_VALUE IS NULL AND DEAL_TYPE in ('NI') THEN
515 OPEN C_FIRST_TRANS;
516 FETCH C_FIRST_TRANS
517 INTO
518 L_FAIR_VALUE;
519 CLOSE C_FIRST_TRANS;
520 END IF;
521 ELSIF P_REALIZED_FLAG_T = 'Y' AND DEAL_TYPE in ('FRA') THEN
522 OPEN C_UNREAL;
523 FETCH C_UNREAL
524 INTO
525 L_FAIR_VALUE;
526 CLOSE C_UNREAL;
527 END IF;
528 RETURN (L_FAIR_VALUE / NVL(P_UNIT
529 ,1000));
530 EXCEPTION
531 WHEN OTHERS THEN
532 RETURN (NULL);
533 END FAIR_VALUEFORMULA;
534
535 FUNCTION BEGIN_RATEFORMULA(COMPANY IN VARCHAR2
536 ,REF_NUMBER_p IN VARCHAR2
537 ,PERIOD_START_p IN DATE
538 ,DEAL_TYPE IN VARCHAR2) RETURN NUMBER IS
539 CURSOR C_BEGIN_RATE IS
540 SELECT
541 ROUND(A.EXCHANGE_RATE_ONE
542 ,4)
543 FROM
544 XTR_REVALUATION_DETAILS_SUM_V A,
545 XTR_BATCHES B
546 WHERE A.COMPANY_CODE = COMPANY
547 AND A.COMPANY_CODE = B.COMPANY_CODE
548 AND A.BATCH_ID = B.BATCH_ID
549 AND A.REF_NUMBER = REF_NUMBER_p
550 AND B.PERIOD_END < PERIOD_START_p
551 AND A.REALIZED_FLAG = P_REALIZED_FLAG_T
552 ORDER BY
553 PERIOD_START desc;
554 CURSOR C_INIT_RATE IS
555 SELECT
556 EXCHANGE_RATE_ONE
557 FROM
558 XTR_DEALS
559 WHERE DEAL_NO = REF_NUMBER_p;
560 CURSOR C_IRS_RATE IS
561 SELECT
562 AVG(EXCHANGE_RATE_ONE)
563 FROM
564 XTR_DEALS
565 WHERE INT_SWAP_REF = REF_NUMBER_p;
566 CURSOR C_NI_INIT_RATE IS
567 SELECT
568 AVG(CURRENCY_EXCHANGE_RATE)
569 FROM
570 XTR_ROLLOVER_TRANSACTIONS
571 WHERE DEAL_NUMBER = REF_NUMBER_p;
572 L_BEGIN_RATE NUMBER;
573 BEGIN
574 IF P_REALIZED_FLAG_T = 'Y' AND DEAL_TYPE not in ('CA','IG','ONC','FX') THEN
575 --IF DEAL_TYPE < 'IRS' THEN
576 IF DEAL_TYPE <> 'IRS' THEN
577 OPEN C_INIT_RATE;
578 FETCH C_INIT_RATE
579 INTO
580 L_BEGIN_RATE;
581 CLOSE C_INIT_RATE;
582 IF L_BEGIN_RATE IS NULL AND DEAL_TYPE = 'NI' THEN
583 OPEN C_NI_INIT_RATE;
584 FETCH C_NI_INIT_RATE
585 INTO
586 L_BEGIN_RATE;
587 CLOSE C_NI_INIT_RATE;
588 END IF;
589 ELSIF DEAL_TYPE = 'IRS' THEN
590 OPEN C_IRS_RATE;
591 FETCH C_IRS_RATE
592 INTO
593 L_BEGIN_RATE;
594 CLOSE C_IRS_RATE;
595 END IF;
596 ELSIF P_REALIZED_FLAG_T = 'N' AND DEAL_TYPE not in ('CA','IG','ONC','FX') THEN
597 OPEN C_BEGIN_RATE;
598 FETCH C_BEGIN_RATE
599 INTO
600 L_BEGIN_RATE;
601 CLOSE C_BEGIN_RATE;
602 IF L_BEGIN_RATE IS NULL THEN
603 IF DEAL_TYPE = 'IRS' THEN
604 OPEN C_IRS_RATE;
605 FETCH C_IRS_RATE
606 INTO
607 L_BEGIN_RATE;
608 CLOSE C_IRS_RATE;
609 --ELSIF DEAL_TYPE < 'IRS' THEN
610 ELSIF DEAL_TYPE <> 'IRS' THEN
611 OPEN C_INIT_RATE;
612 FETCH C_INIT_RATE
613 INTO
614 L_BEGIN_RATE;
615 CLOSE C_INIT_RATE;
616 END IF;
617 IF L_BEGIN_RATE IS NULL AND DEAL_TYPE = 'NI' THEN
618 OPEN C_NI_INIT_RATE;
619 FETCH C_NI_INIT_RATE
620 INTO
621 L_BEGIN_RATE;
622 CLOSE C_NI_INIT_RATE;
623 END IF;
624 END IF;
625 ELSIF DEAL_TYPE in ('CA','IG','ONC','FX') THEN
626 RETURN (NULL);
627 END IF;
628 RETURN (ROUND(L_BEGIN_RATE
629 ,5));
630 EXCEPTION
631 WHEN OTHERS THEN
632 RETURN (NULL);
633 END BEGIN_RATEFORMULA;
634
635 FUNCTION END_RATEFORMULA(COMPANY_p IN VARCHAR2
636 ,REF_NUMBER_p IN VARCHAR2
637 ,PERIOD_END_p IN DATE
638 ,DEAL_TYPE IN VARCHAR2) RETURN NUMBER IS
639
640 CURSOR C_END_RATE IS
641 SELECT
642 A.EXCHANGE_RATE_ONE
643 FROM
644 XTR_REVALUATION_DETAILS_SUM_V A,
645 XTR_BATCHES B
646 WHERE A.COMPANY_CODE = COMPANY_P
647 AND A.COMPANY_CODE = B.COMPANY_CODE
648 AND A.BATCH_ID = B.BATCH_ID
649 AND A.REF_NUMBER = REF_NUMBER_p
650 AND B.PERIOD_END <= PERIOD_END_p
651 AND A.REALIZED_FLAG = P_REALIZED_FLAG_T
652 ORDER BY
653 PERIOD_START DESC;
654 CURSOR C_INIT_RATE IS
655 SELECT
656 EXCHANGE_RATE_ONE
657 FROM
658 XTR_DEALS
659 WHERE DEAL_NO = REF_NUMBER_p;
660 L_END_RATE NUMBER;
661
662 BEGIN
663 IF DEAL_TYPE not in ('CA','IG','ONC','FX') THEN
664 OPEN C_END_RATE;
665 FETCH C_END_RATE
666 INTO
667 L_END_RATE;
668 CLOSE C_END_RATE;
669 ELSE
670 RETURN (NULL);
671 END IF;
672 RETURN (ROUND(L_END_RATE
673 ,5));
674 EXCEPTION
675 WHEN OTHERS THEN
676 RETURN (NULL);
677 END END_RATEFORMULA;
678
679 FUNCTION REPORT_PRDFORMULA(C_DATEFORMAT IN VARCHAR2) RETURN CHAR IS
680 L_TEMP VARCHAR2(100);
681 BEGIN
682 IF P_DATE_FROM_T IS NOT NULL AND P_DATE_TO_T IS NOT NULL THEN
683 L_TEMP := TO_CHAR(TO_DATE(P_DATE_FROM_T
684 ,'YYYY/MM/DD HH24:MI:SS')
685 ,C_DATEFORMAT) || ' - ' || TO_CHAR(TO_DATE(P_DATE_TO_T
686 ,'YYYY/MM/DD HH24:MI:SS')
687 ,C_DATEFORMAT);
688 END IF;
689 RETURN (L_TEMP);
690 EXCEPTION
691 WHEN OTHERS THEN
692 RETURN NULL;
693 END REPORT_PRDFORMULA;
694
695 FUNCTION SOB_CCYFORMULA(COMPANY IN VARCHAR2) RETURN CHAR IS
696 L_SOB_CCY VARCHAR2(10);
697 BEGIN
698 SELECT
699 CURRENCY_CODE
700 INTO
701 L_SOB_CCY
702 FROM
703 GL_SETS_OF_BOOKS B,
704 XTR_PARTY_INFO P
705 WHERE PARTY_CODE = COMPANY
706 AND PARTY_TYPE = 'C'
707 AND P.SET_OF_BOOKS_ID = B.SET_OF_BOOKS_ID;
708 RETURN (L_SOB_CCY);
709 EXCEPTION
710 WHEN OTHERS THEN
711 RETURN (NULL);
712 END SOB_CCYFORMULA;
713
714 FUNCTION FAIR_VALUE_RNDFORMULA(REVAL_CCY IN VARCHAR2
715 ,FAIR_VALUE IN NUMBER) RETURN NUMBER IS
716 L_ROUND NUMBER;
717 CURSOR C_ROUND IS
718 SELECT
719 ROUNDING_FACTOR
720 FROM
721 XTR_MASTER_CURRENCIES_V
722 WHERE CURRENCY = REVAL_CCY;
723 BEGIN
724 OPEN C_ROUND;
725 FETCH C_ROUND
726 INTO
727 L_ROUND;
728 CLOSE C_ROUND;
729 RETURN (ROUND(FAIR_VALUE
730 ,L_ROUND));
731 EXCEPTION
732 WHEN OTHERS THEN
733 RETURN (FAIR_VALUE);
734
735 END FAIR_VALUE_RNDFORMULA;
736
737 FUNCTION GAIN_LOSS_RNDFORMULA(REVAL_CCY IN VARCHAR2
738 ,GAIN_LOSS IN NUMBER) RETURN NUMBER IS
739 L_ROUND NUMBER;
740 CURSOR C_ROUND IS
741 SELECT
742 ROUNDING_FACTOR
743 FROM
744 XTR_MASTER_CURRENCIES_V
745 WHERE CURRENCY = REVAL_CCY;
746 BEGIN
747 OPEN C_ROUND;
748 FETCH C_ROUND
749 INTO
750 L_ROUND;
751 CLOSE C_ROUND;
752 RETURN (ROUND(GAIN_LOSS
753 ,L_ROUND));
754 EXCEPTION
755 WHEN OTHERS THEN
756 RETURN (GAIN_LOSS);
757 END GAIN_LOSS_RNDFORMULA;
758
759 FUNCTION BASE_AMT_RNDFORMULA(REVAL_CCY IN VARCHAR2
760 ,BASE_AMOUNT IN NUMBER) RETURN NUMBER IS
761 L_ROUND NUMBER;
762 CURSOR C_ROUND IS
763 SELECT
764 ROUNDING_FACTOR
765 FROM
766 XTR_MASTER_CURRENCIES_V
767 WHERE CURRENCY = REVAL_CCY;
768 BEGIN
769 OPEN C_ROUND;
770 FETCH C_ROUND
771 INTO
772 L_ROUND;
773 CLOSE C_ROUND;
774 RETURN (ROUND(BASE_AMOUNT
775 ,L_ROUND));
776 EXCEPTION
777 WHEN OTHERS THEN
778 RETURN (BASE_AMOUNT);
779 END BASE_AMT_RNDFORMULA;
780
781 END XTR_XTRCCYGL_XMLP_PKG;
782
[Home] [Help]