[Home] [Help]
PACKAGE BODY: APPS.XTR_XTROHINS_XMLP_PKG
Source
1 PACKAGE BODY XTR_XTROHINS_XMLP_PKG AS
2 /* $Header: XTROHINSB.pls 120.1 2007/12/28 12:58:16 npannamp noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 CURSOR COMPANY(P_COMPANY IN VARCHAR2) IS
5 SELECT
6 SHORT_NAME
7 FROM
8 XTR_PARTIES_V
9 WHERE PARTY_CODE = P_COMPANY;
10 CURSOR CUR_MEAN(P_TYPE IN VARCHAR2,P_CODE IN VARCHAR2) IS
11 SELECT
12 MEANING
13 FROM
14 FND_LOOKUPS
15 WHERE LOOKUP_TYPE = P_TYPE
16 AND LOOKUP_CODE = P_CODE;
17 L_LOOKUP_TYPE VARCHAR2(30);
18 BEGIN
19 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
20 P_AS_OF_DATE_1 := P_AS_OF_DATE;
21 P_DATE := FND_DATE.CANONICAL_TO_DATE(P_AS_OF_DATE_1);
22 P_AS_OF_DATE_1 := P_DATE;
23 IF P_FACTOR = '0' THEN
24 P_UNIT := 1;
25 ELSE
26 P_UNIT := TO_NUMBER(P_FACTOR);
27 END IF;
28 IF REPORT_NAME IS NULL THEN
29 SELECT
30 CP.USER_CONCURRENT_PROGRAM_NAME
31 INTO REPORT_NAME
32 FROM
33 FND_CONCURRENT_PROGRAMS_VL CP,
34 FND_CONCURRENT_REQUESTS CR
35 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
36 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
37 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
38 REPORT_NAME := substr(REPORT_NAME,1,instr(REPORT_NAME,' (XML)'));
39 END IF;
40 IF P_COMPANY IS NOT NULL THEN
41 OPEN COMPANY(P_COMPANY);
42 FETCH COMPANY
43 INTO RP_COMPANY_NAME;
44 CLOSE COMPANY;
45 END IF;
46 IF P_HEDGE_TYPE IS NOT NULL THEN
47 OPEN CUR_MEAN('XTR_HEDGE_TYPES',P_HEDGE_TYPE);
48 FETCH CUR_MEAN
49 INTO RP_HEDGE_TYPE;
50 CLOSE CUR_MEAN;
51 END IF;
52 IF P_OBJECTIVE IS NOT NULL THEN
53 OPEN CUR_MEAN('XTR_HEDGE_OBJECTIVE_TYPES',P_OBJECTIVE);
54 FETCH CUR_MEAN
55 INTO RP_HEDGE_OBJ;
56 CLOSE CUR_MEAN;
57 END IF;
58 IF P_FACTOR IS NOT NULL THEN
59 OPEN CUR_MEAN('XTR_NUM_FACTOR',P_FACTOR);
60 FETCH CUR_MEAN
61 INTO RP_FACTOR;
62 CLOSE CUR_MEAN;
63 END IF;
64 RETURN (TRUE);
65 EXCEPTION
66 WHEN OTHERS THEN
67 /*SRW.MESSAGE(100
68 ,SQLERRM)*/NULL;
69 END AFTERPFORM;
70 FUNCTION GET_EQU_AMT(X_COMPANY_CODE IN VARCHAR2
71 ,X_BASE_CCY IN VARCHAR2
72 ,X_BASE_AMT IN NUMBER) RETURN NUMBER IS
73 CURSOR COMPANY_INFO IS
74 SELECT
75 CP.PARAMETER_VALUE_CODE,
76 DCT.USER_CONVERSION_TYPE,
77 CURRENCY_CODE
78 FROM
79 XTR_PARTIES_V PTY,
80 XTR_COMPANY_PARAMETERS CP,
81 GL_SETS_OF_BOOKS SOB,
82 GL_DAILY_CONVERSION_TYPES DCT
83 WHERE PTY.PARTY_CODE = X_COMPANY_CODE
84 AND CP.COMPANY_CODE = PTY.PARTY_CODE
85 AND CP.PARAMETER_CODE = 'ACCNT_EXRTP'
86 AND PTY.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
87 AND CP.PARAMETER_VALUE_CODE = dct.conversion_type (+);
88 CURSOR C_DATE(P_SOB_CCY IN VARCHAR2,L_CON_TYPE IN VARCHAR2) IS
89 SELECT
90 MAX(CONVERSION_DATE)
91 FROM
92 GL_DAILY_RATES
93 WHERE FROM_CURRENCY = X_BASE_CCY
94 AND TO_CURRENCY = P_SOB_CCY
95 AND CONVERSION_TYPE = L_CON_TYPE
96 AND CONVERSION_DATE <= P_AS_OF_DATE_1;
97 SOB_CURRENCY VARCHAR2(15);
98 L_PTY_CONVERT_TYPE VARCHAR2(30);
99 L_PTY_USER_CONVERT_TYPE VARCHAR2(30);
100 L_EQU_AMOUNT NUMBER;
101 L_DATE DATE;
102 L_CURRENCY VARCHAR2(15);
103 BEGIN
104 OPEN COMPANY_INFO;
105 FETCH COMPANY_INFO
106 INTO L_PTY_CONVERT_TYPE,L_PTY_USER_CONVERT_TYPE,SOB_CURRENCY;
107 CLOSE COMPANY_INFO;
108 L_CURRENCY := NVL(P_REPORT_CCY
109 ,SOB_CURRENCY);
110 OPEN C_DATE(L_CURRENCY,L_PTY_CONVERT_TYPE);
111 FETCH C_DATE
112 INTO L_DATE;
113 CLOSE C_DATE;
114 L_EQU_AMOUNT := GL_CURRENCY_API.CONVERT_AMOUNT(X_BASE_CCY
115 ,L_CURRENCY
116 ,L_DATE
117 ,L_PTY_CONVERT_TYPE
118 ,X_BASE_AMT);
119 RETURN (L_EQU_AMOUNT);
120 EXCEPTION
121 WHEN OTHERS THEN
122 IF CP_NO_GL_RATE IS NULL THEN
123 FND_MESSAGE.SET_NAME('XTR'
124 ,'XTR_HEDGE_NO_GL_RATE_SRW');
125 IF CP_NO_GL_RATE IS NULL THEN
126 CP_NO_GL_RATE := FND_MESSAGE.GET;
127 END IF;
128 END IF;
129 /*SRW.MESSAGE(200
130 ,'Error Calculating the Report Currency Equivalent Amount')*/NULL;
131 RETURN (NULL);
132 END GET_EQU_AMT;
133 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
134 CURSOR GET_LANGUAGE_DESC IS
135 SELECT
136 ITEM_NAME,
137 TEXT LANG_NAME
138 FROM
139 XTR_SYS_LANGUAGES_VL
140 WHERE MODULE_NAME = 'XTROHINS';
141 BEGIN
142 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
143 FOR c IN GET_LANGUAGE_DESC LOOP
144 BEGIN
145 IF C.ITEM_NAME = 'Z1AS_OF_DATE' THEN
146 Z1AS_OF_DATE := C.LANG_NAME;
147 ELSIF C.ITEM_NAME = 'Z1CCY' THEN
148 Z1CCY := C.LANG_NAME;
149 ELSIF C.ITEM_NAME = 'Z1CURRENT' THEN
150 Z1CURRENT := C.LANG_NAME;
151 ELSIF C.ITEM_NAME = 'Z1COMPANY' THEN
152 Z1COMPANY := C.LANG_NAME;
153 ELSIF C.ITEM_NAME = 'Z1CONTRA' THEN
154 Z1CONTRA := C.LANG_NAME;
155 ELSIF C.ITEM_NAME = 'Z1DEAL' THEN
156 Z1DEAL := C.LANG_NAME;
157 ELSIF C.ITEM_NAME = 'Z1DEAL_AMOUNT' THEN
158 Z1DEAL_AMOUNT := C.LANG_NAME;
159 ELSIF C.ITEM_NAME = 'Z1END_OF_REPORT' THEN
160 Z1END_OF_REPORT := C.LANG_NAME;
161 ELSIF C.ITEM_NAME = 'Z1EQUI' THEN
162 Z1EQUI := C.LANG_NAME;
163 ELSIF C.ITEM_NAME = 'Z1FACTOR' THEN
164 Z1FACTOR := C.LANG_NAME;
165 ELSIF C.ITEM_NAME = 'Z1GT_SYSDATE' THEN
166 IF P_AS_OF_DATE_1 > SYSDATE THEN
167 Z1GT_SYSDATE := C.LANG_NAME;
168 ELSE
169 Z1GT_SYSDATE := NULL;
170 END IF;
171 ELSIF C.ITEM_NAME = 'Z1HEDGE' THEN
172 Z1HEDGE := C.LANG_NAME;
173 ELSIF C.ITEM_NAME = 'Z1HEDGE_AMOUNT' THEN
174 Z1HEDGE_AMOUNT := C.LANG_NAME;
175 ELSIF C.ITEM_NAME = 'Z1HEDGE_OBJ' THEN
176 Z1HEDGE_OBJ := C.LANG_NAME;
177 ELSIF C.ITEM_NAME = 'Z1HEDGE_STRATEGY' THEN
178 Z1HEDGE_STRATEGY := C.LANG_NAME;
179 ELSIF C.ITEM_NAME = 'Z1HEDGE_TYPE' THEN
180 Z1HEDGE_TYPE := C.LANG_NAME;
181 ELSIF C.ITEM_NAME = 'Z1HTYPE_TOTAL' THEN
182 Z1HTYPE_TOTAL := C.LANG_NAME;
183 ELSIF C.ITEM_NAME = 'Z1NO_DATA_FOUND' THEN
184 Z1NO_DATA_FOUND := C.LANG_NAME;
185 ELSIF C.ITEM_NAME = 'Z1NO_HISNT_FOUND' THEN
186 Z1NO_HINST_FOUND := C.LANG_NAME;
187 ELSIF C.ITEM_NAME = 'Z1NUMBER' THEN
188 Z1NUMBER := C.LANG_NAME;
189 ELSIF C.ITEM_NAME = 'Z1OBJECTIVE' THEN
190 Z1OBJECTIVE := C.LANG_NAME;
191 ELSIF C.ITEM_NAME = 'Z1PAGE' THEN
192 Z1PAGE := C.LANG_NAME;
193 ELSIF C.ITEM_NAME = 'Z1PARAMETERS' THEN
194 Z1PARAMETERS := C.LANG_NAME;
195 ELSIF C.ITEM_NAME = 'Z1REPORT_CCY' THEN
196 Z1REPORT_CCY := C.LANG_NAME;
197 ELSIF C.ITEM_NAME = 'Z1REPORT_DATE' THEN
198 Z1REPORT_DATE := C.LANG_NAME;
199 ELSIF C.ITEM_NAME = 'Z1STRATEGY' THEN
200 Z1STRATEGY := C.LANG_NAME;
201 ELSIF C.ITEM_NAME = 'Z1SUBTYPE' THEN
202 Z1SUBTYPE := C.LANG_NAME;
203 ELSIF C.ITEM_NAME = 'Z1TYPE' THEN
204 Z1TYPE := C.LANG_NAME;
205 ELSIF C.ITEM_NAME = 'Z1UNASSIGNED' THEN
206 Z1UNASSIGNED := C.LANG_NAME;
207 END IF;
208 EXCEPTION
209 WHEN OTHERS THEN
210 NULL;
211 END;
212 END LOOP;
213 RETURN (TRUE);
214 END BEFOREREPORT;
215 FUNCTION AFTERREPORT RETURN BOOLEAN IS
216 BEGIN
217 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
218 RETURN (TRUE);
219 END AFTERREPORT;
220 FUNCTION GET_DSP_VALUE(TYPE IN VARCHAR2
221 ,CODE IN VARCHAR2) RETURN VARCHAR2 IS
222 CURSOR CUR_MEAN(P_TYPE IN VARCHAR2,P_CODE IN VARCHAR2) IS
223 SELECT
224 MEANING
225 FROM
226 FND_LOOKUPS
227 WHERE LOOKUP_TYPE = P_TYPE
228 AND LOOKUP_CODE = P_CODE;
229 L_MEANING VARCHAR2(80);
230 BEGIN
231 IF CODE IS NOT NULL THEN
232 OPEN CUR_MEAN(TYPE,CODE);
233 FETCH CUR_MEAN
234 INTO L_MEANING;
235 CLOSE CUR_MEAN;
236 END IF;
237 RETURN (L_MEANING);
238 EXCEPTION
239 WHEN OTHERS THEN
240 RETURN (NULL);
241 END GET_DSP_VALUE;
242 FUNCTION RCCY_AMTFORMULA(HEDGE_TYPE IN VARCHAR2
243 ,HEDGE_CURRENCY IN VARCHAR2
244 ,CURRENCY_DSP IN VARCHAR2
245 ,COMPANY_CODE IN VARCHAR2
246 ,HDG_CONTRA_AMT IN NUMBER) RETURN NUMBER IS
247 L_CCY VARCHAR2(15);
248 BEGIN
249 IF HEDGE_TYPE <> 'UNASSIGNED' THEN
250 L_CCY := HEDGE_CURRENCY;
251 ELSE
252 L_CCY := CURRENCY_DSP;
253 END IF;
254 RETURN (ABS(GET_EQU_AMT(COMPANY_CODE
255 ,L_CCY
256 ,HDG_CONTRA_AMT)));
257 END RCCY_AMTFORMULA;
258 FUNCTION DEAL_AMTFORMULA(DEAL_NO_1 IN NUMBER
259 ,CURRENCY_DSP IN VARCHAR2
260 ,HEDGE_TYPE IN VARCHAR2
261 ,DEAL_TYPE IN VARCHAR2
262 ,HEDGE_CURRENCY IN VARCHAR2) RETURN NUMBER IS
263 L_CURR VARCHAR2(15);
264 L_BUY_CURR VARCHAR2(15);
265 L_SELL_CURR VARCHAR2(15);
266 L_BUY_AMOUNT NUMBER;
267 L_SELL_AMOUNT NUMBER;
268 L_FACE_VALUE NUMBER;
269 L_DEAL_AMT NUMBER;
270 L_ALLOC_AMT NUMBER;
271 L_UNASGD_AMT NUMBER;
272 L_AMOUNT NUMBER;
273 ROUNDFAC NUMBER(3,2);
274 CURSOR RND(P_CURR IN VARCHAR2) IS
275 SELECT
276 NVL(M.ROUNDING_FACTOR
277 ,2)
278 FROM
279 XTR_MASTER_CURRENCIES_V M
280 WHERE M.CURRENCY = P_CURR;
281 CURSOR CCY IS
282 SELECT
283 CURRENCY,
284 CURRENCY_BUY,
285 CURRENCY_SELL,
286 BUY_AMOUNT / NVL(P_UNIT
287 ,1) BUY_AMOUNT,
288 SELL_AMOUNT / NVL(P_UNIT
289 ,1) SELL_AMOUNT,
290 FACE_VALUE_AMOUNT / NVL(P_UNIT
291 ,1) FACE_VALUE_AMOUNT
292 FROM
293 XTR_DEALS
294 WHERE DEAL_NO = DEAL_NO_1;
295 BEGIN
296 OPEN CCY;
297 FETCH CCY
298 INTO L_CURR,L_BUY_CURR,L_SELL_CURR,L_BUY_AMOUNT,L_SELL_AMOUNT,L_FACE_VALUE;
299 CLOSE CCY;
300 OPEN RND(CURRENCY_DSP);
301 FETCH RND
302 INTO ROUNDFAC;
303 CLOSE RND;
304 IF HEDGE_TYPE <> 'UNASSIGNED' THEN
305 IF DEAL_TYPE in ('FX','FXO') THEN
306 IF HEDGE_CURRENCY = L_BUY_CURR THEN
307 L_AMOUNT := L_BUY_AMOUNT;
308 ELSE
309 L_AMOUNT := L_SELL_AMOUNT;
310 END IF;
311 ELSE
312 L_AMOUNT := L_FACE_VALUE;
313 END IF;
314 ELSE
315 IF DEAL_TYPE in ('FX','FXO') THEN
316 IF CURRENCY_DSP = L_BUY_CURR THEN
317 L_AMOUNT := L_BUY_AMOUNT;
318 ELSE
319 L_AMOUNT := L_SELL_AMOUNT;
320 END IF;
321 ELSE
322 L_AMOUNT := L_FACE_VALUE;
323 END IF;
324 END IF;
325 RETURN (ROUND(L_AMOUNT
326 ,ROUNDFAC));
327 EXCEPTION
328 WHEN OTHERS THEN
329 RETURN (NULL);
330 END DEAL_AMTFORMULA;
331 FUNCTION CURRFORMULA(DEAL_NO_1 IN NUMBER
332 ,DEAL_TYPE IN VARCHAR2
333 ,HEDGE_TYPE IN VARCHAR2
334 ,HEDGE_CURRENCY IN VARCHAR2) RETURN CHAR IS
335 L_CURR VARCHAR2(15);
336 L_BUY_CURR VARCHAR2(15);
337 L_SELL_CURR VARCHAR2(15);
338 CURSOR CCY IS
339 SELECT
340 CURRENCY,
341 CURRENCY_BUY,
342 CURRENCY_SELL
343 FROM
344 XTR_DEALS
345 WHERE DEAL_NO = DEAL_NO_1;
346 CURSOR CUR_IRS IS
347 SELECT
348 TEXT
349 FROM
350 XTR_SYS_LANGUAGES_VL
351 WHERE MODULE_NAME = 'XTROHINS'
352 AND ITEM_NAME = 'Z1IRS_NOTE';
353 BEGIN
354 IF DEAL_TYPE = 'IRS' THEN
355 IF CP_IRS_NOTE IS NULL THEN
356 OPEN CUR_IRS;
357 FETCH CUR_IRS
358 INTO CP_IRS_NOTE;
359 CLOSE CUR_IRS;
360 END IF;
361 END IF;
362 IF HEDGE_TYPE <> 'UNASSIGNED' THEN
363 RETURN (HEDGE_CURRENCY);
364 ELSE
365 OPEN CCY;
366 FETCH CCY
367 INTO L_CURR,L_BUY_CURR,L_SELL_CURR;
368 CLOSE CCY;
369 IF DEAL_TYPE in ('FX','FXO') THEN
370 RETURN (L_BUY_CURR);
371 ELSE
372 RETURN (L_CURR);
373 END IF;
374 END IF;
375 EXCEPTION
376 WHEN OTHERS THEN
377 RETURN (NULL);
378 END CURRFORMULA;
379 FUNCTION CONTRA_CCY_DSPFORMULA(DEAL_NO_1 IN NUMBER
380 ,HEDGE_TYPE IN VARCHAR2
381 ,HEDGE_CURRENCY IN VARCHAR2
385 L_SELL_CURR VARCHAR2(15);
382 ,DEAL_TYPE IN VARCHAR2) RETURN CHAR IS
383 L_CURR VARCHAR2(15);
384 L_BUY_CURR VARCHAR2(15);
386 CURSOR CCY IS
387 SELECT
388 CURRENCY,
389 CURRENCY_BUY,
390 CURRENCY_SELL
391 FROM
392 XTR_DEALS
393 WHERE DEAL_NO = DEAL_NO_1;
394 CURSOR IRS_CCY IS
395 SELECT
396 CURRENCY
397 FROM
398 XTR_DEALS
399 WHERE INT_SWAP_REF = (
400 SELECT
401 INT_SWAP_REF
402 FROM
403 XTR_DEALS
404 WHERE DEAL_NO = DEAL_NO_1 )
405 AND DEAL_SUBTYPE = 'INVEST'
406 AND DEAL_TYPE = 'IRS';
407 BEGIN
408 IF HEDGE_TYPE <> 'UNASSIGNED' THEN
409 RETURN (HEDGE_CURRENCY);
410 ELSE
411 OPEN CCY;
412 FETCH CCY
413 INTO L_CURR,L_BUY_CURR,L_SELL_CURR;
414 CLOSE CCY;
415 IF DEAL_TYPE in ('FX','FXO') THEN
416 RETURN (L_SELL_CURR);
417 ELSE
418 RETURN (NULL);
419 END IF;
420 END IF;
421 EXCEPTION
422 WHEN OTHERS THEN
423 RETURN (NULL);
424 END CONTRA_CCY_DSPFORMULA;
425 FUNCTION OBJECTIVE_DSPFORMULA(OBJECTIVE_CODE IN VARCHAR2) RETURN CHAR IS
426 BEGIN
427 RETURN (GET_DSP_VALUE('XTR_HEDGE_OBJECTIVE_TYPES'
428 ,OBJECTIVE_CODE));
429 END OBJECTIVE_DSPFORMULA;
430 FUNCTION HDG_CONTRA_AMTFORMULA(DEAL_NO_1 IN NUMBER
431 ,CURRENCY_DSP IN VARCHAR2
432 ,HEDGE_TYPE IN VARCHAR2
433 ,CHEDGE_AMOUNT IN NUMBER
434 ,DEAL_TYPE IN VARCHAR2) RETURN NUMBER IS
435 L_CURR VARCHAR2(15);
436 L_BUY_CURR VARCHAR2(15);
437 L_SELL_CURR VARCHAR2(15);
438 L_BUY_AMOUNT NUMBER;
439 L_SELL_AMOUNT NUMBER;
440 L_FACE_VALUE NUMBER;
441 L_DEAL_AMT NUMBER;
442 L_ALLOC_AMT NUMBER;
443 L_UNASGD_AMT NUMBER;
444 L_AMOUNT NUMBER;
445 ROUNDFAC NUMBER(3,2);
446 CURSOR RND(P_CURR IN VARCHAR2) IS
447 SELECT
448 NVL(M.ROUNDING_FACTOR
449 ,0)
450 FROM
451 XTR_MASTER_CURRENCIES_V M
452 WHERE M.CURRENCY = P_CURR;
453 CURSOR CCY IS
454 SELECT
455 CURRENCY,
456 CURRENCY_BUY,
457 CURRENCY_SELL,
458 BUY_AMOUNT / NVL(P_UNIT
459 ,1) BUY_AMOUNT,
460 SELL_AMOUNT / NVL(P_UNIT
461 ,1) SELL_AMOUNT,
462 FACE_VALUE_AMOUNT / NVL(P_UNIT
463 ,1) FACE_VALUE_AMOUNT
464 FROM
465 XTR_DEALS
466 WHERE DEAL_NO = DEAL_NO_1;
467 CURSOR ALLOC IS
468 SELECT
469 SUM(CUR_PCT_ALLOCATION)
470 FROM
471 XTR_HEDGE_RELATIONSHIPS HR,
472 XTR_HEDGE_ATTRIBUTES HA
473 WHERE HR.HEDGE_ATTRIBUTE_ID = HA.HEDGE_ATTRIBUTE_ID
474 AND PRIMARY_CODE = DEAL_NO_1
475 AND INSTRUMENT_ITEM_FLAG = 'U'
476 AND START_DATE <= P_AS_OF_DATE_1
477 AND ( HEDGE_STATUS IN ( 'CURRENT' , 'DESIGNATE' , 'FULFILLED' )
478 OR ( HEDGE_STATUS in ( 'FAILED' , 'DEDESIGNATED' )
479 AND P_AS_OF_DATE_1 <= HA.DISCONTINUE_DATE ) );
480 BEGIN
481 OPEN CCY;
482 FETCH CCY
483 INTO L_CURR,L_BUY_CURR,L_SELL_CURR,L_BUY_AMOUNT,L_SELL_AMOUNT,L_FACE_VALUE;
484 CLOSE CCY;
485 OPEN RND(CURRENCY_DSP);
486 FETCH RND
487 INTO ROUNDFAC;
488 CLOSE RND;
489 IF HEDGE_TYPE <> 'UNASSIGNED' THEN
490 L_AMOUNT := (ABS(CHEDGE_AMOUNT));
491 ELSE
492 OPEN ALLOC;
493 FETCH ALLOC
494 INTO L_ALLOC_AMT;
495 CLOSE ALLOC;
496 IF DEAL_TYPE in ('FX','FXO') THEN
497 L_AMOUNT := (ABS(L_BUY_AMOUNT) * (100 - NVL(L_ALLOC_AMT
498 ,0)) / 100);
499 ELSE
500 L_AMOUNT := (ABS(L_FACE_VALUE) * (100 - NVL(L_ALLOC_AMT
501 ,0)) / 100);
502 END IF;
503 END IF;
504 RETURN ROUND(L_AMOUNT
505 ,ROUNDFAC);
506 EXCEPTION
507 WHEN OTHERS THEN
508 RETURN (NULL);
509 END HDG_CONTRA_AMTFORMULA;
510 FUNCTION RPT_CCYFORMULA(COMPANY_CODE IN VARCHAR2) RETURN CHAR IS
511 CURSOR COMPANY_INFO IS
512 SELECT
513 CP.PARAMETER_VALUE_CODE,
514 DCT.USER_CONVERSION_TYPE,
515 CURRENCY_CODE
516 FROM
517 XTR_PARTIES_V PTY,
518 XTR_COMPANY_PARAMETERS CP,
519 GL_SETS_OF_BOOKS SOB,
520 GL_DAILY_CONVERSION_TYPES DCT
521 WHERE PTY.PARTY_CODE = COMPANY_CODE
522 AND CP.COMPANY_CODE = PTY.PARTY_CODE
523 AND CP.PARAMETER_CODE = 'ACCNT_EXRTP'
524 AND PTY.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
525 AND CP.PARAMETER_VALUE_CODE = dct.conversion_type (+);
526 SOB_CURRENCY VARCHAR2(15);
527 L_PTY_CONVERT_TYPE VARCHAR2(30);
528 L_PTY_USER_CONVERT_TYPE VARCHAR2(30);
529 L_SOB_AMOUNT NUMBER;
530 L_DATE DATE;
531 L_CURRENCY VARCHAR2(15);
532 BEGIN
533 IF P_REPORT_CCY IS NOT NULL THEN
534 RETURN (P_REPORT_CCY);
535 ELSE
536 OPEN COMPANY_INFO;
537 FETCH COMPANY_INFO
538 INTO L_PTY_CONVERT_TYPE,L_PTY_USER_CONVERT_TYPE,SOB_CURRENCY;
539 CLOSE COMPANY_INFO;
540 RETURN (SOB_CURRENCY);
541 END IF;
542 EXCEPTION
543 WHEN OTHERS THEN
544 RETURN (NULL);
545 END RPT_CCYFORMULA;
546 FUNCTION HEDGE_TYPE_DSPFORMULA(HEDGE_TYPE IN VARCHAR2) RETURN CHAR IS
547 BEGIN
548 IF HEDGE_TYPE = 'UNASSIGNED' THEN
549 RETURN (GET_DSP_VALUE('XTR_HEDGE_MISC'
550 ,HEDGE_TYPE));
551 ELSE
552 RETURN (GET_DSP_VALUE('XTR_HEDGE_TYPES'
553 ,HEDGE_TYPE));
554 END IF;
555 END HEDGE_TYPE_DSPFORMULA;
556 FUNCTION COMPANY_NAMEFORMULA(COMPANY_CODE IN VARCHAR2) RETURN CHAR IS
557 CURSOR COMPANY(P_COMPANY IN VARCHAR2) IS
558 SELECT
559 SHORT_NAME
560 FROM
561 XTR_PARTIES_V
562 WHERE PARTY_CODE = P_COMPANY;
563 L_COMPANY_NAME VARCHAR2(20);
564 BEGIN
565 IF COMPANY_CODE IS NOT NULL THEN
566 OPEN COMPANY(COMPANY_CODE);
567 FETCH COMPANY
568 INTO L_COMPANY_NAME;
569 CLOSE COMPANY;
570 END IF;
571 RETURN (L_COMPANY_NAME);
572 EXCEPTION
573 WHEN OTHERS THEN
574 RETURN (NULL);
575 END COMPANY_NAMEFORMULA;
576 FUNCTION CHEDGE_AMOUNTFORMULA(HEDGE_NO IN NUMBER) RETURN NUMBER IS
577 L_APPROACH VARCHAR2(30);
578 L_ROUND NUMBER;
579 L_GAIN_LOSS_CCY VARCHAR2(15);
580 L_AMOUNT_TYPE VARCHAR2(30);
581 L_HEDGE_AMT NUMBER;
582 L_REF_AMOUNT NUMBER;
583 L_ORIG_HEDGE_AMT NUMBER;
584 L_CUM_REC_HDG_AMT NUMBER;
585 L_REM_HEDGE_AMT NUMBER;
586 L_ORIG_REF_AMT NUMBER;
587 L_CUR_REF_AMT NUMBER;
588 CURSOR RECLASS IS
589 SELECT
590 SUM(RECLASS_HEDGE_AMT)
591 FROM
592 XTR_RECLASS_DETAILS
593 WHERE HEDGE_ATTRIBUTE_ID = HEDGE_NO
594 AND RECLASS_DATE <= P_DATE
595 AND RECLASS_GAIN_LOSS_AMT is not null;
596 CURSOR HDG IS
597 SELECT
598 S.HEDGE_APPROACH,
599 H.HEDGE_AMOUNT
600 FROM
601 XTR_HEDGE_STRATEGIES S,
602 XTR_HEDGE_ATTRIBUTES H
603 WHERE S.STRATEGY_CODE = H.STRATEGY_CODE
604 AND H.HEDGE_ATTRIBUTE_ID = HEDGE_NO;
605 CURSOR REF_AMT(P_FLAG IN VARCHAR2) IS
606 SELECT
607 ABS(SUM(R.REFERENCE_AMOUNT)) REF_AMT
608 FROM
609 XTR_HEDGE_RELATIONSHIPS R
610 WHERE R.HEDGE_ATTRIBUTE_ID = HEDGE_NO
611 AND INSTRUMENT_ITEM_FLAG = P_FLAG;
612 BEGIN
613 OPEN HDG;
614 FETCH HDG
615 INTO L_APPROACH,L_HEDGE_AMT;
616 CLOSE HDG;
617 IF L_APPROACH = 'FORECAST' THEN
618 L_ORIG_HEDGE_AMT := NVL(L_HEDGE_AMT
619 ,0);
620 ELSE
621 OPEN REF_AMT('I');
622 FETCH REF_AMT
623 INTO L_ORIG_HEDGE_AMT;
624 CLOSE REF_AMT;
625 END IF;
626 OPEN REF_AMT('U');
627 FETCH REF_AMT
628 INTO L_ORIG_REF_AMT;
629 CLOSE REF_AMT;
630 OPEN RECLASS;
631 FETCH RECLASS
632 INTO L_CUM_REC_HDG_AMT;
633 CLOSE RECLASS;
634 L_REM_HEDGE_AMT := NVL(L_ORIG_HEDGE_AMT
635 ,0) - NVL(L_CUM_REC_HDG_AMT
636 ,0);
637 L_CUR_REF_AMT := L_ORIG_REF_AMT * L_REM_HEDGE_AMT / L_ORIG_HEDGE_AMT;
638 RETURN (L_CUR_REF_AMT / NVL(P_UNIT
639 ,1));
640 EXCEPTION
641 WHEN OTHERS THEN
642 RETURN NULL;
643 END CHEDGE_AMOUNTFORMULA;
644 FUNCTION CP_NO_GL_RATE_P RETURN VARCHAR2 IS
645 BEGIN
646 RETURN CP_NO_GL_RATE;
647 END CP_NO_GL_RATE_P;
648 FUNCTION CP_IRS_NOTE_P RETURN VARCHAR2 IS
649 BEGIN
650 RETURN CP_IRS_NOTE;
651 END CP_IRS_NOTE_P;
652 END XTR_XTROHINS_XMLP_PKG;
653