1 PACKAGE BODY XTR_XTRREVGL_XMLP_PKG AS
2 /* $Header: XTRREVGLB.pls 120.1 2007/12/28 12:58:48 npannamp noship $ */
3 FUNCTION BEGIN_FVFORMULA(COMPANY IN VARCHAR2
4 ,REF_NUMBER IN VARCHAR2
5 ,PERIOD_START IN DATE
6 ,DEAL_TYPE IN VARCHAR2) RETURN NUMBER IS
7 L_B_FV NUMBER;
8 CURSOR GET_B_FV IS
9 SELECT
10 SUM(A.FAIR_VALUE)
11 FROM
12 XTR_REVALUATION_DETAILS_SUM_V A,
13 XTR_BATCHES B
14 WHERE A.COMPANY_CODE = COMPANY
15 AND A.COMPANY_CODE = B.COMPANY_CODE
16 AND A.BATCH_ID = B.BATCH_ID
17 AND A.REF_NUMBER = BEGIN_FVFORMULA.REF_NUMBER
18 AND B.PERIOD_END < BEGIN_FVFORMULA.PERIOD_START
19 AND A.REALIZED_FLAG = P_REALIZED_FLAG
20 GROUP BY
21 B.PERIOD_START
22 ORDER BY
23 B.PERIOD_START DESC;
24 CURSOR GET_INT_FV IS
25 SELECT
26 SUM(INITIAL_FAIR_VALUE)
27 FROM
28 XTR_DEALS
29 WHERE DEAL_NO = REF_NUMBER;
30 CURSOR GET_NI_INT_FV IS
31 SELECT
32 SUM(INITIAL_FAIR_VALUE)
33 FROM
34 XTR_ROLLOVER_TRANSACTIONS
35 WHERE DEAL_NUMBER = REF_NUMBER;
36 BEGIN
37 OPEN GET_B_FV;
38 FETCH GET_B_FV
39 INTO L_B_FV;
40 CLOSE GET_B_FV;
41 IF L_B_FV IS NULL THEN
42 IF DEAL_TYPE not in ('ONC','EXP','IG','CA','IRS','NI') THEN
43 OPEN GET_INT_FV;
44 FETCH GET_INT_FV
45 INTO L_B_FV;
46 CLOSE GET_INT_FV;
47 ELSIF DEAL_TYPE = 'NI' THEN
48 OPEN GET_NI_INT_FV;
49 FETCH GET_NI_INT_FV
50 INTO L_B_FV;
51 CLOSE GET_NI_INT_FV;
52 END IF;
53 END IF;
54 RETURN (L_B_FV / NVL(P_UNIT
55 ,1000));
56 END BEGIN_FVFORMULA;
57
58 FUNCTION END_FVFORMULA(COMPANY IN VARCHAR2
59 ,REF_NUMBER_1 IN VARCHAR2
60 ,PERIOD_END_1 IN DATE) RETURN NUMBER IS
61 CURSOR GET_E_FV IS
62 SELECT
63 SUM(A.FAIR_VALUE)
64 FROM
65 XTR_REVALUATION_DETAILS_SUM_V A,
66 XTR_BATCHES B
67 WHERE A.COMPANY_CODE = COMPANY
68 AND A.COMPANY_CODE = B.COMPANY_CODE
69 AND A.BATCH_ID = B.BATCH_ID
70 AND A.REF_NUMBER = REF_NUMBER_1
71 AND B.PERIOD_END <= PERIOD_END_1
72 AND A.REALIZED_FLAG = P_REALIZED_FLAG
73 GROUP BY
74 B.PERIOD_END
75 ORDER BY
76 B.PERIOD_END DESC;
77 L_E_FV NUMBER;
78 BEGIN
79 OPEN GET_E_FV;
80 FETCH GET_E_FV
81 INTO L_E_FV;
82 CLOSE GET_E_FV;
83 RETURN (L_E_FV / NVL(P_UNIT
84 ,1000));
85 END END_FVFORMULA;
86
87 FUNCTION AFTERPFORM RETURN BOOLEAN IS
88 L_FACTOR NUMBER(15) := 1;
89 BEGIN
90 BEGIN
91 IF P_FACTOR IS NOT NULL THEN
92 SELECT
93 DECODE(SUBSTR(P_FACTOR
94 ,1
95 ,1)
96 ,'U'
97 ,1
98 ,'T'
99 ,1000
100 ,'M'
101 ,1000000
102 ,'B'
103 ,100000000),
104 MEANING
105 INTO L_FACTOR,P_USER_FACTOR
106 FROM
107 FND_LOOKUPS
108 WHERE LOOKUP_TYPE = 'XTR_FACTOR'
109 AND LOOKUP_CODE = SUBSTR(P_FACTOR
110 ,1
111 ,1);
112 ELSE
113 L_FACTOR := 1000;
114 END IF;
115 P_UNIT := L_FACTOR;
116 EXCEPTION
117 WHEN OTHERS THEN
118 NULL;
119 END;
120 BEGIN
121 IF P_REALIZED_FLAG IS NOT NULL THEN
122 SELECT
123 MEANING
124 INTO Z2REALIZED_FLAG
125 FROM
126 FND_LOOKUPS
127 WHERE LOOKUP_TYPE = 'XTR_MISC'
128 AND LOOKUP_CODE = P_REALIZED_FLAG;
129
130 IF P_REALIZED_FLAG = 'REAL' THEN
131 P_REALIZED_FLAG := 'Y';
132 ELSE
133 P_REALIZED_FLAG := 'N';
134 END IF;
135 ELSE
136 P_REALIZED_FLAG := 'N';
137 END IF;
138 EXCEPTION
139 WHEN OTHERS THEN
140 NULL;
141 END;
142 BEGIN
143 IF P_DEAL_TYPE IS NOT NULL AND P_DEAL_TYPE <> 'HEDGE' THEN
144 SELECT
145 USER_DEAL_TYPE
146 INTO P_USER_DEAL_TYPE
147 FROM
148 XTR_DEAL_TYPES
149 WHERE DEAL_TYPE = P_DEAL_TYPE;
150 ELSIF P_DEAL_TYPE IS NOT NULL AND P_DEAL_TYPE = 'HEDGE' THEN
151 SELECT
152 MEANING
153 INTO P_USER_DEAL_TYPE
154 FROM
155 FND_LOOKUPS
156 WHERE LOOKUP_TYPE = 'XTR_HEDGE_MISC'
157 AND LOOKUP_CODE = 'HEDGE';
158 END IF;
159 EXCEPTION
160 WHEN OTHERS THEN
161 NULL;
162 END;
163 BEGIN
164 IF P_GROUPBY IS NOT NULL THEN
165 SELECT
166 MEANING
167 INTO P_USER_GROUPBY
168 FROM
169 FND_LOOKUPS
170 WHERE LOOKUP_TYPE = 'XTR_MISC'
171 AND LOOKUP_CODE = P_GROUPBY;
172 END IF;
173 EXCEPTION
174 WHEN OTHERS THEN
175 NULL;
176 END;
177 BEGIN
178 IF P_COMPANY IS NOT NULL THEN
179 SELECT
180 SUBSTR(SHORT_NAME
181 ,1
182 ,30)
183 INTO P_USER_COMPANY
184 FROM
185 XTR_PARTY_INFO
186 WHERE PARTY_CODE = P_COMPANY
187 AND PARTY_TYPE = 'C';
188 END IF;
189 EXCEPTION
190 WHEN OTHERS THEN
191 NULL;
192 END;
193 BEGIN
194 IF P_BATCH_ID_FROM IS NOT NULL THEN
195 SELECT
196 TO_CHAR(MIN(PERIOD_START)
197 ,'YYYY/MM/DD HH24:MI:SS')
198 INTO P_DATE_FROM
199 FROM
200 XTR_BATCHES B,
201 XTR_BATCH_EVENTS E
202 WHERE B.BATCH_ID = E.BATCH_ID
203 AND E.EVENT_CODE = 'REVAL'
204 AND B.BATCH_ID >= P_BATCH_ID_FROM;
205 P_USER_DATE_FROM := TO_DATE(P_DATE_FROM
206 ,'YYYY/MM/DD HH24:MI:SS');
207 END IF;
208 IF P_BATCH_ID_TO IS NOT NULL THEN
209 SELECT
210 TO_CHAR(MAX(PERIOD_END)
211 ,'YYYY/MM/DD HH24:MI:SS')
212 INTO P_DATE_TO
213 FROM
214 XTR_BATCHES B,
215 XTR_BATCH_EVENTS E
216 WHERE B.BATCH_ID = E.BATCH_ID
217 AND E.EVENT_CODE = 'REVAL'
218 AND B.BATCH_ID <= P_BATCH_ID_TO;
219 P_USER_DATE_TO := TO_DATE(P_DATE_TO
220 ,'YYYY/MM/DD HH24:MI:SS');
221 END IF;
222 EXCEPTION
223 WHEN OTHERS THEN
224 NULL;
225 END;
226 RETURN (TRUE);
227 END AFTERPFORM;
228
229 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
230 L_DMMY_NUM NUMBER;
231 L_MESSAGE FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
232 CURSOR GET_LANGUAGE_DESC IS
233 SELECT
234 ITEM_NAME,
235 SUBSTR(TEXT
236 ,1
237 ,100) LANG_NAME
238 FROM
239 XTR_SYS_LANGUAGES_VL
240 WHERE MODULE_NAME = 'XTRREVGL';
241 BEGIN
242
243 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
244 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
245 FOR c IN GET_LANGUAGE_DESC LOOP
246 IF C.ITEM_NAME = 'Z2COMPANY' THEN
247 Z2COMPANY := C.LANG_NAME;
248 ELSIF C.ITEM_NAME = 'Z2REVAL_PRD' THEN
249 Z2REVAL_PRD := C.LANG_NAME;
250 ELSIF C.ITEM_NAME = 'Z2REVAL_CCY' THEN
251 Z2REVAL_CCY := C.LANG_NAME;
252 ELSIF C.ITEM_NAME = 'Z2PORTFOLIO' THEN
253 Z2PORTFOLIO := C.LANG_NAME;
254 ELSIF C.ITEM_NAME = 'Z2DEAL_TYPE' THEN
255 Z2DEAL_TYPE := C.LANG_NAME;
256 ELSIF C.ITEM_NAME = 'Z2DEAL_SUBTYPE' THEN
257 Z2DEAL_SUBTYPE := C.LANG_NAME;
258 ELSIF C.ITEM_NAME = 'Z2PRODUCT_TYPE' THEN
259 Z2PRODUCT_TYPE := C.LANG_NAME;
260 ELSIF C.ITEM_NAME = 'Z2REFERENCE' THEN
261 Z2REFERENCE := C.LANG_NAME;
262 ELSIF C.ITEM_NAME = 'Z2DEAL_ENDDATE' THEN
263 Z2DEAL_ENDDATE := C.LANG_NAME;
264 ELSIF C.ITEM_NAME = 'Z2BUY' THEN
265 Z2BUY := C.LANG_NAME;
266 ELSIF C.ITEM_NAME = 'Z2SELL' THEN
267 Z2SELL := C.LANG_NAME;
268 ELSIF C.ITEM_NAME = 'Z2CCY' THEN
269 Z2CCY := C.LANG_NAME;
270 ELSIF C.ITEM_NAME = 'Z2CCY_SHT' THEN
271 Z2CCY_SHT := C.LANG_NAME;
272 ELSIF C.ITEM_NAME = 'Z2AMOUNT' THEN
273 Z2AMOUNT := C.LANG_NAME;
274 ELSIF C.ITEM_NAME = 'Z2TRANS' THEN
275 Z2TRANS := C.LANG_NAME;
276 ELSIF C.ITEM_NAME = 'Z2BEGIN' THEN
277 Z2BEGIN := C.LANG_NAME;
278 ELSIF C.ITEM_NAME = 'Z2FAIR_VALUE' THEN
279 Z2FAIR_VALUE := C.LANG_NAME;
280 ELSIF C.ITEM_NAME = 'Z2END' THEN
281 Z2END := C.LANG_NAME;
282 ELSIF C.ITEM_NAME = 'Z2GAIN_LOSS' THEN
283 Z2GAIN_LOSS := C.LANG_NAME;
284 ELSIF C.ITEM_NAME = 'Z2TOTAL' THEN
285 Z2TOTAL := C.LANG_NAME;
286 ELSIF C.ITEM_NAME = 'Z2END_OF_REPORT' THEN
287 Z2END_OF_REPORT := C.LANG_NAME;
288 ELSIF C.ITEM_NAME = 'Z2NO_DATA_FOUND' THEN
289 Z2NO_DATA_FOUND := C.LANG_NAME;
290 ELSIF C.ITEM_NAME = 'Z1BATCH_ID_FROM' THEN
291 Z1BATCH_ID_FROM := C.LANG_NAME;
292 ELSIF C.ITEM_NAME = 'Z1BATCH_ID_TO' THEN
293 Z1BATCH_ID_TO := C.LANG_NAME;
294 ELSIF C.ITEM_NAME = 'Z1DATE_FROM' THEN
295 Z1DATE_FROM := C.LANG_NAME;
296 ELSIF C.ITEM_NAME = 'Z1DATE_TO' THEN
297 Z1DATE_TO := C.LANG_NAME;
298 ELSIF C.ITEM_NAME = 'Z1PARA_GROUPING' THEN
299 Z1PARA_GROUPING := C.LANG_NAME;
300 ELSIF C.ITEM_NAME = 'Z1REAL_UNREAL' THEN
301 Z1REAL_UNREAL := C.LANG_NAME;
302 ELSIF C.ITEM_NAME = 'Z1FACTOR' THEN
303 Z1FACTOR := C.LANG_NAME;
304 ELSIF C.ITEM_NAME = 'Z1REPHEAD_REAL' THEN
305 Z1REPHEAD_REAL := C.LANG_NAME;
306 ELSIF C.ITEM_NAME = 'Z1REPHEAD_UNREAL' THEN
307 Z1REPHEAD_UNREAL := C.LANG_NAME;
308 END IF;
309
310 END LOOP;
311 LP_REALIZED_FLAG:=P_REALIZED_FLAG;
312
313 RETURN (TRUE);
314 END BEFOREREPORT;
315
316 FUNCTION AFTERREPORT RETURN BOOLEAN IS
317 BEGIN
318 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
319 RETURN (TRUE);
320 END AFTERREPORT;
321
322 FUNCTION C_DATEFORMATFORMULA(C_DATEFORMAT IN VARCHAR2) RETURN CHAR IS
323 BEGIN
324 /*SRW.USER_EXIT('FND DATEFORMAT RESULT=":C_DATEFORMAT"')*/NULL;
325 RETURN (C_DATEFORMAT);
326 END C_DATEFORMATFORMULA;
327
328 FUNCTION CO_SHT_NAMEFORMULA(COMPANY IN VARCHAR2) RETURN CHAR IS
329 L_SHT_NAME VARCHAR2(30);
330 BEGIN
331 IF COMPANY IS NOT NULL THEN
332 SELECT
333 SHORT_NAME
334 INTO L_SHT_NAME
335 FROM
336 XTR_PARTY_INFO
337 WHERE PARTY_CODE = COMPANY
338 AND PARTY_TYPE = 'C';
339 END IF;
340 RETURN (L_SHT_NAME);
341 EXCEPTION
342 WHEN OTHERS THEN
343 RETURN (NULL);
344 END CO_SHT_NAMEFORMULA;
345
346 FUNCTION C_REPORT_NAMEFORMULA RETURN CHAR IS
347 L_REPORT_NAME VARCHAR2(240);
348 BEGIN
349 IF P_REALIZED_FLAG in ('Y','REAL') THEN
350 L_REPORT_NAME := Z1REPHEAD_REAL;
351 ELSE
352 L_REPORT_NAME := Z1REPHEAD_UNREAL;
353 END IF;
354 RETURN (L_REPORT_NAME);
355 EXCEPTION
356 WHEN OTHERS THEN
357 SELECT
358 SUBSTR(CP.USER_CONCURRENT_PROGRAM_NAME
359 ,INSTR(CP.USER_CONCURRENT_PROGRAM_NAME
360 ,'-') + 2)
361 INTO L_REPORT_NAME
362 FROM
363 FND_CONCURRENT_PROGRAMS_VL CP,
364 FND_CONCURRENT_REQUESTS CR
365 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
366 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
367 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
368 RETURN (L_REPORT_NAME);
369 END C_REPORT_NAMEFORMULA;
370
371 FUNCTION C_REV_PERIODFORMULA(PERIOD_START IN DATE
372 ,PERIOD_END IN DATE) RETURN CHAR IS
373 BEGIN
374 RETURN (PERIOD_START || ' - ' || PERIOD_END);
375 END C_REV_PERIODFORMULA;
376
377 FUNCTION USER_DEAL_SUBTYPEFORMULA(DEAL_SUBTYPE_1 IN varchar2
378 ,DEAL_TYPE_1 IN VARCHAR2) RETURN CHAR IS
379 L_USER_DEAL_SUBTYPE VARCHAR2(30);
380 BEGIN
381 IF DEAL_SUBTYPE_1 IS NOT NULL AND DEAL_SUBTYPE_1 not in ('CASHFLOW','FAIRVALUE','ECONOMIC') THEN
382 SELECT
383 SUBSTR(USER_DEAL_SUBTYPE
384 ,1
385 ,30)
386 INTO L_USER_DEAL_SUBTYPE
387 FROM
388 XTR_DEAL_SUBTYPES
389 WHERE
390 DEAL_SUBTYPE = DEAL_SUBTYPE_1
391 AND DEAL_TYPE = DEAL_TYPE_1;
392 ELSIF DEAL_SUBTYPE_1 IS NOT NULL AND DEAL_SUBTYPE_1 in ('CASHFLOW','FAIRVALUE','ECONOMIC') THEN
393 SELECT
394 SUBSTR(MEANING
395 ,1
396 ,30)
397 INTO L_USER_DEAL_SUBTYPE
398 FROM
399 FND_LOOKUPS
400 WHERE LOOKUP_TYPE = 'XTR_HEDGE_TYPES'
401 AND LOOKUP_CODE = DEAL_SUBTYPE_1;
402 END IF;
403 RETURN (L_USER_DEAL_SUBTYPE);
404 EXCEPTION
405 WHEN OTHERS THEN
406 RETURN (NULL);
407 END USER_DEAL_SUBTYPEFORMULA;
408
409 FUNCTION USER_DEAL_TYPEFORMULA(DEAL_TYPE_1 IN VARCHAR2) RETURN CHAR IS
410 L_USER_DEAL_TYPE VARCHAR2(80);
411 BEGIN
412 IF DEAL_TYPE_1 IS NOT NULL AND DEAL_TYPE_1 <> 'HEDGE' THEN
413 SELECT
414 SUBSTR(USER_DEAL_TYPE
415 ,1
416 ,30)
417 INTO L_USER_DEAL_TYPE
418 FROM
419 XTR_DEAL_TYPES
420 WHERE DEAL_TYPE = DEAL_TYPE_1;
421 ELSIF DEAL_TYPE_1 IS NOT NULL AND DEAL_TYPE_1 = 'HEDGE' THEN
422 SELECT
423 MEANING
424 INTO L_USER_DEAL_TYPE
425 FROM
426 FND_LOOKUPS
427 WHERE LOOKUP_TYPE = 'XTR_HEDGE_MISC'
428 AND LOOKUP_CODE = 'HEDGE';
429 END IF;
430 RETURN (L_USER_DEAL_TYPE);
431 EXCEPTION
432 WHEN OTHERS THEN
433 RETURN (NULL);
434 END USER_DEAL_TYPEFORMULA;
435
436 END XTR_XTRREVGL_XMLP_PKG;
437
[Home] [Help]