[Home] [Help]
PACKAGE BODY: APPS.AS_MULTI_CURRENCIES_PKG
Source
1 PACKAGE BODY AS_MULTI_CURRENCIES_PKG as
2 /* $Header: asxtmcpb.pls 120.2 2005/06/14 01:33:02 appldev $ */
3 -- Start of Comments
4 -- Package name : AS_MULTI_CURRENCIES_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_MULTI_CURRENCIES_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxtmcpb.pls';
13
14 PROCEDURE Insert_Type_Mappings(
15 p_TYPE_MAPPINGS_TBL IN TYPE_MAPPINGS_Tbl_Type
16 DEFAULT G_MISS_TYPE_MAPPINGS_TBL,
17 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
18 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
19 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
20 IS
21 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Type_Mappings';
22 i NUMBER;
23 l_last NUMBER;
24 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
25 l_module CONSTANT VARCHAR2(255) := 'as.plsql.mcpk.Insert_Type_Mappings';
26 BEGIN
27 -- Standard Start of API savepoint
28 SAVEPOINT INSERT_TYPE_MAPPINGS_PUB;
29
30 -- Debug Message
31 IF l_debug THEN
32 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
33 l_api_name || ' Start');
34 END IF;
35
36 -- Initialize API return status to SUCCESS
37 x_return_status := FND_API.G_RET_STS_SUCCESS;
38
39 i := p_TYPE_MAPPINGS_TBL.first;
40 l_last := p_TYPE_MAPPINGS_TBL.last;
41 WHILE i <= l_last
42 LOOP
43 INSERT INTO AS_MC_TYPE_MAPPINGS(
44 PERIOD_SET_NAME,
45 PERIOD_TYPE,
46 CONVERSION_TYPE,
47 DESCRIPTION,
48 UPDATEABLE_FLAG,
49 DELETEABLE_FLAG,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 CREATION_DATE,
53 CREATED_BY,
54 LAST_UPDATE_LOGIN
55 --,
56 -- SECURITY_GROUP_ID
57 ) VALUES (
58 decode( p_TYPE_MAPPINGS_TBL(i).PERIOD_SET_NAME,
59 FND_API.G_MISS_CHAR, NULL,
60 p_TYPE_MAPPINGS_TBL(i).PERIOD_SET_NAME),
61 decode( p_TYPE_MAPPINGS_TBL(i).PERIOD_TYPE,
62 FND_API.G_MISS_CHAR, NULL,
63 p_TYPE_MAPPINGS_TBL(i).PERIOD_TYPE),
64 decode( p_TYPE_MAPPINGS_TBL(i).CONVERSION_TYPE,
65 FND_API.G_MISS_CHAR, NULL,
66 p_TYPE_MAPPINGS_TBL(i).CONVERSION_TYPE),
67 decode( p_TYPE_MAPPINGS_TBL(i).DESCRIPTION,
68 FND_API.G_MISS_CHAR, NULL,
69 p_TYPE_MAPPINGS_TBL(i).DESCRIPTION),
70 decode( p_TYPE_MAPPINGS_TBL(i).UPDATEABLE_FLAG,
71 FND_API.G_MISS_CHAR, NULL,
72 p_TYPE_MAPPINGS_TBL(i).UPDATEABLE_FLAG),
73 decode( p_TYPE_MAPPINGS_TBL(i).DELETEABLE_FLAG,
74 FND_API.G_MISS_CHAR, NULL,
75 p_TYPE_MAPPINGS_TBL(i).DELETEABLE_FLAG),
76 SYSDATE,
77 FND_GLOBAL.USER_ID,
78 SYSDATE,
79 FND_GLOBAL.USER_ID,
80 FND_GLOBAL.CONC_LOGIN_ID
81 --,
82 -- decode( p_TYPE_MAPPINGS_TBL(i).SECURITY_GROUP_ID,
83 -- FND_API.G_MISS_NUM, NULL,
84 -- p_TYPE_MAPPINGS_TBL(i).SECURITY_GROUP_ID)
85 );
86 i := i + 1;
87 END LOOP;
88
89 -- Standard call to get message count and if count is 1, get message info.
90 FND_MSG_PUB.Count_And_Get
91 ( p_count => x_msg_count,
92 p_data => x_msg_data
93 );
94 EXCEPTION
95 WHEN FND_API.G_EXC_ERROR THEN
96 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
97 P_MODULE => l_module
98 ,P_API_NAME => L_API_NAME
99 ,P_PKG_NAME => G_PKG_NAME
100 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
101 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
102 ,X_MSG_COUNT => X_MSG_COUNT
103 ,X_MSG_DATA => X_MSG_DATA
104 ,X_RETURN_STATUS => X_RETURN_STATUS);
105
106 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
107 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
108 P_MODULE => l_module
109 ,P_API_NAME => L_API_NAME
110 ,P_PKG_NAME => G_PKG_NAME
111 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
112 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
113 ,X_MSG_COUNT => X_MSG_COUNT
114 ,X_MSG_DATA => X_MSG_DATA
115 ,X_RETURN_STATUS => X_RETURN_STATUS);
116
117 WHEN OTHERS THEN
118 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
119 P_MODULE => l_module
120 ,P_API_NAME => L_API_NAME
121 ,P_PKG_NAME => G_PKG_NAME
122 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
123 ,P_SQLCODE => SQLCODE
124 ,P_SQLERRM => SQLERRM
125 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
126 ,X_MSG_COUNT => X_MSG_COUNT
127 ,X_MSG_DATA => X_MSG_DATA
128 ,X_RETURN_STATUS => X_RETURN_STATUS);
129 End Insert_Type_Mappings;
130
131 PROCEDURE Update_Type_Mappings(
132 p_TYPE_MAPPINGS_TBL IN TYPE_MAPPINGS_Tbl_Type
133 DEFAULT G_MISS_TYPE_MAPPINGS_TBL,
134 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
135 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
136 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
137 IS
138 l_api_name CONSTANT VARCHAR2(30) := 'Update_Type_Mappings';
139 i NUMBER;
140 l_last NUMBER;
141 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
142 l_module CONSTANT VARCHAR2(255) := 'as.plsql.mcpk.Update_Type_Mappings';
143 BEGIN
144 -- Standard Start of API savepoint
145 SAVEPOINT UPDATE_TYPE_MAPPINGS_PUB;
146
147 -- Debug Message
148 IF l_debug THEN
149 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
150 l_api_name || ' Start');
151 END IF;
152
153 -- Initialize API return status to SUCCESS
154 x_return_status := FND_API.G_RET_STS_SUCCESS;
155
156 i := p_TYPE_MAPPINGS_TBL.first;
157 l_last := p_TYPE_MAPPINGS_TBL.last;
158
159 WHILE i <= l_last
160 LOOP
161 IF l_debug THEN
162 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
163 'i=' || i);
164 END IF;
165 UPDATE AS_MC_TYPE_MAPPINGS
166 SET
167 PERIOD_SET_NAME = decode( p_TYPE_MAPPINGS_TBL(i).PERIOD_SET_NAME,
168 FND_API.G_MISS_CHAR, PERIOD_SET_NAME,
169 p_TYPE_MAPPINGS_TBL(i).PERIOD_SET_NAME),
170 PERIOD_TYPE = decode( p_TYPE_MAPPINGS_TBL(i).PERIOD_TYPE,
171 FND_API.G_MISS_CHAR, PERIOD_TYPE,
172 p_TYPE_MAPPINGS_TBL(i).PERIOD_TYPE),
173 CONVERSION_TYPE = decode( p_TYPE_MAPPINGS_TBL(i).CONVERSION_TYPE,
174 FND_API.G_MISS_CHAR, CONVERSION_TYPE,
175 p_TYPE_MAPPINGS_TBL(i).CONVERSION_TYPE),
176 DESCRIPTION = decode( p_TYPE_MAPPINGS_TBL(i).DESCRIPTION,
177 FND_API.G_MISS_CHAR, DESCRIPTION,
178 p_TYPE_MAPPINGS_TBL(i).DESCRIPTION),
179 UPDATEABLE_FLAG = decode( p_TYPE_MAPPINGS_TBL(i).UPDATEABLE_FLAG,
180 FND_API.G_MISS_CHAR, UPDATEABLE_FLAG,
181 p_TYPE_MAPPINGS_TBL(i).UPDATEABLE_FLAG),
182 DELETEABLE_FLAG = decode( p_TYPE_MAPPINGS_TBL(i).DELETEABLE_FLAG,
183 FND_API.G_MISS_CHAR, DELETEABLE_FLAG,
184 p_TYPE_MAPPINGS_TBL(i).DELETEABLE_FLAG),
185 LAST_UPDATE_DATE = SYSDATE,
186 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
187 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
188 --,
189 -- SECURITY_GROUP_ID = decode(
190 -- p_TYPE_MAPPINGS_TBL(i).SECURITY_GROUP_ID,
191 -- FND_API.G_MISS_NUM, SECURITY_GROUP_ID,
192 -- p_TYPE_MAPPINGS_TBL(i).SECURITY_GROUP_ID)
193 WHERE PERIOD_SET_NAME = p_TYPE_MAPPINGS_TBL(i).PERIOD_SET_NAME
194 AND PERIOD_TYPE = p_TYPE_MAPPINGS_TBL(i).PERIOD_TYPE;
195
196 IF (SQL%NOTFOUND) THEN
197 RAISE NO_DATA_FOUND;
198 END IF;
199 i := i + 1;
200 END LOOP;
201
202 -- Standard call to get message count and if count is 1, get message info.
203 FND_MSG_PUB.Count_And_Get
204 ( p_count => x_msg_count,
205 p_data => x_msg_data
206 );
207 EXCEPTION
208 WHEN FND_API.G_EXC_ERROR THEN
209 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
210 P_MODULE => l_module
211 ,P_API_NAME => L_API_NAME
212 ,P_PKG_NAME => G_PKG_NAME
213 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
214 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
215 ,X_MSG_COUNT => X_MSG_COUNT
216 ,X_MSG_DATA => X_MSG_DATA
217 ,X_RETURN_STATUS => X_RETURN_STATUS);
218
219 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
220 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
221 P_MODULE => l_module
222 ,P_API_NAME => L_API_NAME
223 ,P_PKG_NAME => G_PKG_NAME
224 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
225 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
226 ,X_MSG_COUNT => X_MSG_COUNT
227 ,X_MSG_DATA => X_MSG_DATA
228 ,X_RETURN_STATUS => X_RETURN_STATUS);
229
230 WHEN OTHERS THEN
231 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
232 P_MODULE => l_module
233 ,P_API_NAME => L_API_NAME
234 ,P_PKG_NAME => G_PKG_NAME
235 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
236 ,P_SQLCODE => SQLCODE
237 ,P_SQLERRM => SQLERRM
238 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
239 ,X_MSG_COUNT => X_MSG_COUNT
240 ,X_MSG_DATA => X_MSG_DATA
241 ,X_RETURN_STATUS => X_RETURN_STATUS);
242 END Update_Type_Mappings;
243
244 PROCEDURE Delete_Type_Mappings(
245 p_TYPE_MAPPINGS_TBL IN TYPE_MAPPINGS_Tbl_Type
246 DEFAULT G_MISS_TYPE_MAPPINGS_TBL,
247 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
248 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
249 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
250 IS
251 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Type_Mappings';
252 i NUMBER;
253 l_last NUMBER;
254 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
255 l_module CONSTANT VARCHAR2(255) := 'as.plsql.mcpk.Delete_Type_Mappings';
256 BEGIN
257 -- Standard Start of API savepoint
258 SAVEPOINT DELETE_TYPE_MAPPINGS_PUB;
259
260 -- Debug Message
261 IF l_debug THEN
262 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
263 l_api_name || ' Start');
264 END IF;
265
266 -- Initialize API return status to SUCCESS
267 x_return_status := FND_API.G_RET_STS_SUCCESS;
268
269 i := p_TYPE_MAPPINGS_TBL.first;
270 l_last := p_TYPE_MAPPINGS_TBL.last;
271 WHILE i <= l_last
272 LOOP
273 DELETE FROM AS_MC_TYPE_MAPPINGS
274 WHERE PERIOD_SET_NAME = p_TYPE_MAPPINGS_TBL(i).PERIOD_SET_NAME
275 AND PERIOD_TYPE = p_TYPE_MAPPINGS_TBL(i).PERIOD_TYPE;
276
277 IF (SQL%NOTFOUND) THEN
278 RAISE NO_DATA_FOUND;
279 END IF;
280 i := i + 1;
281 END LOOP;
282
283 -- Standard call to get message count and if count is 1, get message info.
284 FND_MSG_PUB.Count_And_Get
285 ( p_count => x_msg_count,
286 p_data => x_msg_data
287 );
288
289 EXCEPTION
290 WHEN FND_API.G_EXC_ERROR THEN
291 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
292 P_MODULE => l_module
293 ,P_API_NAME => L_API_NAME
294 ,P_PKG_NAME => G_PKG_NAME
295 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
296 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
297 ,X_MSG_COUNT => X_MSG_COUNT
298 ,X_MSG_DATA => X_MSG_DATA
299 ,X_RETURN_STATUS => X_RETURN_STATUS);
300
301 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
302 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
303 P_MODULE => l_module
304 ,P_API_NAME => L_API_NAME
305 ,P_PKG_NAME => G_PKG_NAME
306 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
307 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
308 ,X_MSG_COUNT => X_MSG_COUNT
309 ,X_MSG_DATA => X_MSG_DATA
310 ,X_RETURN_STATUS => X_RETURN_STATUS);
311
312 WHEN OTHERS THEN
313 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
314 P_MODULE => l_module
315 ,P_API_NAME => L_API_NAME
316 ,P_PKG_NAME => G_PKG_NAME
317 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
318 ,P_SQLCODE => SQLCODE
319 ,P_SQLERRM => SQLERRM
320 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
321 ,X_MSG_COUNT => X_MSG_COUNT
322 ,X_MSG_DATA => X_MSG_DATA
323 ,X_RETURN_STATUS => X_RETURN_STATUS);
324 END Delete_Type_Mappings;
325
326
327 PROCEDURE Insert_Period_Rates(
328 p_PERIOD_RATES_TBL IN PERIOD_RATES_Tbl_Type
329 DEFAULT G_MISS_PERIOD_RATES_TBL,
330 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
331 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
332 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
333 IS
334 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Period_Rates';
335 i NUMBER;
336 l_last NUMBER;
337 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
338 l_module CONSTANT VARCHAR2(255) := 'as.plsql.mcpk.Insert_Period_Rates';
339 BEGIN
340 -- Standard Start of API savepoint
341 SAVEPOINT INSERT_PERIOD_RATES_PUB;
342
343 -- Debug Message
344 IF l_debug THEN
345 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
346 l_api_name || ' Start');
347 END IF;
348
349 -- Initialize API return status to SUCCESS
350 x_return_status := FND_API.G_RET_STS_SUCCESS;
351
352 i := p_PERIOD_RATES_TBL.first;
353 l_last := p_PERIOD_RATES_TBL.last;
354 WHILE i <= l_last
355 LOOP
356 INSERT INTO GL_DAILY_RATES_INTERFACE(
357 FROM_CURRENCY,
358 TO_CURRENCY,
359 FROM_CONVERSION_DATE,
360 TO_CONVERSION_DATE,
361 USER_CONVERSION_TYPE,
362 CONVERSION_RATE,
363 MODE_FLAG,
364 INVERSE_CONVERSION_RATE
365 ) VALUES (
366 decode( p_PERIOD_RATES_TBL(i).FROM_CURRENCY,
367 FND_API.G_MISS_CHAR, NULL,
368 p_PERIOD_RATES_TBL(i).FROM_CURRENCY),
369 decode( p_PERIOD_RATES_TBL(i).TO_CURRENCY,
373 FND_API.G_MISS_DATE, NULL,
370 FND_API.G_MISS_CHAR, NULL,
371 p_PERIOD_RATES_TBL(i).TO_CURRENCY),
372 decode( p_PERIOD_RATES_TBL(i).CONVERSION_DATE,
374 p_PERIOD_RATES_TBL(i).CONVERSION_DATE),
375 decode( p_PERIOD_RATES_TBL(i).CONVERSION_DATE,
376 FND_API.G_MISS_DATE, NULL,
377 p_PERIOD_RATES_TBL(i).CONVERSION_DATE),
378 decode( p_PERIOD_RATES_TBL(i).CONVERSION_TYPE,
379 FND_API.G_MISS_CHAR, NULL,
380 p_PERIOD_RATES_TBL(i).CONVERSION_TYPE),
381 decode( p_PERIOD_RATES_TBL(i).CONVERSION_RATE,
382 FND_API.G_MISS_NUM, NULL,
383 p_PERIOD_RATES_TBL(i).CONVERSION_RATE),
384 'I',
385 NULL);
386
387 i := i + 1;
388 END LOOP;
389
390 -- Standard call to get message count and if count is 1, get message info.
391 FND_MSG_PUB.Count_And_Get
392 ( p_count => x_msg_count,
393 p_data => x_msg_data
394 );
395 EXCEPTION
396 WHEN FND_API.G_EXC_ERROR THEN
397 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
398 P_MODULE => l_module
399 ,P_API_NAME => L_API_NAME
400 ,P_PKG_NAME => G_PKG_NAME
401 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
402 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
403 ,X_MSG_COUNT => X_MSG_COUNT
404 ,X_MSG_DATA => X_MSG_DATA
405 ,X_RETURN_STATUS => X_RETURN_STATUS);
406
407 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
408 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
409 P_MODULE => l_module
410 ,P_API_NAME => L_API_NAME
411 ,P_PKG_NAME => G_PKG_NAME
412 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
413 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
414 ,X_MSG_COUNT => X_MSG_COUNT
415 ,X_MSG_DATA => X_MSG_DATA
416 ,X_RETURN_STATUS => X_RETURN_STATUS);
417
418 WHEN OTHERS THEN
419 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
420 P_MODULE => l_module
421 ,P_API_NAME => L_API_NAME
422 ,P_PKG_NAME => G_PKG_NAME
423 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
424 ,P_SQLCODE => SQLCODE
425 ,P_SQLERRM => SQLERRM
426 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
427 ,X_MSG_COUNT => X_MSG_COUNT
428 ,X_MSG_DATA => X_MSG_DATA
429 ,X_RETURN_STATUS => X_RETURN_STATUS);
430 End Insert_Period_Rates;
431
432
433
434 PROCEDURE Update_Period_Rates(
435 p_PERIOD_RATES_TBL IN PERIOD_RATES_Tbl_Type
436 DEFAULT G_MISS_PERIOD_RATES_TBL,
437 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
438 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
439 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
440 IS
441 l_api_name CONSTANT VARCHAR2(30) := 'Update_Period_Rates';
442 i NUMBER;
443 l_last NUMBER;
444 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
445 l_module CONSTANT VARCHAR2(255) := 'as.plsql.mcpk.Update_Period_Rates';
446 BEGIN
447 -- Standard Start of API savepoint
448 SAVEPOINT UPDATE_PERIOD_RATES_PUB;
449
450 -- Debug Message
451 IF l_debug THEN
452 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
453 l_api_name || ' Start');
454 END IF;
455
456 -- Initialize API return status to SUCCESS
457 x_return_status := FND_API.G_RET_STS_SUCCESS;
458
459 i := p_PERIOD_RATES_TBL.first;
460 l_last := p_PERIOD_RATES_TBL.last;
461 WHILE i <= l_last
462 LOOP
463 INSERT INTO GL_DAILY_RATES_INTERFACE(
464 FROM_CURRENCY,
465 TO_CURRENCY,
466 FROM_CONVERSION_DATE,
467 TO_CONVERSION_DATE,
468 USER_CONVERSION_TYPE,
469 CONVERSION_RATE,
470 MODE_FLAG,
471 INVERSE_CONVERSION_RATE
472 ) VALUES (
473 decode( p_PERIOD_RATES_TBL(i).FROM_CURRENCY,
474 FND_API.G_MISS_CHAR, NULL,
475 p_PERIOD_RATES_TBL(i).FROM_CURRENCY),
476 decode( p_PERIOD_RATES_TBL(i).TO_CURRENCY,
477 FND_API.G_MISS_CHAR, NULL,
478 p_PERIOD_RATES_TBL(i).TO_CURRENCY),
479 decode( p_PERIOD_RATES_TBL(i).CONVERSION_DATE,
480 FND_API.G_MISS_DATE, NULL,
481 p_PERIOD_RATES_TBL(i).CONVERSION_DATE),
482 decode( p_PERIOD_RATES_TBL(i).CONVERSION_DATE,
483 FND_API.G_MISS_DATE, NULL,
484 p_PERIOD_RATES_TBL(i).CONVERSION_DATE),
485 decode( p_PERIOD_RATES_TBL(i).CONVERSION_TYPE,
486 FND_API.G_MISS_CHAR, NULL,
487 p_PERIOD_RATES_TBL(i).CONVERSION_TYPE),
488 decode( p_PERIOD_RATES_TBL(i).CONVERSION_RATE,
492 NULL);
489 FND_API.G_MISS_NUM, NULL,
490 p_PERIOD_RATES_TBL(i).CONVERSION_RATE),
491 'I',
493
494 i := i + 1;
495 END LOOP;
496
497 -- Standard call to get message count and if count is 1, get message info.
498 FND_MSG_PUB.Count_And_Get
499 ( p_count => x_msg_count,
500 p_data => x_msg_data
501 );
502 EXCEPTION
503 WHEN FND_API.G_EXC_ERROR THEN
504 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
505 P_MODULE => l_module
506 ,P_API_NAME => L_API_NAME
507 ,P_PKG_NAME => G_PKG_NAME
508 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
509 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
510 ,X_MSG_COUNT => X_MSG_COUNT
511 ,X_MSG_DATA => X_MSG_DATA
512 ,X_RETURN_STATUS => X_RETURN_STATUS);
513
514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
515 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
516 P_MODULE => l_module
517 ,P_API_NAME => L_API_NAME
518 ,P_PKG_NAME => G_PKG_NAME
519 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
520 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
521 ,X_MSG_COUNT => X_MSG_COUNT
522 ,X_MSG_DATA => X_MSG_DATA
523 ,X_RETURN_STATUS => X_RETURN_STATUS);
524
525 WHEN OTHERS THEN
526 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
527 P_MODULE => l_module
528 ,P_API_NAME => L_API_NAME
529 ,P_PKG_NAME => G_PKG_NAME
530 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
531 ,P_SQLCODE => SQLCODE
532 ,P_SQLERRM => SQLERRM
533 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
534 ,X_MSG_COUNT => X_MSG_COUNT
535 ,X_MSG_DATA => X_MSG_DATA
536 ,X_RETURN_STATUS => X_RETURN_STATUS);
537 End Update_Period_Rates;
538
539
540
541 PROCEDURE Delete_Period_Rates(
542 p_PERIOD_RATES_TBL IN PERIOD_RATES_Tbl_Type
543 DEFAULT G_MISS_PERIOD_RATES_TBL,
544 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
545 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
546 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
547 IS
548 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Period_Rates';
549 i NUMBER;
550 l_last NUMBER;
551 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
552 l_module CONSTANT VARCHAR2(255) := 'as.plsql.mcpk.Delete_Period_Rates';
553 BEGIN
554 -- Standard Start of API savepoint
555 SAVEPOINT DELETE_PERIOD_RATES_PUB;
556
557 -- Debug Message
558 IF l_debug THEN
559 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
560 l_api_name || ' Start');
561 END IF;
562
563 -- Initialize API return status to SUCCESS
564 x_return_status := FND_API.G_RET_STS_SUCCESS;
565
566 i := p_PERIOD_RATES_TBL.first;
567 l_last := p_PERIOD_RATES_TBL.last;
568 WHILE i <= l_last
569 LOOP
570 INSERT INTO GL_DAILY_RATES_INTERFACE(
571 FROM_CURRENCY,
572 TO_CURRENCY,
573 FROM_CONVERSION_DATE,
574 TO_CONVERSION_DATE,
575 USER_CONVERSION_TYPE,
576 CONVERSION_RATE,
577 MODE_FLAG,
578 INVERSE_CONVERSION_RATE
579 ) VALUES (
580 decode( p_PERIOD_RATES_TBL(i).FROM_CURRENCY,
581 FND_API.G_MISS_CHAR, NULL,
582 p_PERIOD_RATES_TBL(i).FROM_CURRENCY),
583 decode( p_PERIOD_RATES_TBL(i).TO_CURRENCY,
584 FND_API.G_MISS_CHAR, NULL,
585 p_PERIOD_RATES_TBL(i).TO_CURRENCY),
586 decode( p_PERIOD_RATES_TBL(i).CONVERSION_DATE,
587 FND_API.G_MISS_DATE, NULL,
588 p_PERIOD_RATES_TBL(i).CONVERSION_DATE),
589 decode( p_PERIOD_RATES_TBL(i).CONVERSION_DATE,
590 FND_API.G_MISS_DATE, NULL,
591 p_PERIOD_RATES_TBL(i).CONVERSION_DATE),
592 decode( p_PERIOD_RATES_TBL(i).CONVERSION_TYPE,
593 FND_API.G_MISS_CHAR, NULL,
594 p_PERIOD_RATES_TBL(i).CONVERSION_TYPE),
595 decode( p_PERIOD_RATES_TBL(i).CONVERSION_RATE,
596 FND_API.G_MISS_NUM, NULL,
597 p_PERIOD_RATES_TBL(i).CONVERSION_RATE),
598 'D',
599 NULL);
600 i := i + 1;
601 END LOOP;
602
603 -- Standard call to get message count and if count is 1, get message info.
604 FND_MSG_PUB.Count_And_Get
605 ( p_count => x_msg_count,
606 p_data => x_msg_data
607 );
608 EXCEPTION
609 WHEN FND_API.G_EXC_ERROR THEN
610 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
611 P_MODULE => l_module
612 ,P_API_NAME => L_API_NAME
616 ,X_MSG_COUNT => X_MSG_COUNT
613 ,P_PKG_NAME => G_PKG_NAME
614 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
615 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
617 ,X_MSG_DATA => X_MSG_DATA
618 ,X_RETURN_STATUS => X_RETURN_STATUS);
619
620 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
621 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
622 P_MODULE => l_module
623 ,P_API_NAME => L_API_NAME
624 ,P_PKG_NAME => G_PKG_NAME
625 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
626 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
627 ,X_MSG_COUNT => X_MSG_COUNT
628 ,X_MSG_DATA => X_MSG_DATA
629 ,X_RETURN_STATUS => X_RETURN_STATUS);
630
631 WHEN OTHERS THEN
632 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
633 P_MODULE => l_module
634 ,P_API_NAME => L_API_NAME
635 ,P_PKG_NAME => G_PKG_NAME
636 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
637 ,P_SQLCODE => SQLCODE
638 ,P_SQLERRM => SQLERRM
639 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
640 ,X_MSG_COUNT => X_MSG_COUNT
641 ,X_MSG_DATA => X_MSG_DATA
642 ,X_RETURN_STATUS => X_RETURN_STATUS);
643 End Delete_Period_Rates;
644
645 End AS_MULTI_CURRENCIES_PKG;