[Home] [Help]
PACKAGE BODY: APPS.OZF_CODE_CONVERSION_PVT
Source
1 PACKAGE BODY OZF_CODE_CONVERSION_PVT as
2 /* $Header: ozfvsccb.pls 120.12 2011/06/08 06:05:34 muthsubr ship $ */
3 -- Start of Comments
4 -- Package name : ozf_code_conversion_pvt
5 -- Purpose :
6 -- History : 09-OCT-2003 vansub Created
7 -- : 19-NOV-2004 kdhulipa [Bug 3928270] Not able to delete an
8 -- end date from code mapping scrn.
9 -- : 12-JUL-2005 kdhulipa R12 Enhancements
10 -- : 18-May-2006 kdhulipa Bug 5226248 NOT ABLE TO REMOVE A CODE
11 -- CONVERSION FOR PARTY SITE
12 -- : Dec-2007 gdeepika Code conversion for supplier trade profile
13 -- : 3/25/2008 gdeepika Changed the duplicate check for supplier code conv.
14 -- : 31/3/2011 muthsubr Bug#11906081-DUPLICATE CODE CONVERSION MAP MESSAGE DISPLAYED INCORRECTLY
15 -- NOTE :
16 -- End of Comments
17
18 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ozf_code_conversion_pvt';
19 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvsccb.pls';
20 G_PARTY_ID NUMBER;
21 G_ACCOUNT_ID NUMBER;
22
23
24 PROCEDURE create_code_conversion
25 (
26 p_api_version_number IN NUMBER,
27 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
28 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
29 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_msg_count OUT NOCOPY NUMBER,
32 x_msg_data OUT NOCOPY VARCHAR2,
33 p_code_conversion_tbl IN code_conversion_tbl_type,
34 x_code_conversion_id_tbl OUT NOCOPY JTF_NUMBER_TABLE
35 )
36 IS
37 l_api_name CONSTANT VARCHAR2(30) := 'create_code_conversion';
38 l_api_version_number CONSTANT NUMBER := 1.0;
39 l_return_status_full VARCHAR2(1);
40 l_object_version_number NUMBER := 1;
41 l_org_id NUMBER;
42 l_code_conversion_id NUMBER;
43 l_code_conversion_rec code_conversion_rec_type;
44
45
46
47 BEGIN
48 -- Standard Start of API savepoint
49 SAVEPOINT create_code_conversion_pvt;
50
51 -- Standard call to check for call compatibility.
52 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
53 p_api_version_number,
54 l_api_name,
55 G_PKG_NAME)
56 THEN
57 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
58 END IF;
59
60 -- Initialize message list if p_init_msg_list is set to TRUE.
61 IF FND_API.to_Boolean( p_init_msg_list )
62 THEN
63 FND_MSG_PUB.initialize;
64 END IF;
65
66
67 -- ******************************************************************
68 -- Validate Environment
69 -- ******************************************************************
70 IF FND_GLOBAL.User_Id IS NULL
71 THEN
72 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
73 THEN
74 FND_MESSAGE.Set_Name('OZF', 'OZF_USER_PROFILE_MISSING');
75 FND_MSG_PUB.ADD;
76 END IF;
77 RAISE FND_API.G_EXC_ERROR;
78 END IF;
79
80 -- Debug Message
81 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
82 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
83 END IF;
84
85 -- Initialize API return status to SUCCESS
86 x_return_status := FND_API.G_RET_STS_SUCCESS;
87
88 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
89 OZF_UTILITY_PVT.debug_message( 'No of records to be created'||p_code_conversion_tbl.count);
90 END IF;
91
92 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
93 -- Debug message
94 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
95 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name);
96 END IF;
97
98 -- Invoke validation procedures
99 Validate_Code_Conversion(
100 p_api_version_number => 1.0,
101 p_init_msg_list => FND_API.G_FALSE,
102 p_validation_level => p_validation_level,
103 p_validation_mode => JTF_PLSQL_API.G_CREATE,
104 p_code_conversion_tbl => p_code_conversion_tbl,
105 x_return_status => x_return_status,
106 x_msg_count => x_msg_count,
107 x_msg_data => x_msg_data);
108 END IF;
109
110
111 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
112 RAISE FND_API.G_EXC_ERROR;
113 END IF;
114
115 -- Debug Message
116 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
117 OZF_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
118 END IF;
119
120 l_code_conversion_id := NULL;
121 l_object_version_number := NULL;
122
123 x_code_conversion_id_tbl := JTF_NUMBER_TABLE();
124
125 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
126 OZF_UTILITY_PVT.debug_message( 'No of rows to be created '|| p_code_conversion_tbl.count);
127 END IF;
128
129 FOR i IN 1 .. p_code_conversion_tbl.count
130 LOOP
131
132 l_code_conversion_rec := p_code_conversion_tbl(i);
133
134 /*
135 IF (l_code_conversion_rec.org_id IS NULL) THEN
136 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID(); -- R12 Enhancements
137 ELSE
138 l_org_id := l_code_conversion_rec.org_id;
139 END IF;
140 */
141
142
143 l_org_id := l_code_conversion_rec.org_id;
144
145
146 SELECT ozf_code_conversions_all_s.nextval INTO l_code_conversion_id FROM DUAL;
147
148 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
149 OZF_UTILITY_PVT.debug_message( 'l_org_id ' || l_org_id);
150 OZF_UTILITY_PVT.debug_message( 'Code Conversion ID '|| l_code_conversion_id);
151 END IF;
152
153 BEGIN
154
155
156 OZF_CODE_CONVERSION_PKG.Insert_Row(
157 px_code_conversion_id => l_code_conversion_id,
158 px_object_version_number => l_object_version_number,
159 p_last_update_date => SYSDATE,
160 p_last_updated_by => FND_GLOBAL.USER_ID,
161 p_creation_date => SYSDATE,
162 p_created_by => FND_GLOBAL.USER_ID,
163 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
164 px_org_id => l_org_id,
165 p_party_id => l_code_conversion_rec.party_id,
166 p_cust_account_id => l_code_conversion_rec.cust_account_id,
167 p_code_conversion_type => l_code_conversion_rec.code_conversion_type,
168 p_external_code => l_code_conversion_rec.external_code,
169 p_internal_code => l_code_conversion_rec.internal_code,
170 p_description => l_code_conversion_rec.description,
171 p_start_date_active => nvl(l_code_conversion_rec.start_date_active,sysdate),
172 p_end_date_active => l_code_conversion_rec.end_date_active,
173 p_attribute_category => l_code_conversion_rec.attribute_category,
174 p_attribute1 => l_code_conversion_rec.attribute1,
175 p_attribute2 => l_code_conversion_rec.attribute2,
176 p_attribute3 => l_code_conversion_rec.attribute3,
177 p_attribute4 => l_code_conversion_rec.attribute4,
178 p_attribute5 => l_code_conversion_rec.attribute5,
179 p_attribute6 => l_code_conversion_rec.attribute6,
180 p_attribute7 => l_code_conversion_rec.attribute7,
181 p_attribute8 => l_code_conversion_rec.attribute8,
182 p_attribute9 => l_code_conversion_rec.attribute9,
183 p_attribute10 => l_code_conversion_rec.attribute10,
184 p_attribute11 => l_code_conversion_rec.attribute11,
185 p_attribute12 => l_code_conversion_rec.attribute12,
186 p_attribute13 => l_code_conversion_rec.attribute13,
187 p_attribute14 => l_code_conversion_rec.attribute14,
188 p_attribute15 => l_code_conversion_rec.attribute15);
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 OZF_UTILITY_PVT.debug_message (SQLERRM ||' Error in creating code conversion map');
193 RAISE FND_API.G_EXC_ERROR;
194 END;
195 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
196 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' after insert call - code conversion id' || l_code_conversion_id);
197 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' after insert call - obj version no ' || l_code_conversion_rec.Object_Version_Number);
198 END IF;
199
200 x_code_conversion_id_tbl.extend;
201 x_code_conversion_id_tbl(x_code_conversion_id_tbl.count) := l_code_conversion_id;
202
203 end loop;
204
205 -- Standard check for p_commit
206 IF FND_API.to_Boolean( p_commit )
207 THEN
208 COMMIT WORK;
209 END IF;
210
211 -- Debug Message
212 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
213 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
214 END IF;
215
216 -- Standard call to get message count and if count is 1, get message info.
217 FND_MSG_PUB.Count_And_Get
218 (p_count => x_msg_count,
219 p_data => x_msg_data
220 );
221
222
223
224 EXCEPTION
225 WHEN OZF_UTILITY_PVT.resource_locked THEN
226 x_return_status := FND_API.g_ret_sts_error;
227 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
228 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCES_LOCKED');
229 FND_MSG_PUB.add;
230 END IF;
231 WHEN FND_API.G_EXC_ERROR THEN
232 ROLLBACK TO create_code_conversion_pvt;
233 x_return_status := FND_API.G_RET_STS_ERROR;
234 -- Standard call to get message count and if count=1, get the message
235 FND_MSG_PUB.Count_And_Get (
236 p_encoded => FND_API.G_FALSE,
237 p_count => x_msg_count,
238 p_data => x_msg_data
239 );
240 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
241 ROLLBACK TO create_code_conversion_pvt;
242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
243 -- Standard call to get message count and if count=1, get the message
244 FND_MSG_PUB.Count_And_Get (
245 p_encoded => FND_API.G_FALSE,
246 p_count => x_msg_count,
247 p_data => x_msg_data
248 );
249 WHEN OTHERS THEN
250 ROLLBACK TO create_code_conversion_pvt;
251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
252 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
253 THEN
254 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
255 END IF;
256 -- Standard call to get message count and if count=1, get the message
257 FND_MSG_PUB.Count_And_Get (
258 p_encoded => FND_API.G_FALSE,
259 p_count => x_msg_count,
260 p_data => x_msg_data
261 );
262
263 END create_code_conversion ;
264
265
266 PROCEDURE Update_code_conversion
267 (
268 p_api_version_number IN NUMBER,
269 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
270 p_commit IN VARCHAR2 := FND_API.G_FALSE,
271 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
272 x_return_status OUT NOCOPY VARCHAR2,
273 x_msg_count OUT NOCOPY NUMBER,
274 x_msg_data OUT NOCOPY VARCHAR2,
275 p_code_conversion_tbl IN code_conversion_tbl_type ,
276 x_object_version_number OUT NOCOPY JTF_NUMBER_TABLE
277 )
278 IS
279 l_api_name CONSTANT VARCHAR2(30) := 'Update_code_conversion';
280 l_api_version_number CONSTANT NUMBER := 1.0;
281 l_object_version_number NUMBER;
282
283 l_code_conversion_id NUMBER;
284
285 CURSOR csr_code_conversion(cv_code_conversion_id NUMBER)
286 IS
287 SELECT code_conversion_id,
288 object_version_number,
289 last_update_date,
290 last_updated_by,
291 creation_date,
292 created_by,
293 last_update_login,
294 org_id,
295 party_id,
296 cust_account_id,
297 code_conversion_type,
298 external_code,
299 internal_code,
300 description,
301 start_date_active,
302 end_date_active,
303 attribute_category,
304 attribute1,
305 attribute2,
306 attribute3,
307 attribute4,
308 attribute5,
309 attribute6,
310 attribute7,
311 attribute8,
312 attribute9,
313 attribute10,
314 attribute11,
315 attribute12,
316 attribute13,
317 attribute14,
318 attribute15,
319 security_group_id
320 FROM ozf_code_conversions_all
321 WHERE code_conversion_id = cv_code_conversion_id;
322
323 CURSOR get_org
324 IS
325 SELECT org_id FROM ozf_sys_parameters;
326
327 l_code_conversion_rec code_conversion_rec_type;
328 l_code_conversion_tbl code_conversion_tbl_type;
329 l_org_id NUMBER;
330 p_code_conversion_rec code_conversion_rec_type;
331
332
333 BEGIN
334 -- Standard Start of API savepoint
335 SAVEPOINT update_code_conversion_pvt;
336
337 -- Standard call to check for call compatibility.
338 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
339 p_api_version_number,
340 l_api_name,
341 G_PKG_NAME)
342 THEN
343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
344 END IF;
345
346 -- Initialize message list if p_init_msg_list is set to TRUE.
347 IF FND_API.to_Boolean( p_init_msg_list )
348 THEN
349 FND_MSG_PUB.initialize;
350 END IF;
351
352 -- ******************************************************************
353 -- Validate Environment
354 -- ******************************************************************
355 IF FND_GLOBAL.User_Id IS NULL
356 THEN
357 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
358 THEN
359 FND_MESSAGE.Set_Name('OZF', 'OZF_USER_PROFILE_MISSING');
360 FND_MSG_PUB.ADD;
361 END IF;
362 RAISE FND_API.G_EXC_ERROR;
363 END IF;
364
365 -- Debug Message
366 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
367 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
368 END IF;
369
370 -- Initialize API return status to SUCCESS
371 x_return_status := FND_API.G_RET_STS_SUCCESS;
372 x_object_version_number := JTF_NUMBER_TABLE();
373
374 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
375 OZF_UTILITY_PVT.debug_message('p_code_conversion_tbl(1).description: ' || p_code_conversion_tbl(1).description );
376 END IF;
377
378 FOR i in 1 .. p_code_conversion_tbl.count
379 LOOP
380 p_code_conversion_rec := p_code_conversion_tbl(i);
381 l_code_conversion_id := p_code_conversion_rec.code_conversion_id;
382
383 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
384 OZF_UTILITY_PVT.debug_message( 'Code Conversion ID' || l_code_conversion_id);
385 END IF;
386
387 -- Check for the existance of the record
388 OPEN csr_code_conversion(l_code_conversion_id);
389 FETCH csr_code_conversion
390 INTO l_code_conversion_rec.code_conversion_id
391 ,l_code_conversion_rec.object_version_number
392 ,l_code_conversion_rec.last_update_date
393 ,l_code_conversion_rec.last_updated_by
394 ,l_code_conversion_rec.creation_date
395 ,l_code_conversion_rec.created_by
396 ,l_code_conversion_rec.last_update_login
397 ,l_code_conversion_rec.org_id
398 ,l_code_conversion_rec.party_id
399 ,l_code_conversion_rec.cust_account_id
400 ,l_code_conversion_rec.code_conversion_type
401 ,l_code_conversion_rec.external_code
402 ,l_code_conversion_rec.internal_code
403 ,l_code_conversion_rec.description
404 ,l_code_conversion_rec.start_date_active
405 ,l_code_conversion_rec.end_date_active
406 ,l_code_conversion_rec.attribute_category
407 ,l_code_conversion_rec.attribute1
408 ,l_code_conversion_rec.attribute2
409 ,l_code_conversion_rec.attribute3
410 ,l_code_conversion_rec.attribute4
411 ,l_code_conversion_rec.attribute5
412 ,l_code_conversion_rec.attribute6
413 ,l_code_conversion_rec.attribute7
414 ,l_code_conversion_rec.attribute8
415 ,l_code_conversion_rec.attribute9
416 ,l_code_conversion_rec.attribute10
417 ,l_code_conversion_rec.attribute11
418 ,l_code_conversion_rec.attribute12
419 ,l_code_conversion_rec.attribute13
420 ,l_code_conversion_rec.attribute14
421 ,l_code_conversion_rec.attribute15
422 ,l_code_conversion_rec.security_group_id;
423
424 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
425 OZF_UTILITY_PVT.debug_message('Existing description '|| l_code_conversion_rec.description);
426 END IF;
427
428 IF ( csr_code_conversion%NOTFOUND) THEN
429 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
430 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'nodata for upd');
431 END IF;
432
433 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
434 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
435 FND_MSG_PUB.add;
436 END IF;
437 RAISE FND_API.G_EXC_ERROR;
438 END IF;
439 CLOSE csr_code_conversion;
440
441
442 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
443 OZF_UTILITY_PVT.debug_message( 'Pre Object Version Number ' || l_code_conversion_rec.object_version_number);
444 OZF_UTILITY_PVT.debug_message( 'Post Object Version Number' || P_code_conversion_rec.object_version_number);
445 END IF;
446
447 --- Check the Version Number for Locking
448 IF l_code_conversion_rec.object_version_number <> P_code_conversion_rec.Object_Version_number
449 THEN
450 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
451 OZF_UTILITY_PVT.debug_message( 'dbver' || l_code_conversion_rec.object_version_number);
452 OZF_UTILITY_PVT.debug_message( 'reqver' || P_code_conversion_rec.object_version_number);
453 END IF;
454
455 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
456 THEN
457 FND_MESSAGE.Set_Name('OZF', 'OZF_API_RESOURCE_LOCKED');
458 FND_MSG_PUB.ADD;
459 END IF;
460 RAISE FND_API.G_EXC_ERROR;
461 END IF;
462
463 -- Update internal code only when it is NUll
464 IF l_code_conversion_rec.internal_code IS NOT NULL AND
465 l_code_conversion_rec.internal_code <> P_code_conversion_rec.internal_code
466 THEN
467 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
468 THEN
469 FND_MESSAGE.Set_Name('OZF', 'OZF_CODE_CONV_UPD_INTLCODE');
470 FND_MSG_PUB.ADD;
471 END IF;
472 RAISE FND_API.G_EXC_ERROR;
473 END IF;
474
475 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
476 OZF_UTILITY_PVT.debug_message( 'Existing End Date' || l_code_conversion_rec.End_Date_Active);
477 OZF_UTILITY_PVT.debug_message( 'Updated End Date' || p_code_conversion_rec.End_Date_Active);
478 END IF;
479
480 -- Update End date only when it is NUll or a future date
481 IF trunc(nvl(l_code_conversion_Rec.End_Date_Active,sysdate+1)) <= TRUNC(SYSDATE)
482 AND
483 Trunc(l_code_conversion_Rec.End_Date_Active) <> Trunc(P_code_conversion_Rec.End_Date_Active)
484 THEN
485 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
486 THEN
487 FND_MESSAGE.Set_Name('OZF', 'OZF_CODE_CONV_UPD_ENDDATE');
488 FND_MSG_PUB.ADD;
489 END IF;
490 RAISE FND_API.G_EXC_ERROR;
491 END IF;
495 OZF_UTILITY_PVT.debug_message( 'Updated Start Date' || p_code_conversion_rec.Start_Date_Active);
492
493 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
494 OZF_UTILITY_PVT.debug_message( 'Existing Start Date' || l_code_conversion_rec.Start_Date_Active);
496 END IF;
497
498 ---Update not allowed for Start Date when start date is earlier than current date
499 IF trunc(l_code_conversion_Rec.Start_Date_Active)
500 <> trunc(P_code_conversion_Rec.Start_Date_Active)
501 THEN
502 IF p_code_conversion_Rec.Start_Date_Active < TRUNC(SYSDATE)
503 THEN
504 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
505 THEN
506 FND_MESSAGE.Set_Name('OZF', 'OZF_CODE_CONV_UPD_STARTDATE');
507 FND_MSG_PUB.ADD;
508 END IF;
509 RAISE FND_API.G_EXC_ERROR;
510 END IF;
511
512 IF l_code_conversion_Rec.end_date_active < p_code_conversion_Rec.Start_Date_Active THEN
513 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
514 THEN
515 OZF_UTILITY_PVT.debug_message ('Cannot update an end dated code conversion map');
516 END IF;
517 raise FND_API.G_EXC_ERROR;
518 END IF;
519
520 END IF;
521
522 -- Update not allowed for External Code
523 IF l_code_conversion_Rec.external_Code <> P_code_conversion_Rec.external_Code
524 THEN
525 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
526 THEN
527 FND_MESSAGE.Set_Name('OZF', 'OZF_CODE_CONV_UPD_EXTCD');
528 FND_MSG_PUB.ADD;
529 END IF;
530 RAISE FND_API.G_EXC_ERROR;
531 END IF;
532
533 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
534 -- Debug message
535 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
536 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name);
537 END IF;
538 -- Invoke validation procedures
539 Validate_Code_Conversion(
540 p_api_version_number => 1.0,
541 p_init_msg_list => FND_API.G_FALSE,
542 p_validation_level => p_validation_level,
543 p_validation_mode => JTF_PLSQL_API.G_UPDATE,
544 p_code_conversion_tbl => p_code_conversion_tbl,
545 x_return_status => x_return_status,
546 x_msg_count => x_msg_count,
547 x_msg_data => x_msg_data);
548 END IF;
549
550 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
551 RAISE FND_API.G_EXC_ERROR;
552 END IF;
553
554 IF (l_code_conversion_rec.org_id IS NULL) THEN
555 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID(); -- R12 Enhancements
556 ELSE
557 l_org_id := l_code_conversion_rec.org_id;
558 END IF;
559
560
561 -- Call Update Table Handler
562 -- Debug Message
563 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
564 OZF_UTILITY_PVT.debug_message( 'Private API: Calling Update table handler');
565 END IF;
566 BEGIN
567 OZF_CODE_CONVERSION_PKG.Update_Row(
568 p_code_conversion_id => l_code_conversion_id,
569 p_object_version_number => p_code_conversion_rec.object_version_number,
570 p_last_update_date => SYSDATE,
571 p_last_updated_by => FND_GLOBAL.USER_ID,
572 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
573 p_org_id => l_org_id,
574 p_party_id => p_code_conversion_rec.party_id,
575 p_cust_account_id => p_code_conversion_rec.cust_account_id,
576 p_code_conversion_type => p_code_conversion_rec.code_conversion_type,
577 p_external_code => p_code_conversion_rec.external_code,
578 p_internal_code => p_code_conversion_rec.internal_code,
579 p_description => p_code_conversion_rec.description,
580 p_start_date_active => p_code_conversion_rec.start_date_active,
581 p_end_date_active => p_code_conversion_rec.end_date_active,
582 p_attribute_category => p_code_conversion_rec.attribute_category,
583 p_attribute1 => p_code_conversion_rec.attribute1,
584 p_attribute2 => p_code_conversion_rec.attribute2,
585 p_attribute3 => p_code_conversion_rec.attribute3,
586 p_attribute4 => p_code_conversion_rec.attribute4,
587 p_attribute5 => p_code_conversion_rec.attribute5,
588 p_attribute6 => p_code_conversion_rec.attribute6,
589 p_attribute7 => p_code_conversion_rec.attribute7,
590 p_attribute8 => p_code_conversion_rec.attribute8,
591 p_attribute9 => p_code_conversion_rec.attribute9,
592 p_attribute10 => p_code_conversion_rec.attribute10,
593 p_attribute11 => p_code_conversion_rec.attribute11,
594 p_attribute12 => p_code_conversion_rec.attribute12,
595 p_attribute13 => p_code_conversion_rec.attribute13,
596 p_attribute14 => p_code_conversion_rec.attribute14,
597 p_attribute15 => p_code_conversion_rec.attribute15);
598
599
600
601 EXCEPTION
602 WHEN OTHERS THEN
603 OZF_UTILITY_PVT.debug_message (SQLERRM ||' Error in updating code conversion map');
604 RAISE FND_API.G_EXC_ERROR;
605 END;
606
607 x_object_version_number.EXTEND;
608 x_object_Version_number(x_object_version_number.count) := p_code_conversion_rec.Object_Version_Number;
609
610 END LOOP;
611
612
613 -- Standard check for p_commit
614 IF FND_API.to_Boolean( p_commit )
615 THEN
616 COMMIT WORK;
617 END IF;
618
619
620 -- Debug Message
621 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
622 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
623 END IF;
624 -- Standard call to get message count and if count is 1, get message info.
625 FND_MSG_PUB.Count_And_Get
626 (p_count => x_msg_count,
627 p_data => x_msg_data
628 );
629 EXCEPTION
630 WHEN FND_API.G_EXC_ERROR THEN
631 ROLLBACK TO update_code_conversion_pvt;
632 x_return_status := FND_API.G_RET_STS_ERROR;
633 -- Standard call to get message count and if count=1, get the message
634 FND_MSG_PUB.Count_And_Get (
635 p_encoded => FND_API.G_FALSE,
636 p_count => x_msg_count,
637 p_data => x_msg_data
638 );
639 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
640 ROLLBACK TO update_code_conversion_pvt;
641 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
642 -- Standard call to get message count and if count=1, get the message
643 FND_MSG_PUB.Count_And_Get (
644 p_encoded => FND_API.G_FALSE,
645 p_count => x_msg_count,
646 p_data => x_msg_data
647 );
648 WHEN OTHERS THEN
649 ROLLBACK TO update_code_conversion_pvt;
650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
651 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
652 THEN
653 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
654 END IF;
655 -- Standard call to get message count and if count=1, get the message
656 FND_MSG_PUB.Count_And_Get (
657 p_encoded => FND_API.G_FALSE,
658 p_count => x_msg_count,
659 p_data => x_msg_data
660 );
661
662 END Update_Code_Conversion;
663
664
665
666 PROCEDURE Update_Code_Conversion_Tbl(
667 P_Api_Version_Number IN NUMBER,
668 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
669 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
670 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
671 X_Return_Status OUT NOCOPY VARCHAR2,
672 X_Msg_Count OUT NOCOPY NUMBER,
673 X_Msg_Data OUT NOCOPY VARCHAR2,
674 P_code_conversion_Tbl IN code_conversion_tbl_type
675 )
676 IS
677 l_api_name CONSTANT VARCHAR2(30) := 'update_code_conversion_tbl';
678 l_api_version_number CONSTANT NUMBER := 1.0;
679
680 p_code_conversion_rec code_conversion_rec_type;
681
682 l_code_conversion_id NUMBER;
683 v_code_conversion_id JTF_NUMBER_TABLE;
684 v_object_version_number JTF_NUMBER_TABLE;
685
686 l_create_flag VARCHAR2(10);
687
688 l_create_code_conv_tbl code_conversion_tbl_type := code_conversion_tbl_type();
689 l_update_code_conv_tbl code_conversion_tbl_type := code_conversion_tbl_type();
690
691 l_cc_cnt NUMBER := 0;
692 l_up_cnt NUMBER := 0;
693
694 BEGIN
695 -- Standard Start of API savepoint
696 SAVEPOINT update_code_conversion_tbl_pvt;
697
698 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
699 OZF_UTILITY_PVT.debug_message('Entered the proc tbl');
700 END IF;
701
702 -- Standard call to check for call compatibility.
703 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
704 p_api_version_number,
705 l_api_name,
706 G_PKG_NAME)
707 THEN
708 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
709 END IF;
710
711 -- Initialize message list if p_init_msg_list is set to TRUE.
712 IF FND_API.to_Boolean( p_init_msg_list )
713 THEN
714 FND_MSG_PUB.initialize;
715 END IF;
716
717 -- ******************************************************************
718 -- Validate Environment
719 -- ******************************************************************
720 IF FND_GLOBAL.User_Id IS NULL
721 THEN
722 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
723 THEN
724 FND_MESSAGE.Set_Name('OZF', 'OZF_USER_PROFILE_MISSING');
725 FND_MSG_PUB.ADD;
726 END IF;
727 RAISE FND_API.G_EXC_ERROR;
728 END IF;
729
730 -- Debug Message
731 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
732 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
733 OZF_UTILITY_PVT.debug_message('Total Number of records '||P_code_conversion_Tbl.count);
734 END IF;
735 -- Initialize API return status to SUCCESS
736 x_return_status := FND_API.G_RET_STS_SUCCESS;
737
738 v_code_conversion_id := JTF_NUMBER_TABLE();
739 v_object_version_number := JTF_NUMBER_TABLE();
740
741 FOR i IN P_code_conversion_Tbl.first .. P_code_conversion_Tbl.last
742 LOOP
743
744 l_code_conversion_id := P_code_conversion_Tbl(i).code_conversion_id;
745 P_code_conversion_Rec := P_code_conversion_Tbl(i);
746
747 -- Fix for 3928270
748
749 -- IF p_code_conversion_rec.end_date_active = FND_API.g_miss_date
750 -- THEN
751 -- p_code_conversion_rec.end_date_active := NULL;
752 -- END IF;
753
754
755 IF l_code_conversion_id IS NULL OR l_code_conversion_id = -1 then
756 l_cc_cnt := l_cc_cnt + 1;
757
758 l_create_code_conv_tbl.extend;
759 l_create_code_conv_tbl(l_cc_cnt) := P_code_conversion_Rec;
760
761 ELSE
762 l_up_cnt := l_up_cnt + 1;
763
764 l_update_code_conv_tbl.extend;
765 l_update_code_conv_tbl(l_up_cnt) := P_code_conversion_Rec;
766 END IF;
767
768 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
769 OZF_UTILITY_PVT.debug_message('End Date '||P_code_conversion_Rec.end_date_active);
770 END IF;
771
772 END LOOP;
773
774 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
775 OZF_UTILITY_PVT.debug_message('No of rows to be created: ' || l_cc_cnt);
776 OZF_UTILITY_PVT.debug_message('No of rows to be updated: ' || l_up_cnt);
777 END IF;
778
779 IF l_cc_cnt > 0 THEN
780 --- Call to Create Procedure
781 Create_Code_Conversion
782 (
783 p_api_version_number => p_api_version_number,
784 p_init_msg_list => p_init_msg_list,
785 p_commit => p_commit,
786 p_validation_level => p_validation_level,
787 x_return_status => x_return_Status,
788 x_msg_count => x_msg_Count,
789 x_msg_data => x_msg_Data,
790 p_code_conversion_tbl => l_create_code_conv_tbl,
791 x_code_conversion_id_tbl => v_code_conversion_id
792 );
793
794 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
795 RAISE FND_API.G_EXC_ERROR;
796 END IF;
797 END IF;
798
799 IF l_up_cnt > 0 THEN
800 -- Call to Update Procedure
801 Update_code_conversion (
802 p_api_version_number => p_api_version_number ,
803 p_init_msg_list => p_init_msg_list,
804 p_commit => p_commit,
805 p_validation_level => p_validation_level,
806 x_return_status => x_return_Status,
807 x_msg_count => x_msg_Count,
808 x_msg_data => x_msg_Data,
809 p_code_conversion_tbl => l_update_code_conv_tbl,
810 x_object_version_number => v_object_version_number
811 );
812
813 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
814 RAISE FND_API.G_EXC_ERROR;
815 END IF;
816 END IF;
817
818 -- Standard check for p_commit
819 IF FND_API.to_Boolean( p_commit )
820 THEN
821 COMMIT WORK;
822 END IF;
823
824 -- Debug Message
825 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
826 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
827 END IF;
828 -- Standard call to get message count and if count is 1, get message info.
829 FND_MSG_PUB.Count_And_Get
830 (p_count => x_msg_count,
831 p_data => x_msg_data
832 );
833 EXCEPTION
834 WHEN FND_API.G_EXC_ERROR THEN
835 ROLLBACK TO update_code_conversion_tbl_pvt;
836 x_return_status := FND_API.G_RET_STS_ERROR;
837 -- Standard call to get message count and if count=1, get the message
838 FND_MSG_PUB.Count_And_Get (
839 p_encoded => FND_API.G_FALSE,
840 p_count => x_msg_count,
841 p_data => x_msg_data
842 );
843 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
844 ROLLBACK TO update_code_conversion_tbl_pvt;
845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
846 -- Standard call to get message count and if count=1, get the message
847 FND_MSG_PUB.Count_And_Get (
848 p_encoded => FND_API.G_FALSE,
849 p_count => x_msg_count,
850 p_data => x_msg_data
851 );
852 WHEN OTHERS THEN
853 ROLLBACK TO update_code_conversion_tbl_pvt;
854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
855 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
856 THEN
857 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
858 END IF;
859 -- Standard call to get message count and if count=1, get the message
860 FND_MSG_PUB.Count_And_Get (
861 p_encoded => FND_API.G_FALSE,
862 p_count => x_msg_count,
863 p_data => x_msg_data
864 );
865
866
867 END Update_Code_Conversion_Tbl;
868
869
870 PROCEDURE Delete_Code_Conversion_Tbl
871 (
872 p_api_version_number IN NUMBER,
873 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
874 p_commit IN VARCHAR2 := FND_API.G_FALSE,
875 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
876 x_return_status OUT NOCOPY VARCHAR2,
877 x_msg_count OUT NOCOPY NUMBER,
878 x_msg_data OUT NOCOPY VARCHAR2,
879 p_code_conversion_tbl IN code_conversion_Tbl_Type
880 )
881 IS
882 l_api_name CONSTANT VARCHAR2(30) := 'delete_code_conversion_tbl';
883 l_api_version_number CONSTANT NUMBER := 1.0;
884
885 p_code_conversion_rec code_conversion_rec_type;
886
887 l_code_conversion_id NUMBER;
888 l_object_version_number NUMBER;
889
890
891 BEGIN
892 -- Standard Start of API savepoint
893 SAVEPOINT delete_code_conversion_tbl_pvt;
894
895 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
896 OZF_UTILITY_PVT.debug_message('Entered the proc tbl');
897 END IF;
898
899 -- Standard call to check for call compatibility.
900 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
901 p_api_version_number,
902 l_api_name,
903 G_PKG_NAME)
904 THEN
905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
906 END IF;
907
908 -- Initialize message list if p_init_msg_list is set to TRUE.
909 IF FND_API.to_Boolean( p_init_msg_list )
910 THEN
911 FND_MSG_PUB.initialize;
912 END IF;
913
914 -- ******************************************************************
915 -- Validate Environment
916 -- ******************************************************************
917 IF FND_GLOBAL.User_Id IS NULL
918 THEN
919 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
920 THEN
921 FND_MESSAGE.Set_Name('OZF', 'OZF_USER_PROFILE_MISSING');
922 FND_MSG_PUB.ADD;
923 END IF;
924 RAISE FND_API.G_EXC_ERROR;
925 END IF;
926
927 -- Debug Message
928 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
929 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
930 END IF;
931
932
933 -- Initialize API return status to SUCCESS
934 x_return_status := FND_API.G_RET_STS_SUCCESS;
935
936 FOR i IN P_code_conversion_Tbl.first .. P_code_conversion_Tbl.last
937 LOOP
938
939 l_code_conversion_id := P_code_conversion_Tbl(i).code_conversion_id;
940 l_object_version_number := P_code_conversion_Tbl(i).object_version_number;
941
942 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
943 OZF_UTILITY_PVT.debug_message('Code Conversion ID ' || l_code_conversion_id);
944 OZF_UTILITY_PVT.debug_message('Object Version Number ' || l_object_version_number);
945 END IF;
946
947 IF l_object_version_number IS NULL
948 OR l_code_conversion_id IS NULL THEN
949
950 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
951 OZF_UTILITY_PVT.debug_message('In If block');
952 END IF;
953 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
954 FND_MESSAGE.set_name('OZF', 'OZF_REQ_PARAMETERS_MISSING');
955 FND_MSG_PUB.add;
956 END IF;
957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
958
959
960 ELSE
961
962 IF P_code_conversion_Tbl(i).party_id IS NOT NULL THEN
963 G_PARTY_ID := P_code_conversion_Tbl(i).party_id;
964 ELSE
965 G_PARTY_ID := NULL;
966 END IF;
967 IF P_code_conversion_Tbl(i).cust_account_id IS NOT NULL THEN
968 G_ACCOUNT_ID := P_code_conversion_Tbl(i).cust_account_id;
969 ELSE
970 G_ACCOUNT_ID := NULL;
971 END IF;
972
973 Delete_Code_Conversion(
974 p_api_version_number => 1.0,
975 p_init_msg_list => FND_API.G_FALSE,
976 p_commit => FND_API.G_FALSE,
977 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
978 x_return_status => X_Return_Status ,
979 x_msg_count => X_Msg_Count ,
980 x_msg_data => X_Msg_Data ,
981 p_code_conversion_id => l_code_conversion_id,
982 p_object_version_number => l_object_version_number,
983 p_external_code => P_code_conversion_Tbl(i).external_code ,
984 p_code_conversion_type => P_code_conversion_Tbl(i).code_conversion_type);
985
986
987 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
988 RAISE FND_API.G_EXC_ERROR;
989 END IF;
990
991 END IF;
992
993 END LOOP;
994
995
996 -- Standard check for p_commit
997 IF FND_API.to_Boolean( p_commit )
998 THEN
999 COMMIT WORK;
1000 END IF;
1001
1002 -- Debug Message
1003 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1004 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1005 END IF;
1006 -- Standard call to get message count and if count is 1, get message info.
1007 FND_MSG_PUB.Count_And_Get
1008 (p_count => x_msg_count,
1009 p_data => x_msg_data
1010 );
1011 EXCEPTION
1012 WHEN FND_API.G_EXC_ERROR THEN
1013 ROLLBACK TO delete_code_conversion_tbl_pvt;
1014 x_return_status := FND_API.G_RET_STS_ERROR;
1015 -- Standard call to get message count and if count=1, get the message
1016 FND_MSG_PUB.Count_And_Get (
1017 p_encoded => FND_API.G_FALSE,
1018 p_count => x_msg_count,
1019 p_data => x_msg_data
1020 );
1021 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1022 ROLLBACK TO delete_code_conversion_tbl_pvt;
1023 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1024 -- Standard call to get message count and if count=1, get the message
1025 FND_MSG_PUB.Count_And_Get (
1026 p_encoded => FND_API.G_FALSE,
1027 p_count => x_msg_count,
1028 p_data => x_msg_data
1029 );
1030 WHEN OTHERS THEN
1031 ROLLBACK TO delete_code_conversion_tbl_pvt;
1032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1033 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1034 THEN
1035 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1036 END IF;
1037 -- Standard call to get message count and if count=1, get the message
1038 FND_MSG_PUB.Count_And_Get (
1039 p_encoded => FND_API.G_FALSE,
1040 p_count => x_msg_count,
1041 p_data => x_msg_data
1042 );
1043
1044
1045 END Delete_Code_Conversion_Tbl;
1046
1047
1048
1049
1050 PROCEDURE Delete_Code_Conversion
1051 (
1052 p_api_version_number IN NUMBER,
1053 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1054 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1055 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1056 x_return_status OUT NOCOPY VARCHAR2,
1057 x_msg_count OUT NOCOPY NUMBER,
1058 x_msg_data OUT NOCOPY VARCHAR2,
1059 p_code_conversion_id IN NUMBER,
1060 p_object_version_number IN NUMBER,
1061 p_external_code IN VARCHAR2,
1062 p_code_conversion_type IN VARCHAR2
1063 )
1064 IS
1065 l_api_name CONSTANT VARCHAR2(30) := 'Delete_code_conversion';
1066 l_api_version_number CONSTANT NUMBER := 1.0;
1067 l_object_version_number NUMBER;
1068
1069 l_dummy NUMBER;
1070 l_acc_dummy NUMBER;
1071 l_party_id NUMBER;
1072 l_account_id NUMBER;
1073
1074 TYPE case_chk_cur IS REF CURSOR;
1075 csr_code_conv_party case_chk_cur;
1076 csr_code_conv_location case_chk_cur;
1077 csr_code_conv_uom case_chk_cur;
1078
1079 l_sql VARCHAR2(32000);
1080 l_where VARCHAR2(32000);
1081 l_interface_sql VARCHAR2(32000);
1082
1083 CURSOR csr_code_conv_reason (cv_external_code VARCHAR2) IS
1084 SELECT COUNT(customer_reason)
1085 FROM ozf_claims
1086 WHERE customer_reason = cv_external_code;
1087
1088
1089 CURSOR csr_code_conv_product_pt (cv_external_code VARCHAR2
1090 ,cv_party_id NUMBER
1091 ,cv_account_id NUMBER) IS
1092 SELECT MAX(pt_cnt)
1093 FROM (
1094 SELECT COUNT(orig_system_item_number) pt_cnt
1095 FROM ozf_resale_lines_int lin
1096 ,ozf_resale_batches bat
1097 WHERE lin.resale_batch_id = bat.resale_batch_id
1098 AND orig_system_item_number = cv_external_code
1099 AND (bat.partner_party_id = cv_party_id OR bat.partner_cust_account_id = cv_account_id)
1100 UNION
1101 SELECT COUNT(orig_system_item_number) pt_cnt
1102 FROM ozf_resale_lines lin
1103 ,ozf_resale_batches bat
1104 ,ozf_resale_batch_line_maps map
1105 WHERE orig_system_item_number = cv_external_code
1106 AND lin.resale_line_id = map.resale_line_id
1107 AND bat.resale_batch_id = map.resale_batch_id
1108 AND (bat.partner_party_id = cv_party_id OR bat.partner_cust_account_id = cv_account_id) ) x;
1109
1110
1111 CURSOR csr_code_conv_product (cv_external_code VARCHAR2)
1112 IS
1113 SELECT COUNT(orig_system_item_number)
1114 FROM ozf_resale_lines_int lin
1115 WHERE orig_system_item_number = cv_external_code;
1116
1117 CURSOR csr_code_conv_agreement_pt (cv_external_code VARCHAR2
1118 ,cv_party_id NUMBER
1119 ,cv_account_id NUMBER)
1120 IS
1121 SELECT MAX(pt_cnt)
1122 FROM
1123 (
1124 SELECT COUNT(orig_system_agreement_name) pt_cnt
1125 FROM ozf_resale_lines_int lin
1126 ,ozf_resale_batches bat
1127 WHERE lin.resale_batch_id = bat.resale_batch_id
1128 AND orig_system_agreement_name = cv_external_code
1129 AND (bat.partner_party_id = cv_party_id OR bat.partner_cust_account_id = cv_account_id)
1130 UNION
1131 SELECT COUNT(orig_system_agreement_name) pt_cnt
1132 FROM ozf_resale_adjustments lin
1133 ,ozf_resale_batches bat
1134 WHERE orig_system_agreement_name = cv_external_code
1135 AND bat.resale_batch_id = lin.resale_batch_id
1136 AND (bat.partner_party_id = cv_party_id OR bat.partner_cust_account_id = cv_account_id) ) x;
1137
1138 CURSOR csr_code_conv_agreement (cv_external_code VARCHAR2) IS
1139 SELECT COUNT(orig_system_agreement_name)
1140 FROM ozf_resale_lines_int lin
1141 WHERE orig_system_agreement_name = cv_external_code;
1142
1143 CURSOR csr_code_conv_agrmt_uom (cv_external_code VARCHAR2
1144 ,cv_party_id NUMBER
1145 ,cv_account_id NUMBER ) IS
1146 SELECT COUNT(orig_system_agreement_uom)
1147 FROM ozf_resale_adjustments lin
1148 ,ozf_resale_batches bat
1149 WHERE orig_system_agreement_uom = cv_external_code
1150 AND bat.resale_batch_id = lin.resale_batch_id
1151 AND (bat.partner_party_id = cv_party_id OR bat.partner_cust_account_id = cv_account_id) ;
1152
1153 CURSOR csr_code_conv_agmt_uom (cv_external_code VARCHAR2 ) IS
1154 SELECT COUNT(orig_system_agreement_uom)
1155 FROM ozf_resale_adjustments lin
1156 WHERE orig_system_agreement_uom = cv_external_code;
1157
1158 BEGIN
1159 -- Standard Start of API savepoint
1160 SAVEPOINT delete_code_conversion_pvt;
1161
1162 -- Standard call to check for call compatibility.
1163 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1164 p_api_version_number,
1165 l_api_name,
1166 G_PKG_NAME)
1167 THEN
1168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1169 END IF;
1170
1171 -- Initialize message list if p_init_msg_list is set to TRUE.
1172 IF FND_API.to_Boolean( p_init_msg_list )
1173 THEN
1174 FND_MSG_PUB.initialize;
1175 END IF;
1176
1177 -- Debug Message
1178 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1179 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1180 END IF;
1181
1182
1183 -- Initialize API return status to SUCCESS
1184 x_return_status := FND_API.G_RET_STS_SUCCESS;
1185
1186 -- Validate the Delete Condition
1187 --
1188
1189 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1190 OZF_UTILITY_PVT.debug_message('Party ID in delete code conersion: ' || l_party_id);
1191 END IF;
1192 IF p_external_code IS NOT NULL THEN
1193
1194 IF p_code_conversion_type = 'OZF_REASON_CODES' THEN
1195
1196 l_dummy := null;
1197
1198 OPEN csr_code_conv_reason (p_external_code);
1199 FETCH csr_code_conv_reason INTO l_dummy;
1200 CLOSE csr_code_conv_reason;
1201
1202 IF l_dummy <> 0 Then
1203 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1204 FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_RSNMAP_DELETE');
1205 FND_MSG_PUB.ADD;
1206 END IF;
1207 RAISE FND_API.G_EXC_ERROR;
1208 END IF;
1209
1210 ELSIF p_code_conversion_type = 'OZF_UOM_CODES' THEN
1211
1212 l_dummy := null;
1213 l_sql := 'SELECT ( CASE WHEN orig_system_uom = :1 THEN 1'||
1214 ' WHEN orig_system_purchase_uom = :1 THEN 1 ';
1215 l_where := ' (orig_system_uom = :1 '||
1216 ' OR orig_system_purchase_uom = :1 ';
1217
1218 l_interface_sql := l_sql ||' WHEN orig_system_agreement_uom :1 THEN 1';
1219 l_sql := l_sql||' ELSE 0 END ) pt_cnt'|| ' FROM ';
1220 l_interface_sql := l_interface_sql || l_sql;
1221
1222
1223
1224 IF G_PARTY_ID IS NOT NULL THEN
1225
1226 l_sql := l_sql||'ozf_resale_batches bat, ozf_resale_lines lin ,ozf_resale_batch_line_maps map ';
1227 l_sql := l_sql||'WHERE lin.resale_line_id = map.resale_line_id ';
1228 l_sql := l_sql||'AND map.resale_batch_id = bat.resale_batch_id ';
1229 l_sql := l_sql|| 'AND '||l_where ||') ';
1230
1231 l_interface_sql := l_sql ||'ozf_resale_batches bat, ozf_resale_lines_int lin '||
1232 ' WHERE lin.resale_batch_id = bat.resale_batch_id ';
1233 l_interface_sql := l_interface_sql ||' AND '||l_where || 'OR orig_system_agreement_uom = :1 )';
1234
1235
1236 l_sql := l_sql||' AND bat.partner_party_id = :2 ' ;
1237 l_interface_sql := l_interface_sql||' AND bat.partner_party_id = :2' ;
1238
1239 IF G_ACCOUNT_ID IS NOT NULL THEN
1240 l_sql := l_sql ||' AND bat.partner_cust_account_id = :3 ' ;
1241 l_interface_sql := l_sql ||'AND bat.partner_cust_account_id = :3 ';
1242 END IF;
1243
1244
1245 ELSIF G_PARTY_ID IS NULL AND G_ACCOUNT_ID IS NULL THEN
1246 l_sql := l_sql ||' ozf_resale_lines WHERE';
1247 l_interface_sql := l_sql ||' ozf_resale_lines_int WHERE ';
1248 l_sql := l_sql || l_where ||') ';
1249 l_interface_sql := l_interface_sql ||l_where ||
1250 'OR orig_system_agreement_uom = :1 )';
1251
1252 END IF;
1253
1254 for i in 1..ceil((length(l_sql)/100)) loop
1255 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1256 OZF_UTILITY_PVT.debug_message(substr(l_sql, (i-1)*100+1, 100));
1257 END IF;
1258 end loop;
1259 IF g_party_id IS NULL THEN
1260 OPEN csr_code_conv_uom FOR l_sql USING p_external_code
1264 ELSIF g_party_id IS NOT NULL AND g_account_id IS NOT NULL THEN
1261 ,p_external_code
1262 ,p_external_code
1263 ,p_external_code;
1265
1266 OPEN csr_code_conv_uom FOR l_sql USING p_external_code
1267 ,p_external_code
1268 ,p_external_code
1269 ,p_external_code
1270 ,g_party_id
1271 ,g_account_id;
1272 ELSIF g_party_id IS NOT NULL AND g_account_id IS NULL THEN
1273 OPEN csr_code_conv_uom FOR l_sql USING p_external_code
1274 ,p_external_code
1275 ,p_external_code
1276 ,p_external_code
1277 ,g_party_id;
1278 END IF;
1279 FETCH csr_code_conv_uom INTO l_dummy;
1280 CLOSE csr_code_conv_uom;
1281 IF l_dummy = 0 THEN
1282 IF g_party_id IS NULL THEN
1283 OPEN csr_code_conv_uom FOR l_interface_sql USING p_external_code
1284 ,p_external_code
1285 ,p_external_code
1286 ,p_external_code
1287 ,p_external_code
1288 ,p_external_code;
1289 ELSIF g_party_id IS NOT NULL AND g_account_id IS NOT NULL THEN
1290
1291 OPEN csr_code_conv_uom FOR l_interface_sql USING p_external_code
1292 ,p_external_code
1293 ,p_external_code
1294 ,p_external_code
1295 ,p_external_code
1296 ,p_external_code
1297 ,g_party_id
1298 ,g_account_id;
1299 ELSIF g_party_id IS NOT NULL AND g_account_id IS NULL THEN
1300 OPEN csr_code_conv_uom FOR l_interface_sql USING p_external_code
1301 ,p_external_code
1302 ,p_external_code
1303 ,p_external_code
1304 ,p_external_code
1305 ,p_external_code
1306 ,g_party_id;
1307 END IF;
1308
1309 FETCH csr_code_conv_uom INTO l_dummy;
1310 CLOSE csr_code_conv_uom;
1311 END IF;
1312 IF l_dummy <> 0 THEN
1313 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1314 FND_MESSAGE.Set_Name('OZF', 'OZF_RESALE_UOM_DELETE');
1315 FND_MSG_PUB.ADD;
1316 END IF;
1317 RAISE FND_API.G_EXC_ERROR;
1318 ELSE
1319 IF g_party_id IS NOT NULL THEN
1320 OPEN csr_code_conv_agrmt_uom ( p_external_code
1321 ,g_party_id
1322 ,g_account_id );
1323 FETCH csr_code_conv_agrmt_uom INTO l_dummy;
1324 CLOSE csr_code_conv_agrmt_uom;
1325
1326 IF l_dummy <> 0 THEN
1327 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1328 FND_MESSAGE.Set_Name('OZF', 'OZF_RESALE_UOM_DELETE');
1329 FND_MSG_PUB.ADD;
1330 END IF;
1331 RAISE FND_API.G_EXC_ERROR;
1332 END IF;
1333 ELSE
1334 OPEN csr_code_conv_agmt_uom ( p_external_code);
1335 FETCH csr_code_conv_agmt_uom INTO l_dummy;
1336 CLOSE csr_code_conv_agmt_uom;
1337
1338 IF l_dummy <> 0 THEN
1339 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1340 FND_MESSAGE.Set_Name('OZF', 'OZF_RESALE_UOM_DELETE');
1341 FND_MSG_PUB.ADD;
1342 END IF;
1343 RAISE FND_API.G_EXC_ERROR;
1344 END IF;
1345 END IF;
1346 END IF;
1347
1348
1349
1350 ELSIF p_code_conversion_type = 'OZF_AGREEMENT_CODES' THEN
1351
1352 l_dummy := null;
1353 IF G_PARTY_ID IS NOT NULL OR G_ACCOUNT_ID IS NOT NULL THEN
1354 OPEN csr_code_conv_agreement_pt (p_external_code
1355 , g_party_id
1356 , g_account_id);
1357 FETCH csr_code_conv_agreement_pt INTO l_dummy;
1358 CLOSE csr_code_conv_agreement_pt;
1359
1360 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1361 OZF_UTILITY_PVT.debug_message('l_dummy in delete code conersion: ' || l_dummy);
1362 END IF;
1363 END IF;
1364 IF G_PARTY_ID IS NULL AND G_ACCOUNT_ID IS NULL THEN
1365 OPEN csr_code_conv_agreement (p_external_code);
1366 FETCH csr_code_conv_agreement INTO l_dummy;
1367 CLOSE csr_code_conv_agreement;
1368 END IF;
1369 IF l_dummy <> 0 THEN
1370 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1371 FND_MESSAGE.Set_Name('OZF', 'OZF_RESALE_AGREEMENT_DELETE');
1372 FND_MSG_PUB.ADD;
1373 END IF;
1374 raise FND_API.G_EXC_ERROR;
1375 END IF;
1376 ELSIF p_code_conversion_type = 'OZF_PRODUCT_CODES' THEN
1377 l_dummy := null;
1378 -- Delete from Trade Profile at Party or Account level
1379 IF G_PARTY_ID IS NOT NULL OR G_ACCOUNT_ID IS NOT NULL THEN
1380
1381 OPEN csr_code_conv_product_pt (p_external_code
1382 ,g_party_id
1383 ,g_account_id);
1384 FETCH csr_code_conv_product_pt INTO l_dummy;
1385 CLOSE csr_code_conv_product_pt;
1386 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1387 OZF_UTILITY_PVT.debug_message('l_dummy in delete code conersion: ' || l_dummy);
1388 END IF;
1389 END IF;
1390 -- Delete from site Profile
1391 IF G_PARTY_ID IS NULL AND G_ACCOUNT_ID IS NULL THEN
1392 OPEN csr_code_conv_product (p_external_code);
1393 FETCH csr_code_conv_product INTO l_dummy;
1394 CLOSE csr_code_conv_product;
1395 END IF;
1396 IF l_dummy <> 0 THEN
1397 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1398 FND_MESSAGE.Set_Name('OZF', 'OZF_RESALE_PRODUCT_DELETE');
1399 FND_MSG_PUB.ADD;
1400 END IF;
1401 raise FND_API.G_EXC_ERROR;
1402 END IF;
1403 ELSIF p_code_conversion_type = 'OZF_PARTY_CODES' THEN
1404 l_dummy := null;
1405 -- Make this cursor static when application is upgraded to 9i
1406
1407
1408 l_sql := 'SELECT ( CASE WHEN bill_to_party_name = :1 THEN 1'||
1409 ' WHEN ship_to_party_name = :1 THEN 1 '||
1410 ' ELSE 0 END ) pt_cnt'||
1411 ' FROM ';
1412 l_where := ' (bill_to_party_name = :1 '||
1413 ' OR ship_to_party_name = :1 )';
1414
1415
1416 IF G_PARTY_ID IS NOT NULL THEN
1417 l_sql := l_sql||'ozf_resale_batches bat, ozf_resale_lines lin ,ozf_resale_batch_line_maps map ';
1418 l_sql := l_sql||'WHERE lin.resale_line_id = map.resale_line_id ';
1419 l_sql := l_sql||'AND map.resale_batch_id = bat.resale_batch_id ';
1420
1421 l_where := l_where||' AND bat.partner_party_id = :2 ' ;
1422
1423 IF G_ACCOUNT_ID IS NOT NULL THEN
1424 l_where := l_where||' AND bat.partner_cust_account_id = :3 ' ;
1425 END IF;
1426
1427 l_sql := l_sql || 'AND '||l_where;
1428 l_interface_sql := l_interface_sql ||'AND '|| l_where;
1429
1430 ELSIF G_PARTY_ID IS NULL AND G_ACCOUNT_ID IS NULL THEN
1431 l_sql := l_sql ||' ozf_resale_lines WHERE';
1432 l_interface_sql := l_sql ||' ozf_resale_lines_int WHERE ';
1433 l_sql := l_sql || l_where;
1434 l_interface_sql := l_interface_sql || l_where;
1435 END IF;
1436 for i in 1..ceil((length(l_sql)/100)) loop
1437 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1438 OZF_UTILITY_PVT.debug_message(substr(l_sql, (i-1)*100+1, 100));
1439 END IF;
1440 end loop;
1441 IF g_party_id IS NULL THEN
1442 OPEN csr_code_conv_party FOR l_sql USING p_external_code
1443 ,p_external_code
1444 ,p_external_code
1445 ,p_external_code;
1446 ELSIF g_party_id IS NOT NULL AND g_account_id IS NOT NULL THEN
1447
1448 OPEN csr_code_conv_party FOR l_sql USING p_external_code
1449 ,p_external_code
1450 ,p_external_code
1451 ,p_external_code
1452 ,g_party_id
1453 ,g_account_id;
1454 ELSIF g_party_id IS NOT NULL AND g_account_id IS NULL THEN
1455 OPEN csr_code_conv_party FOR l_sql USING p_external_code
1456 ,p_external_code
1457 ,p_external_code
1458 ,p_external_code
1459 ,g_party_id;
1460 END IF;
1461 FETCH csr_code_conv_party INTO l_dummy;
1462 CLOSE csr_code_conv_party;
1463 IF l_dummy = 0 THEN
1464 IF g_party_id IS NULL THEN
1465 OPEN csr_code_conv_party FOR l_interface_sql USING p_external_code
1466 ,p_external_code
1467 ,p_external_code
1468 ,p_external_code;
1469 ELSIF g_party_id IS NOT NULL AND g_account_id IS NOT NULL THEN
1470
1471 OPEN csr_code_conv_party FOR l_sql USING p_external_code
1472 ,p_external_code
1473 ,p_external_code
1474 ,p_external_code
1475 ,g_party_id
1476 ,g_account_id;
1477 ELSIF g_party_id IS NOT NULL AND g_account_id IS NULL THEN
1478 OPEN csr_code_conv_party FOR l_sql USING p_external_code
1479 ,p_external_code
1480 ,p_external_code
1481 ,p_external_code
1482 ,g_party_id;
1483 END IF;
1484 FETCH csr_code_conv_party INTO l_dummy;
1485 CLOSE csr_code_conv_party;
1486 END IF;
1487 IF l_dummy <> 0 THEN
1488 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1489 FND_MESSAGE.Set_Name('OZF', 'OZF_RESALE_PARTY_DELETE');
1490 FND_MSG_PUB.ADD;
1491 END IF;
1492 RAISE FND_API.G_EXC_ERROR;
1493 END IF;
1494
1495
1496 ELSIF p_code_conversion_type = 'OZF_PARTY_SITE_CODES' THEN
1497
1498
1499 l_sql := 'SELECT ( CASE WHEN bill_to_location = :1 THEN 1'||
1500 ' WHEN ship_to_location = :1 THEN 1 '||
1501 ' ELSE 0 END ) pt_cnt'||
1502 ' FROM ';
1503 l_where := ' (bill_to_location = :1 '||
1504 ' OR ship_to_location = :1 )';
1505
1506 -- fix for 5226248
1507 IF G_PARTY_ID IS NOT NULL THEN
1508 l_sql := l_sql||'ozf_resale_batches bat, ozf_resale_lines lin ,ozf_resale_batch_line_maps map ';
1509 l_interface_sql := l_sql ||'ozf_resale_batches bat, ozf_resale_lines_int lin '||
1510 ' WHERE lin.resale_batch_id = bat.resale_batch_id ';
1511 l_sql := l_sql||'WHERE lin.resale_line_id = map.resale_line_id ';
1512 l_sql := l_sql||'AND map.resale_batch_id = bat.resale_batch_id ';
1513
1514 l_where := l_where ||' AND bat.partner_party_id = :2' ;
1515
1516 IF G_ACCOUNT_ID IS NOT NULL THEN
1517 l_where := l_where||' AND bat.partner_cust_account_id = :3 ' ;
1518 END IF;
1519
1520 l_sql := l_sql || 'AND '||l_where;
1521 l_interface_sql := l_interface_sql || 'AND '||l_where;
1522
1523 ELSIF G_PARTY_ID IS NULL AND G_ACCOUNT_ID IS NULL THEN
1524 l_interface_sql := l_sql ||' ozf_resale_lines_int WHERE ';
1525 l_sql := l_sql ||' ozf_resale_lines WHERE';
1526 l_sql := l_sql || l_where;
1527 l_interface_sql := l_interface_sql || l_where;
1528 END IF;
1529
1530 IF g_party_id IS NULL THEN
1531 OPEN csr_code_conv_party FOR l_interface_sql USING p_external_code
1532 ,p_external_code
1533 ,p_external_code
1534 ,p_external_code;
1535 ELSIF g_party_id IS NOT NULL AND g_account_id IS NOT NULL THEN
1536
1537 OPEN csr_code_conv_party FOR l_sql USING p_external_code
1538 ,p_external_code
1539 ,p_external_code
1540 ,p_external_code
1541 ,g_party_id
1542 ,g_account_id;
1543 ELSIF g_party_id IS NOT NULL AND g_account_id IS NULL THEN
1544 OPEN csr_code_conv_party FOR l_sql USING p_external_code
1545 ,p_external_code
1546 ,p_external_code
1547 ,p_external_code
1548 ,g_party_id;
1549 END IF;
1550 FETCH csr_code_conv_party INTO l_dummy;
1551 CLOSE csr_code_conv_party;
1552 IF l_dummy = 0 THEN
1553 IF g_party_id IS NULL THEN
1554 OPEN csr_code_conv_party FOR l_sql USING p_external_code
1555 ,p_external_code
1556 ,p_external_code
1557 ,p_external_code;
1558
1559 ELSIF g_party_id IS NOT NULL AND g_account_id IS NOT NULL THEN
1560
1561 OPEN csr_code_conv_party FOR l_sql USING p_external_code
1562 ,p_external_code
1563 ,p_external_code
1564 ,p_external_code
1565 ,g_party_id
1566 ,g_account_id;
1567 ELSIF g_party_id IS NOT NULL AND g_account_id IS NULL THEN
1568 OPEN csr_code_conv_party FOR l_sql USING p_external_code
1569 ,p_external_code
1570 ,p_external_code
1574 FETCH csr_code_conv_party INTO l_dummy;
1571 ,p_external_code
1572 ,g_party_id;
1573 END IF;
1575 CLOSE csr_code_conv_party;
1576 END IF;
1577 IF l_dummy <> 0 THEN
1578 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1579 FND_MESSAGE.Set_Name('OZF', 'OZF_RESALE_PARTY_SITE_DELETE');
1580 FND_MSG_PUB.ADD;
1581 END IF;
1582 RAISE FND_API.G_EXC_ERROR;
1583 END IF;
1584 END IF; -- p_code_conversion_type
1585
1586 END IF; -- p_external_code is not null
1587
1588 -- Api body
1589 --
1590 -- Debug Message
1591 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1592 OZF_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1593 END IF;
1594
1595 BEGIN
1596 OZF_CODE_CONVERSION_PKG.Delete_Row( p_code_conversion_id => p_code_conversion_id,
1597 p_object_version_number => p_object_version_number );
1598 EXCEPTION
1599 WHEN OTHERS THEN
1600 RAISE FND_API.G_EXC_ERROR;
1601 END;
1602
1603 -- Standard check for p_commit
1604 IF FND_API.to_Boolean( p_commit )
1605 THEN
1606 COMMIT WORK;
1607 END IF;
1608
1609 -- Debug Message
1610 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1611 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1612 END IF;
1613
1614 -- Standard call to get message count and if count is 1, get message info.
1615 FND_MSG_PUB.Count_And_Get
1616 (p_count => x_msg_count,
1617 p_data => x_msg_data
1618 );
1619 EXCEPTION
1620 WHEN FND_API.G_EXC_ERROR THEN
1621 ROLLBACK TO delete_code_conversion_pvt;
1622 x_return_status := FND_API.G_RET_STS_ERROR;
1623 -- Standard call to get message count and if count=1, get the message
1624 FND_MSG_PUB.Count_And_Get (
1625 p_encoded => FND_API.G_FALSE,
1626 p_count => x_msg_count,
1627 p_data => x_msg_data
1628 );
1629 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1630 ROLLBACK TO delete_code_conversion_pvt;
1631 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1632 -- Standard call to get message count and if count=1, get the message
1633 FND_MSG_PUB.Count_And_Get (
1634 p_encoded => FND_API.G_FALSE,
1635 p_count => x_msg_count,
1636 p_data => x_msg_data
1637 );
1638 WHEN OTHERS THEN
1639 ROLLBACK TO delete_code_conversion_pvt;
1640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1641 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1642 THEN
1643 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1644 END IF;
1645 -- Standard call to get message count and if count=1, get the message
1646 FND_MSG_PUB.Count_And_Get (
1647 p_encoded => FND_API.G_FALSE,
1648 p_count => x_msg_count,
1649 p_data => x_msg_data
1650 );
1651
1652 END Delete_Code_Conversion;
1653
1654 PROCEDURE Check_uniq_code_conversion(
1655 p_code_conversion_rec IN code_conversion_rec_type,
1656 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1657 x_return_status OUT NOCOPY VARCHAR2
1658 )
1659 IS
1660 l_api_name CONSTANT VARCHAR2(30) := 'Check_uniq_code_conversion';
1661 l_api_version_number CONSTANT NUMBER := 1.0;
1662
1663 CURSOR csr_code_conv_4party(cv_party_id NUMBER
1664 , cv_external_code VARCHAR2
1665 , cv_start_date_active DATE
1666 , cv_end_date_active DATE
1667 , cv_code_conversion_type VARCHAR2
1668 , cv_org_id NUMBER)
1669 IS
1670 SELECT COUNT(party_id)
1671 FROM ozf_code_conversions
1672 WHERE party_id = cv_party_id
1673 AND code_conversion_type = cv_code_conversion_type -- Bug#11906081
1674 AND org_id = NVL(cv_org_id,0)
1675 AND UPPER(external_code) LIKE UPPER(cv_external_code)
1676 AND TRUNC(start_date_active) <= TRUNC(NVL(cv_start_date_active,SYSDATE))
1677 AND TRUNC(NVL(end_date_active,NVL(cv_end_date_active,SYSDATE)+1)) >= TRUNC(NVL(cv_end_date_active,SYSDATE))
1678 AND TRUNC(NVL(end_date_active,NVL(cv_start_date_active,SYSDATE)+1)) >= TRUNC(NVL(cv_start_date_active,SYSDATE))
1679 AND cust_account_id IS NULL;
1680
1681 CURSOR csr_code_conv_4acct(cv_cust_account_id NUMBER
1682 ,cv_external_code VARCHAR2
1683 ,cv_start_date_active DATE
1684 ,cv_end_date_active DATE
1685 ,cv_code_conversion_type VARCHAR2
1686 ,cv_org_id NUMBER)
1687 IS
1688 SELECT COUNT(cust_account_id)
1689 FROM ozf_code_conversions
1690 WHERE cust_account_id = cv_cust_account_id
1691 AND code_conversion_type = cv_code_conversion_type -- Bug#11906081
1692 AND org_id = NVL(cv_org_id,0)
1693 AND UPPER(external_code) LIKE UPPER(cv_external_code)
1694 AND TRUNC(start_date_active) <= TRUNC(NVL(cv_start_date_active,SYSDATE))
1695 AND TRUNC(NVL(end_date_active,NVL(cv_start_date_active,SYSDATE)+1)) >= TRUNC(NVL(cv_start_date_active,SYSDATE))
1696 AND TRUNC(NVL(end_date_active,NVL(cv_end_date_active,SYSDATE)+1)) >= TRUNC(NVL(cv_end_date_active,SYSDATE));
1697
1698 CURSOR csr_code_conv_4org(cv_external_code VARCHAR2,
1699 cv_start_date_active DATE,
1700 cv_end_date_active DATE,
1701 cv_code_conversion_type VARCHAR2,
1702 cv_org_id NUMBER)
1703 IS
1704 SELECT COUNT(external_code)
1705 FROM ozf_code_conversions
1706 WHERE UPPER(external_code) LIKE UPPER(cv_external_code)
1707 AND code_conversion_type = cv_code_conversion_type -- Bug#11906081
1708 AND org_id = NVL(cv_org_id,0)
1709 AND TRUNC(start_date_active) <= TRUNC(NVL(cv_start_date_active,SYSDATE))
1710 AND TRUNC(NVL(end_date_active,NVL(cv_start_date_active,SYSDATE)+1)) >= TRUNC(NVL(cv_start_date_active,SYSDATE))
1711 AND TRUNC(NVL(end_date_active,NVL(cv_end_date_active,SYSDATE)+1)) >= TRUNC(NVL(cv_end_date_active,SYSDATE))
1712 AND party_id IS NULL
1713 AND cust_account_id IS NULL;
1714
1715
1716 l_party_dummy NUMBER;
1717 l_acct_dummy NUMBER;
1718 l_valid_flag VARCHAR2(30);
1719 l_org_dummy NUMBER := 0;
1720 l_external_code VARCHAR2(30);
1721
1722
1723 BEGIN
1724
1725 x_return_status := FND_API.g_ret_sts_success;
1726 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1727 OZF_UTILITY_PVT.debug_message('in '||l_api_name);
1728 OZF_UTILITY_PVT.debug_message('Dummy value ' || l_org_dummy);
1729 END IF;
1730 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1731
1732 IF p_code_conversion_rec.cust_account_id = FND_API.g_miss_num OR
1733 p_code_conversion_rec.cust_account_id IS NULL
1734 THEN
1735 IF p_code_conversion_rec.party_id = FND_API.g_miss_num OR
1736 p_code_conversion_rec.party_id IS NULL
1737 THEN
1738 l_org_dummy := 0;
1739
1740 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1741 OZF_UTILITY_PVT.debug_message('External code ' ||p_code_conversion_rec.external_code );
1742 OZF_UTILITY_PVT.debug_message('Start date active '|| p_code_conversion_rec.start_date_active );
1743 OZF_UTILITY_PVT.debug_message('End date active ' || p_code_conversion_rec.end_date_active );
1744 OZF_UTILITY_PVT.debug_message('Org ID ' || p_code_conversion_rec.org_id );
1745 END IF;
1746
1747 OPEN csr_code_conv_4org(p_code_conversion_rec.external_code,
1748 p_code_conversion_rec.start_date_active,
1749 p_code_conversion_rec.end_date_active,
1750 p_code_conversion_rec.code_conversion_type, -- Bug#11906081
1751 p_code_conversion_rec.org_id);
1752 FETCH csr_code_conv_4org
1753 INTO l_org_dummy;
1754 CLOSE csr_code_conv_4org;
1755
1756
1757 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1758 OZF_UTILITY_PVT.debug_message('Duplicate code map value ' || p_code_conversion_rec.external_code || ' for org ' || p_code_conversion_rec.org_id );
1759 OZF_UTILITY_PVT.debug_message('and internal code ' ||p_code_conversion_rec.internal_code );
1760 OZF_UTILITY_PVT.debug_message('with start date active '|| p_code_conversion_rec.start_date_active );
1761 OZF_UTILITY_PVT.debug_message('and end date active ' || p_code_conversion_rec.end_date_active );
1762 OZF_UTILITY_PVT.debug_message('External Code ' || l_external_code);
1763 OZF_UTILITY_PVT.debug_message('Org ID ' || p_code_conversion_rec.org_id );
1764 END IF;
1765 IF l_org_dummy > 0 THEN
1766 l_valid_flag := FND_API.g_false;
1767 END IF;
1768 ELSE
1769 l_party_dummy := NULL;
1770 OPEN csr_code_conv_4party(p_code_conversion_rec.party_id,
1771 p_code_conversion_rec.external_code,
1772 p_code_conversion_rec.start_date_active,
1773 p_code_conversion_rec.end_date_active,
1774 p_code_conversion_rec.code_conversion_type,
1775 p_code_conversion_rec.org_id); -- Bug#11906081
1776 FETCH csr_code_conv_4party
1777 INTO l_party_dummy;
1778 CLOSE csr_code_conv_4party;
1779
1780 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1781 OZF_UTILITY_PVT.debug_message('Duplicate code map value ' || p_code_conversion_rec.external_code || ' for party ' || p_code_conversion_rec.party_id );
1782 OZF_UTILITY_PVT.debug_message('and internal code ' ||p_code_conversion_rec.internal_code );
1783 OZF_UTILITY_PVT.debug_message('with start date active '|| p_code_conversion_rec.start_date_active );
1784 OZF_UTILITY_PVT.debug_message('and end date active ' || p_code_conversion_rec.end_date_active );
1785 OZF_UTILITY_PVT.debug_message('Org ID ' || p_code_conversion_rec.org_id );
1786 END IF;
1787
1788 IF l_party_dummy > 0 THEN
1789 l_valid_flag := FND_API.g_false;
1790 END IF;
1791
1792 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1793 OZF_UTILITY_PVT.debug_message('Count of code map for party ' || l_party_dummy);
1794 END IF;
1795 END IF;
1796 ELSE
1797 l_acct_dummy := NULL;
1798 OPEN csr_code_conv_4acct(p_code_conversion_rec.cust_account_id,
1799 p_code_conversion_rec.external_code,
1800 p_code_conversion_rec.start_date_active,
1801 p_code_conversion_rec.end_date_active,
1802 p_code_conversion_rec.code_conversion_type, -- Bug#11906081
1803 p_code_conversion_rec.org_id);
1804 FETCH csr_code_conv_4acct INTO l_acct_dummy;
1805 CLOSE csr_code_conv_4acct;
1806
1807 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1808 OZF_UTILITY_PVT.debug_message('Duplicate code map value ' || p_code_conversion_rec.external_code || ' for account ' || p_code_conversion_rec.cust_account_id );
1809 OZF_UTILITY_PVT.debug_message('and internal code ' ||p_code_conversion_rec.internal_code );
1810 OZF_UTILITY_PVT.debug_message('with start date active '|| p_code_conversion_rec.start_date_active );
1811 OZF_UTILITY_PVT.debug_message('and end date active ' ||nvl(p_code_conversion_rec.end_date_active,sysdate) );
1812 OZF_UTILITY_PVT.debug_message('Count of code map for account ' || l_acct_dummy);
1813 OZF_UTILITY_PVT.debug_message('Org ID ' || p_code_conversion_rec.org_id );
1814 END IF;
1815 IF l_acct_dummy > 0 THEN
1816 l_valid_flag := FND_API.g_false;
1817 END IF;
1818 END IF;
1819 ELSE
1820 l_valid_flag := FND_API.g_true;
1821 END IF;
1822
1823 IF l_valid_flag = FND_API.g_false THEN
1824 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1825 FND_MESSAGE.set_name('OZF', 'OZF_CODE_CONVERSION_DUPLICATE');
1826 FND_MSG_PUB.add;
1827 END IF;
1828 x_return_status := FND_API.G_RET_STS_ERROR;
1829 END IF;
1830
1831 END Check_Uniq_Code_Conversion;
1832
1833
1834 -- Start of Comments
1835 --
1836 -- Required Items Check procedure
1837 --
1838 -- p_validation_mode is a constant defined in OZF_UTILITY_PVT package
1839 -- For create: G_CREATE, for update: G_UPDATE
1840 -- End of Comments
1841
1842 PROCEDURE Check_Code_Conv_Req_Items
1843 (
1844 p_code_conversion_rec IN code_conversion_rec_type,
1845 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1846 x_return_status OUT NOCOPY VARCHAR2
1847 )
1848 IS
1849 l_api_name CONSTANT VARCHAR2(30) := 'Check_Code_Conv_Req_Items';
1850 l_api_version_number CONSTANT NUMBER := 1.0;
1851
1852 BEGIN
1853
1854 x_return_status := FND_API.g_ret_sts_success;
1855
1856 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1857 OZF_UTILITY_PVT.debug_message('in '||l_api_name);
1858 END IF;
1859
1860 IF p_code_conversion_rec.external_code = FND_API.g_miss_char OR
1861 p_code_conversion_rec.external_code IS NULL
1862 THEN
1863 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1864 FND_MESSAGE.set_name('OZF', 'OZF_EXTERNAL_CODE_MISSING'||NVL(p_code_conversion_rec.external_code,'NULL'));
1865 FND_MSG_PUB.add;
1866 END IF;
1867 x_return_status := FND_API.G_RET_STS_ERROR;
1868 RETURN;
1869 END IF;
1870
1871 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1872 OZF_UTILITY_PVT.debug_message('external code '||p_code_conversion_rec.external_code);
1873 OZF_UTILITY_PVT.debug_message('end of check_code_conv_req_items');
1874 END IF;
1875
1876 END check_code_conv_req_items;
1877
1878 -- Start of Comments
1879 --
1880 -- Start date and End Date Check
1881 --
1882 -- End of Comments
1883
1884 PROCEDURE Check_Code_Conversion_Dt
1885 (
1886 p_code_conversion_rec IN code_conversion_rec_type,
1887 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1888 x_return_status OUT NOCOPY VARCHAR2
1889 )
1890 IS
1891 l_api_name CONSTANT VARCHAR2(30) := 'check_code_conversion_dt';
1892 l_api_version_number CONSTANT NUMBER := 1.0;
1893
1894 BEGIN
1895
1896 x_return_status := FND_API.g_ret_sts_success;
1897
1898 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1899 OZF_UTILITY_PVT.debug_message('in '||l_api_name);
1900 END IF;
1901
1902 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1903
1904 IF NVL(p_code_conversion_rec.start_date_active,TRUNC(SYSDATE)) < TRUNC(SYSDATE)
1905 THEN
1906 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1907 FND_MESSAGE.set_name('OZF', 'OZF_CODE_CONV_STDATE_BKDATED');
1908 FND_MSG_PUB.add;
1909 END IF;
1910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1911 END IF;
1912 END IF;
1913
1914 -- Fix for 3928270
1915
1916 -- IF NVL(p_code_conversion_rec.end_date_active,TRUNC(SYSDATE)) < TRUNC(SYSDATE)
1917 IF (TO_DATE(TO_CHAR(NVL(p_code_conversion_rec.end_date_active,TRUNC(SYSDATE)), 'DD/MM/YYYY'),'DD/MM/YYYY') < TRUNC(SYSDATE))
1918 THEN
1919 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1920 FND_MESSAGE.set_name('OZF', 'OZF_CODE_CONV_ENDDATE_BKDATED');
1921 FND_MSG_PUB.add;
1922 END IF;
1923 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1924 END IF;
1925
1926 -- Fix for 3928270
1927
1928 --IF NVL(p_code_conversion_rec.Start_Date_Active,TRUNC(SYSDATE)) >
1929 -- NVL(p_code_conversion_rec.end_date_active,NVL(p_code_conversion_rec.Start_Date_Active,TRUNC(SYSDATE)) + 1)
1930 IF (TO_DATE(TO_CHAR(NVL(p_code_conversion_rec.Start_Date_Active,TRUNC(SYSDATE)),'DD/MM/YYYY'),'DD/MM/YYYY') >
1931 TO_DATE(TO_CHAR(NVL(p_code_conversion_rec.end_date_active, NVL(p_code_conversion_rec.Start_Date_Active,TRUNC(SYSDATE)) +1 ), 'DD/MM/YYYY'),'DD/MM/YYYY'))
1932 THEN
1933 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1934 FND_MESSAGE.set_name('OZF', 'OZF_CODE_CONV_STDATE_GREATE');
1935 FND_MSG_PUB.add;
1939
1936 END IF;
1937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1938 END IF;
1940 END check_code_conversion_dt;
1941
1942 PROCEDURE check_code_conversion_items
1943 (
1944 p_code_conversion_rec IN code_conversion_rec_type,
1945 p_validation_mode IN VARCHAR2,
1946 x_return_status OUT NOCOPY VARCHAR2
1947 )
1948
1949 IS
1950 l_api_name CONSTANT VARCHAR2(30) := 'check_code_conversion_items';
1951 l_api_version_number CONSTANT NUMBER := 1.0;
1952
1953 BEGIN
1954
1955 x_return_status := FND_API.g_ret_sts_success;
1956
1957 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1958 OZF_UTILITY_PVT.debug_message('in '||l_api_name);
1959 END IF;
1960
1961 check_code_conv_req_items( p_code_conversion_rec,
1962 p_validation_mode,
1963 x_return_status
1964 );
1965 IF x_return_status <> FND_API.g_ret_sts_success THEN
1966 RETURN;
1967 END IF;
1968
1969 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1970 OZF_UTILITY_PVT.debug_message('after check_code_conv_req_items ' );
1971 END IF;
1972
1973
1974 check_uniq_code_conversion( p_code_conversion_rec,
1975 p_validation_mode,
1976 x_return_status
1977 );
1978 IF x_return_status <> FND_API.g_ret_sts_success THEN
1979 RETURN;
1980 END IF;
1981
1982 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1983 OZF_UTILITY_PVT.debug_message('after check_uniq_code_conversion ' );
1984 END IF;
1985
1986 check_code_conversion_dt (p_code_conversion_rec,
1987 p_validation_mode,
1988 x_return_status
1989 );
1990 IF x_return_status <> FND_API.g_ret_sts_success Then
1991 RETURN;
1992 END IF;
1993
1994 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
1995 OZF_UTILITY_PVT.debug_message('after check_code_conversion_dt ' );
1996 END IF;
1997
1998 END check_code_conversion_items;
1999 -- Start of Comments
2000 --
2001 -- validation procedures
2002 --
2003 -- p_validation_mode is a constant defined in OZF_UTILITY_PVT package
2004 -- For create: G_CREATE, for update: G_UPDATE
2005 -- End of Comments
2006
2007 PROCEDURE Validate_Code_Conversion(
2008 p_api_version_number IN NUMBER,
2009 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2010 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2011 p_validation_mode IN VARCHAR2,
2012 p_code_conversion_tbl IN code_conversion_tbl_type,
2013 x_return_status OUT NOCOPY VARCHAR2,
2014 x_msg_count OUT NOCOPY NUMBER,
2015 x_msg_data OUT NOCOPY VARCHAR2
2016 )
2017
2018 IS
2019 l_api_name CONSTANT VARCHAR2(30) := 'Validate_code_conversion';
2020 l_api_version_number CONSTANT NUMBER := 1.0;
2021 l_object_version_number NUMBER;
2022
2023 l_code_conversion_rec code_conversion_rec_type ;
2024
2025
2026 BEGIN
2027 -- Standard Start of API savepoint
2028 SAVEPOINT validate_code_conversion_pvt;
2029
2030 -- Standard call to check for call compatibility.
2031 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2032 p_api_version_number,
2033 l_api_name,
2034 G_PKG_NAME)
2035 THEN
2036 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2037 END IF;
2038
2039 -- Initialize message list if p_init_msg_list is set to TRUE.
2040 IF FND_API.to_Boolean( p_init_msg_list )
2041 THEN
2042 FND_MSG_PUB.initialize;
2043 END IF;
2044 -- Call the Validate Item procedure for the item(field level validations )
2045 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2046 OZF_UTILITY_PVT.debug_message('in '||l_api_name );
2047 END IF;
2048 x_return_status := FND_API.g_ret_sts_success;
2049
2050 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
2051
2052 FOR i in 1 .. p_code_conversion_tbl.count
2053 LOOP
2054 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2055 OZF_UTILITY_PVT.debug_message('inside the loop p_code_conversion_tbl ' );
2056 END IF;
2057 l_code_conversion_rec := p_code_conversion_tbl(i);
2058
2059 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2060 OZF_UTILITY_PVT.debug_message('External code ' || l_code_conversion_rec.external_code );
2061 OZF_UTILITY_PVT.debug_message('Start date active '|| l_code_conversion_rec.start_date_active );
2062 OZF_UTILITY_PVT.debug_message('End date active ' || l_code_conversion_rec.end_date_active );
2063 END IF;
2064
2065 check_code_conversion_items(
2066 p_code_conversion_rec => l_code_conversion_rec,
2067 p_validation_mode => p_validation_mode,
2068 x_return_status => x_return_status);
2069
2070
2071 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2072 RAISE FND_API.G_EXC_ERROR;
2073 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2075 END IF;
2076 END LOOP;
2077
2078 END IF;
2079 -- Initialize API return status to SUCCESS
2080 x_return_status := FND_API.G_RET_STS_SUCCESS;
2081 -- Standard call to get message count and if count is 1, get message info.
2082 FND_MSG_PUB.Count_And_Get
2083 (p_count => x_msg_count,
2084 p_data => x_msg_data
2085 );
2086 EXCEPTION
2087 WHEN OZF_Utility_PVT.resource_locked THEN
2088 x_return_status := FND_API.g_ret_sts_error;
2089 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2090 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCE_LOCKED ');
2091 FND_MSG_PUB.add;
2092 END IF;
2093 WHEN FND_API.G_EXC_ERROR THEN
2094 ROLLBACK TO validate_code_conversion_pvt;
2095 x_return_status := FND_API.G_RET_STS_ERROR;
2096 -- Standard call to get message count and if count=1, get the message
2097 FND_MSG_PUB.Count_And_Get (
2098 p_encoded => FND_API.G_FALSE,
2099 p_count => x_msg_count,
2100 p_data => x_msg_data
2101 );
2102 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2103 ROLLBACK TO validate_code_conversion_pvt;
2104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2105 -- Standard call to get message count and if count=1, get the message
2106 FND_MSG_PUB.Count_And_Get (
2107 p_encoded => FND_API.G_FALSE,
2108 p_count => x_msg_count,
2109 p_data => x_msg_data
2110 );
2111 WHEN OTHERS THEN
2112 ROLLBACK TO validate_code_conversion_pvt;
2113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2114 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2115 THEN
2116 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2117 END IF;
2118 -- Standard call to get message count and if count=1, get the message
2119 FND_MSG_PUB.Count_And_Get (
2120 p_encoded => FND_API.G_FALSE,
2121 p_count => x_msg_count,
2122 p_data => x_msg_data
2123 );
2124
2125 END Validate_code_conversion;
2126
2127
2128 -- Start of Comments
2129 --
2130 -- get_claim_reason
2131 -- Translate the Customer Reason to the Internal (Oracle) Reason.
2132 --
2133 -- End of Comments
2134 PROCEDURE convert_code(
2135 p_cust_account_id IN NUMBER,
2136 p_party_id IN NUMBER, -- added new
2137 p_code_conversion_type IN VARCHAR2,
2138 p_external_code IN VARCHAR2,
2139 x_internal_code OUT NOCOPY VARCHAR2,
2140 x_return_status OUT NOCOPY VARCHAR2,
2141 x_msg_count OUT NOCOPY NUMBER,
2142 x_msg_data OUT NOCOPY VARCHAR2
2143 )
2144
2145 IS
2146
2147 l_api_name CONSTANT VARCHAR2(30) := 'convert_code';
2148 l_api_version_number CONSTANT NUMBER := 1.0;
2149
2150 CURSOR csr_get_internal_code_Acct( cv_Cust_Account_Id NUMBER
2151 , cv_external_code VARCHAR2
2152 , cv_conv_type varchar2 )
2153 IS
2154 SELECT internal_code
2155 FROM ozf_code_conversions
2156 WHERE cust_account_id = cv_cust_account_id
2157 AND UPPER(external_code) LIKE UPPER(cv_external_code)
2158 AND start_date_active <= SYSDATE
2159 AND (end_date_active >= SYSDATE
2160 OR end_date_active IS NULL)
2161 AND code_conversion_type = cv_conv_type;
2162
2163 CURSOR csr_get_internal_code_Party( cv_party_Id NUMBER,
2164 cv_external_code VARCHAR2,
2165 cv_conv_type VARCHAR2 ) IS
2166 SELECT internal_code
2167 FROM ozf_code_conversions
2168 WHERE party_id = cv_party_id
2169 AND UPPER(external_code) LIKE UPPER(cv_external_code)
2170 AND start_date_active <= SYSDATE
2171 AND (end_date_active >= SYSDATE
2172 OR end_date_active IS NULL)
2173 AND cust_account_id IS NULL
2174 AND code_conversion_type = cv_conv_type;
2175
2176 CURSOR csr_get_internal_code( cv_external_code VARCHAR2,
2177 cv_conv_type VARCHAR2) IS
2178 SELECT internal_code
2179 FROM ozf_code_conversions
2180 WHERE UPPER(external_code) LIKE UPPER(cv_external_code)
2181 AND start_date_active <= SYSDATE
2182 AND (end_date_active >= SYSDATE
2183 or end_date_active IS NULL)
2184 AND party_id IS NULL
2185 AND cust_account_id IS NULL
2186 AND code_conversion_type = cv_conv_type;
2187
2188 l_external_code VARCHAR2(150) := NULL;
2189 l_internal_code VARCHAR2(150) := NULL;
2190
2191 l_party_id NUMBER := Null;
2192 l_org_id NUMBER := null;
2193
2194
2195 BEGIN
2196
2197 X_Return_Status := FND_API.g_ret_sts_success;
2198
2199 --- in case of multiple rows what will be the result? error out or get the first record.
2200
2201 OPEN csr_get_internal_code_Acct(p_cust_account_id
2202 , p_external_code
2203 , p_code_conversion_type);
2204 FETCH csr_get_internal_code_Acct
2205 INTO l_internal_code;
2206 CLOSE csr_get_internal_code_Acct;
2207
2208 x_internal_code := NULL;
2209
2210 IF l_internal_code IS NULL THEN
2211 OPEN csr_get_internal_code_party(p_party_id,
2212 p_external_code,
2213 p_code_conversion_type);
2214 FETCH csr_get_internal_code_party Into l_internal_code;
2215
2216 IF csr_get_internal_code_party%NOTFOUND THEN
2217 OPEN csr_get_internal_code( p_external_code
2218 ,p_code_conversion_type);
2219 FETCH csr_get_internal_code INTO l_internal_code;
2220
2221 IF csr_get_internal_code%NOTFOUND THEN
2222 l_internal_code := NULL;
2223 END IF;
2224 CLOSE csr_get_internal_code;
2225
2226 END IF;
2227
2228
2229 CLOSE csr_get_internal_code_party;
2230 END IF;
2231
2232 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2233 OZF_UTILITY_PVT.debug_message(' Internal Code ' || l_internal_code);
2234 END IF;
2235 x_internal_code := l_internal_code;
2236
2237 EXCEPTION
2238 WHEN OTHERS THEN
2239
2240 x_return_status := FND_API.g_ret_sts_unexp_error;
2241
2242 IF csr_get_internal_code_acct%ISOPEN THEN
2243 CLOSE csr_get_internal_code_acct;
2244 END IF;
2245
2246 IF csr_get_internal_code_party%ISOPEN THEN
2247 CLOSE csr_get_internal_code_party;
2248 END IF;
2249
2250 IF csr_get_internal_code%ISOPEN THEN
2251 CLOSE csr_get_internal_code;
2252 END IF;
2253 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2254 THEN
2255 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2256 END IF;
2257 -- Standard call to get message count and if count=1, get the message
2258 FND_MSG_PUB.Count_And_Get (
2259 p_encoded => FND_API.G_FALSE,
2260 p_count => x_msg_count,
2261 p_data => x_msg_data
2262 );
2263
2264
2265 END Convert_Code;
2266
2267 PROCEDURE Check_uniq_supp_code_conv(
2268 p_supp_code_conversion_rec IN supp_code_conversion_rec_type,
2269 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2270 x_return_status OUT NOCOPY VARCHAR2
2271 )
2272 IS
2273 l_api_name CONSTANT VARCHAR2(50) := 'Check_uniq_supp_code_conversion';
2274 l_api_version_number CONSTANT NUMBER := 1.0;
2275
2276 CURSOR csr_code_conv(cv_supp_trade_profile_id NUMBER
2277 , cv_external_code VARCHAR2
2278 , cv_internal_code VARCHAR2
2279 , cv_start_date_active DATE
2280 , cv_end_date_active DATE
2281 , cv_conv_id NUMBER := -1)
2282 IS
2283 select code_conversion_id from ozf_supp_code_conversions_all where external_code = cv_external_code
2284 and code_conversion_id <> cv_conv_id
2285 and supp_trade_profile_id = cv_supp_trade_profile_id
2286 and ( to_date(cv_start_date_active,'dd-mm-yyyy') between
2287 to_date(start_date_active,'dd-mm-yyyy') and nvl(end_date_active,to_Date('31-12-9999','dd-mm-yyyy'))
2288 or nvl(to_date(cv_end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')) between
2289 to_date(start_date_Active,'dd-mm-yyyy') and nvl(to_date(end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')))
2290 union
2291 select code_conversion_id from ozf_supp_code_conversions_all where internal_code = cv_internal_code
2292 and code_conversion_id <> cv_conv_id
2293 and supp_trade_profile_id = cv_supp_trade_profile_id
2294 and ( to_date(cv_start_date_active,'dd-mm-yyyy') between to_date(start_date_active,'dd-mm-yyyy')
2295 and nvl(end_date_active,to_Date('31-12-9999','dd-mm-yyyy'))
2296 or nvl(to_date(cv_end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')) between
2297 to_date(start_date_Active,'dd-mm-yyyy') and nvl(to_date(end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')));
2298
2299 l_valid_flag VARCHAR2(30);
2300 l_dummy NUMBER := 0;
2301 l_external_code VARCHAR2(30);
2302
2303
2304 BEGIN
2305 /* 3/25/2008 -gdeepika- Bug 6832508 */
2306 /* For the code conversions at a supplier site , only one valid internal code
2307 should exist for a particular external code on a particular date.*/
2308 x_return_status := FND_API.g_ret_sts_success;
2309 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2310 OZF_UTILITY_PVT.debug_message('in '||l_api_name);
2311 OZF_UTILITY_PVT.debug_message('Dummy value ' || l_dummy);
2312 END IF;
2313
2314 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
2315
2316 OPEN csr_code_conv(p_supp_code_conversion_rec.supp_trade_profile_id,
2317 p_supp_code_conversion_rec.external_code,
2318 p_supp_code_conversion_rec.internal_code,
2319 p_supp_code_conversion_rec.start_date_active,
2320 p_supp_code_conversion_rec.end_date_active);
2321
2322
2323 ELSIF p_validation_mode = JTF_PLSQL_API.g_update THEN
2324 OPEN csr_code_conv(p_supp_code_conversion_rec.supp_trade_profile_id,
2325 p_supp_code_conversion_rec.external_code,
2326 p_supp_code_conversion_rec.internal_code,
2327 p_supp_code_conversion_rec.start_date_active,
2328 p_supp_code_conversion_rec.end_date_active,
2329 p_supp_code_conversion_rec.code_conversion_id);
2330
2331 END IF;
2332 FETCH csr_code_conv
2333 INTO l_dummy;
2334 CLOSE csr_code_conv;
2335
2336 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2340 OZF_UTILITY_PVT.debug_message('and end date active ' || p_supp_code_conversion_rec.end_date_active );
2337 OZF_UTILITY_PVT.debug_message('Duplicate code map value ' || p_supp_code_conversion_rec.external_code || ' for org ' || p_supp_code_conversion_rec.org_id );
2338 OZF_UTILITY_PVT.debug_message('and internal code ' ||p_supp_code_conversion_rec.internal_code );
2339 OZF_UTILITY_PVT.debug_message('with start date active '|| p_supp_code_conversion_rec.start_date_active );
2341 OZF_UTILITY_PVT.debug_message('External Code ' || l_external_code);
2342 END IF;
2343 IF l_dummy > 0 THEN
2344 l_valid_flag := FND_API.g_false;
2345
2346 ELSE
2347 l_valid_flag := FND_API.g_true;
2348
2349 END IF;
2350
2351 IF l_valid_flag = FND_API.g_false THEN
2352 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2353 FND_MESSAGE.set_name('OZF', 'OZF_CODE_CONVERSION_DUPLICATE');
2354 FND_MSG_PUB.add;
2355 END IF;
2356 x_return_status := FND_API.G_RET_STS_ERROR;
2357 RETURN;
2358 END IF;
2359
2360
2361 END Check_Uniq_supp_Code_Conv;
2362
2363 -- Start of Comments
2364 --
2365 -- Required Items Check procedure
2366 --
2367 -- p_validation_mode is a constant defined in OZF_UTILITY_PVT package
2368 -- For create: G_CREATE, for update: G_UPDATE
2369 -- End of Comments
2370
2371 PROCEDURE Check_supp_code_Conv_Req_Items
2372 (
2373 p_supp_code_conversion_rec IN supp_code_conversion_rec_type,
2374 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2375 x_return_status OUT NOCOPY VARCHAR2
2376 )
2377 IS
2378 l_api_name CONSTANT VARCHAR2(50) := 'Check_supp_code_Conv_Req_Items';
2379 l_api_version_number CONSTANT NUMBER := 1.0;
2380
2381 BEGIN
2382
2383 x_return_status := FND_API.g_ret_sts_success;
2384
2385 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2386 OZF_UTILITY_PVT.debug_message('in '||l_api_name);
2387 END IF;
2388
2389 IF p_supp_code_conversion_rec.external_code = FND_API.g_miss_char OR
2390 p_supp_code_conversion_rec.external_code IS NULL
2391 THEN
2392 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2393 FND_MESSAGE.set_name('OZF', 'OZF_EXTERNAL_CODE_MISSING'||NVL(p_supp_code_conversion_rec.external_code,'NULL'));
2394 FND_MSG_PUB.add;
2395 END IF;
2396 x_return_status := FND_API.G_RET_STS_ERROR;
2397 RETURN;
2398 END IF;
2399
2400 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2401 OZF_UTILITY_PVT.debug_message('external code '||p_supp_code_conversion_rec.external_code);
2402 OZF_UTILITY_PVT.debug_message('end of check_supp_code_conv_req_items');
2403 END IF;
2404
2405 END check_supp_code_conv_req_items;
2406
2407 -- Start of Comments
2408 --
2409 -- Start date and End Date Check
2410 --
2411 -- End of Comments
2412
2413 PROCEDURE Check_supp_code_Conversion_Dt
2414 (
2415 p_supp_code_conversion_rec IN supp_code_conversion_rec_type,
2416 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2417 x_return_status OUT NOCOPY VARCHAR2
2418 )
2419 IS
2420 l_api_name CONSTANT VARCHAR2(50) := 'check_supp_code_conversion_dt';
2421 l_api_version_number CONSTANT NUMBER := 1.0;
2422
2423 BEGIN
2424
2425 x_return_status := FND_API.g_ret_sts_success;
2426
2427 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2428 OZF_UTILITY_PVT.debug_message('in '||l_api_name);
2429 END IF;
2430
2431 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
2432
2433 IF NVL(p_supp_code_conversion_rec.start_date_active,TRUNC(SYSDATE)) < TRUNC(SYSDATE)
2434 THEN
2435 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2436 FND_MESSAGE.set_name('OZF', 'OZF_CODE_CONV_STDATE_BKDATED');
2437 FND_MSG_PUB.add;
2438 END IF;
2439 x_return_status := FND_API.G_RET_STS_ERROR;
2440 RETURN;
2441 END IF;
2442 END IF;
2443
2444 -- Fix for 3928270
2445
2446 -- IF NVL(p_supp_code_conversion_rec.end_date_active,TRUNC(SYSDATE)) < TRUNC(SYSDATE)
2447 IF (TO_DATE(TO_CHAR(NVL(p_supp_code_conversion_rec.end_date_active,TRUNC(SYSDATE)), 'DD/MM/YYYY'),'DD/MM/YYYY') < TRUNC(SYSDATE))
2448 THEN
2449 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2450 FND_MESSAGE.set_name('OZF', 'OZF_CODE_CONV_ENDDATE_BKDATED');
2451 FND_MSG_PUB.add;
2452 END IF;
2453 x_return_status := FND_API.G_RET_STS_ERROR;
2454 RETURN;
2455 END IF;
2456
2457
2458 IF (TO_DATE(TO_CHAR(NVL(p_supp_code_conversion_rec.Start_Date_Active,TRUNC(SYSDATE)),'DD/MM/YYYY'),'DD/MM/YYYY') >
2459 TO_DATE(TO_CHAR(NVL(p_supp_code_conversion_rec.end_date_active, NVL(p_supp_code_conversion_rec.Start_Date_Active,TRUNC(SYSDATE)) +1 ), 'DD/MM/YYYY'),'DD/MM/YYYY'))
2460 THEN
2461 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2462 FND_MESSAGE.set_name('OZF', 'OZF_CODE_CONV_STDATE_GREATE');
2463 FND_MSG_PUB.add;
2464 END IF;
2465 x_return_status := FND_API.G_RET_STS_ERROR;
2466 RETURN;
2467 END IF;
2468
2469 END check_supp_code_conversion_dt;
2470
2471
2472 -- Start of Comments
2473 --
2474 -- validation procedures
2475 --
2476 -- p_validation_mode is a constant defined in OZF_UTILITY_PVT package
2480
2477 -- For create: G_CREATE, for update: G_UPDATE
2478 -- End of Comments
2479
2481
2482 -- Start of Comments
2483 --
2484 -- get_claim_reason
2485 -- Translate the Customer Reason to the Internal (Oracle) Reason.
2486 --
2487 -- End of Comments
2488 PROCEDURE convert_supp_code(
2489 p_supp_trade_profile_id IN NUMBER,
2490 p_code_conversion_type IN VARCHAR2,
2491 p_external_code IN VARCHAR2,
2492 x_internal_code OUT NOCOPY VARCHAR2,
2493 x_return_status OUT NOCOPY VARCHAR2,
2494 x_msg_count OUT NOCOPY NUMBER,
2495 x_msg_data OUT NOCOPY VARCHAR2
2496 )
2497
2498 IS
2499
2500 l_api_name CONSTANT VARCHAR2(30) := 'convert_code';
2501 l_api_version_number CONSTANT NUMBER := 1.0;
2502
2503
2504 CURSOR csr_get_internal_code( cv_supp_trade_profile_id NUMBER,
2505 cv_external_code VARCHAR2,
2506 cv_conv_type VARCHAR2) IS
2507 SELECT internal_code
2508 FROM ozf_supp_code_conversions
2509 WHERE UPPER(external_code) LIKE UPPER(cv_external_code)
2510 AND start_date_active <= SYSDATE
2511 AND (end_date_active >= SYSDATE
2512 or end_date_active IS NULL)
2513 AND code_conversion_type = cv_conv_type
2514 AND supp_trade_profile_id = cv_supp_trade_profile_id;
2515
2516 l_external_code VARCHAR2(150) := NULL;
2517 l_internal_code VARCHAR2(150) := NULL;
2518
2519
2520 BEGIN
2521
2522 X_Return_Status := FND_API.g_ret_sts_success;
2523
2524
2525 OPEN csr_get_internal_code( p_supp_trade_profile_id
2526 ,p_external_code
2527 ,p_code_conversion_type);
2528 FETCH csr_get_internal_code INTO l_internal_code;
2529
2530 IF csr_get_internal_code%NOTFOUND THEN
2531 l_internal_code := NULL;
2532 END IF;
2533 CLOSE csr_get_internal_code;
2534
2535
2536 x_internal_code := l_internal_code;
2537
2538 EXCEPTION
2539 WHEN OTHERS THEN
2540
2541 x_return_status := FND_API.g_ret_sts_unexp_error;
2542
2543 IF csr_get_internal_code%ISOPEN THEN
2544 CLOSE csr_get_internal_code;
2545 END IF;
2546 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2547 THEN
2548 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2549 END IF;
2550 -- Standard call to get message count and if count=1, get the message
2551 FND_MSG_PUB.Count_And_Get (
2552 p_encoded => FND_API.G_FALSE,
2553 p_count => x_msg_count,
2554 p_data => x_msg_data
2555 );
2556
2557
2558 END Convert_Supp_Code;
2559
2560 PROCEDURE check_supp_code_conv_items
2561 (
2562 p_supp_code_conversion_rec IN supp_code_conversion_rec_type,
2563 p_validation_mode IN VARCHAR2,
2564 x_return_status OUT NOCOPY VARCHAR2
2565 )
2566
2567 IS
2568 l_api_name CONSTANT VARCHAR2(50) := 'check_supp_code_conversion_items';
2569 l_api_version_number CONSTANT NUMBER := 1.0;
2570
2571 BEGIN
2572
2573 x_return_status := FND_API.g_ret_sts_success;
2574
2575 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2576 OZF_UTILITY_PVT.debug_message('in '||l_api_name);
2577 END IF;
2578
2579 check_supp_code_conv_req_items( p_supp_code_conversion_rec,
2580 p_validation_mode,
2581 x_return_status
2582 );
2583 IF x_return_status <> FND_API.g_ret_sts_success THEN
2584 RETURN;
2585 END IF;
2586
2587 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2588 OZF_UTILITY_PVT.debug_message('after check_supp_code_conv_req_items ' );
2589 END IF;
2590
2591
2592 check_uniq_supp_code_conv( p_supp_code_conversion_rec,
2593 p_validation_mode,
2594 x_return_status
2595 );
2596 IF x_return_status <> FND_API.g_ret_sts_success THEN
2597 RETURN;
2598 END IF;
2599
2600 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2601 OZF_UTILITY_PVT.debug_message('after check_uniq_supp_code_conversion ' );
2602 END IF;
2603
2604 check_supp_code_conversion_dt (p_supp_code_conversion_rec,
2605 p_validation_mode,
2606 x_return_status
2607 );
2608 IF x_return_status <> FND_API.g_ret_sts_success Then
2609 RETURN;
2610 END IF;
2611
2612 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2613 OZF_UTILITY_PVT.debug_message('after check_supp_code_conversion_dt ' );
2614 END IF;
2615
2616 END check_supp_code_conv_items;
2617 PROCEDURE Validate_supp_code_Conv(
2618 p_api_version_number IN NUMBER,
2619 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2620 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2621 p_validation_mode IN VARCHAR2,
2622 p_supp_code_conversion_tbl IN supp_code_conversion_tbl_type,
2623 x_return_status OUT NOCOPY VARCHAR2,
2624 x_msg_count OUT NOCOPY NUMBER,
2625 x_msg_data OUT NOCOPY VARCHAR2
2626 )
2627
2628 IS
2629 l_api_name CONSTANT VARCHAR2(50) := 'Validate_supp_code_conversion';
2630 l_api_version_number CONSTANT NUMBER := 1.0;
2631 l_object_version_number NUMBER;
2632
2633 l_supp_code_conversion_rec supp_code_conversion_rec_type ;
2634
2635
2636 BEGIN
2637 -- Standard Start of API savepoint
2638 SAVEPOINT validate_supp_code_conv_pvt;
2639
2640 -- Standard call to check for call compatibility.
2641 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2642 p_api_version_number,
2643 l_api_name,
2644 G_PKG_NAME)
2645 THEN
2646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2647 END IF;
2648
2649 -- Initialize message list if p_init_msg_list is set to TRUE.
2650 IF FND_API.to_Boolean( p_init_msg_list )
2651 THEN
2652 FND_MSG_PUB.initialize;
2653 END IF;
2654 -- Call the Validate Item procedure for the item(field level validations )
2655 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2656 OZF_UTILITY_PVT.debug_message('in '||l_api_name );
2657 END IF;
2658 x_return_status := FND_API.g_ret_sts_success;
2659
2660 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
2661
2662 FOR i in 1 .. p_supp_code_conversion_tbl.count
2663 LOOP
2664 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2665 OZF_UTILITY_PVT.debug_message('inside the loop p_supp_code_conversion_tbl ' );
2666 END IF;
2667 l_supp_code_conversion_rec := p_supp_code_conversion_tbl(i);
2668
2669 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2670 OZF_UTILITY_PVT.debug_message('External code ' || l_supp_code_conversion_rec.external_code );
2671 OZF_UTILITY_PVT.debug_message('Start date active '|| l_supp_code_conversion_rec.start_date_active );
2672 OZF_UTILITY_PVT.debug_message('End date active ' || l_supp_code_conversion_rec.end_date_active );
2673 END IF;
2674
2675 check_supp_code_conv_items(
2676 p_supp_code_conversion_rec => l_supp_code_conversion_rec,
2677 p_validation_mode => p_validation_mode,
2678 x_return_status => x_return_status);
2679
2680 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2681 RAISE FND_API.G_EXC_ERROR;
2682 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2683 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2684 END IF;
2685 END LOOP;
2686
2687 END IF;
2688 -- Initialize API return status to SUCCESS
2689 x_return_status := FND_API.G_RET_STS_SUCCESS;
2690 -- Standard call to get message count and if count is 1, get message info.
2691 FND_MSG_PUB.Count_And_Get
2692 (p_count => x_msg_count,
2693 p_data => x_msg_data
2694 );
2695 EXCEPTION
2696 WHEN OZF_Utility_PVT.resource_locked THEN
2697 x_return_status := FND_API.g_ret_sts_error;
2698 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2699 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCE_LOCKED ');
2700 FND_MSG_PUB.add;
2701 END IF;
2702 WHEN FND_API.G_EXC_ERROR THEN
2703 ROLLBACK TO validate_supp_code_conv_pvt;
2704 x_return_status := FND_API.G_RET_STS_ERROR;
2705 -- Standard call to get message count and if count=1, get the message
2706 FND_MSG_PUB.Count_And_Get (
2707 p_encoded => FND_API.G_FALSE,
2708 p_count => x_msg_count,
2709 p_data => x_msg_data
2710 );
2711 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2712 ROLLBACK TO validate_supp_code_conv_pvt;
2713 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2714 -- Standard call to get message count and if count=1, get the message
2715 FND_MSG_PUB.Count_And_Get (
2716 p_encoded => FND_API.G_FALSE,
2717 p_count => x_msg_count,
2718 p_data => x_msg_data
2719 );
2720 WHEN OTHERS THEN
2721 ROLLBACK TO validate_supp_code_conv_pvt;
2722 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2723 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2724 THEN
2725 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2726 END IF;
2727 -- Standard call to get message count and if count=1, get the message
2728 FND_MSG_PUB.Count_And_Get (
2729 p_encoded => FND_API.G_FALSE,
2730 p_count => x_msg_count,
2731 p_data => x_msg_data
2732 );
2733
2734 END Validate_supp_code_conv;
2735
2736 PROCEDURE create_supp_code_conversion
2737 (
2738 p_api_version_number IN NUMBER,
2739 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2740 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2741 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2742 x_return_status OUT NOCOPY VARCHAR2,
2743 x_msg_count OUT NOCOPY NUMBER,
2744 x_msg_data OUT NOCOPY VARCHAR2,
2745 p_supp_code_conversion_tbl IN supp_code_conversion_tbl_type,
2746 x_supp_code_conversion_id_tbl OUT NOCOPY JTF_NUMBER_TABLE
2747 )
2748 IS
2749 l_api_name CONSTANT VARCHAR2(30) := 'create_supp_code_conversion';
2750 l_api_version_number CONSTANT NUMBER := 1.0;
2751 l_return_status_full VARCHAR2(1);
2752 l_object_version_number NUMBER := 1;
2753 l_org_id NUMBER;
2754 l_code_conversion_id NUMBER;
2755 l_supp_code_conversion_rec supp_code_conversion_rec_type;
2756
2757
2758
2759 BEGIN
2760 -- Standard Start of API savepoint
2761 SAVEPOINT create_supp_code_conv_pvt;
2762
2763 -- Standard call to check for call compatibility.
2764 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2765 p_api_version_number,
2766 l_api_name,
2767 G_PKG_NAME)
2768 THEN
2769 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2770 END IF;
2771
2772 -- Initialize message list if p_init_msg_list is set to TRUE.
2773 IF FND_API.to_Boolean( p_init_msg_list )
2774 THEN
2775 FND_MSG_PUB.initialize;
2776 END IF;
2777
2778
2779 -- ******************************************************************
2780 -- Validate Environment
2781 -- ******************************************************************
2782 IF FND_GLOBAL.User_Id IS NULL
2783 THEN
2784 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2785 THEN
2786 FND_MESSAGE.Set_Name('OZF', 'OZF_USER_PROFILE_MISSING');
2787 FND_MSG_PUB.ADD;
2788 END IF;
2789 RAISE FND_API.G_EXC_ERROR;
2790 END IF;
2791
2792 -- Debug Message
2793 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2794 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2795 END IF;
2796
2797 -- Initialize API return status to SUCCESS
2798 x_return_status := FND_API.G_RET_STS_SUCCESS;
2799
2800 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2801 OZF_UTILITY_PVT.debug_message( 'No of records to be created'||p_supp_code_conversion_tbl.count);
2802 END IF;
2803
2804 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
2805 -- Debug message
2806 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name);
2807 -- Invoke validation procedures
2808 Validate_supp_code_Conv(
2809 p_api_version_number => 1.0,
2810 p_init_msg_list => FND_API.G_FALSE,
2811 p_validation_level => p_validation_level,
2812 p_validation_mode => JTF_PLSQL_API.G_CREATE,
2813 p_supp_code_conversion_tbl => p_supp_code_conversion_tbl,
2814 x_return_status => x_return_status,
2815 x_msg_count => x_msg_count,
2816 x_msg_data => x_msg_data);
2817 END IF;
2818
2819 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2820 RAISE FND_API.G_EXC_ERROR;
2821 END IF;
2822
2823 -- Debug Message
2824 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2825 OZF_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
2826 END IF;
2827
2828 l_code_conversion_id := NULL;
2829 l_object_version_number := NULL;
2830
2831 x_supp_code_conversion_id_tbl := JTF_NUMBER_TABLE();
2832
2833 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2834 OZF_UTILITY_PVT.debug_message( 'No of rows to be created '|| p_supp_code_conversion_tbl.count);
2835 END IF;
2836
2837 FOR i IN 1 .. p_supp_code_conversion_tbl.count
2838 LOOP
2839
2840 l_supp_code_conversion_rec := p_supp_code_conversion_tbl(i);
2841
2842 IF (l_supp_code_conversion_rec.org_id IS NULL) THEN
2843 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID(); -- R12 Enhancements
2844 ELSE
2845 l_org_id := l_supp_code_conversion_rec.org_id;
2846 END IF;
2847 SELECT ozf_supp_code_conv_all_s.nextval INTO l_code_conversion_id FROM DUAL;
2848
2849 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2850 OZF_UTILITY_PVT.debug_message( 'l_org_id ' || l_org_id);
2851 OZF_UTILITY_PVT.debug_message( 'Code Conversion ID '|| l_code_conversion_id);
2852 END IF;
2853
2854 BEGIN
2855
2856
2857 OZF_CODE_CONVERSION_PKG.Insert_Supp_code_conv_Row(
2858 px_code_conversion_id => l_code_conversion_id,
2859 px_object_version_number => l_object_version_number,
2860 p_last_update_date => SYSDATE,
2861 p_last_updated_by => FND_GLOBAL.USER_ID,
2862 p_creation_date => SYSDATE,
2863 p_created_by => FND_GLOBAL.USER_ID,
2864 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
2865 px_org_id => l_org_id,
2866 p_supp_trade_profile_id => l_supp_code_conversion_rec.supp_trade_profile_id,
2867 p_code_conversion_type => l_supp_code_conversion_rec.code_conversion_type,
2868 p_external_code => l_supp_code_conversion_rec.external_code,
2869 p_internal_code => l_supp_code_conversion_rec.internal_code,
2870 p_description => l_supp_code_conversion_rec.description,
2871 p_start_date_active => nvl(l_supp_code_conversion_rec.start_date_active,sysdate),
2872 p_end_date_active => l_supp_code_conversion_rec.end_date_active,
2873 p_attribute_category => l_supp_code_conversion_rec.attribute_category,
2874 p_attribute1 => l_supp_code_conversion_rec.attribute1,
2875 p_attribute2 => l_supp_code_conversion_rec.attribute2,
2876 p_attribute3 => l_supp_code_conversion_rec.attribute3,
2877 p_attribute4 => l_supp_code_conversion_rec.attribute4,
2878 p_attribute5 => l_supp_code_conversion_rec.attribute5,
2879 p_attribute6 => l_supp_code_conversion_rec.attribute6,
2880 p_attribute7 => l_supp_code_conversion_rec.attribute7,
2881 p_attribute8 => l_supp_code_conversion_rec.attribute8,
2882 p_attribute9 => l_supp_code_conversion_rec.attribute9,
2883 p_attribute10 => l_supp_code_conversion_rec.attribute10,
2884 p_attribute11 => l_supp_code_conversion_rec.attribute11,
2885 p_attribute12 => l_supp_code_conversion_rec.attribute12,
2886 p_attribute13 => l_supp_code_conversion_rec.attribute13,
2887 p_attribute14 => l_supp_code_conversion_rec.attribute14,
2888 p_attribute15 => l_supp_code_conversion_rec.attribute15);
2889
2890 EXCEPTION
2891 WHEN OTHERS THEN
2892 OZF_UTILITY_PVT.debug_message (SQLERRM ||' Error in creating supp_code conversion map');
2893 RAISE FND_API.G_EXC_ERROR;
2894 END;
2895 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2896 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' after insert call - supp_code conversion id' || l_code_conversion_id);
2897 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' after insert call - obj version no ' || l_supp_code_conversion_rec.Object_Version_Number);
2898 END IF;
2899
2900 x_supp_code_conversion_id_tbl.extend;
2901 x_supp_code_conversion_id_tbl(x_supp_code_conversion_id_tbl.count) := l_code_conversion_id;
2902
2903 end loop;
2904
2905 -- Standard check for p_commit
2906 IF FND_API.to_Boolean( p_commit )
2907 THEN
2908 COMMIT WORK;
2909 END IF;
2910
2911 -- Debug Message
2912 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
2913 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2914 END IF;
2915
2916 -- Standard call to get message count and if count is 1, get message info.
2917 FND_MSG_PUB.Count_And_Get
2918 (p_count => x_msg_count,
2919 p_data => x_msg_data
2920 );
2921
2922
2923
2924 EXCEPTION
2925 WHEN OZF_UTILITY_PVT.resource_locked THEN
2926 x_return_status := FND_API.g_ret_sts_error;
2927 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2928 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCES_LOCKED');
2929 FND_MSG_PUB.add;
2930 END IF;
2931 WHEN FND_API.G_EXC_ERROR THEN
2932 ROLLBACK TO create_supp_code_conv_pvt;
2933 x_return_status := FND_API.G_RET_STS_ERROR;
2934 -- Standard call to get message count and if count=1, get the message
2935 FND_MSG_PUB.Count_And_Get (
2936 p_encoded => FND_API.G_FALSE,
2937 p_count => x_msg_count,
2938 p_data => x_msg_data
2939 );
2940 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2941 ROLLBACK TO create_code_conversion_pvt;
2942 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2943 -- Standard call to get message count and if count=1, get the message
2944 FND_MSG_PUB.Count_And_Get (
2945 p_encoded => FND_API.G_FALSE,
2946 p_count => x_msg_count,
2947 p_data => x_msg_data
2948 );
2949 WHEN OTHERS THEN
2950 ROLLBACK TO create_supp_code_conv_pvt;
2951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2952 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2953 THEN
2954 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2955 END IF;
2956 -- Standard call to get message count and if count=1, get the message
2957 FND_MSG_PUB.Count_And_Get (
2958 p_encoded => FND_API.G_FALSE,
2959 p_count => x_msg_count,
2960 p_data => x_msg_data
2961 );
2962
2963 END create_supp_code_conversion ;
2964
2965
2966 PROCEDURE Update_supp_code_conversion
2967 (
2968 p_api_version_number IN NUMBER,
2969 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2970 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2971 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2972 x_return_status OUT NOCOPY VARCHAR2,
2973 x_msg_count OUT NOCOPY NUMBER,
2974 x_msg_data OUT NOCOPY VARCHAR2,
2978 IS
2975 p_supp_code_conversion_tbl IN supp_code_conversion_tbl_type ,
2976 x_object_version_number OUT NOCOPY JTF_NUMBER_TABLE
2977 )
2979 l_api_name CONSTANT VARCHAR2(30) := 'Update_supp_code_conversion';
2980 l_api_version_number CONSTANT NUMBER := 1.0;
2981 l_object_version_number NUMBER;
2982
2983 l_code_conversion_id NUMBER;
2984
2985 CURSOR csr_supp_code_conversion(cv_code_conversion_id NUMBER)
2986 IS
2987 SELECT code_conversion_id,
2988 object_version_number,
2989 last_update_date,
2990 last_updated_by,
2991 creation_date,
2992 created_by,
2993 last_update_login,
2994 org_id,
2995 supp_trade_profile_id,
2996 code_conversion_type,
2997 external_code,
2998 internal_code,
2999 description,
3000 start_date_active,
3001 end_date_active,
3002 attribute_category,
3003 attribute1,
3004 attribute2,
3005 attribute3,
3006 attribute4,
3007 attribute5,
3008 attribute6,
3009 attribute7,
3010 attribute8,
3011 attribute9,
3012 attribute10,
3013 attribute11,
3014 attribute12,
3015 attribute13,
3016 attribute14,
3017 attribute15,
3018 security_group_id
3019 FROM ozf_supp_code_conversions_all
3020 WHERE code_conversion_id = cv_code_conversion_id;
3021
3022 CURSOR get_org
3023 IS
3024 SELECT org_id FROM ozf_sys_parameters;
3025
3026 l_supp_code_conversion_rec supp_code_conversion_rec_type;
3027 l_supp_code_conversion_tbl supp_code_conversion_tbl_type;
3028 l_org_id NUMBER;
3029 p_supp_code_conversion_rec supp_code_conversion_rec_type;
3030
3031
3032 BEGIN
3033 -- Standard Start of API savepoint
3034 SAVEPOINT update_supp_code_conv_pvt;
3035
3036 -- Standard call to check for call compatibility.
3037 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3038 p_api_version_number,
3039 l_api_name,
3040 G_PKG_NAME)
3041 THEN
3042 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3043 END IF;
3044
3045 -- Initialize message list if p_init_msg_list is set to TRUE.
3046 IF FND_API.to_Boolean( p_init_msg_list )
3047 THEN
3048 FND_MSG_PUB.initialize;
3049 END IF;
3050
3051 -- ******************************************************************
3052 -- Validate Environment
3053 -- ******************************************************************
3054 IF FND_GLOBAL.User_Id IS NULL
3055 THEN
3056 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3057 THEN
3058 FND_MESSAGE.Set_Name('OZF', 'OZF_USER_PROFILE_MISSING');
3059 FND_MSG_PUB.ADD;
3060 END IF;
3061 RAISE FND_API.G_EXC_ERROR;
3062 END IF;
3063
3064 -- Debug Message
3065 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3066 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
3067 END IF;
3068
3069 -- Initialize API return status to SUCCESS
3070 x_return_status := FND_API.G_RET_STS_SUCCESS;
3071 x_object_version_number := JTF_NUMBER_TABLE();
3072
3073 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3074 OZF_UTILITY_PVT.debug_message('p_supp_code_conversion_tbl(1).description: ' || p_supp_code_conversion_tbl(1).description );
3075 END IF;
3076
3077 FOR i in 1 .. p_supp_code_conversion_tbl.count
3078 LOOP
3079 p_supp_code_conversion_rec := p_supp_code_conversion_tbl(i);
3080 l_code_conversion_id := p_supp_code_conversion_rec.code_conversion_id;
3081
3082 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3083 OZF_UTILITY_PVT.debug_message( 'supp_code Conversion ID' || l_code_conversion_id);
3084 END IF;
3085
3086 -- Check for the existance of the record
3087 OPEN csr_supp_code_conversion(l_code_conversion_id);
3088 FETCH csr_supp_code_conversion
3089 INTO l_supp_code_conversion_rec.code_conversion_id
3090 ,l_supp_code_conversion_rec.object_version_number
3091 ,l_supp_code_conversion_rec.last_update_date
3092 ,l_supp_code_conversion_rec.last_updated_by
3093 ,l_supp_code_conversion_rec.creation_date
3094 ,l_supp_code_conversion_rec.created_by
3095 ,l_supp_code_conversion_rec.last_update_login
3096 ,l_supp_code_conversion_rec.org_id
3097 ,l_supp_code_conversion_rec.supp_trade_profile_id
3098 ,l_supp_code_conversion_rec.code_conversion_type
3099 ,l_supp_code_conversion_rec.external_code
3100 ,l_supp_code_conversion_rec.internal_code
3101 ,l_supp_code_conversion_rec.description
3102 ,l_supp_code_conversion_rec.start_date_active
3103 ,l_supp_code_conversion_rec.end_date_active
3104 ,l_supp_code_conversion_rec.attribute_category
3105 ,l_supp_code_conversion_rec.attribute1
3106 ,l_supp_code_conversion_rec.attribute2
3107 ,l_supp_code_conversion_rec.attribute3
3108 ,l_supp_code_conversion_rec.attribute4
3109 ,l_supp_code_conversion_rec.attribute5
3110 ,l_supp_code_conversion_rec.attribute6
3111 ,l_supp_code_conversion_rec.attribute7
3112 ,l_supp_code_conversion_rec.attribute8
3113 ,l_supp_code_conversion_rec.attribute9
3114 ,l_supp_code_conversion_rec.attribute10
3115 ,l_supp_code_conversion_rec.attribute11
3116 ,l_supp_code_conversion_rec.attribute12
3117 ,l_supp_code_conversion_rec.attribute13
3118 ,l_supp_code_conversion_rec.attribute14
3119 ,l_supp_code_conversion_rec.attribute15
3120 ,l_supp_code_conversion_rec.security_group_id;
3121
3122 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3123 OZF_UTILITY_PVT.debug_message('Existing description '|| l_supp_code_conversion_rec.description);
3124 END IF;
3125
3126 IF ( csr_supp_code_conversion%NOTFOUND) THEN
3127 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3128 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'nodata for upd');
3129 END IF;
3130
3131 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3132 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
3133 FND_MSG_PUB.add;
3134 END IF;
3135 RAISE FND_API.G_EXC_ERROR;
3136 END IF;
3137 CLOSE csr_supp_code_conversion;
3138
3139
3140 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3141 OZF_UTILITY_PVT.debug_message( 'Pre Object Version Number ' || l_supp_code_conversion_rec.object_version_number);
3142 OZF_UTILITY_PVT.debug_message( 'Post Object Version Number' || P_supp_code_conversion_rec.object_version_number);
3143 END IF;
3144
3145 --- Check the Version Number for Locking
3146 IF l_supp_code_conversion_rec.object_version_number <> P_supp_code_conversion_rec.Object_Version_number
3147 THEN
3148 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3149 OZF_UTILITY_PVT.debug_message( 'dbver' || l_supp_code_conversion_rec.object_version_number);
3150 OZF_UTILITY_PVT.debug_message( 'reqver' || P_supp_code_conversion_rec.object_version_number);
3151 END IF;
3152
3153 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3154 THEN
3155 FND_MESSAGE.Set_Name('OZF', 'OZF_API_RESOURCE_LOCKED');
3156 FND_MSG_PUB.ADD;
3157 END IF;
3158 RAISE FND_API.G_EXC_ERROR;
3159 END IF;
3160
3161
3162 -- Update internal code only when it is NUll
3163 IF l_supp_code_conversion_rec.internal_code IS NOT NULL AND
3164 l_supp_code_conversion_rec.internal_code <> P_supp_code_conversion_rec.internal_code
3165 THEN
3166 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3167 THEN
3168 FND_MESSAGE.Set_Name('OZF', 'OZF_CODE_CONV_UPD_INTLCODE');
3169 FND_MSG_PUB.ADD;
3170 END IF;
3171 RAISE FND_API.G_EXC_ERROR;
3172 END IF;
3173
3174 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3175 OZF_UTILITY_PVT.debug_message( 'Existing End Date' || l_supp_code_conversion_rec.End_Date_Active);
3176 OZF_UTILITY_PVT.debug_message( 'Updated End Date' || p_supp_code_conversion_rec.End_Date_Active);
3177 END IF;
3178
3179 -- Update End date only when it is NUll or a future date
3180 IF trunc(nvl(l_supp_code_conversion_Rec.End_Date_Active,sysdate+1)) <= TRUNC(SYSDATE)
3181 AND
3182 Trunc(l_supp_code_conversion_Rec.End_Date_Active) <> Trunc(P_supp_code_conversion_Rec.End_Date_Active)
3183 THEN
3184 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3185 THEN
3186 FND_MESSAGE.Set_Name('OZF', 'OZF_CODE_CONV_UPD_ENDDATE');
3187 FND_MSG_PUB.ADD;
3188 END IF;
3189 RAISE FND_API.G_EXC_ERROR;
3190 END IF;
3191
3192 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3193 OZF_UTILITY_PVT.debug_message( 'Existing Start Date' || l_supp_code_conversion_rec.Start_Date_Active);
3194 OZF_UTILITY_PVT.debug_message( 'Updated Start Date' || p_supp_code_conversion_rec.Start_Date_Active);
3195 END IF;
3196
3197 ---Update not allowed for Start Date when start date is earlier than current date
3198 IF trunc(l_supp_code_conversion_Rec.Start_Date_Active)
3199 <> trunc(P_supp_code_conversion_Rec.Start_Date_Active)
3200 THEN
3201 IF p_supp_code_conversion_Rec.Start_Date_Active < TRUNC(SYSDATE)
3202 THEN
3203 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3204 THEN
3205 FND_MESSAGE.Set_Name('OZF', 'OZF_CODE_CONV_UPD_STARTDATE');
3206 FND_MSG_PUB.ADD;
3207 END IF;
3208 RAISE FND_API.G_EXC_ERROR;
3209 END IF;
3210
3211 IF l_supp_code_conversion_Rec.end_date_active < p_supp_code_conversion_Rec.Start_Date_Active THEN
3212 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3213 THEN
3214 OZF_UTILITY_PVT.debug_message ('Cannot update an end dated code conversion map');
3215 END IF;
3216 raise FND_API.G_EXC_ERROR;
3217 END IF;
3218
3219 END IF;
3220
3221 -- Update not allowed for External Code
3222 IF l_supp_code_conversion_Rec.external_Code <> P_supp_code_conversion_Rec.external_Code
3223 THEN
3224 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3225 THEN
3226 FND_MESSAGE.Set_Name('OZF', 'OZF_CODE_CONV_UPD_EXTCD');
3227 FND_MSG_PUB.ADD;
3228 END IF;
3229 RAISE FND_API.G_EXC_ERROR;
3230 END IF;
3231
3232 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
3233 -- Debug message
3234 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3235 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name);
3236 END IF;
3237 -- Invoke validation procedures
3238 Validate_supp_code_Conv(
3239 p_api_version_number => 1.0,
3240 p_init_msg_list => FND_API.G_FALSE,
3241 p_validation_level => p_validation_level,
3242 p_validation_mode => JTF_PLSQL_API.G_UPDATE,
3243 p_supp_code_conversion_tbl => p_supp_code_conversion_tbl,
3244 x_return_status => x_return_status,
3245 x_msg_count => x_msg_count,
3246 x_msg_data => x_msg_data);
3247 END IF;
3248
3249 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
3250 RAISE FND_API.G_EXC_ERROR;
3251 END IF;
3252
3253 IF (l_supp_code_conversion_rec.org_id IS NULL) THEN
3254 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID(); -- R12 Enhancements
3255 ELSE
3256 l_org_id := l_supp_code_conversion_rec.org_id;
3257 END IF;
3258
3259
3260 -- Call Update Table Handler
3261 -- Debug Message
3262 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3263 OZF_UTILITY_PVT.debug_message( 'Private API: Calling Update table handler');
3264 END IF;
3265 BEGIN
3266 OZF_CODE_CONVERSION_PKG.Update_Supp_Code_Conv_Row(
3267 p_code_conversion_id => l_code_conversion_id,
3268 p_object_version_number => p_supp_code_conversion_rec.object_version_number,
3269 p_last_update_date => SYSDATE,
3270 p_last_updated_by => FND_GLOBAL.USER_ID,
3271 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
3272 p_org_id => l_org_id,
3273 p_supp_trade_profile_id => p_supp_code_conversion_rec.supp_trade_profile_id,
3274 p_code_conversion_type => p_supp_code_conversion_rec.code_conversion_type,
3275 p_external_code => p_supp_code_conversion_rec.external_code,
3276 p_internal_code => p_supp_code_conversion_rec.internal_code,
3277 p_description => p_supp_code_conversion_rec.description,
3278 p_start_date_active => p_supp_code_conversion_rec.start_date_active,
3279 p_end_date_active => p_supp_code_conversion_rec.end_date_active,
3280 p_attribute_category => p_supp_code_conversion_rec.attribute_category,
3281 p_attribute1 => p_supp_code_conversion_rec.attribute1,
3282 p_attribute2 => p_supp_code_conversion_rec.attribute2,
3283 p_attribute3 => p_supp_code_conversion_rec.attribute3,
3284 p_attribute4 => p_supp_code_conversion_rec.attribute4,
3285 p_attribute5 => p_supp_code_conversion_rec.attribute5,
3286 p_attribute6 => p_supp_code_conversion_rec.attribute6,
3287 p_attribute7 => p_supp_code_conversion_rec.attribute7,
3288 p_attribute8 => p_supp_code_conversion_rec.attribute8,
3289 p_attribute9 => p_supp_code_conversion_rec.attribute9,
3290 p_attribute10 => p_supp_code_conversion_rec.attribute10,
3291 p_attribute11 => p_supp_code_conversion_rec.attribute11,
3292 p_attribute12 => p_supp_code_conversion_rec.attribute12,
3293 p_attribute13 => p_supp_code_conversion_rec.attribute13,
3294 p_attribute14 => p_supp_code_conversion_rec.attribute14,
3295 p_attribute15 => p_supp_code_conversion_rec.attribute15);
3296
3297
3298
3299 EXCEPTION
3300 WHEN OTHERS THEN
3301 OZF_UTILITY_PVT.debug_message (SQLERRM ||' Error in updating code conversion map');
3302 RAISE FND_API.G_EXC_ERROR;
3303 END;
3304
3305 x_object_version_number.EXTEND;
3306 x_object_Version_number(x_object_version_number.count) := p_supp_code_conversion_rec.Object_Version_Number;
3307
3308 END LOOP;
3309
3310
3311 -- Standard check for p_commit
3312 IF FND_API.to_Boolean( p_commit )
3313 THEN
3314 COMMIT WORK;
3315 END IF;
3316
3317
3318 -- Debug Message
3319 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3320 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
3321 END IF;
3322 -- Standard call to get message count and if count is 1, get message info.
3323 FND_MSG_PUB.Count_And_Get
3324 (p_count => x_msg_count,
3325 p_data => x_msg_data
3326 );
3327 EXCEPTION
3328 WHEN FND_API.G_EXC_ERROR THEN
3329 ROLLBACK TO update_supp_code_conv_pvt;
3330 x_return_status := FND_API.G_RET_STS_ERROR;
3331 -- Standard call to get message count and if count=1, get the message
3332 FND_MSG_PUB.Count_And_Get (
3333 p_encoded => FND_API.G_FALSE,
3334 p_count => x_msg_count,
3335 p_data => x_msg_data
3336 );
3337 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3338 ROLLBACK TO update_supp_code_conv_pvt;
3339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3340 -- Standard call to get message count and if count=1, get the message
3341 FND_MSG_PUB.Count_And_Get (
3342 p_encoded => FND_API.G_FALSE,
3343 p_count => x_msg_count,
3344 p_data => x_msg_data
3345 );
3346 WHEN OTHERS THEN
3347 ROLLBACK TO update_supp_code_conv_pvt;
3348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3349 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3350 THEN
3351 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3352 END IF;
3353 -- Standard call to get message count and if count=1, get the message
3354 FND_MSG_PUB.Count_And_Get (
3355 p_encoded => FND_API.G_FALSE,
3356 p_count => x_msg_count,
3357 p_data => x_msg_data
3358 );
3359
3360 END Update_supp_code_Conversion;
3361
3362
3363
3364 PROCEDURE Update_supp_code_Conv_Tbl(
3365 P_Api_Version_Number IN NUMBER,
3366 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3367 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
3368 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3369 X_Return_Status OUT NOCOPY VARCHAR2,
3370 X_Msg_Count OUT NOCOPY NUMBER,
3371 X_Msg_Data OUT NOCOPY VARCHAR2,
3372 P_supp_code_conversion_Tbl IN supp_code_conversion_tbl_type
3373 )
3374 IS
3375 l_api_name CONSTANT VARCHAR2(35) := 'update_supp_code_conversion_tbl';
3376 l_api_version_number CONSTANT NUMBER := 1.0;
3377
3378 p_supp_code_conversion_rec supp_code_conversion_rec_type;
3379
3380 l_code_conversion_id NUMBER;
3381 v_code_conversion_id JTF_NUMBER_TABLE;
3382 v_object_version_number JTF_NUMBER_TABLE;
3383
3384 l_create_flag VARCHAR2(10);
3385
3386 l_create_supp_code_conv_tbl supp_code_conversion_tbl_type := supp_code_conversion_tbl_type();
3387 l_update_supp_code_conv_tbl supp_code_conversion_tbl_type := supp_code_conversion_tbl_type();
3388
3389 l_cc_cnt NUMBER := 0;
3390 l_up_cnt NUMBER := 0;
3391
3392 BEGIN
3393 -- Standard Start of API savepoint
3394 SAVEPOINT update_supp_code_conv_tbl_pvt;
3395
3396 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3397 OZF_UTILITY_PVT.debug_message('Entered the proc tbl');
3398 END IF;
3399
3400 -- Standard call to check for call compatibility.
3401 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3402 p_api_version_number,
3403 l_api_name,
3404 G_PKG_NAME)
3405 THEN
3406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3407 END IF;
3408
3409 -- Initialize message list if p_init_msg_list is set to TRUE.
3410 IF FND_API.to_Boolean( p_init_msg_list )
3411 THEN
3412 FND_MSG_PUB.initialize;
3413 END IF;
3414
3415 -- ******************************************************************
3416 -- Validate Environment
3417 -- ******************************************************************
3418 IF FND_GLOBAL.User_Id IS NULL
3419 THEN
3420 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3421 THEN
3422 FND_MESSAGE.Set_Name('OZF', 'OZF_USER_PROFILE_MISSING');
3423 FND_MSG_PUB.ADD;
3424 END IF;
3425 RAISE FND_API.G_EXC_ERROR;
3426 END IF;
3427
3428 -- Debug Message
3429 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3430 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
3431 OZF_UTILITY_PVT.debug_message('Total Number of records '||P_supp_code_conversion_Tbl.count);
3432 END IF;
3433 -- Initialize API return status to SUCCESS
3434 x_return_status := FND_API.G_RET_STS_SUCCESS;
3435
3436 v_code_conversion_id := JTF_NUMBER_TABLE();
3437 v_object_version_number := JTF_NUMBER_TABLE();
3438
3439 FOR i IN P_supp_code_conversion_Tbl.first .. P_supp_code_conversion_Tbl.last
3440 LOOP
3441
3442 l_code_conversion_id := P_supp_code_conversion_Tbl(i).code_conversion_id;
3443 P_supp_code_conversion_Rec := P_supp_code_conversion_Tbl(i);
3444
3445 -- Fix for 3928270
3446
3447 -- IF p_supp_code_conversion_rec.end_date_active = FND_API.g_miss_date
3448 -- THEN
3449 -- p_supp_code_conversion_rec.end_date_active := NULL;
3450 -- END IF;
3451
3452
3453 IF l_code_conversion_id IS NULL OR l_code_conversion_id = -1 then
3454 l_cc_cnt := l_cc_cnt + 1;
3455
3456 l_create_supp_code_conv_tbl.extend;
3457 l_create_supp_code_conv_tbl(l_cc_cnt) := P_supp_code_conversion_Rec;
3458
3459 ELSE
3460 l_up_cnt := l_up_cnt + 1;
3461
3462 l_update_supp_code_conv_tbl.extend;
3463 l_update_supp_code_conv_tbl(l_up_cnt) := P_supp_code_conversion_Rec;
3464 END IF;
3465
3466 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3467 OZF_UTILITY_PVT.debug_message('End Date '||P_supp_code_conversion_Rec.end_date_active);
3468 END IF;
3469
3470 END LOOP;
3471
3472 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3473 OZF_UTILITY_PVT.debug_message('No of rows to be created: ' || l_cc_cnt);
3474 OZF_UTILITY_PVT.debug_message('No of rows to be updated: ' || l_up_cnt);
3475 END IF;
3476
3477 IF l_cc_cnt > 0 THEN
3478 --- Call to Create Procedure
3479 Create_supp_code_Conversion
3480 (
3481 p_api_version_number => p_api_version_number,
3482 p_init_msg_list => p_init_msg_list,
3483 p_commit => p_commit,
3484 p_validation_level => p_validation_level,
3485 x_return_status => x_return_Status,
3486 x_msg_count => x_msg_Count,
3487 x_msg_data => x_msg_Data,
3488 p_supp_code_conversion_tbl => l_create_supp_code_conv_tbl,
3489 x_supp_code_conversion_id_tbl => v_code_conversion_id
3490 );
3491
3492 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
3493 RAISE FND_API.G_EXC_ERROR;
3494 END IF;
3495 END IF;
3496
3497 IF l_up_cnt > 0 THEN
3498 -- Call to Update Procedure
3499 Update_supp_code_conversion (
3500 p_api_version_number => p_api_version_number ,
3501 p_init_msg_list => p_init_msg_list,
3502 p_commit => p_commit,
3503 p_validation_level => p_validation_level,
3504 x_return_status => x_return_Status,
3505 x_msg_count => x_msg_Count,
3506 x_msg_data => x_msg_Data,
3507 p_supp_code_conversion_tbl => l_update_supp_code_conv_tbl,
3508 x_object_version_number => v_object_version_number
3509 );
3510
3511 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
3512 RAISE FND_API.G_EXC_ERROR;
3513 END IF;
3514 END IF;
3515
3516 -- Standard check for p_commit
3517 IF FND_API.to_Boolean( p_commit )
3518 THEN
3519 COMMIT WORK;
3520 END IF;
3521
3522 -- Debug Message
3523 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3524 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
3525 END IF;
3526 -- Standard call to get message count and if count is 1, get message info.
3527 FND_MSG_PUB.Count_And_Get
3528 (p_count => x_msg_count,
3529 p_data => x_msg_data
3530 );
3531 EXCEPTION
3532 WHEN FND_API.G_EXC_ERROR THEN
3533 ROLLBACK TO update_supp_code_conv_tbl_pvt;
3534 x_return_status := FND_API.G_RET_STS_ERROR;
3535 -- Standard call to get message count and if count=1, get the message
3536 FND_MSG_PUB.Count_And_Get (
3537 p_encoded => FND_API.G_FALSE,
3538 p_count => x_msg_count,
3539 p_data => x_msg_data
3540 );
3541 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3542 ROLLBACK TO update_supp_code_conv_tbl_pvt;
3543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3544 -- Standard call to get message count and if count=1, get the message
3545 FND_MSG_PUB.Count_And_Get (
3546 p_encoded => FND_API.G_FALSE,
3547 p_count => x_msg_count,
3548 p_data => x_msg_data
3549 );
3550 WHEN OTHERS THEN
3551 ROLLBACK TO update_supp_code_conv_tbl_pvt;
3552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3553 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3554 THEN
3555 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3556 END IF;
3557 -- Standard call to get message count and if count=1, get the message
3558 FND_MSG_PUB.Count_And_Get (
3559 p_encoded => FND_API.G_FALSE,
3560 p_count => x_msg_count,
3561 p_data => x_msg_data
3562 );
3563
3564
3565 END Update_supp_code_Conv_Tbl;
3566
3567
3568 PROCEDURE Delete_Supp_Code_Conv_Tbl
3569 (
3570 p_api_version_number IN NUMBER,
3571 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3572 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3573 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3574 x_return_status OUT NOCOPY VARCHAR2,
3575 x_msg_count OUT NOCOPY NUMBER,
3576 x_msg_data OUT NOCOPY VARCHAR2,
3577 p_supp_code_conversion_tbl IN supp_code_conversion_Tbl_Type
3578 )
3579 IS
3580 l_api_name CONSTANT VARCHAR2(50) := 'delete_supp_code_conversion_tbl';
3581 l_api_version_number CONSTANT NUMBER := 1.0;
3582
3583 p_supp_code_conversion_rec supp_code_conversion_rec_type;
3584
3585 l_code_conversion_id NUMBER;
3586 l_object_version_number NUMBER;
3587
3588
3589 BEGIN
3590 -- Standard Start of API savepoint
3591 SAVEPOINT delete_supp_code_conv_tbl_pvt;
3592
3593 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3594 OZF_UTILITY_PVT.debug_message('Entered the proc tbl');
3595 END IF;
3596
3597 -- Standard call to check for call compatibility.
3598 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3599 p_api_version_number,
3600 l_api_name,
3601 G_PKG_NAME)
3602 THEN
3603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3604 END IF;
3605
3606 -- Initialize message list if p_init_msg_list is set to TRUE.
3607 IF FND_API.to_Boolean( p_init_msg_list )
3608 THEN
3609 FND_MSG_PUB.initialize;
3610 END IF;
3611
3612 -- ******************************************************************
3613 -- Validate Environment
3614 -- ******************************************************************
3615 IF FND_GLOBAL.User_Id IS NULL
3616 THEN
3617 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3618 THEN
3619 FND_MESSAGE.Set_Name('OZF', 'OZF_USER_PROFILE_MISSING');
3620 FND_MSG_PUB.ADD;
3621 END IF;
3622 RAISE FND_API.G_EXC_ERROR;
3623 END IF;
3624
3625 -- Debug Message
3626 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3627 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
3628 END IF;
3629
3630
3631 -- Initialize API return status to SUCCESS
3632 x_return_status := FND_API.G_RET_STS_SUCCESS;
3633
3634 FOR i IN P_supp_code_conversion_Tbl.first .. P_supp_code_conversion_Tbl.last
3635 LOOP
3636
3637 l_code_conversion_id := P_supp_code_conversion_Tbl(i).code_conversion_id;
3638 l_object_version_number := P_supp_code_conversion_Tbl(i).object_version_number;
3639
3640 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3641 OZF_UTILITY_PVT.debug_message('supp_code Conversion ID ' || l_code_conversion_id);
3642 OZF_UTILITY_PVT.debug_message('Object Version Number ' || l_object_version_number);
3643 END IF;
3644
3645 IF l_object_version_number IS NULL
3646 OR l_code_conversion_id IS NULL THEN
3647
3648 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3649 OZF_UTILITY_PVT.debug_message('In If block');
3650 END IF;
3651 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3652 FND_MESSAGE.set_name('OZF', 'OZF_REQ_PARAMETERS_MISSING');
3653 FND_MSG_PUB.add;
3654 END IF;
3655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3656 ELSE
3657 Delete_supp_code_Conversion(
3658 p_api_version_number => 1.0,
3659 p_init_msg_list => FND_API.G_FALSE,
3660 p_commit => FND_API.G_FALSE,
3661 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3662 x_return_status => X_Return_Status ,
3663 x_msg_count => X_Msg_Count ,
3664 x_msg_data => X_Msg_Data ,
3665 p_code_conversion_id => l_code_conversion_id,
3666 p_object_version_number => l_object_version_number
3667 );
3668
3669
3670 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
3671 RAISE FND_API.G_EXC_ERROR;
3672 END IF;
3673
3674 END IF;
3675
3676 END LOOP;
3677
3678
3679 -- Standard check for p_commit
3680 IF FND_API.to_Boolean( p_commit )
3681 THEN
3682 COMMIT WORK;
3683 END IF;
3684
3685 -- Debug Message
3686 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3687 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
3688 END IF;
3689 -- Standard call to get message count and if count is 1, get message info.
3690 FND_MSG_PUB.Count_And_Get
3691 (p_count => x_msg_count,
3692 p_data => x_msg_data
3693 );
3694 EXCEPTION
3695 WHEN FND_API.G_EXC_ERROR THEN
3696 ROLLBACK TO delete_supp_code_conv_tbl_pvt;
3697 x_return_status := FND_API.G_RET_STS_ERROR;
3698 -- Standard call to get message count and if count=1, get the message
3699 FND_MSG_PUB.Count_And_Get (
3700 p_encoded => FND_API.G_FALSE,
3701 p_count => x_msg_count,
3702 p_data => x_msg_data
3703 );
3704 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3705 ROLLBACK TO delete_supp_code_conv_tbl_pvt;
3706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3707 -- Standard call to get message count and if count=1, get the message
3708 FND_MSG_PUB.Count_And_Get (
3709 p_encoded => FND_API.G_FALSE,
3710 p_count => x_msg_count,
3711 p_data => x_msg_data
3712 );
3713 WHEN OTHERS THEN
3714 ROLLBACK TO delete_supp_code_conv_tbl_pvt;
3715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3716 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3717 THEN
3718 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3719 END IF;
3720 -- Standard call to get message count and if count=1, get the message
3721 FND_MSG_PUB.Count_And_Get (
3722 p_encoded => FND_API.G_FALSE,
3723 p_count => x_msg_count,
3724 p_data => x_msg_data
3725 );
3726
3727
3728 END Delete_supp_code_Conv_Tbl;
3729
3730
3731
3732
3733 PROCEDURE Delete_supp_code_Conversion
3734 (
3735 p_api_version_number IN NUMBER,
3736 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3737 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3738 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3739 x_return_status OUT NOCOPY VARCHAR2,
3740 x_msg_count OUT NOCOPY NUMBER,
3741 x_msg_data OUT NOCOPY VARCHAR2,
3742 p_code_conversion_id IN NUMBER,
3743 p_object_version_number IN NUMBER
3744 )
3745 IS
3746 l_api_name CONSTANT VARCHAR2(50) := 'Delete_supp_code_conversion';
3747 l_api_version_number CONSTANT NUMBER := 1.0;
3748 l_object_version_number NUMBER;
3749
3750 l_dummy NUMBER;
3751
3752 BEGIN
3753 -- Standard Start of API savepoint
3754 SAVEPOINT delete_supp_code_conv_pvt;
3755
3756 -- Standard call to check for call compatibility.
3757 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3758 p_api_version_number,
3759 l_api_name,
3760 G_PKG_NAME)
3761 THEN
3762 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3763 END IF;
3764
3765 -- Initialize message list if p_init_msg_list is set to TRUE.
3766 IF FND_API.to_Boolean( p_init_msg_list )
3767 THEN
3768 FND_MSG_PUB.initialize;
3769 END IF;
3770
3771 -- Debug Message
3772 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3773 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
3774 END IF;
3775
3776
3777 -- Initialize API return status to SUCCESS
3778 x_return_status := FND_API.G_RET_STS_SUCCESS;
3779
3780
3781
3782
3783 -- Api body
3784 --
3785 -- Debug Message
3786 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3787 OZF_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
3788 END IF;
3789
3790 BEGIN
3791 OZF_CODE_CONVERSION_PKG.Delete_Supp_Code_Conv_Row( p_code_conversion_id => p_code_conversion_id,
3792 p_object_version_number => p_object_version_number );
3793 EXCEPTION
3794 WHEN OTHERS THEN
3795 RAISE FND_API.G_EXC_ERROR;
3796 END;
3797
3798 -- Standard check for p_commit
3799 IF FND_API.to_Boolean( p_commit )
3800 THEN
3801 COMMIT WORK;
3802 END IF;
3803
3804 -- Debug Message
3805 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low) THEN
3806 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
3807 END IF;
3808
3809 -- Standard call to get message count and if count is 1, get message info.
3810 FND_MSG_PUB.Count_And_Get
3811 (p_count => x_msg_count,
3812 p_data => x_msg_data
3813 );
3814 EXCEPTION
3815 WHEN FND_API.G_EXC_ERROR THEN
3816 ROLLBACK TO delete_supp_code_conv_pvt;
3817 x_return_status := FND_API.G_RET_STS_ERROR;
3818 -- Standard call to get message count and if count=1, get the message
3819 FND_MSG_PUB.Count_And_Get (
3820 p_encoded => FND_API.G_FALSE,
3821 p_count => x_msg_count,
3822 p_data => x_msg_data
3823 );
3824 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3825 ROLLBACK TO delete_supp_code_conv_pvt;
3826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3827 -- Standard call to get message count and if count=1, get the message
3828 FND_MSG_PUB.Count_And_Get (
3829 p_encoded => FND_API.G_FALSE,
3830 p_count => x_msg_count,
3831 p_data => x_msg_data
3832 );
3833 WHEN OTHERS THEN
3834 ROLLBACK TO delete_supp_code_conv_pvt;
3835 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3836 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3837 THEN
3838 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3839 END IF;
3840 -- Standard call to get message count and if count=1, get the message
3841 FND_MSG_PUB.Count_And_Get (
3842 p_encoded => FND_API.G_FALSE,
3843 p_count => x_msg_count,
3844 p_data => x_msg_data
3845 );
3846
3847 END Delete_Supp_Code_Conversion;
3848
3849
3850
3851 END Ozf_Code_Conversion_Pvt;
3852