[Home] [Help]
PACKAGE BODY: APPS.XTR_XTRFXAVG_XMLP_PKG
Source
1 PACKAGE BODY XTR_XTRFXAVG_XMLP_PKG AS
2 /* $Header: XTRFXAVGB.pls 120.5 2007/12/31 11:58:33 vjaganat noship $ */
3 FUNCTION CF_SET_PARAFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 SELECT
6 SUBSTR(USER
7 ,1
8 ,10)
9 INTO
10 CP_PARA
11 FROM
12 DUAL;
13 RETURN (CP_PARA);
14 END CF_SET_PARAFORMULA;
15
16 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
17 L_DMMY_NUM NUMBER;
18 L_MESSAGE FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
19 CURSOR CHK_LANG IS
20 SELECT
21 1
22 FROM
23 XTR_SYS_LANGUAGES_VL
24 WHERE MODULE_NAME = 'XTR_XTRFXAVG_XMLP_PKG';
25 CURSOR GET_LANGUAGE_DESC IS
26 SELECT
27 ITEM_NAME,
28 SUBSTR(TEXT
29 ,1
30 ,100) LANG_NAME
31 FROM
32 XTR_SYS_LANGUAGES_VL
33 WHERE MODULE_NAME = 'XTRFXAVG';
34 BEGIN
35 BEGIN
36 COMPANY_NAME_HEADER := CEP_STANDARD.GET_WINDOW_SESSION_TITLE;
37 EXCEPTION
38 WHEN OTHERS THEN
39 FND_MESSAGE.SET_NAME('XTR'
40 ,'XTR_LOOKUP_ERR');
41 L_MESSAGE := FND_MESSAGE.GET;
42 RAISE_APPLICATION_ERROR(-20101
43 ,NULL);
44 END;
45 IF (P_DISPLAY_DEBUG = 'Y') THEN
46 NULL;
47 END IF;
48 FOR c IN GET_LANGUAGE_DESC LOOP
49 IF C.ITEM_NAME = 'Z1AND' THEN
50 Z1AND := C.LANG_NAME;
51 ELSIF C.ITEM_NAME = 'Z1BETWEEN' THEN
52 Z1BETWEEN := C.LANG_NAME;
53 ELSIF C.ITEM_NAME = 'Z1CONTRACTS' THEN
54 Z1CONTRACTS := C.LANG_NAME;
55 ELSIF C.ITEM_NAME = 'Z1COUNTER_CCY' THEN
56 Z1COUNTER_CCY := C.LANG_NAME;
57 ELSIF C.ITEM_NAME = 'Z1NOTE_ALL_AMOUNTS' THEN
58 Z1NOTE_ALL_AMOUNTS := C.LANG_NAME;
59 ELSIF C.ITEM_NAME = 'Z1PARAMETERS' THEN
60 Z1PARAMETERS := C.LANG_NAME;
61 ELSIF C.ITEM_NAME = 'Z1REF_CCY' THEN
62 Z1REF_CCY := C.LANG_NAME;
63 ELSIF C.ITEM_NAME = 'Z1SHOW_TRANSACTIONS' THEN
64 Z1SHOW_TRANSACTIONS := C.LANG_NAME;
65 ELSIF C.ITEM_NAME = 'Z2AVG_RATE' THEN
66 Z2AVG_RATE := C.LANG_NAME;
67 ELSIF C.ITEM_NAME = 'Z2AVG_RATE_SELL' THEN
68 Z2AVG_RATE_SELL := C.LANG_NAME;
69 ELSIF C.ITEM_NAME = 'Z2AVG_RATE_CROSS' THEN
70 Z2AVG_RATE_CROSS := C.LANG_NAME;
71 ELSIF C.ITEM_NAME = 'Z2BASE_BUY_AMT' THEN
72 Z2BASE_BUY_AMT := C.LANG_NAME;
73 ELSIF C.ITEM_NAME = 'Z2BASE_CCY' THEN
74 Z2BASE_CCY := C.LANG_NAME;
75 ELSIF C.ITEM_NAME = 'Z2BASE_NET_AMT' THEN
76 Z2BASE_NET_AMT := C.LANG_NAME;
77 ELSIF C.ITEM_NAME = 'Z2BASE_SALES_AMT' THEN
78 Z2BASE_SALES_AMT := C.LANG_NAME;
79 ELSIF C.ITEM_NAME = 'Z2BUY_AMT' THEN
80 Z2BUY_AMT := C.LANG_NAME;
81 ELSIF C.ITEM_NAME = 'Z2BUY_CCY' THEN
82 Z2BUY_CCY := C.LANG_NAME;
83 ELSIF C.ITEM_NAME = 'Z2CONTRA_BUY_AMT' THEN
84 Z2CONTRA_BUY_AMT := C.LANG_NAME;
85 ELSIF C.ITEM_NAME = 'Z2CONTRA_NET_AMT' THEN
86 Z2CONTRA_NET_AMT := C.LANG_NAME;
87 ELSIF C.ITEM_NAME = 'Z2CONTRA_SALES_AMT' THEN
88 Z2CONTRA_SALES_AMT := C.LANG_NAME;
89 ELSIF C.ITEM_NAME = 'Z2CPARTY' THEN
90 Z2CPARTY := C.LANG_NAME;
91 ELSIF C.ITEM_NAME = 'Z2CURRENCY' THEN
92 Z2CURRENCY := C.LANG_NAME;
93 ELSIF C.ITEM_NAME = 'Z2DEAL_NOS' THEN
94 Z2DEAL_NOS := C.LANG_NAME;
95 ELSIF C.ITEM_NAME = 'Z2END_OF_REPORT' THEN
96 Z2END_OF_REPORT := C.LANG_NAME;
97 ELSIF C.ITEM_NAME = 'Z2RATE' THEN
98 Z2RATE := C.LANG_NAME;
99 ELSIF C.ITEM_NAME = 'Z2SELL_AMT' THEN
100 Z2SELL_AMT := C.LANG_NAME;
101 ELSIF C.ITEM_NAME = 'Z2SELL_CCY' THEN
102 Z2SELL_CCY := C.LANG_NAME;
103 ELSIF C.ITEM_NAME = 'Z2TRANS_DATE' THEN
104 Z2TRANS_DATE := C.LANG_NAME;
105 ELSIF C.ITEM_NAME = 'Z2VALUE_DATE' THEN
106 Z2VALUE_DATE := C.LANG_NAME;
107 ELSIF C.ITEM_NAME = 'Z2PAGE' THEN
108 Z2PAGE := C.LANG_NAME;
109 ELSIF C.ITEM_NAME = 'REPORT_DATE' THEN
110 REPORT_DATE := C.LANG_NAME;
111 ELSIF C.ITEM_NAME = 'Z1P_FACTOR' THEN
112 Z1P_FACTOR := C.LANG_NAME;
113 ELSIF C.ITEM_NAME = 'Z1COMPANY' THEN
114 Z1COMPANY := C.LANG_NAME;
115 END IF;
116 END LOOP;
117 RETURN (TRUE);
118 END BEFOREREPORT;
119
120 FUNCTION AVG_RATE1FORMULA(BASE_BUY_AMT IN NUMBER
121 ,CONTRA_SALES_AMT IN NUMBER) RETURN NUMBER IS
122 BEGIN
123 IF BASE_BUY_AMT <> 0 THEN
124 RETURN (ROUND(ABS(CONTRA_SALES_AMT / BASE_BUY_AMT)
125 ,2));
126 ELSE
127 RETURN (NULL);
128 END IF;
129 RETURN NULL;
130 END AVG_RATE1FORMULA;
131
132 FUNCTION AVG_RATE2FORMULA(BASE_SALES_AMT IN NUMBER
133 ,CONTRA_BUY_AMT IN NUMBER) RETURN NUMBER IS
134 BEGIN
135 IF BASE_SALES_AMT <> 0 THEN
136 RETURN (ROUND(ABS(CONTRA_BUY_AMT / BASE_SALES_AMT)
137 ,2));
138 ELSE
139 RETURN (NULL);
140 END IF;
141 RETURN NULL;
142 END AVG_RATE2FORMULA;
143
144 FUNCTION CALCFORMULA(BASE_BUY_AMT IN NUMBER
145 ,BASE_SALES_AMT IN NUMBER
146 ,CONTRA_BUY_AMT IN NUMBER
147 ,CONTRA_SALES_AMT IN NUMBER) RETURN VARCHAR2 IS
148 BEGIN
149 BASE_NET_AMT := NVL(BASE_BUY_AMT
150 ,0) + NVL(BASE_SALES_AMT
151 ,0);
152 CONTRA_NET_AMT := NVL(CONTRA_BUY_AMT
153 ,0) + NVL(CONTRA_SALES_AMT
154 ,0);
155 IF NVL(BASE_NET_AMT
156 ,0) <> 0 THEN
157 AVG_RATE3 := ROUND(ABS(CONTRA_NET_AMT / BASE_NET_AMT)
158 ,4);
159 ELSE
160 AVG_RATE3 := NULL;
161 END IF;
162 RETURN ('');
163 END CALCFORMULA;
164
165 FUNCTION AFTERPFORM RETURN BOOLEAN IS
166 TEMP NUMBER(15) := 1000;
167 BEGIN
168 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
169
170 IF P_FACTOR IS NOT NULL THEN
171 SELECT
172 SUBSTRB(DESCRIPTION
173 ,1
174 ,20),
175 DECODE(SUBSTR(P_FACTOR
176 ,1
177 ,1)
178 ,'U'
179 ,1
180 ,'T'
181 ,1000
182 ,'M'
183 ,1000000
184 ,'B'
185 ,100000000)
186 INTO
187 LP_FACTOR_DESC
188 ,TEMP
189 FROM
190 FND_LOOKUPS
191 WHERE LOOKUP_TYPE = 'XTR_FACTOR'
192 AND LOOKUP_CODE = SUBSTR(P_FACTOR
193 ,1
194 ,1);
195 ELSE
196 SELECT
197 SUBSTRB(DESCRIPTION
198 ,1
199 ,20)
200 INTO
201 LP_FACTOR_DESC
202 FROM
203 FND_LOOKUPS
204 WHERE LOOKUP_TYPE = 'XTR_FACTOR'
205 AND LOOKUP_CODE = 'T';
206 TEMP := 1000;
207 END IF;
208 AMT_UNIT2 := TEMP;
209 SELECT
210 CP.USER_CONCURRENT_PROGRAM_NAME
211 INTO
212 REPORT_SHORT_NAME
213 FROM
214 FND_CONCURRENT_PROGRAMS_VL CP,
215 FND_CONCURRENT_REQUESTS CR
216 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
217 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
218 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
219 P_REFERENCE_DATE_FROM_1 := TO_CHAR(TO_DATE(P_REFERENCE_DATE_FROM
220 ,'YYYY/MM/DD HH24:MI:SS')
221 ,'DD-MON-YY');
222 P_REFERENCE_DATE_TO_1 := TO_CHAR(TO_DATE(P_REFERENCE_DATE_TO
223 ,'YYYY/MM/DD HH24:MI:SS')
224 ,'DD-MON-YY');
225 CURRENCY_A2 := P_REFERENCE_CURRENCY;
226 CURRENCY_B2 := P_COUNTER_CURRENCY;
227 MATURING_FROM2 := P_REFERENCE_DATE_FROM_1;
228 MATURING_TO2 := P_REFERENCE_DATE_TO_1;
229 SHOW_DETAILS2 := P_SHOW_CONTRACTS;
230 SELECT
231 UPPER(MEANING)
232 INTO
233 DATE_BASE2
234 FROM
235 FND_LOOKUPS
236 WHERE LOOKUP_CODE = P_DATE_TYPE;
237 REPORT_SHORT_NAME := nvl(REPORT_SHORT_NAME,'AVG FX RATES FOR DEALS');
238 REPORT_SHORT_NAME := substr(REPORT_SHORT_NAME,1,instr(REPORT_SHORT_NAME,' (XML)'));
239 RETURN (TRUE);
240 END AFTERPFORM;
241
242 FUNCTION AFTERREPORT RETURN BOOLEAN IS
243 BEGIN
244 RETURN (TRUE);
245 END AFTERREPORT;
246
247 FUNCTION DATA_BASE_ON_MDFORMULA RETURN VARCHAR2 IS
248 BEGIN
249 IF NVL(DATE_BASE2
250 ,'M') = 'M' THEN
251 RETURN ('Maturity');
252 ELSE
253 RETURN ('Dealt');
254 END IF;
255 RETURN NULL;
256 END DATA_BASE_ON_MDFORMULA;
257
258 FUNCTION BASE_NET_AMT_P RETURN NUMBER IS
259 BEGIN
260 RETURN BASE_NET_AMT;
261 END BASE_NET_AMT_P;
262
263 FUNCTION CONTRA_NET_AMT_P RETURN NUMBER IS
264 BEGIN
265 RETURN CONTRA_NET_AMT;
266 END CONTRA_NET_AMT_P;
267
268 FUNCTION AVG_RATE3_P RETURN NUMBER IS
269 BEGIN
270 RETURN AVG_RATE3;
271 END AVG_RATE3_P;
272
273 FUNCTION CP_PARA_P RETURN VARCHAR2 IS
274 BEGIN
275 RETURN CP_PARA;
276 END CP_PARA_P;
277
278 END XTR_XTRFXAVG_XMLP_PKG;
279