[Home] [Help]
PACKAGE BODY: APPS.XTR_XTRFXEXP_XMLP_PKG
Source
1 PACKAGE BODY XTR_XTRFXEXP_XMLP_PKG AS
2 /* $Header: XTRFXEXPB.pls 120.1 2007/12/31 12:14:13 vjaganat noship $ */
3 FUNCTION TOTAL_HEDGE1FORMULA RETURN VARCHAR2 IS
4 BEGIN
5 /* EXECUTE IMMEDIATE
6 'SELECT nvl(:FX_COVER,0) + nvl(:CF_COH_CABAL,0) INTO TOTAL_HEDGE from DUAL';
7 COMMIT;*/
8 RETURN (' ');
9 END TOTAL_HEDGE1FORMULA;
10
11 FUNCTION TOTAL_HEDGEFORMULA RETURN NUMBER IS
12 BEGIN
13 RETURN NULL;
14 END TOTAL_HEDGEFORMULA;
15
16 FUNCTION CF_SET_PARAFORMULA RETURN VARCHAR2 IS
17 BEGIN
18 SELECT
19 SUBSTR(USER
20 ,1
21 ,10)
22 INTO
23 CP_PARA
24 FROM
25 DUAL;
26 RETURN (CP_PARA);
27 END CF_SET_PARAFORMULA;
28
29 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
30 L_DMMY_NUM NUMBER;
31 L_MESSAGE FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
32 CURSOR CHK_LANG IS
33 SELECT
34 1
35 FROM
36 XTR_SYS_LANGUAGES_VL
37 WHERE MODULE_NAME = 'XTRFXEXP';
38 CURSOR GET_LANGUAGE_DESC IS
39 SELECT
40 ITEM_NAME,
41 SUBSTR(TEXT
42 ,1
43 ,100) LANG_NAME
44 FROM
45 XTR_SYS_LANGUAGES_VL
46 WHERE MODULE_NAME = 'XTRFXEXP';
47 BEGIN
48 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
49 BEGIN
50 COMPANY_NAME_HEADER := CEP_STANDARD.GET_WINDOW_SESSION_TITLE;
51 EXCEPTION
52 WHEN OTHERS THEN
53 FND_MESSAGE.SET_NAME('XTR'
54 ,'XTR_LOOKUP_ERR');
55 L_MESSAGE := FND_MESSAGE.GET;
56 RAISE_APPLICATION_ERROR(-20101
57 ,NULL);
58 END;
59 IF (P_DISPLAY_DEBUG = 'Y') THEN
60 NULL;
61 END IF;
62 FOR c IN GET_LANGUAGE_DESC LOOP
63 IF C.ITEM_NAME = 'Z1COMPANY' THEN
64 Z1COMPANY := C.LANG_NAME;
65 ELSIF C.ITEM_NAME = 'Z1CURRENCY' THEN
66 Z1CURRENCY := C.LANG_NAME;
67 ELSIF C.ITEM_NAME = 'Z1PARAMETERS' THEN
68 Z1PARAMETERS := C.LANG_NAME;
69 ELSIF C.ITEM_NAME = 'Z2AMOUNT_REQ' THEN
70 Z2AMOUNT_REQ := C.LANG_NAME;
71 ELSIF C.ITEM_NAME = 'Z1AND_CALL_DEP_ADV' THEN
72 Z1AND_CALL_DEP_ADV := C.LANG_NAME;
73 ELSIF C.ITEM_NAME = 'Z2CCY' THEN
74 Z2CCY := C.LANG_NAME;
75 ELSIF C.ITEM_NAME = 'Z2COVER' THEN
76 Z2COVER := C.LANG_NAME;
77 ELSIF C.ITEM_NAME = 'Z2CURR_AC_BALANCE' THEN
78 Z2CURR_AC_BALANCE := C.LANG_NAME;
79 ELSIF C.ITEM_NAME = 'Z2END_OF_REPORT' THEN
80 Z2END_OF_REPORT := C.LANG_NAME;
81 ELSIF C.ITEM_NAME = 'Z2FOR_POLICY' THEN
82 Z2FOR_POLICY := C.LANG_NAME;
83 ELSIF C.ITEM_NAME = 'Z2FX_OPTION' THEN
84 Z2FX_OPTION := C.LANG_NAME;
85 ELSIF C.ITEM_NAME = 'Z2FX_SPOT_FWDS' THEN
86 Z2FX_SPOT_FWDS := C.LANG_NAME;
87 ELSIF C.ITEM_NAME = 'Z2NET_EXPOSURE' THEN
88 Z2NET_EXPOSURE := C.LANG_NAME;
89 ELSIF C.ITEM_NAME = 'Z2NOTE_ALL_AMOUNTS' THEN
90 Z2NOTE_ALL_AMOUNTS := C.LANG_NAME;
91 ELSIF C.ITEM_NAME = 'Z2TOTAL_EXPOSURE' THEN
92 Z2TOTAL_EXPOSURE := C.LANG_NAME;
93 ELSIF C.ITEM_NAME = 'Z2TOTAL_HEDGE' THEN
94 Z2TOTAL_HEDGE := C.LANG_NAME;
95 ELSIF C.ITEM_NAME = 'Z2PAGE' THEN
96 Z2PAGE := C.LANG_NAME;
97 ELSIF C.ITEM_NAME = 'REPORT_DATE' THEN
98 REPORT_DATE := C.LANG_NAME;
99 ELSIF C.ITEM_NAME = 'Z1P_FACTOR' THEN
100 Z1P_FACTOR := C.LANG_NAME;
101 ELSIF C.ITEM_NAME = 'Z1P_EXPOSURE_TYPE' THEN
102 Z1P_EXPOSURE_TYPE := C.LANG_NAME;
103 ELSIF C.ITEM_NAME = 'Z1P_INCLUDE_IC' THEN
104 Z1P_INCLUDE_IC := C.LANG_NAME;
105 END IF;
106 END LOOP;
107 RETURN (TRUE);
108 END BEFOREREPORT;
109
110 FUNCTION AFTERREPORT RETURN BOOLEAN IS
111 BEGIN
112 RETURN (TRUE);
113 END AFTERREPORT;
114
115 FUNCTION AFTERPFORM RETURN BOOLEAN IS
116 TEMP NUMBER(15) := 1000;
117 BEGIN
118 IF P_FACTOR IS NOT NULL THEN
119 SELECT
120 SUBSTRB(DESCRIPTION
121 ,1
122 ,20),
123 DECODE(SUBSTR(P_FACTOR
124 ,1
125 ,1)
126 ,'U'
127 ,1
128 ,'T'
129 ,1000
130 ,'M'
131 ,1000000
132 ,'B'
133 ,100000000)
134 INTO
135 LP_FACTOR_DESC
136 ,TEMP
137 FROM
138 FND_LOOKUPS
139 WHERE LOOKUP_TYPE = 'XTR_FACTOR'
140 AND LOOKUP_CODE = SUBSTR(P_FACTOR
141 ,1
142 ,1);
143 ELSE
144 SELECT
145 SUBSTRB(DESCRIPTION
146 ,1
147 ,20)
148 INTO
149 LP_FACTOR_DESC
150 FROM
151 FND_LOOKUPS
152 WHERE LOOKUP_TYPE = 'XTR_FACTOR'
153 AND LOOKUP_CODE = 'T';
154 TEMP := 1000;
155 END IF;
156 AMT_UNIT2 := TEMP;
157 COMPANY_CODE2 := P_COMPANY;
158 CURRENCY_CODE2 := P_CURRENCY;
159 IF P_EXPOSURE_TYPE = 'FIRM' THEN
160 P_1 := 'and deal_subtype = ''FIRM''';
161 ELSIF P_EXPOSURE_TYPE = 'INDICATIVE' THEN
162 P_1 := 'and deal_subtype = ''INDIC''';
163 ELSIF P_EXPOSURE_TYPE = 'NONE' THEN
164 P_1 := 'and deal_subtype not in (''FIRM'',''INDIC'')';
165 ELSIF P_EXPOSURE_TYPE = 'ALL' THEN
166 P_1 := 'and deal_subtype in (''FIRM'',''INDIC'')';
167 END IF;
168 SELECT
169 CP.USER_CONCURRENT_PROGRAM_NAME
170 INTO
171 REPORT_SHORT_NAME2
172 FROM
173 FND_CONCURRENT_PROGRAMS_VL CP,
174 FND_CONCURRENT_REQUESTS CR
175 WHERE CR.REQUEST_ID = FND_GLOBAL.conc_request_id
176 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
177 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
178 REPORT_SHORT_NAME2 := substr(REPORT_SHORT_NAME2,1,instr(REPORT_SHORT_NAME2,' (XML)'));
179 SELECT
180 MEANING
181 INTO
182 P_EXPOSURE_TYPE_DSP
183 FROM
184 FND_LOOKUPS
185 WHERE LOOKUP_TYPE = 'XTR_MISC'
186 AND LOOKUP_CODE = P_EXPOSURE_TYPE;
187 SELECT
188 MEANING
189 INTO
190 P_FACTOR_DSP
191 FROM
192 FND_LOOKUPS
193 WHERE LOOKUP_TYPE = 'XTR_MISC'
194 AND LOOKUP_CODE = P_FACTOR;
195 SELECT
196 MEANING
197 INTO
198 P_YES_NO_DSP
199 FROM
200 FND_LOOKUPS
201 WHERE LOOKUP_TYPE = 'YES_NO'
202 AND LOOKUP_CODE = NVL(P_INCLUDE_INTERCOMP
203 ,'N');
204 RETURN (TRUE);
205 END AFTERPFORM;
206
207 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
208 BEGIN
209 RETURN (TRUE);
210 END BEFOREPFORM;
211
212 FUNCTION BETWEENPAGE RETURN BOOLEAN IS
213 BEGIN
214 RETURN (TRUE);
215 END BETWEENPAGE;
216
217 FUNCTION CF_COH_CABALFORMULA(COMPANY IN VARCHAR2
218 ,CCY IN VARCHAR2
219 ,CASH_ON_HAND IN NUMBER) RETURN NUMBER IS
220 L_COH_CABAL NUMBER := 0;
221 BEGIN
222 IF P_INCLUDE_INTERCOMP = 'Y' THEN
223 SELECT
224 NVL(SUM(AMOUNT)
225 ,0) / AMT_UNIT2
226 INTO
227 L_COH_CABAL
228 FROM
229 XTR_DEAL_DATE_AMOUNTS DDA,
230 XTR_BANK_ACCOUNTS BA
231 WHERE DDA.COMPANY_CODE = COMPANY
232 AND DDA.ACCOUNT_NO = BA.ACCOUNT_NUMBER
233 AND DDA.CURRENCY = BA.CURRENCY
234 AND BA.PARTY_TYPE = 'CP'
235 AND DDA.DEAL_TYPE = 'CA'
236 AND DDA.CURRENCY = CCY
237 AND DDA.AMOUNT_TYPE = 'BAL';
238 END IF;
239 L_COH_CABAL := NVL(L_COH_CABAL
240 ,0) + NVL(CASH_ON_HAND
241 ,0);
242 RETURN (L_COH_CABAL);
243 EXCEPTION
244 WHEN OTHERS THEN
245 RETURN (NVL(L_COH_CABAL
246 ,0) + NVL(CASH_ON_HAND
247 ,0));
248 END CF_COH_CABALFORMULA;
249
250 FUNCTION CF_TEXP_IGBALFORMULA(COMPANY IN VARCHAR2
251 ,CCY IN VARCHAR2
252 ,TOTAL_EXP IN NUMBER) RETURN NUMBER IS
253 L_TEXP_IGBAL NUMBER := 0;
254 BEGIN
255 IF P_INCLUDE_INTERCOMP = 'Y' THEN
256 SELECT
257 NVL(SUM(DECODE(ACTION_CODE
258 ,'REC'
259 ,AMOUNT
260 ,'PAY'
261 ,-AMOUNT))
262 ,0) / AMT_UNIT2
263 INTO
264 L_TEXP_IGBAL
265 FROM
266 XTR_DEAL_DATE_AMOUNTS
267 WHERE COMPANY_CODE = COMPANY
268 AND DEAL_TYPE = 'IG'
269 AND CURRENCY = CCY
270 AND AMOUNT_TYPE = 'BAL'
271 AND DATE_TYPE = 'BALANCE';
272 END IF;
273 L_TEXP_IGBAL := NVL(L_TEXP_IGBAL
274 ,0) + NVL(TOTAL_EXP
275 ,0);
276 RETURN (L_TEXP_IGBAL);
277 EXCEPTION
278 WHEN OTHERS THEN
279 RETURN (NVL(L_TEXP_IGBAL
280 ,0) + NVL(TOTAL_EXP
281 ,0));
282 END CF_TEXP_IGBALFORMULA;
283
284 FUNCTION CF_AMT_REQFORMULA(COVER IN VARCHAR2
285 ,MIN_HEDGE IN NUMBER
286 ,CF_TEXP_IGBAL IN NUMBER
287 ,MAX_HEDGE IN NUMBER
288 ,TOTAL_HEDGE IN NUMBER) RETURN NUMBER IS
289 BEGIN
290 IF COVER < MIN_HEDGE THEN
291
292 RETURN (-1 * (CF_TEXP_IGBAL * MIN_HEDGE / 100 + TOTAL_HEDGE));
293 ELSIF COVER > MIN_HEDGE AND COVER <= MAX_HEDGE THEN
294 RETURN 0;
295 ELSE
296 RETURN CF_TEXP_IGBAL * MAX_HEDGE / 100 + TOTAL_HEDGE;
297 END IF;
298 END CF_AMT_REQFORMULA;
299
300 FUNCTION TOTAL_HEDGE_P RETURN NUMBER IS
301 BEGIN
302 RETURN TOTAL_HEDGE;
303 END TOTAL_HEDGE_P;
304
305 FUNCTION CP_PARA_P RETURN VARCHAR2 IS
306 BEGIN
307 RETURN CP_PARA;
308 END CP_PARA_P;
309
310 END XTR_XTRFXEXP_XMLP_PKG;
311