[Home] [Help]
PACKAGE BODY: APPS.GL_EBI_PUB
Source
1 PACKAGE BODY GL_EBI_PUB AS
2 /* $Header: gleipub.pls 120.2 2011/11/29 13:02:33 sommukhe ship $ */
3
4 PROCEDURE process_currency_exc_rate_list(
5 p_api_version IN VARCHAR2
6 ,p_commit IN VARCHAR2
7 ,p_integration_id IN VARCHAR2
8 ,p_lang_code IN VARCHAR2
9 ,p_name_value_tbl IN gl_ebi_name_value_tbl
10 ,p_daily_rates_tbl IN gl_ebi_daily_rates_tbl
11 ,x_request_id OUT NOCOPY NUMBER
12 ,x_return_status OUT NOCOPY VARCHAR2
13 ,x_msg_count OUT NOCOPY NUMBER
14 ,x_msg_data OUT NOCOPY VARCHAR2
15 )
16 IS
17 l_request_id NUMBER:=0;
18 l_count NUMBER:=0;
19 l_sorted_table gl_ebi_daily_rates_tbl;
20 l_param_name VARCHAR2(30):='AUTO_ADJUST_TO_DATE';
21 l_auto_adjust_to_date VARCHAR2(30):='FALSE';
22 BEGIN
23 FND_MSG_PUB.initialize();
24 x_return_status := FND_API.G_RET_STS_SUCCESS;
25
26 IF(p_name_value_tbl IS NOT NULL AND p_name_value_tbl.COUNT > 0) THEN
27 FOR i IN p_name_value_tbl.FIRST..p_name_value_tbl.LAST LOOP
28 IF (UPPER(p_name_value_tbl(i).param_name) = UPPER(l_param_name))THEN
29 l_auto_adjust_to_date := p_name_value_tbl(i).param_value;
30 END IF;
31 END LOOP;
32 END IF;
33
34
35 IF p_daily_rates_tbl IS NOT NULL AND p_daily_rates_tbl.COUNT >0 THEN
36 FOR i IN p_daily_rates_tbl.FIRST..p_daily_rates_tbl.LAST LOOP
37 l_count := 0;
38
39 SELECT count(1) INTO l_count
40 FROM GL_DAILY_RATES_INTERFACE
41 WHERE FROM_CURRENCY = p_daily_rates_tbl(i).TO_CURRENCY
42 AND TO_CURRENCY = p_daily_rates_tbl(i).FROM_CURRENCY
43 AND FROM_CONVERSION_DATE = p_daily_rates_tbl(i).FROM_CONVERSION_DATE
44 AND TO_CONVERSION_DATE = p_daily_rates_tbl(i).TO_CONVERSION_DATE
45 AND USER_CONVERSION_TYPE = p_daily_rates_tbl(i).USER_CONVERSION_TYPE
46 AND BATCH_NUMBER = p_integration_id;
47
48 IF (l_count > 0) THEN
49 UPDATE GL_DAILY_RATES_INTERFACE
50 SET INVERSE_CONVERSION_RATE = p_daily_rates_tbl(i).CONVERSION_RATE
51 WHERE FROM_CURRENCY = p_daily_rates_tbl(i).TO_CURRENCY
52 AND TO_CURRENCY = p_daily_rates_tbl(i).FROM_CURRENCY
53 AND FROM_CONVERSION_DATE = p_daily_rates_tbl(i).FROM_CONVERSION_DATE
54 AND TO_CONVERSION_DATE = p_daily_rates_tbl(i).TO_CONVERSION_DATE
55 AND USER_CONVERSION_TYPE = p_daily_rates_tbl(i).USER_CONVERSION_TYPE
56 AND BATCH_NUMBER = p_integration_id;
57
58 ELSE
59 INSERT INTO GL_DAILY_RATES_INTERFACE(
60 FROM_CURRENCY,
61 TO_CURRENCY,
62 FROM_CONVERSION_DATE,
63 TO_CONVERSION_DATE,
64 USER_CONVERSION_TYPE,
65 CONVERSION_RATE,
66 MODE_FLAG,
67 INVERSE_CONVERSION_RATE,
68 USER_ID,
69 LAUNCH_RATE_CHANGE,
70 ERROR_CODE,
71 CONTEXT,
72 ATTRIBUTE1,
73 ATTRIBUTE2,
74 ATTRIBUTE3,
75 ATTRIBUTE4,
76 ATTRIBUTE5,
77 ATTRIBUTE6,
78 ATTRIBUTE7,
79 ATTRIBUTE8,
80 ATTRIBUTE9,
81 ATTRIBUTE10,
82 ATTRIBUTE11,
83 ATTRIBUTE12,
84 ATTRIBUTE13,
85 ATTRIBUTE14,
86 USED_FOR_AB_TRANSLATION,
87 ATTRIBUTE15,
88 BATCH_NUMBER
89 )
90 VALUES(
91 p_daily_rates_tbl(i).FROM_CURRENCY,
92 p_daily_rates_tbl(i).TO_CURRENCY,
93 p_daily_rates_tbl(i).FROM_CONVERSION_DATE,
94 p_daily_rates_tbl(i).TO_CONVERSION_DATE,
95 p_daily_rates_tbl(i).USER_CONVERSION_TYPE,
96 p_daily_rates_tbl(i).CONVERSION_RATE,
97 nvl(p_daily_rates_tbl(i).MODE_FLAG,'I'),
98 p_daily_rates_tbl(i).INVERSE_CONVERSION_RATE,
99 fnd_global.user_id,
100 p_daily_rates_tbl(i).LAUNCH_RATE_CHANGE,
101 p_daily_rates_tbl(i).ERROR_CODE,
102 p_daily_rates_tbl(i).CONTEXT,
103 p_daily_rates_tbl(i).ATTRIBUTE1,
104 p_daily_rates_tbl(i).ATTRIBUTE2,
105 p_daily_rates_tbl(i).ATTRIBUTE3,
106 p_daily_rates_tbl(i).ATTRIBUTE4,
107 p_daily_rates_tbl(i).ATTRIBUTE5,
108 p_daily_rates_tbl(i).ATTRIBUTE6,
109 p_daily_rates_tbl(i).ATTRIBUTE7,
110 p_daily_rates_tbl(i).ATTRIBUTE8,
111 p_daily_rates_tbl(i).ATTRIBUTE9,
112 p_daily_rates_tbl(i).ATTRIBUTE10,
113 p_daily_rates_tbl(i).ATTRIBUTE11,
114 p_daily_rates_tbl(i).ATTRIBUTE12,
115 p_daily_rates_tbl(i).ATTRIBUTE13,
116 p_daily_rates_tbl(i).ATTRIBUTE14,
117 p_daily_rates_tbl(i).USED_FOR_AB_TRANSLATION,
118 p_daily_rates_tbl(i).ATTRIBUTE15,
119 p_integration_id
120 );
121 END IF;
122 END LOOP;
123 END IF;
124
125 IF UPPER(l_auto_adjust_to_date) = 'TRUE' THEN
126 SELECT gl_ebi_daily_rates_obj(FROM_CURRENCY,
127 TO_CURRENCY,
128 FROM_CONVERSION_DATE,
129 TO_CONVERSION_DATE,
130 USER_CONVERSION_TYPE,
131 CONVERSION_RATE,
132 MODE_FLAG,
133 INVERSE_CONVERSION_RATE,
134 USER_ID,
135 LAUNCH_RATE_CHANGE,
136 ERROR_CODE,
137 CONTEXT,
138 ATTRIBUTE1,
139 ATTRIBUTE2,
140 ATTRIBUTE3,
141 ATTRIBUTE4,
142 ATTRIBUTE5,
143 ATTRIBUTE6,
144 ATTRIBUTE7,
145 ATTRIBUTE8,
146 ATTRIBUTE9,
147 ATTRIBUTE10,
148 ATTRIBUTE11,
149 ATTRIBUTE12,
150 ATTRIBUTE13,
151 ATTRIBUTE14,
152 USED_FOR_AB_TRANSLATION,
153 ATTRIBUTE15)
154 BULK COLLECT INTO l_sorted_table
155 FROM GL_DAILY_RATES_INTERFACE
156 WHERE BATCH_NUMBER = p_integration_id
157 ORDER BY FROM_CURRENCY,TO_CURRENCY,USER_CONVERSION_TYPE,FROM_CONVERSION_DATE;
158
159 IF l_sorted_table IS NOT NULL AND l_sorted_table.COUNT >0 THEN
160 FOR i IN l_sorted_table.FIRST..l_sorted_table.LAST LOOP
161 IF (i <>l_sorted_table.LAST) AND (l_sorted_table(i).FROM_CURRENCY = l_sorted_table(i+1).FROM_CURRENCY
162 AND l_sorted_table(i).TO_CURRENCY = l_sorted_table(i+1).TO_CURRENCY
163 AND l_sorted_table(i).USER_CONVERSION_TYPE = l_sorted_table(i+1).USER_CONVERSION_TYPE) THEN
164
165 UPDATE GL_DAILY_RATES_INTERFACE
166 SET TO_CONVERSION_DATE = l_sorted_table(i+1).FROM_CONVERSION_DATE-1
167 WHERE FROM_CURRENCY = l_sorted_table(i).FROM_CURRENCY
168 AND TO_CURRENCY = l_sorted_table(i).TO_CURRENCY
169 AND FROM_CONVERSION_DATE = l_sorted_table(i).FROM_CONVERSION_DATE
170 AND TO_CONVERSION_DATE = l_sorted_table(i).TO_CONVERSION_DATE
171 AND USER_CONVERSION_TYPE = l_sorted_table(i).USER_CONVERSION_TYPE
172 AND BATCH_NUMBER = p_integration_id;
173
174 END IF;
175 END LOOP;
176 END IF;
177 END IF;
178
179 l_request_id := fnd_request.submit_request('SQLGL', 'GLDRICCP', '', '', FALSE,p_integration_id,
180 CHR(0), '', '', '', '', '', '', '',
181 '', '', '', '', '', '', '', '', '', '',
182 '', '', '', '', '', '', '', '', '', '',
183 '', '', '', '', '', '', '', '', '', '',
184 '', '', '', '', '', '', '', '', '', '',
185 '', '', '', '', '', '', '', '', '', '',
186 '', '', '', '', '', '', '', '', '', '',
187 '', '', '', '', '', '', '', '', '', '',
188 '', '', '', '', '', '', '', '', '', '',
189 '', '', '', '', '', '', '', '', '', '',
190 '');
191 x_request_id := l_request_id;
192
193 IF p_commit = FND_API.g_true THEN
194 COMMIT;
195 END IF;
196
197 IF l_request_id = 0 THEN
198 FND_MESSAGE.RETRIEVE(x_msg_data);
199 RAISE FND_API.g_exc_error;
200 END IF;
201
202
203 EXCEPTION
204 WHEN FND_API.g_exc_error THEN
205 x_return_status := FND_API.g_ret_sts_error;
206 WHEN OTHERS THEN
207 x_return_status := FND_API.g_ret_sts_unexp_error;
208 x_msg_data := SQLERRM ||' at process_currency_exc_rate_list';
209 END process_currency_exc_rate_list;
210
211 PROCEDURE purge_currency_exc_rate_list(
212 p_api_version IN VARCHAR2
213 ,p_commit IN VARCHAR2
214 ,p_integration_id IN VARCHAR2
215 ,p_lang_code IN VARCHAR2
216 ,x_daily_rates_tbl OUT NOCOPY gl_ebi_daily_rates_tbl
217 ,x_return_status OUT NOCOPY VARCHAR2
218 ,x_msg_count OUT NOCOPY NUMBER
219 ,x_msg_data OUT NOCOPY VARCHAR2
220 )
221 IS
222
223 CURSOR c_get_err_rows(p_integration_id VARCHAR2) IS
224 SELECT gl_ebi_daily_rates_obj(FROM_CURRENCY,
225 TO_CURRENCY,
226 FROM_CONVERSION_DATE,
227 TO_CONVERSION_DATE,
228 USER_CONVERSION_TYPE,
229 CONVERSION_RATE,
230 MODE_FLAG,
231 INVERSE_CONVERSION_RATE,
232 USER_ID,
233 LAUNCH_RATE_CHANGE,
234 ERROR_CODE,
235 CONTEXT,
236 ATTRIBUTE1,
237 ATTRIBUTE2,
238 ATTRIBUTE3,
239 ATTRIBUTE4,
240 ATTRIBUTE5,
241 ATTRIBUTE6,
242 ATTRIBUTE7,
243 ATTRIBUTE8,
244 ATTRIBUTE9,
245 ATTRIBUTE10,
246 ATTRIBUTE11,
247 ATTRIBUTE12,
248 ATTRIBUTE13,
249 ATTRIBUTE14,
250 USED_FOR_AB_TRANSLATION,
251 ATTRIBUTE15)
252 FROM GL_DAILY_RATES_INTERFACE
253 WHERE
254 BATCH_NUMBER = p_integration_id;
255
256 l_err_msg VARCHAR2(32000);
257 BEGIN
258 x_return_status := FND_API.G_RET_STS_SUCCESS;
259
260 OPEN c_get_err_rows(p_integration_id);
261 FETCH c_get_err_rows BULK COLLECT INTO x_daily_rates_tbl;
262 CLOSE c_get_err_rows;
263
264 IF x_daily_rates_tbl IS NOT NULL AND x_daily_rates_tbl.COUNT >0 THEN
265 x_return_status := FND_API.g_ret_sts_error;
266
267 FOR i IN x_daily_rates_tbl.FIRST..x_daily_rates_tbl.LAST LOOP
268
269 l_err_msg := 'From Currency-'||x_daily_rates_tbl(i).FROM_CURRENCY||' To Currency-'||
270 x_daily_rates_tbl(i).TO_CURRENCY||' Conversion Type-'||x_daily_rates_tbl(i).USER_CONVERSION_TYPE||
271 ' Failed. Reason-'|| x_daily_rates_tbl(i).ERROR_CODE;
272
273 IF x_msg_data IS NULL THEN
274 x_msg_data := l_err_msg || FND_GLOBAL.newline;
275 ELSE
276 x_msg_data := x_msg_data || l_err_msg || FND_GLOBAL.newline;
277 END IF;
278
279 END LOOP;
280
281 DELETE FROM gl_daily_rates_interface WHERE batch_number = p_integration_id;
282
283 END IF;
284
285 IF p_commit = FND_API.g_true THEN
286 COMMIT;
287 END IF;
288
289 EXCEPTION
290 WHEN OTHERS THEN
291 x_return_status := FND_API.g_ret_sts_unexp_error;
292 x_msg_data := SQLERRM ||' at purge_currency_exc_rate_list';
293
294 IF (c_get_err_rows%ISOPEN) THEN
295 CLOSE c_get_err_rows;
296 END IF;
297 END purge_currency_exc_rate_list;
298
299 PROCEDURE process_accounting_period_list(
300 p_api_version IN VARCHAR2
301 ,p_commit IN VARCHAR2
302 ,p_acct_period_tbl IN gl_ebi_acct_period_tbl
303 ,x_return_status OUT NOCOPY VARCHAR2
304 ,x_msg_count OUT NOCOPY NUMBER
305 ,x_msg_data OUT NOCOPY VARCHAR2
306 )
307
308 IS
309
310 CURSOR c_resp_id (cp_resp_name VARCHAR2) IS
311 SELECT responsibility_id
312 FROM fnd_responsibility_vl
313 WHERE (responsibility_name = trim(cp_resp_name));
314
315 l_err_msg VARCHAR2(32000);
316 l_ret_status VARCHAR2(10);
317 l_ret_code NUMBER;
318 l_responsibility VARCHAR2(240);
319 l_user_id NUMBER;
320 l_resp_id NUMBER;
321 l_apps_id NUMBER;
322
323 BEGIN
324 x_return_status := FND_API.G_RET_STS_SUCCESS;
325
326 IF p_acct_period_tbl IS NOT NULL AND p_acct_period_tbl.COUNT >0 THEN
327
328 FOR i IN p_acct_period_tbl.FIRST..p_acct_period_tbl.LAST LOOP
329
330 l_responsibility := p_acct_period_tbl(i).RESPONSIBILITY;
331 IF l_responsibility IS NOT NULL THEN
332 l_user_id := fnd_global.user_id;
333 OPEN c_resp_id (l_responsibility);
334 FETCH c_resp_id INTO l_resp_id;
335 CLOSE c_resp_id;
336
337 l_apps_id := fnd_global.resp_appl_id; --101
338 fnd_global.apps_initialize (l_user_id, l_resp_id, l_apps_id);
339 END IF;
340
341 GL_PERIOD_STATUS_SYNC_PUB.period_status_sync(
342 p_ledger_short_name => p_acct_period_tbl(i).ledger_short_name
343 ,p_start_date => p_acct_period_tbl(i).start_date
344 ,p_end_date => p_acct_period_tbl(i).end_date
345 ,x_return_status => l_ret_status
346 ,errbuf => l_err_msg
347 ,retcode => l_ret_code
348 );
349
350 IF l_ret_status <> FND_API.g_ret_sts_success THEN
351 x_return_status := FND_API.g_ret_sts_error;
352 IF x_msg_data IS NULL THEN
353 x_msg_data := l_err_msg || FND_GLOBAL.newline;
354 ELSE
355 x_msg_data := x_msg_data || l_err_msg || FND_GLOBAL.newline;
356 END IF;
357 END IF;
358
359 END LOOP;
360 END IF;
361
362 IF p_commit = FND_API.g_true THEN
363 COMMIT;
364 END IF;
365
366 EXCEPTION
367 WHEN OTHERS THEN
368 x_return_status := FND_API.g_ret_sts_unexp_error;
369 x_msg_data := SQLERRM ||' at process_accounting_period_list';
370 END process_accounting_period_list;
371
372 END GL_EBI_PUB;