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