1 PACKAGE BODY XTR_XTRDDHTY_XMLP_PKG AS
2 /* $Header: XTRDDHTYB.pls 120.1 2007/12/28 12:46:58 npannamp noship $ */
3 FUNCTION HITEM_AMTFORMULA(HEDGE_NO IN NUMBER
4 ,HEDGE_APPROACH IN VARCHAR2
5 ,HEDGE_AMOUNT IN NUMBER) RETURN NUMBER IS
6 SOURCE_CURSOR INTEGER;
7 DESTINATION_CURSOR INTEGER;
8 IGNORE INTEGER;
9 NATIVE CONSTANT INTEGER DEFAULT 1;
10 V_AMOUNT NUMBER;
11 V_QUERY VARCHAR2(1000);
12 L_TOTAL NUMBER;
13 CURSOR XTR_AMT IS
14 SELECT
15 SUM(REFERENCE_AMOUNT)
16 FROM
17 XTR_HEDGE_RELATIONSHIPS HR
18 WHERE HR.INSTRUMENT_ITEM_FLAG = 'I'
19 AND HR.HEDGE_ATTRIBUTE_ID = HEDGE_NO;
20 CURSOR RECLASS IS
21 SELECT
22 SUM(RECLASS_HEDGE_AMT)
23 FROM
24 XTR_RECLASS_DETAILS
25 WHERE HEDGE_ATTRIBUTE_ID = HEDGE_NO
26 AND RECLASS_DATE <= P_DATE
27 AND RECLASS_GAIN_LOSS_AMT is not null;
28 L_RECLASS_AMT NUMBER;
29 L_ORIG_HDG_AMT NUMBER;
30 BEGIN
31 OPEN RECLASS;
32 FETCH RECLASS
33 INTO L_RECLASS_AMT;
34 CLOSE RECLASS;
35 IF HEDGE_APPROACH = 'FORECAST' THEN
36 L_ORIG_HDG_AMT := NVL(HEDGE_AMOUNT
37 ,0);
38 ELSIF NVL(HEDGE_APPROACH , ' @#@ ') <> 'FORECAST' THEN
39 OPEN XTR_AMT;
40 FETCH XTR_AMT
41 INTO L_ORIG_HDG_AMT;
42 CLOSE XTR_AMT;
43 END IF;
44 IF NVL(L_ORIG_HDG_AMT
45 ,0) <> 0 THEN
46 L_TOTAL := (ABS(L_ORIG_HDG_AMT) - NVL(L_RECLASS_AMT
47 ,0)) * SIGN(L_ORIG_HDG_AMT);
48 ELSE
49 L_TOTAL := (-NVL(L_RECLASS_AMT
50 ,0));
51 END IF;
52 RETURN (L_TOTAL / NVL(P_UNIT
53 ,1));
54 EXCEPTION
55 WHEN OTHERS THEN
56 /*SRW.MESSAGE(100
57 ,SQLERRM)*/NULL;
58 RETURN (NULL);
59 END HITEM_AMTFORMULA;
60
61 FUNCTION HINST_AMTFORMULA(CHINST_AMT IN NUMBER) RETURN NUMBER IS
62 CURSOR ITEM(P_HEDGE_NO IN NUMBER) IS
63 SELECT
64 SUM(REFERENCE_AMOUNT)
65 FROM
66 XTR_HEDGE_RELATIONSHIPS
67 WHERE HEDGE_ATTRIBUTE_ID = P_HEDGE_NO
68 AND INSTRUMENT_ITEM_FLAG = 'U';
69 L_AMOUNT NUMBER;
70 BEGIN
71 L_AMOUNT := CHINST_AMT;
72 RETURN (L_AMOUNT / NVL(P_UNIT
73 ,1));
74 EXCEPTION
75 WHEN OTHERS THEN
76 /*SRW.MESSAGE(100
77 ,SQLERRM)*/NULL;
78 RETURN (NULL);
79 END HINST_AMTFORMULA;
80
81 FUNCTION HITEM_RCCYFORMULA(COMPANY_CODE IN VARCHAR2
82 ,HEDGE_CURRENCY IN VARCHAR2
83 ,HITEM_AMT IN NUMBER) RETURN NUMBER IS
84 BEGIN
85 RETURN (ABS(GET_EQU_AMT(COMPANY_CODE
86 ,HEDGE_CURRENCY
87 ,HITEM_AMT)));
88 END HITEM_RCCYFORMULA;
89
90 FUNCTION HINST_RCCYFORMULA(COMPANY_CODE IN VARCHAR2
91 ,HEDGE_CURRENCY IN VARCHAR2
92 ,HINST_AMT IN NUMBER) RETURN NUMBER IS
93 BEGIN
94 RETURN (ABS(GET_EQU_AMT(COMPANY_CODE
95 ,HEDGE_CURRENCY
96 ,HINST_AMT)));
97 END HINST_RCCYFORMULA;
98
99 FUNCTION AFTERPFORM RETURN BOOLEAN IS
100 CURSOR COMPANY(P_COMPANY IN VARCHAR2) IS
101 SELECT
102 SHORT_NAME
103 FROM
104 XTR_PARTIES_V
105 WHERE PARTY_CODE = P_COMPANY;
106 CURSOR STRATEGY(P_STRATEGY IN VARCHAR2) IS
107 SELECT
108 STRATEGY_NAME
109 FROM
110 XTR_HEDGE_STRATEGIES
111 WHERE STRATEGY_CODE = P_STRATEGY;
112 CURSOR CUR_MEAN(P_TYPE IN VARCHAR2,P_CODE IN VARCHAR2) IS
113 SELECT
114 MEANING
115 FROM
116 FND_LOOKUPS
117 WHERE LOOKUP_TYPE = P_TYPE
118 AND LOOKUP_CODE = P_CODE;
119 L_LOOKUP_TYPE VARCHAR2(30);
120 BEGIN
121 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
122 P_AS_OF_DATE_1 := P_AS_OF_DATE;
123 P_DATE := FND_DATE.CANONICAL_TO_DATE(P_AS_OF_DATE_1);
124 P_AS_OF_DATE_1 := P_DATE;
125 IF P_FACTOR = '0' THEN
126 P_UNIT := 1;
127 ELSE
128 P_UNIT := TO_NUMBER(P_FACTOR);
129 END IF;
130 IF REPORT_NAME IS NULL THEN
131 SELECT
132 CP.USER_CONCURRENT_PROGRAM_NAME
133 INTO REPORT_NAME
134 FROM
135 FND_CONCURRENT_PROGRAMS_VL CP,
136 FND_CONCURRENT_REQUESTS CR
137 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
138 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
139 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
140 REPORT_NAME := substr(REPORT_NAME,1,instr(REPORT_NAME,' (XML)'));
141 END IF;
142 IF P_COMPANY IS NOT NULL THEN
143 OPEN COMPANY(P_COMPANY);
144 FETCH COMPANY
145 INTO RP_COMPANY_NAME;
146 CLOSE COMPANY;
147 END IF;
148 IF P_RISK_TYPE IS NOT NULL THEN
149 OPEN CUR_MEAN('XTR_HEDGE_RISK_TYPES',P_RISK_TYPE);
150 FETCH CUR_MEAN
151 INTO RP_RISK_TYPE;
152 CLOSE CUR_MEAN;
153 END IF;
154 IF P_HEDGE_TYPE IS NOT NULL THEN
155 OPEN CUR_MEAN('XTR_HEDGE_TYPES',P_HEDGE_TYPE);
156 FETCH CUR_MEAN
157 INTO RP_HEDGE_TYPE;
158 CLOSE CUR_MEAN;
159 END IF;
160 IF P_OBJECTIVE IS NOT NULL THEN
161 OPEN CUR_MEAN('XTR_HEDGE_OBJECTIVE_TYPES',P_OBJECTIVE);
162 FETCH CUR_MEAN
163 INTO RP_OBJECTIVE;
164 CLOSE CUR_MEAN;
165 END IF;
166 IF P_STRATEGY IS NOT NULL THEN
167 OPEN STRATEGY(P_STRATEGY);
168 FETCH STRATEGY
169 INTO RP_STRATEGY_NAME;
170 CLOSE STRATEGY;
171 END IF;
172 IF P_HEDGE_STATUS IS NOT NULL THEN
173 IF P_HEDGE_STATUS = 'ALLXCANC' THEN
174 L_LOOKUP_TYPE := 'XTR_HEDGE_MISC';
175 ELSE
176 L_LOOKUP_TYPE := 'XTR_HEDGE_STATUS';
177 END IF;
178 OPEN CUR_MEAN(L_LOOKUP_TYPE,P_HEDGE_STATUS);
179 FETCH CUR_MEAN
180 INTO RP_HEDGE_STATUS;
181 CLOSE CUR_MEAN;
182 END IF;
183 IF P_FACTOR IS NOT NULL THEN
184 OPEN CUR_MEAN('XTR_NUM_FACTOR',P_FACTOR);
185 FETCH CUR_MEAN
186 INTO RP_FACTOR;
187 CLOSE CUR_MEAN;
188 END IF;
189 RETURN (TRUE);
190 EXCEPTION
191 WHEN OTHERS THEN
192 /*SRW.MESSAGE(100
193 ,SQLERRM)*/NULL;
194 END AFTERPFORM;
195
196 FUNCTION GET_EQU_AMT(X_COMPANY_CODE IN VARCHAR2
197 ,X_BASE_CCY IN VARCHAR2
198 ,X_BASE_AMT IN NUMBER) RETURN NUMBER IS
199 CURSOR COMPANY_INFO IS
200 SELECT
201 CP.PARAMETER_VALUE_CODE,
202 DCT.USER_CONVERSION_TYPE,
203 CURRENCY_CODE
207 GL_SETS_OF_BOOKS SOB,
204 FROM
205 XTR_PARTIES_V PTY,
206 XTR_COMPANY_PARAMETERS CP,
208 GL_DAILY_CONVERSION_TYPES DCT
209 WHERE PTY.PARTY_CODE = X_COMPANY_CODE
210 AND CP.COMPANY_CODE = PTY.PARTY_CODE
211 AND CP.PARAMETER_CODE = 'ACCNT_EXRTP'
212 AND PTY.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
213 AND CP.PARAMETER_VALUE_CODE = dct.conversion_type (+);
214 CURSOR C_DATE(P_SOB_CCY IN VARCHAR2,L_CON_TYPE IN VARCHAR2) IS
215 SELECT
216 MAX(CONVERSION_DATE)
217 FROM
218 GL_DAILY_RATES
219 WHERE FROM_CURRENCY = X_BASE_CCY
220 AND TO_CURRENCY = P_SOB_CCY
221 AND CONVERSION_TYPE = L_CON_TYPE
222 AND CONVERSION_DATE <= P_AS_OF_DATE_1;
223 SOB_CURRENCY VARCHAR2(15);
224 L_PTY_CONVERT_TYPE VARCHAR2(30);
225 L_PTY_USER_CONVERT_TYPE VARCHAR2(30);
226 L_EQU_AMOUNT NUMBER;
227 L_DATE DATE;
228 L_CURRENCY VARCHAR2(15);
229 BEGIN
230 OPEN COMPANY_INFO;
231 FETCH COMPANY_INFO
232 INTO L_PTY_CONVERT_TYPE,L_PTY_USER_CONVERT_TYPE,SOB_CURRENCY;
233 CLOSE COMPANY_INFO;
234 L_CURRENCY := NVL(P_REPORT_CCY
235 ,SOB_CURRENCY);
236 OPEN C_DATE(L_CURRENCY,L_PTY_CONVERT_TYPE);
237 FETCH C_DATE
238 INTO L_DATE;
239 CLOSE C_DATE;
240 L_EQU_AMOUNT := GL_CURRENCY_API.CONVERT_AMOUNT(X_BASE_CCY
241 ,L_CURRENCY
242 ,L_DATE
243 ,L_PTY_CONVERT_TYPE
244 ,X_BASE_AMT);
245 RETURN (L_EQU_AMOUNT);
246 EXCEPTION
247 WHEN OTHERS THEN
248 IF CP_NO_GL_RATE IS NULL THEN
249 FND_MESSAGE.SET_NAME('XTR'
250 ,'XTR_HEDGE_NO_GL_RATE_SRW');
251 IF CP_NO_GL_RATE IS NULL THEN
252 CP_NO_GL_RATE := FND_MESSAGE.GET;
253 END IF;
254 END IF;
255 /*SRW.MESSAGE(100
256 ,'Error Calculating the Report Currency Equivalent Amount')*/NULL;
257 RETURN (NULL);
258 END GET_EQU_AMT;
259
260 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
261 CURSOR GET_LANGUAGE_DESC IS
262 SELECT
263 ITEM_NAME,
264 TEXT LANG_NAME
265 FROM
266 XTR_SYS_LANGUAGES_VL
267 WHERE MODULE_NAME = 'XTRDDHTY';
268 BEGIN
269
270 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
271 FOR c IN GET_LANGUAGE_DESC LOOP
272 IF C.ITEM_NAME = 'Z1AMOUNT' THEN
273 Z1AMOUNT := C.LANG_NAME;
274 ELSIF C.ITEM_NAME = 'Z1CUR_HEDGE' THEN
275 Z1CUR_HEDGE := C.LANG_NAME;
276 ELSIF C.ITEM_NAME = 'Z1ITEM_AMOUNT' THEN
277 Z1ITEM_AMOUNT := C.LANG_NAME;
278 ELSIF C.ITEM_NAME = 'Z1INST_AMOUNT' THEN
279 Z1INST_AMOUNT := C.LANG_NAME;
280 ELSIF C.ITEM_NAME = 'Z1AS_OF_DATE' THEN
281 Z1AS_OF_DATE := C.LANG_NAME;
282 ELSIF C.ITEM_NAME = 'Z1CCY' THEN
283 Z1CCY := C.LANG_NAME;
284 ELSIF C.ITEM_NAME = 'Z1COMPANY' THEN
285 Z1COMPANY := C.LANG_NAME;
286 ELSIF C.ITEM_NAME = 'Z1DER_DISCL' THEN
287 Z1DER_DISCL := C.LANG_NAME;
288 ELSIF C.ITEM_NAME = 'Z1END_DATE' THEN
289 Z1END_DATE := C.LANG_NAME;
290 ELSIF C.ITEM_NAME = 'Z1EQUI' THEN
291 Z1EQUI := C.LANG_NAME;
292 ELSIF C.ITEM_NAME = 'Z1FACTOR' THEN
293 Z1FACTOR := C.LANG_NAME;
294 ELSIF C.ITEM_NAME = 'Z1GT_SYSDATE' THEN
295 IF P_AS_OF_DATE_1 > SYSDATE THEN
296 Z1GT_SYSDATE := C.LANG_NAME;
297 ELSE
298 Z1GT_SYSDATE := NULL;
299 END IF;
300 ELSIF C.ITEM_NAME = 'Z1HEDGE' THEN
301 Z1HEDGE := C.LANG_NAME;
302 ELSIF C.ITEM_NAME = 'Z1HEDGE_INST' THEN
303 Z1HEDGE_INST := C.LANG_NAME;
304 ELSIF C.ITEM_NAME = 'Z1HEDGE_ITEM' THEN
305 Z1HEDGE_ITEM := C.LANG_NAME;
306 ELSIF C.ITEM_NAME = 'Z1HEDGE_TYPE' THEN
307 Z1HEDGE_TYPE := C.LANG_NAME;
308 ELSIF C.ITEM_NAME = 'Z1HSTGY_TOTAL' THEN
309 Z1HSTGY_TOTAL := C.LANG_NAME;
310 ELSIF C.ITEM_NAME = 'Z1HTYPE_TOTAL' THEN
311 Z1HTYPE_TOTAL := C.LANG_NAME;
312 ELSIF C.ITEM_NAME = 'Z1NUMBER' THEN
313 Z1NUMBER := C.LANG_NAME;
314 ELSIF C.ITEM_NAME = 'Z1OBJECTIVE' THEN
315 Z1OBJECTIVE := C.LANG_NAME;
316 ELSIF C.ITEM_NAME = 'Z1OBJECTIVE_DESC' THEN
317 Z1OBJECTIVE_DESC := C.LANG_NAME;
318 ELSIF C.ITEM_NAME = 'Z1PARAMETERS' THEN
319 Z1PARAMETERS := C.LANG_NAME;
320 ELSIF C.ITEM_NAME = 'Z1POLICY_REF' THEN
321 Z1POLICY_REF := C.LANG_NAME;
322 ELSIF C.ITEM_NAME = 'Z1REPORT_CCY' THEN
323 Z1REPORT_CCY := C.LANG_NAME;
324 ELSIF C.ITEM_NAME = 'Z1RISK_TYPE' THEN
325 Z1RISK_TYPE := C.LANG_NAME;
326 ELSIF C.ITEM_NAME = 'Z1START_DATE' THEN
327 Z1START_DATE := C.LANG_NAME;
328 ELSIF C.ITEM_NAME = 'Z1STRATEGY' THEN
329 Z1STRATEGY := C.LANG_NAME;
330 ELSIF C.ITEM_NAME = 'Z1STRATEGY_NAME' THEN
331 Z1STRATEGY_NAME := C.LANG_NAME;
332 ELSIF C.ITEM_NAME = 'Z2END_OF_REPORT' THEN
333 Z2END_OF_REPORT := C.LANG_NAME;
334 ELSIF C.ITEM_NAME = 'Z2HEDGE_STATUS' THEN
335 Z2HEDGE_STATUS := C.LANG_NAME;
336 ELSIF C.ITEM_NAME = 'Z2NO_DATA_FOUND' THEN
337 Z2NO_DATA_FOUND := C.LANG_NAME;
338 ELSIF C.ITEM_NAME = 'Z2PAGE' THEN
339 Z2PAGE := C.LANG_NAME;
340 ELSIF C.ITEM_NAME = 'Z2REPORT_DATE' THEN
341 Z2REPORT_DATE := C.LANG_NAME;
342 ELSIF C.ITEM_NAME = 'Z2STRATEGY' THEN
343 Z2STRATEGY := C.LANG_NAME;
344 END IF;
345 END LOOP;
346 RETURN (TRUE);
347 END BEFOREREPORT;
348
349 FUNCTION COMPANY_NAMEFORMULA(COMPANY_CODE IN VARCHAR2) RETURN CHAR IS
350 CURSOR COMPANY(P_COMPANY IN VARCHAR2) IS
351 SELECT
352 SHORT_NAME
353 FROM
354 XTR_PARTIES_V
355 WHERE PARTY_CODE = P_COMPANY;
356 L_COMPANY VARCHAR2(20);
357 BEGIN
358 IF COMPANY_CODE IS NOT NULL THEN
359 OPEN COMPANY(COMPANY_CODE);
360 FETCH COMPANY
361 INTO L_COMPANY;
362 CLOSE COMPANY;
363 END IF;
364 RETURN (L_COMPANY);
365 EXCEPTION
366 WHEN OTHERS THEN
367 RETURN (NULL);
368 END COMPANY_NAMEFORMULA;
369
370 FUNCTION HEDGE_TYPE_DSPFORMULA(HEDGE_TYPE IN VARCHAR2) RETURN CHAR IS
371 CURSOR HTYPE(P_HTYPE IN VARCHAR2) IS
372 SELECT
373 MEANING
374 FROM
375 FND_LOOKUPS
376 WHERE LOOKUP_TYPE = 'XTR_HEDGE_TYPES'
377 AND LOOKUP_CODE = P_HTYPE;
378 L_HEDGE_TYPE VARCHAR2(80);
379 BEGIN
380 IF HEDGE_TYPE IS NOT NULL THEN
381 OPEN HTYPE(HEDGE_TYPE);
382 FETCH HTYPE
383 INTO L_HEDGE_TYPE;
384 CLOSE HTYPE;
385 END IF;
386 RETURN (L_HEDGE_TYPE);
387 EXCEPTION
388 WHEN OTHERS THEN
389 RETURN (NULL);
390 END HEDGE_TYPE_DSPFORMULA;
391
392 FUNCTION OBJECTIVE_NAMEFORMULA(OBJECTIVE_CODE IN VARCHAR2) RETURN CHAR IS
393 CURSOR OBJ(P_OBJ IN VARCHAR2) IS
394 SELECT
395 MEANING
396 FROM
397 FND_LOOKUPS
398 WHERE LOOKUP_TYPE = 'XTR_HEDGE_OBJECTIVE_TYPES'
399 AND LOOKUP_CODE = P_OBJ;
400 L_OBJNAME VARCHAR2(80);
401 BEGIN
402 IF OBJECTIVE_CODE IS NOT NULL THEN
403 OPEN OBJ(OBJECTIVE_CODE);
404 FETCH OBJ
405 INTO L_OBJNAME;
406 CLOSE OBJ;
407 END IF;
408 RETURN (L_OBJNAME);
409 EXCEPTION
410 WHEN OTHERS THEN
411 RETURN (NULL);
412 END OBJECTIVE_NAMEFORMULA;
413
414 FUNCTION OBJECTIVE_DESCFORMULA(OBJECTIVE_CODE IN VARCHAR2) RETURN CHAR IS
415 CURSOR OBJ(P_OBJ IN VARCHAR2) IS
416 SELECT
417 DESCRIPTION
418 FROM
419 FND_LOOKUPS
420 WHERE LOOKUP_TYPE = 'XTR_HEDGE_OBJECTIVE_TYPES'
421 AND LOOKUP_CODE = P_OBJ;
422 L_OBJDESC VARCHAR2(240);
423 BEGIN
424 IF OBJECTIVE_CODE IS NOT NULL THEN
425 OPEN OBJ(OBJECTIVE_CODE);
426 FETCH OBJ
427 INTO L_OBJDESC;
428 CLOSE OBJ;
429 END IF;
430 RETURN (L_OBJDESC);
431 EXCEPTION
432 WHEN OTHERS THEN
433 RETURN (NULL);
434 END OBJECTIVE_DESCFORMULA;
435
436 FUNCTION RISK_TYPE_DSPFORMULA(RISK_TYPE IN VARCHAR2) RETURN CHAR IS
437 CURSOR RISK(P_CODE IN VARCHAR2) IS
438 SELECT
439 MEANING
440 FROM
441 FND_LOOKUPS
442 WHERE LOOKUP_TYPE = 'XTR_HEDGE_RISK_TYPES'
443 AND LOOKUP_CODE = P_CODE;
444 L_RISKNAME VARCHAR2(80);
445 BEGIN
446 IF RISK_TYPE IS NOT NULL THEN
447 OPEN RISK(RISK_TYPE);
448 FETCH RISK
449 INTO L_RISKNAME;
450 CLOSE RISK;
451 END IF;
452 RETURN (L_RISKNAME);
453 EXCEPTION
454 WHEN OTHERS THEN
455 RETURN (NULL);
456 END RISK_TYPE_DSPFORMULA;
457
458 FUNCTION STARFORMULA(HEDGE_STATUS IN VARCHAR2) RETURN CHAR IS
459 BEGIN
460 IF HEDGE_STATUS <> 'CURRENT' THEN
461 FND_MESSAGE.SET_NAME('XTR'
462 ,'XTR_HEDGE_NOT_CURRENT_SRW');
463 FND_MESSAGE.SET_TOKEN('STATUS'
464 ,'Current');
465 IF CP_NOT_CURRENT IS NULL THEN
466 CP_NOT_CURRENT := FND_MESSAGE.GET;
467 END IF;
468 RETURN ('**');
469 ELSE
470 RETURN (NULL);
471 END IF;
472 END STARFORMULA;
473
474 FUNCTION RPT_CCYFORMULA(COMPANY_CODE IN VARCHAR2) RETURN CHAR IS
475 CURSOR COMPANY_INFO IS
476 SELECT
477 CP.PARAMETER_VALUE_CODE,
478 DCT.USER_CONVERSION_TYPE,
479 CURRENCY_CODE
480 FROM
481 XTR_PARTIES_V PTY,
482 XTR_COMPANY_PARAMETERS CP,
483 GL_SETS_OF_BOOKS SOB,
484 GL_DAILY_CONVERSION_TYPES DCT
485 WHERE PTY.PARTY_CODE = COMPANY_CODE
486 AND CP.COMPANY_CODE = PTY.PARTY_CODE
487 AND CP.PARAMETER_CODE = 'ACCNT_EXRTP'
488 AND PTY.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
489 AND CP.PARAMETER_VALUE_CODE = dct.conversion_type (+);
490 SOB_CURRENCY VARCHAR2(15);
491 L_PTY_CONVERT_TYPE VARCHAR2(30);
492 L_PTY_USER_CONVERT_TYPE VARCHAR2(30);
493 L_SOB_AMOUNT NUMBER;
494 L_DATE DATE;
495 L_CURRENCY VARCHAR2(15);
496 BEGIN
497 IF P_REPORT_CCY IS NOT NULL THEN
498 RETURN (P_REPORT_CCY);
499 ELSE
500 OPEN COMPANY_INFO;
501 FETCH COMPANY_INFO
502 INTO L_PTY_CONVERT_TYPE,L_PTY_USER_CONVERT_TYPE,SOB_CURRENCY;
503 CLOSE COMPANY_INFO;
504 RETURN (SOB_CURRENCY);
505 END IF;
506 EXCEPTION
507 WHEN OTHERS THEN
508 RETURN (NULL);
509 END RPT_CCYFORMULA;
510
511 FUNCTION AFTERREPORT RETURN BOOLEAN IS
512 BEGIN
513 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
514 RETURN (TRUE);
515 END AFTERREPORT;
516
517 FUNCTION CHINST_AMTFORMULA(HEDGE_NO IN NUMBER) RETURN NUMBER IS
518 L_APPROACH VARCHAR2(30);
519 L_ROUND NUMBER;
520 L_GAIN_LOSS_CCY VARCHAR2(15);
521 L_AMOUNT_TYPE VARCHAR2(30);
522 L_HEDGE_AMT NUMBER;
523 L_REF_AMOUNT NUMBER;
524 L_ORIG_HEDGE_AMT NUMBER;
525 L_CUM_REC_HDG_AMT NUMBER;
526 L_REM_HEDGE_AMT NUMBER;
527 L_ORIG_REF_AMT NUMBER;
528 L_CUR_REF_AMT NUMBER;
529 CURSOR RECLASS IS
530 SELECT
531 SUM(RECLASS_HEDGE_AMT)
532 FROM
533 XTR_RECLASS_DETAILS
534 WHERE HEDGE_ATTRIBUTE_ID = HEDGE_NO
535 AND RECLASS_DATE <= P_DATE
536 AND RECLASS_GAIN_LOSS_AMT is not null;
537 CURSOR HDG IS
538 SELECT
539 S.HEDGE_APPROACH,
540 H.HEDGE_AMOUNT
541 FROM
542 XTR_HEDGE_STRATEGIES S,
543 XTR_HEDGE_ATTRIBUTES H
544 WHERE S.STRATEGY_CODE = H.STRATEGY_CODE
545 AND H.HEDGE_ATTRIBUTE_ID = HEDGE_NO;
546 CURSOR REF_AMT(P_FLAG IN VARCHAR2) IS
547 SELECT
548 ABS(SUM(R.REFERENCE_AMOUNT)) REF_AMT
549 FROM
550 XTR_HEDGE_RELATIONSHIPS R
551 WHERE R.HEDGE_ATTRIBUTE_ID = HEDGE_NO
552 AND INSTRUMENT_ITEM_FLAG = P_FLAG;
553 BEGIN
554 OPEN HDG;
555 FETCH HDG
556 INTO L_APPROACH,L_HEDGE_AMT;
557 CLOSE HDG;
558 IF L_APPROACH = 'FORECAST' THEN
559 L_ORIG_HEDGE_AMT := NVL(L_HEDGE_AMT
560 ,0);
561 ELSE
562 OPEN REF_AMT('I');
563 FETCH REF_AMT
564 INTO L_ORIG_HEDGE_AMT;
565 CLOSE REF_AMT;
566 END IF;
567 OPEN REF_AMT('U');
568 FETCH REF_AMT
569 INTO L_ORIG_REF_AMT;
570 CLOSE REF_AMT;
571 OPEN RECLASS;
572 FETCH RECLASS
573 INTO L_CUM_REC_HDG_AMT;
574 CLOSE RECLASS;
575 L_REM_HEDGE_AMT := NVL(L_ORIG_HEDGE_AMT
576 ,0) - NVL(L_CUM_REC_HDG_AMT
577 ,0);
578 L_CUR_REF_AMT := L_ORIG_REF_AMT * L_REM_HEDGE_AMT / L_ORIG_HEDGE_AMT;
579 RETURN (L_CUR_REF_AMT);
580 EXCEPTION
581 WHEN OTHERS THEN
582 RETURN NULL;
583 END CHINST_AMTFORMULA;
584
585 FUNCTION CP_NO_GL_RATE_P RETURN VARCHAR2 IS
586 BEGIN
587 RETURN CP_NO_GL_RATE;
588 END CP_NO_GL_RATE_P;
589
590 FUNCTION CP_NOT_CURRENT_P RETURN VARCHAR2 IS
591 BEGIN
592 RETURN CP_NOT_CURRENT;
593 END CP_NOT_CURRENT_P;
594
595
596 END XTR_XTRDDHTY_XMLP_PKG;
597
[Home] [Help]