[Home] [Help]
PACKAGE BODY: APPS.CN_CREDIT_TYPE_CONV_PVT
Source
1 PACKAGE BODY CN_CREDIT_TYPE_CONV_PVT AS
2 /*$Header: cnvctcnb.pls 115.4 2001/10/29 17:19:24 pkm ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CN_CREDIT_TYPE_CONV_PVT';
5
6 --{{{ create conversion
7
8 -- Start of comments
9 -- API name : Create_Conversion
10 -- Type : Private.
11 -- Function :
12 -- Pre-reqs : None.
13 -- Parameters :
14 -- IN : p_api_version IN NUMBER Required
15 -- p_init_msg_list IN VARCHAR2 Optional
16 -- Default = FND_API.G_FALSE
17 -- p_commit IN VARCHAR2 Optional
18 -- Default = FND_API.G_FALSE
19 -- p_validation_level IN NUMBER Optional
20 -- Default = FND_API.G_VALID_LEVEL_FULL
21 -- p_from_credit_type IN NUMBER Required
22 -- p_to_credit_type IN NUMBER Required
23 -- p_conv_factor IN NUMBER Required
24 -- p_start_date IN DATE Required
25 -- p_end_date IN DATE Required
26 -- OUT : x_return_status OUT VARCHAR2(1)
27 -- x_msg_count OUT NUMBER
28 -- x_msg_data OUT VARCHAR2(2000)
29 -- Version : Current version 1.0
30 -- Initial version 1.0
31 --
32 -- Notes : Note text
33 --
34 -- End of comments
35 PROCEDURE Create_Conversion
36 (p_api_version IN number,
37 p_init_msg_list IN varchar2 := FND_API.G_FALSE,
38 p_commit IN varchar2 := FND_API.G_FALSE,
39 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
40 p_from_credit_type IN number,
41 p_to_credit_type IN number,
42 p_conv_factor IN number,
43 p_start_date IN date,
44 p_end_date IN date,
45 x_return_status OUT varchar2,
46 x_msg_count OUT number,
47 x_msg_data OUT varchar2) is
48 G_LAST_UPDATE_DATE DATE := Sysdate;
49 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
50 G_CREATION_DATE DATE := Sysdate;
51 G_CREATED_BY NUMBER := fnd_global.user_id;
52 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
53 l_api_name CONSTANT VARCHAR2(30) := 'Create_Conversion';
54 l_api_version CONSTANT NUMBER := 1.0;
55
56 l_conv_id number;
57 l_loading_status varchar2(80);
58 CURSOR l_similar_conv_csr IS
59 SELECT from_credit_type_id, to_credit_type_id, start_date, end_date
60 FROM cn_credit_conv_fcts
61 WHERE from_credit_type_id = p_from_credit_type
62 AND to_credit_type_id = p_to_credit_type;
63 BEGIN
64 -- Standard Start of API savepoint
65 SAVEPOINT create_conversion_pvt;
66 -- Standard call to check for call compatibility.
67 IF NOT FND_API.Compatible_API_Call
68 (l_api_version ,
69 p_api_version ,
70 l_api_name ,
71 G_PKG_NAME )
72 THEN
73 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
74 END IF;
75 -- Initialize message list if p_init_msg_list is set to TRUE.
76 IF FND_API.to_Boolean( p_init_msg_list ) THEN
77 FND_MSG_PUB.initialize;
78 END IF;
79 -- Initialize API return status to success
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81 -- API body
82 IF (cn_api.invalid_date_range(p_start_date, p_end_date,
83 FND_API.G_TRUE, l_loading_status, l_loading_status, FND_API.G_TRUE)
84 <> FND_API.G_FALSE) THEN
85 RAISE fnd_api.g_exc_error;
86 END IF;
87
88 FOR eachrow in l_similar_conv_csr LOOP
89 IF (cn_api.date_range_overlap(p_start_date, p_end_date,
90 eachrow.start_date, eachrow.end_date)) THEN
91 fnd_message.set_name('CN', 'CN_CTC_DATE_OVERLAP');
92 fnd_msg_pub.ADD;
93 RAISE fnd_api.g_exc_error;
94 END IF;
95 END LOOP;
96
97 if (p_from_credit_type = p_to_credit_type) then
98 fnd_message.set_name('CN', 'CN_CTC_SAME_ERR');
99 fnd_msg_pub.ADD;
100 RAISE fnd_api.g_exc_error;
101 end if;
102
103 IF (p_conv_factor < 0) THEN
104 fnd_message.set_name('CN', 'CN_CTC_NEG_CONV_ERR');
105 fnd_msg_pub.ADD;
106 RAISE fnd_api.g_exc_error;
107 END IF;
108
109 SELECT cn_credit_conv_fcts_s.nextval
110 INTO l_conv_id
111 FROM dual;
112
113 CN_CREDIT_CONV_FCTS_PKG.Insert_Row(
114 x_credit_conv_fct_id => l_conv_id,
115 x_from_credit_type_id => p_from_credit_type,
116 x_to_credit_type_id => p_to_credit_type,
117 x_conversion_factor => p_conv_factor,
118 x_start_date => p_start_date,
119 x_end_date => p_end_date,
120 x_created_by => g_created_by,
121 x_creation_date => g_creation_date,
122 x_last_update_login => g_last_update_login,
123 x_last_update_date => g_last_update_date,
124 x_last_updated_by => g_last_updated_by);
125
126 -- End of API body.
127 -- Standard check of p_commit.
128 IF FND_API.To_Boolean( p_commit ) THEN
129 COMMIT WORK;
130 END IF;
131 -- Standard call to get message count and if count is 1, get message info.
132 FND_MSG_PUB.Count_And_Get
133 (p_count => x_msg_count ,
134 p_data => x_msg_data ,
135 p_encoded => FND_API.G_FALSE );
136 EXCEPTION
137 WHEN FND_API.G_EXC_ERROR THEN
138 ROLLBACK TO create_conversion_pvt;
139 x_return_status := FND_API.G_RET_STS_ERROR ;
140 FND_MSG_PUB.Count_And_Get
141 (p_count => x_msg_count ,
142 p_data => x_msg_data ,
143 p_encoded => FND_API.G_FALSE );
144 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
145 ROLLBACK TO create_conversion_pvt;
146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
147 FND_MSG_PUB.Count_And_Get
148 (p_count => x_msg_count ,
149 p_data => x_msg_data ,
150 p_encoded => FND_API.G_FALSE );
151 WHEN OTHERS THEN
152 ROLLBACK TO create_conversion_pvt;
153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
154 IF FND_MSG_PUB.Check_Msg_Level
155 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
156 THEN
157 FND_MSG_PUB.Add_Exc_Msg
158 (G_PKG_NAME ,
159 l_api_name );
160 END IF;
161 FND_MSG_PUB.Count_And_Get
162 (p_count => x_msg_count ,
163 p_data => x_msg_data ,
164 p_encoded => FND_API.G_FALSE );
165 END Create_Conversion;
166
167 --}}}
168
169 --{{{ update conversion
170
171 -- Start of comments
172 -- API name : Update_Conversion
173 -- Type : Private.
174 -- Function :
175 -- Pre-reqs : None.
176 -- Parameters :
177 -- IN : p_api_version IN NUMBER Required
178 -- p_init_msg_list IN VARCHAR2 Optional
179 -- Default = FND_API.G_FALSE
180 -- p_commit IN VARCHAR2 Optional
181 -- Default = FND_API.G_FALSE
182 -- p_validation_level IN NUMBER Optional
183 -- Default = FND_API.G_VALID_LEVEL_FULL
184 -- p_object_version IN NUMBER Required
185 -- p_conv_id IN NUMBER Required
186 -- p_from_credit_type IN NUMBER Required
187 -- p_to_credit_type IN NUMBER Required
188 -- p_conv_factor IN NUMBER Required
189 -- p_start_date IN DATE Required
190 -- p_end_date IN DATE Required
191 -- OUT : x_return_status OUT VARCHAR2(1)
192 -- x_msg_count OUT NUMBER
193 -- x_msg_data OUT VARCHAR2(2000)
194 -- Version : Current version x.x
195 -- Changed....
196 -- Previous version y.y
197 -- Changed....
198 -- .
199 -- .
200 -- Previous version 2.0
201 -- Changed....
202 -- Initial version 1.0
203 --
204 -- Notes : Note text
205 --
206 -- End of comments
207 PROCEDURE Update_Conversion
208 (p_api_version IN number,
209 p_init_msg_list IN varchar2 := FND_API.G_FALSE,
210 p_commit IN varchar2 := FND_API.G_FALSE,
211 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
212 p_object_version IN number,
213 p_conv_id IN number,
214 p_from_credit_type IN number,
215 p_to_credit_type IN number,
216 p_conv_factor IN number,
217 p_start_date IN date,
218 p_end_date IN date,
219 x_return_status OUT varchar2,
220 x_msg_count OUT number,
221 x_msg_data OUT varchar2) is
222 G_LAST_UPDATE_DATE DATE := Sysdate;
223 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
224 G_CREATION_DATE DATE := Sysdate;
225 G_CREATED_BY NUMBER := fnd_global.user_id;
226 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
227 l_api_name CONSTANT VARCHAR2(30)
228 := 'Update_Conversion';
229 l_api_version CONSTANT NUMBER := 1.0;
230 l_object_version number := 0;
231 l_loading_status varchar2(80);
232
233 CURSOR l_ovn_csr IS
234 SELECT object_version_number
235 FROM cn_credit_conv_fcts
236 WHERE credit_conv_fct_id = p_conv_id;
237
238 CURSOR l_similar_conv_csr IS
239 SELECT from_credit_type_id, to_credit_type_id, start_date, end_date
240 FROM cn_credit_conv_fcts
241 WHERE from_credit_type_id = p_from_credit_type
242 AND to_credit_type_id = p_to_credit_type
243 AND credit_conv_fct_id <> p_conv_id;
244 BEGIN
245 -- Standard Start of API savepoint
246 SAVEPOINT update_conversion_pvt;
247 -- Standard call to check for call compatibility.
248 IF NOT FND_API.Compatible_API_Call
249 (l_api_version ,
250 p_api_version ,
251 l_api_name ,
252 G_PKG_NAME )
253 THEN
254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
255 END IF;
256 -- Initialize message list if p_init_msg_list is set to TRUE.
257 IF FND_API.to_Boolean( p_init_msg_list ) THEN
258 FND_MSG_PUB.initialize;
259 END IF;
260 -- Initialize API return status to success
261 x_return_status := FND_API.G_RET_STS_SUCCESS;
262 -- API body
263 OPEN l_ovn_csr;
264 FETCH l_ovn_csr INTO l_object_version;
265 close l_ovn_csr;
266
267 IF (l_object_version <> p_object_version) THEN
268 fnd_message.set_name('CN', 'CN_INVALID_OBJECT_VERSION');
269 fnd_msg_pub.ADD;
270 RAISE fnd_api.g_exc_error;
271 END IF;
272
273 IF (cn_api.invalid_date_range(p_start_date, p_end_date,
274 FND_API.G_TRUE, l_loading_status, l_loading_status, FND_API.G_TRUE)
275 <> FND_API.G_FALSE) THEN
276 RAISE fnd_api.g_exc_error;
277 END IF;
278
279 FOR eachrow in l_similar_conv_csr LOOP
280 IF (cn_api.date_range_overlap(p_start_date, p_end_date,
281 eachrow.start_date, eachrow.end_date)) THEN
282 fnd_message.set_name('CN', 'CN_CTC_DATE_OVERLAP');
283 fnd_msg_pub.ADD;
284 RAISE fnd_api.g_exc_error;
285 END IF;
286 END LOOP;
287
288 if (p_from_credit_type = p_to_credit_type) then
289 fnd_message.set_name('CN', 'CN_CTC_SAME_ERR');
290 fnd_msg_pub.ADD;
291 RAISE fnd_api.g_exc_error;
292 end if;
293
294 IF (p_conv_factor < 0) THEN
295 fnd_message.set_name('CN', 'CN_CTC_NEG_CONV_ERR');
296 fnd_msg_pub.ADD;
297 RAISE fnd_api.g_exc_error;
298 END IF;
299
300 CN_CREDIT_CONV_FCTS_PKG.Update_Row(
301 x_credit_conv_fct_id => p_conv_id,
302 x_object_version => p_object_version,
303 x_from_credit_type_id => p_from_credit_type,
304 x_to_credit_type_id => p_to_credit_type,
305 x_conversion_factor => p_conv_factor,
306 x_start_date => p_start_date,
307 x_end_date => p_end_date,
308 x_created_by => g_created_by,
309 x_creation_date => g_creation_date,
310 x_last_update_login => g_last_update_login,
311 x_last_update_date => g_last_update_date,
312 x_last_updated_by => g_last_updated_by);
313 -- End of API body.
314 -- Standard check of p_commit.
315 IF FND_API.To_Boolean( p_commit ) THEN
316 COMMIT WORK;
317 END IF;
318 -- Standard call to get message count and if count is 1, get message info.
319 FND_MSG_PUB.Count_And_Get
320 (p_count => x_msg_count ,
321 p_data => x_msg_data ,
322 p_encoded => FND_API.G_FALSE );
323 EXCEPTION
324 WHEN FND_API.G_EXC_ERROR THEN
325 ROLLBACK TO update_conversion_pvt;
326 x_return_status := FND_API.G_RET_STS_ERROR ;
327 FND_MSG_PUB.Count_And_Get
328 (p_count => x_msg_count ,
329 p_data => x_msg_data ,
330 p_encoded => FND_API.G_FALSE );
331 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
332 ROLLBACK TO update_conversion_pvt;
333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
334 FND_MSG_PUB.Count_And_Get
335 (p_count => x_msg_count ,
336 p_data => x_msg_data ,
337 p_encoded => FND_API.G_FALSE );
338 WHEN OTHERS THEN
339 ROLLBACK TO update_conversion_pvt;
340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
341 IF FND_MSG_PUB.Check_Msg_Level
342 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
343 THEN
344 FND_MSG_PUB.Add_Exc_Msg
345 (G_PKG_NAME ,
346 l_api_name );
347 END IF;
348 FND_MSG_PUB.Count_And_Get
349 (p_count => x_msg_count ,
350 p_data => x_msg_data ,
351 p_encoded => FND_API.G_FALSE );
352 END Update_Conversion;
353
354 --}}}
355
356 --{{{ delete conversion
357
358 -- Start of comments
359 -- API name : Delete_Conversion
360 -- Type : Private.
361 -- Function :
362 -- Pre-reqs : None.
363 -- Parameters :
364 -- IN : p_api_version IN NUMBER Required
365 -- p_init_msg_list IN VARCHAR2 Optional
366 -- Default = FND_API.G_FALSE
367 -- p_commit IN VARCHAR2 Optional
368 -- Default = FND_API.G_FALSE
369 -- p_validation_level IN NUMBER Optional
370 -- Default = FND_API.G_VALID_LEVEL_FULL
371 -- p_object_version IN NUMBER Required
372 -- p_conv_id IN NUMBER Required
373 -- OUT : x_return_status OUT VARCHAR2(1)
374 -- x_msg_count OUT NUMBER
375 -- x_msg_data OUT VARCHAR2(2000)
376 -- Version : Current version x.x
377 -- Changed....
378 -- Previous version y.y
379 -- Changed....
380 -- .
381 -- .
382 -- Previous version 2.0
383 -- Changed....
384 -- Initial version 1.0
385 --
386 -- Notes : Note text
387 --
388 -- End of comments
389 PROCEDURE Delete_Conversion
390 (p_api_version IN number,
391 p_init_msg_list IN varchar2 := FND_API.G_FALSE,
392 p_commit IN varchar2 := FND_API.G_FALSE,
393 p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
394 p_object_version IN number,
395 p_conv_id IN number,
396 x_return_status OUT varchar2,
397 x_msg_count OUT number,
398 x_msg_data OUT varchar2) is
399 G_LAST_UPDATE_DATE DATE := Sysdate;
400 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
401 G_CREATION_DATE DATE := Sysdate;
402 G_CREATED_BY NUMBER := fnd_global.user_id;
403 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
404 l_api_name CONSTANT VARCHAR2(30)
405 := 'Delete_Conversion';
406 l_api_version CONSTANT NUMBER := 1.0;
407 l_object_version number := 0;
408 CURSOR l_ovn_csr IS
409 SELECT object_version_number
410 FROM cn_credit_conv_fcts
411 WHERE credit_conv_fct_id = p_conv_id;
412 BEGIN
413 -- Standard Start of API savepoint
414 SAVEPOINT delete_conversion_pvt;
415 -- Standard call to check for call compatibility.
416 IF NOT FND_API.Compatible_API_Call
417 (l_api_version ,
418 p_api_version ,
419 l_api_name ,
420 G_PKG_NAME )
421 THEN
422 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
423 END IF;
424 -- Initialize message list if p_init_msg_list is set to TRUE.
425 IF FND_API.to_Boolean( p_init_msg_list ) THEN
426 FND_MSG_PUB.initialize;
427 END IF;
428 -- Initialize API return status to success
429 x_return_status := FND_API.G_RET_STS_SUCCESS;
430 -- API body
431 OPEN l_ovn_csr;
432 FETCH l_ovn_csr INTO l_object_version;
433 close l_ovn_csr;
434
435 IF (l_object_version <> p_object_version) THEN
436 fnd_message.set_name('CN', 'CN_INVALID_OBJECT_VERSION');
437 fnd_msg_pub.ADD;
438 RAISE fnd_api.g_exc_error;
439 END IF;
440
441 CN_CREDIT_CONV_FCTS_PKG.Delete_Row(x_credit_conv_fct_id => p_conv_id);
442
443 -- End of API body.
444 -- Standard check of p_commit.
445 IF FND_API.To_Boolean( p_commit ) THEN
446 COMMIT WORK;
447 END IF;
448 -- Standard call to get message count and if count is 1, get message info.
449 FND_MSG_PUB.Count_And_Get
450 (p_count => x_msg_count ,
451 p_data => x_msg_data ,
452 p_encoded => FND_API.G_FALSE );
453 EXCEPTION
454 WHEN FND_API.G_EXC_ERROR THEN
455 ROLLBACK TO delete_conversion_pvt;
456 x_return_status := FND_API.G_RET_STS_ERROR ;
457 FND_MSG_PUB.Count_And_Get
458 (p_count => x_msg_count ,
459 p_data => x_msg_data ,
460 p_encoded => FND_API.G_FALSE );
461 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
462 ROLLBACK TO delete_conversion_pvt;
463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
464 FND_MSG_PUB.Count_And_Get
465 (p_count => x_msg_count ,
466 p_data => x_msg_data ,
467 p_encoded => FND_API.G_FALSE );
468 WHEN OTHERS THEN
469 ROLLBACK TO delete_conversion_pvt;
470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
471 IF FND_MSG_PUB.Check_Msg_Level
472 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
473 THEN
474 FND_MSG_PUB.Add_Exc_Msg
475 (G_PKG_NAME ,
476 l_api_name );
477 END IF;
478 FND_MSG_PUB.Count_And_Get
479 (p_count => x_msg_count ,
480 p_data => x_msg_data ,
481 p_encoded => FND_API.G_FALSE );
482 END Delete_Conversion;
483
484 --}}}
485
486 END CN_CREDIT_TYPE_CONV_PVT;