[Home] [Help]
PACKAGE BODY: APPS.AMS_COMPETITOR_PRODUCT_PVT
Source
1 PACKAGE BODY Ams_Competitor_Product_Pvt as
2 /* $Header: amsvcprb.pls 120.1 2005/08/04 08:24:57 appldev ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- Ams_Competitor_Product_Pvt
7 -- Purpose
8 --
9 -- History
10 -- 01-Oct-2001 musman created
11 -- 05-Nov-2001 musman Commented out the reference to security_group_id
12 -- 17-MAY-2002 abhola removed g_user_id and g_login_id
13 -- 10-Sep-2003 Musman Added Changes reqd for interest type to category
14 -- 04-Aug-2005 inanaiah R12 change - added a DFF
15 -- NOTE
16 --
17 -- End of Comments
18 -- ===============================================================
19
20
21 G_PKG_NAME CONSTANT VARCHAR2(30):= 'Ams_Competitor_Product_Pvt';
22 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvcprb.pls';
23
24
25 -- Hint: Primary key needs to be returned.
26 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
27 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
28 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
29
30 PROCEDURE Create_Comp_Product(
31 p_api_version_number IN NUMBER,
32 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
33 p_commit IN VARCHAR2 := FND_API.G_FALSE,
34 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
35
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2,
39
40 p_comp_prod_rec IN comp_prod_rec_type := g_miss_comp_prod_type_rec ,
41 x_competitor_product_id OUT NOCOPY NUMBER
42 )
43
44 IS
45 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Ams_Comp_Product';
46 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
47 l_return_status_full VARCHAR2(1);
48 l_object_version_number NUMBER := 1;
49 l_org_id NUMBER := FND_API.G_MISS_NUM;
50 l_COMPETITOR_PRODUCT_ID NUMBER;
51 l_dummy NUMBER;
52
53 CURSOR c_id IS
54 SELECT AMS_COMPETITOR_PRODUCTS_B_s.NEXTVAL
55 FROM dual;
56
57 CURSOR c_id_exists (l_id IN NUMBER) IS
58 SELECT 1
59 FROM AMS_COMPETITOR_PRODUCTS_B
60 WHERE COMPETITOR_PRODUCT_ID = l_id;
61
62 BEGIN
63 -- Standard Start of API savepoint
64 SAVEPOINT CREATE_Ams_Comp_Product_PVT;
65
66 -- Standard call to check for call compatibility.
67 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
68 p_api_version_number,
69 l_api_name,
70 G_PKG_NAME)
71 THEN
72 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
73 END IF;
74
75 -- Initialize message list if p_init_msg_list is set to TRUE.
76 IF FND_API.to_Boolean( p_init_msg_list )
77 THEN
78 FND_MSG_PUB.initialize;
79 END IF;
80
81 -- Debug Message
82 IF (AMS_DEBUG_HIGH_ON) THEN
83
84 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
85 END IF;
86
87
88 -- Initialize API return status to SUCCESS
89 x_return_status := FND_API.G_RET_STS_SUCCESS;
90
91 -- Local variable initialization
92
93 IF p_comp_prod_rec.COMPETITOR_PRODUCT_ID IS NULL
94 OR
95 p_comp_prod_rec.COMPETITOR_PRODUCT_ID = FND_API.g_miss_num
96 THEN
97 LOOP
98 l_dummy := NULL;
99 OPEN c_id;
100 FETCH c_id INTO l_COMPETITOR_PRODUCT_ID;
101 CLOSE c_id;
102
103 OPEN c_id_exists(l_COMPETITOR_PRODUCT_ID);
104 FETCH c_id_exists INTO l_dummy;
105 CLOSE c_id_exists;
106 EXIT WHEN l_dummy IS NULL;
107 END LOOP;
108 END IF;
109
110 -- =========================================================================
111 -- Validate Environment
112 -- =========================================================================
113
114 IF FND_GLOBAL.User_Id IS NULL
115 THEN
116 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
117 RAISE FND_API.G_EXC_ERROR;
118 END IF;
119
120 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
121 THEN
122 -- Debug message
123 IF (AMS_DEBUG_HIGH_ON) THEN
124
125 AMS_UTILITY_PVT.debug_message('Private API: Validate_Ams_Comp_Product');
126 END IF;
127
128 -- Invoke validation procedures
129 Validate_comp_prod(
130 p_api_version_number => 1.0,
131 p_init_msg_list => FND_API.G_FALSE,
132 p_validation_level => p_validation_level,
133 p_comp_prod_rec => p_comp_prod_rec,
134 x_return_status => x_return_status,
135 x_msg_count => x_msg_count,
136 x_msg_data => x_msg_data);
137 END IF;
138
139 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
140 RAISE FND_API.G_EXC_ERROR;
141 END IF;
142
143
144 -- Invoke table handler(AMS_COMPETITOR_PRODUCTS_B_PKG.Insert_Row)
145 AMS_COMPETITOR_PRODUCTS_B_PKG.Insert_Row(
146 px_competitor_product_id => l_competitor_product_id,
147 px_object_version_number => l_object_version_number,
148 p_last_update_date => SYSDATE,
149 p_last_updated_by => FND_GLOBAL.USER_ID,
150 p_creation_date => SYSDATE,
151 p_created_by => FND_GLOBAL.USER_ID,
152 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
153 p_competitor_party_id => p_comp_prod_rec.competitor_party_id,
154 p_competitor_product_code => p_comp_prod_rec.competitor_product_code,
155 p_interest_type_id => p_comp_prod_rec.interest_type_id,
156 p_inventory_item_id => p_comp_prod_rec.inventory_item_id,
157 p_organization_id => p_comp_prod_rec.organization_id,
158 p_comp_product_url => p_comp_prod_rec.comp_product_url,
159 p_original_system_ref => p_comp_prod_rec.original_system_ref,
160 --p_security_group_id => p_comp_prod_rec.security_group_id,
161 p_competitor_product_name => p_comp_prod_rec.competitor_product_name,
162 p_description => p_comp_prod_rec.description,
163 p_start_date => p_comp_prod_rec.start_date,
164 p_end_date => p_comp_prod_rec.end_date
165 ,p_category_id => p_comp_prod_rec.category_id
166 ,p_category_set_id => p_comp_prod_rec.category_set_id
167 , p_context => p_comp_prod_rec.context
168 , p_attribute1 => p_comp_prod_rec.attribute1
169 , p_attribute2 => p_comp_prod_rec.attribute2
170 , p_attribute3 => p_comp_prod_rec.attribute3
171 , p_attribute4 => p_comp_prod_rec.attribute4
172 , p_attribute5 => p_comp_prod_rec.attribute5
173 , p_attribute6 => p_comp_prod_rec.attribute6
174 , p_attribute7 => p_comp_prod_rec.attribute7
175 , p_attribute8 => p_comp_prod_rec.attribute8
176 , p_attribute9 => p_comp_prod_rec.attribute9
177 , p_attribute10 => p_comp_prod_rec.attribute10
178 , p_attribute11 => p_comp_prod_rec.attribute11
179 , p_attribute12 => p_comp_prod_rec.attribute12
180 , p_attribute13 => p_comp_prod_rec.attribute13
181 , p_attribute14 => p_comp_prod_rec.attribute14
182 , p_attribute15 => p_comp_prod_rec.attribute15
183 );
184
185 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
186 RAISE FND_API.G_EXC_ERROR;
187 END IF;
188 --
189 -- End of API body
190 --
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 x_competitor_product_id := l_competitor_product_id;
199
200 -- Standard call to get message count and if count is 1, get message info.
201 FND_MSG_PUB.Count_And_Get
202 (p_count => x_msg_count,
203 p_data => x_msg_data
204 );
205 EXCEPTION
206
207 WHEN AMS_Utility_PVT.resource_locked THEN
208 x_return_status := FND_API.g_ret_sts_error;
209 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
210
211 WHEN FND_API.G_EXC_ERROR THEN
212 ROLLBACK TO CREATE_Ams_Comp_Product_PVT;
213 x_return_status := FND_API.G_RET_STS_ERROR;
214 -- Standard call to get message count and if count=1, get the message
215 FND_MSG_PUB.Count_And_Get (
216 p_encoded => FND_API.G_FALSE,
217 p_count => x_msg_count,
218 p_data => x_msg_data
219 );
220
221 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
222 ROLLBACK TO CREATE_Ams_Comp_Product_PVT;
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
224 -- Standard call to get message count and if count=1, get the message
225 FND_MSG_PUB.Count_And_Get (
226 p_encoded => FND_API.G_FALSE,
227 p_count => x_msg_count,
228 p_data => x_msg_data
229 );
230
231 WHEN OTHERS THEN
232 ROLLBACK TO CREATE_Ams_Comp_Product_PVT;
233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
234 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
235 THEN
236 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
237 END IF;
238 -- Standard call to get message count and if count=1, get the message
239 FND_MSG_PUB.Count_And_Get (
240 p_encoded => FND_API.G_FALSE,
241 p_count => x_msg_count,
242 p_data => x_msg_data
243 );
244
245 End Create_Comp_Product;
246
247
248 PROCEDURE Update_Comp_Product(
249 p_api_version_number IN NUMBER,
250 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
251 p_commit IN VARCHAR2 := FND_API.G_FALSE,
252 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
253
254 x_return_status OUT NOCOPY VARCHAR2,
255 x_msg_count OUT NOCOPY NUMBER,
256 x_msg_data OUT NOCOPY VARCHAR2,
257
258 p_comp_prod_rec IN comp_prod_rec_type,
259 x_object_version_number OUT NOCOPY NUMBER
260 )
261
262 IS
263
264
265
266 CURSOR c_get_ams_Comp_Product(competitor_product_id NUMBER) IS
267 SELECT *
268 FROM AMS_COMPETITOR_PRODUCTS_B
269 WHERE competitor_product_id = p_comp_prod_rec.competitor_product_id;
270
271 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Ams_Comp_Product';
272 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
273 -- Local Variables
274 l_object_version_number NUMBER;
275 l_COMPETITOR_PRODUCT_ID NUMBER;
276 l_ref_comp_prod_rec c_get_Ams_Comp_Product%ROWTYPE ;
277 l_tar_comp_prod_type_rec Ams_Competitor_Product_Pvt.comp_prod_rec_type := p_comp_prod_rec;
278 l_rowid ROWID;
279
280 BEGIN
281 -- Standard Start of API savepoint
282 SAVEPOINT UPDATE_Ams_Comp_Product_PVT;
283
284 -- Standard call to check for call compatibility.
285 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
286 p_api_version_number,
287 l_api_name,
288 G_PKG_NAME)
289 THEN
290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
291 END IF;
292
293 -- Initialize message list if p_init_msg_list is set to TRUE.
294 IF FND_API.to_Boolean( p_init_msg_list )
295 THEN
296 FND_MSG_PUB.initialize;
297 END IF;
298
299 -- Debug Message
300 IF (AMS_DEBUG_HIGH_ON) THEN
301
302 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
303 END IF;
304
305
306 -- Initialize API return status to SUCCESS
307 x_return_status := FND_API.G_RET_STS_SUCCESS;
308
309 -- Debug Message
310 IF (AMS_DEBUG_HIGH_ON) THEN
311
312 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
313 END IF;
314
315
316 OPEN c_get_Ams_Comp_Product( l_tar_comp_prod_type_rec.competitor_product_id);
317 FETCH c_get_Ams_Comp_Product INTO l_ref_comp_prod_rec ;
318
319 If ( c_get_Ams_Comp_Product%NOTFOUND) THEN
320 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
321 p_token_name => 'INFO',
322 p_token_value => 'Ams_Competitor_Product_Pvt') ;
323 RAISE FND_API.G_EXC_ERROR;
324 END IF;
325 -- Debug Message
326 IF (AMS_DEBUG_HIGH_ON) THEN
327
328 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
329 END IF;
330 CLOSE c_get_Ams_Comp_Product;
331
332
333 If (l_tar_comp_prod_type_rec.object_version_number is NULL or
334 l_tar_comp_prod_type_rec.object_version_number = FND_API.G_MISS_NUM ) Then
335 AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
336 p_token_name => 'COLUMN',
337 p_token_value => 'Last_Update_Date') ;
338 raise FND_API.G_EXC_ERROR;
339 End if;
340 -- Check Whether record has been changed by someone else
341 If (l_tar_comp_prod_type_rec.object_version_number <> l_ref_comp_prod_rec.object_version_number) Then
342 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
343 p_token_name => 'INFO',
344 p_token_value => 'Ams_Competitor_Product_Pvt') ;
345 raise FND_API.G_EXC_ERROR;
346 End if;
347 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
348 THEN
349 -- Debug message
350 IF (AMS_DEBUG_HIGH_ON) THEN
351
352 AMS_UTILITY_PVT.debug_message('Private API: Validate_comp_prod');
353 END IF;
354
355 -- Invoke validation procedures
356 Validate_comp_prod(
357 p_api_version_number => 1.0,
358 p_init_msg_list => FND_API.G_FALSE,
359 p_validation_level => p_validation_level,
360 p_validation_mode => JTF_PLSQL_API.g_update,
361 p_comp_prod_rec => p_comp_prod_rec,
362 x_return_status => x_return_status,
363 x_msg_count => x_msg_count,
364 x_msg_data => x_msg_data);
365 END IF;
366
367 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
368 RAISE FND_API.G_EXC_ERROR;
369 END IF;
370
371
372
373 -- Invoke table handler(AMS_COMPETITOR_PRODUCTS_B_PKG.Update_Row)
374 AMS_COMPETITOR_PRODUCTS_B_PKG.Update_Row(
378 p_last_updated_by => FND_GLOBAL.USER_ID,
375 p_competitor_product_id => p_comp_prod_rec.competitor_product_id
376 , p_object_version_number => p_comp_prod_rec.object_version_number,
377 p_last_update_date => SYSDATE,
379 p_creation_date => SYSDATE,
380 p_created_by => FND_GLOBAL.USER_ID,
381 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
382 p_competitor_party_id => p_comp_prod_rec.competitor_party_id,
383 p_competitor_product_code => p_comp_prod_rec.competitor_product_code,
384 p_interest_type_id => p_comp_prod_rec.interest_type_id,
385 p_inventory_item_id => p_comp_prod_rec.inventory_item_id,
386 p_organization_id => p_comp_prod_rec.organization_id,
387 p_comp_product_url => p_comp_prod_rec.comp_product_url,
388 p_original_system_ref => p_comp_prod_rec.original_system_ref,
389 --p_security_group_id => p_comp_prod_rec.security_group_id,
390 p_competitor_product_name => p_comp_prod_rec.competitor_product_name,
391 p_description => p_comp_prod_rec.description,
392 p_start_date => p_comp_prod_rec.start_date,
393 p_end_date => p_comp_prod_rec.end_date
394 ,p_category_id => p_comp_prod_rec.category_id
395 ,p_category_set_id => p_comp_prod_rec.category_set_id
396 , p_context => p_comp_prod_rec.context
397 , p_attribute1 => p_comp_prod_rec.attribute1
398 , p_attribute2 => p_comp_prod_rec.attribute2
399 , p_attribute3 => p_comp_prod_rec.attribute3
400 , p_attribute4 => p_comp_prod_rec.attribute4
401 , p_attribute5 => p_comp_prod_rec.attribute5
402 , p_attribute6 => p_comp_prod_rec.attribute6
403 , p_attribute7 => p_comp_prod_rec.attribute7
404 , p_attribute8 => p_comp_prod_rec.attribute8
405 , p_attribute9 => p_comp_prod_rec.attribute9
406 , p_attribute10 => p_comp_prod_rec.attribute10
407 , p_attribute11 => p_comp_prod_rec.attribute11
408 , p_attribute12 => p_comp_prod_rec.attribute12
409 , p_attribute13 => p_comp_prod_rec.attribute13
410 , p_attribute14 => p_comp_prod_rec.attribute14
411 , p_attribute15 => p_comp_prod_rec.attribute15
412 );
413 --
414 -- End of API body.
415 --
416
417 -- Standard check for p_commit
418 IF FND_API.to_Boolean( p_commit )
419 THEN
420 COMMIT WORK;
421 END IF;
422
423
424 -- Debug Message
425 IF (AMS_DEBUG_HIGH_ON) THEN
426
427 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
428 END IF;
429
430 -- Standard call to get message count and if count is 1, get message info.
431 FND_MSG_PUB.Count_And_Get
432 (p_count => x_msg_count,
433 p_data => x_msg_data
434 );
435 EXCEPTION
436
437 WHEN AMS_Utility_PVT.resource_locked THEN
438 x_return_status := FND_API.g_ret_sts_error;
439 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
440
441 WHEN FND_API.G_EXC_ERROR THEN
442 ROLLBACK TO UPDATE_Ams_Comp_Product_PVT;
443 x_return_status := FND_API.G_RET_STS_ERROR;
444 -- Standard call to get message count and if count=1, get the message
445 FND_MSG_PUB.Count_And_Get (
446 p_encoded => FND_API.G_FALSE,
447 p_count => x_msg_count,
448 p_data => x_msg_data
449 );
450
451 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
452 ROLLBACK TO UPDATE_Ams_Comp_Product_PVT;
453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
454 -- Standard call to get message count and if count=1, get the message
455 FND_MSG_PUB.Count_And_Get (
456 p_encoded => FND_API.G_FALSE,
457 p_count => x_msg_count,
458 p_data => x_msg_data
459 );
460
461 WHEN OTHERS THEN
462 ROLLBACK TO UPDATE_Ams_Comp_Product_PVT;
463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
464 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
465 THEN
466 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
467 END IF;
468 -- Standard call to get message count and if count=1, get the message
469 FND_MSG_PUB.Count_And_Get (
470 p_encoded => FND_API.G_FALSE,
471 p_count => x_msg_count,
472 p_data => x_msg_data
473 );
474
475 End Update_Comp_Product;
476
477
478 PROCEDURE Delete_Comp_Product(
479 p_api_version_number IN NUMBER,
480 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
481 p_commit IN VARCHAR2 := FND_API.G_FALSE,
482 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
483 x_return_status OUT NOCOPY VARCHAR2,
484 x_msg_count OUT NOCOPY NUMBER,
485 x_msg_data OUT NOCOPY VARCHAR2,
486 p_competitor_product_id IN NUMBER,
487 p_object_version_number IN NUMBER
488 )
489
490 IS
494
491 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Ams_Comp_Product';
492 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
493 l_object_version_number NUMBER;
495 BEGIN
496 -- Standard Start of API savepoint
497 SAVEPOINT DELETE_Ams_Comp_Product_PVT;
498
499 -- Standard call to check for call compatibility.
500 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
501 p_api_version_number,
502 l_api_name,
503 G_PKG_NAME)
504 THEN
505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
506 END IF;
507
508 -- Initialize message list if p_init_msg_list is set to TRUE.
509 IF FND_API.to_Boolean( p_init_msg_list )
510 THEN
511 FND_MSG_PUB.initialize;
512 END IF;
513
514 -- Debug Message
515 IF (AMS_DEBUG_HIGH_ON) THEN
516
517 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
518 END IF;
519
520
521 -- Initialize API return status to SUCCESS
522 x_return_status := FND_API.G_RET_STS_SUCCESS;
523
524 --
525 -- Api body
526 --
527 -- Debug Message
528 IF (AMS_DEBUG_HIGH_ON) THEN
529
530 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
531 END IF;
532
533 -- Invoke table handler(AMS_COMPETITOR_PRODUCTS_B_PKG.Delete_Row)
534 AMS_COMPETITOR_PRODUCTS_B_PKG.Delete_Row(
535 p_COMPETITOR_PRODUCT_ID => p_COMPETITOR_PRODUCT_ID,
536 p_Object_Version_number => p_object_version_number
537 );
538 --
539 -- End of API body
540 --
541
542 -- Standard check for p_commit
543 IF FND_API.to_Boolean( p_commit )
544 THEN
545 COMMIT WORK;
546 END IF;
547
548
549 -- Debug Message
550 IF (AMS_DEBUG_HIGH_ON) THEN
551
552 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
553 END IF;
554
555 -- Standard call to get message count and if count is 1, get message info.
556 FND_MSG_PUB.Count_And_Get
557 (p_count => x_msg_count,
558 p_data => x_msg_data
559 );
560 EXCEPTION
561
562 WHEN AMS_Utility_PVT.resource_locked THEN
563 x_return_status := FND_API.g_ret_sts_error;
564 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
565
566 WHEN FND_API.G_EXC_ERROR THEN
567 ROLLBACK TO DELETE_Ams_Comp_Product_PVT;
568 x_return_status := FND_API.G_RET_STS_ERROR;
569 -- Standard call to get message count and if count=1, get the message
570 FND_MSG_PUB.Count_And_Get (
571 p_encoded => FND_API.G_FALSE,
572 p_count => x_msg_count,
573 p_data => x_msg_data
574 );
575
576 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
577 ROLLBACK TO DELETE_Ams_Comp_Product_PVT;
578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
579 -- Standard call to get message count and if count=1, get the message
580 FND_MSG_PUB.Count_And_Get (
581 p_encoded => FND_API.G_FALSE,
582 p_count => x_msg_count,
583 p_data => x_msg_data
584 );
585
586 WHEN OTHERS THEN
587 ROLLBACK TO DELETE_Ams_Comp_Product_PVT;
588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
589 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
590 THEN
591 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
592 END IF;
593 -- Standard call to get message count and if count=1, get the message
594 FND_MSG_PUB.Count_And_Get (
595 p_encoded => FND_API.G_FALSE,
596 p_count => x_msg_count,
597 p_data => x_msg_data
598 );
599 End Delete_Comp_Product;
600
601
602
603 -- Hint: Primary key needs to be returned.
604
605 PROCEDURE Lock_Comp_Product(
606 p_api_version_number IN NUMBER,
607 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
608
609 x_return_status OUT NOCOPY VARCHAR2,
610 x_msg_count OUT NOCOPY NUMBER,
611 x_msg_data OUT NOCOPY VARCHAR2,
612
613 p_competitor_product_id IN NUMBER,
614 p_object_version IN NUMBER
615 )
616
617 IS
618 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Ams_Comp_Product';
619 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
620 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
621 l_COMPETITOR_PRODUCT_ID NUMBER;
622
623 CURSOR c_Ams_Comp_Product IS
624 SELECT COMPETITOR_PRODUCT_ID
625 FROM AMS_COMPETITOR_PRODUCTS_B
626 WHERE COMPETITOR_PRODUCT_ID = p_COMPETITOR_PRODUCT_ID
627 AND object_version_number = p_object_version
628 FOR UPDATE NOWAIT;
629
630 BEGIN
631
632 -- Debug Message
633 IF (AMS_DEBUG_HIGH_ON) THEN
634
635 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
636 END IF;
637
641 FND_MSG_PUB.initialize;
638 -- Initialize message list if p_init_msg_list is set to TRUE.
639 IF FND_API.to_Boolean( p_init_msg_list )
640 THEN
642 END IF;
643
644 -- Standard call to check for call compatibility.
645 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
646 p_api_version_number,
647 l_api_name,
648 G_PKG_NAME)
649 THEN
650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
651 END IF;
652
653
654 -- Initialize API return status to SUCCESS
655 x_return_status := FND_API.G_RET_STS_SUCCESS;
656
657
658 ------------------------ lock -------------------------
659
660 IF (AMS_DEBUG_HIGH_ON) THEN
661
662
663
664 AMS_Utility_PVT.debug_message(l_full_name||': start');
665
666 END IF;
667 OPEN c_Ams_Comp_Product;
668
669 FETCH c_Ams_Comp_Product INTO l_COMPETITOR_PRODUCT_ID;
670
671 IF (c_Ams_Comp_Product%NOTFOUND) THEN
672 CLOSE c_Ams_Comp_Product;
673 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
674 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
675 FND_MSG_PUB.add;
676 END IF;
677 RAISE FND_API.g_exc_error;
678 END IF;
679
680 CLOSE c_Ams_Comp_Product;
681
682 -------------------- finish --------------------------
683 FND_MSG_PUB.count_and_get(
684 p_encoded => FND_API.g_false,
685 p_count => x_msg_count,
686 p_data => x_msg_data);
687 IF (AMS_DEBUG_HIGH_ON) THEN
688
689 AMS_Utility_PVT.debug_message(l_full_name ||': end');
690 END IF;
691 EXCEPTION
692
693 WHEN AMS_Utility_PVT.resource_locked THEN
694 x_return_status := FND_API.g_ret_sts_error;
695 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
696
697 WHEN FND_API.G_EXC_ERROR THEN
698 ROLLBACK TO LOCK_Ams_Comp_Product_PVT;
699 x_return_status := FND_API.G_RET_STS_ERROR;
700 -- Standard call to get message count and if count=1, get the message
701 FND_MSG_PUB.Count_And_Get (
702 p_encoded => FND_API.G_FALSE,
703 p_count => x_msg_count,
704 p_data => x_msg_data
705 );
706
707 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
708 ROLLBACK TO LOCK_Ams_Comp_Product_PVT;
709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
710 -- Standard call to get message count and if count=1, get the message
711 FND_MSG_PUB.Count_And_Get (
712 p_encoded => FND_API.G_FALSE,
713 p_count => x_msg_count,
714 p_data => x_msg_data
715 );
716
717 WHEN OTHERS THEN
718 ROLLBACK TO LOCK_Ams_Comp_Product_PVT;
719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
720 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
721 THEN
722 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
723 END IF;
724 -- Standard call to get message count and if count=1, get the message
725 FND_MSG_PUB.Count_And_Get (
726 p_encoded => FND_API.G_FALSE,
727 p_count => x_msg_count,
728 p_data => x_msg_data
729 );
730 End ;
731
732
733
734
735 PROCEDURE Complete_comp_prod_rec (
736 p_comp_prod_rec IN comp_prod_rec_type,
737 x_complete_rec OUT NOCOPY comp_prod_rec_type)
738 IS
739 l_return_status VARCHAR2(1);
740
741 CURSOR c_complete IS
742 SELECT *
743 FROM ams_competitor_products_vl
744 WHERE competitor_product_id = p_comp_prod_rec.competitor_product_id;
745 l_comp_prod_rec_type_rec c_complete%ROWTYPE;
746 BEGIN
747 x_complete_rec := p_comp_prod_rec;
748
749
750 OPEN c_complete;
751 FETCH c_complete INTO l_comp_prod_rec_type_rec;
752 CLOSE c_complete;
753
754 -- competitor_product_id
755 IF p_comp_prod_rec.competitor_product_id = FND_API.g_miss_num THEN
756 x_complete_rec.competitor_product_id := l_comp_prod_rec_type_rec.competitor_product_id;
757 END IF;
758
759 -- object_version_number
760 IF p_comp_prod_rec.object_version_number = FND_API.g_miss_num THEN
761 x_complete_rec.object_version_number := l_comp_prod_rec_type_rec.object_version_number;
762 END IF;
763
764 -- last_update_date
765 IF p_comp_prod_rec.last_update_date = FND_API.g_miss_date THEN
766 x_complete_rec.last_update_date := l_comp_prod_rec_type_rec.last_update_date;
767 END IF;
768
769 -- last_updated_by
770 IF p_comp_prod_rec.last_updated_by = FND_API.g_miss_num THEN
771 x_complete_rec.last_updated_by := l_comp_prod_rec_type_rec.last_updated_by;
772 END IF;
773
774 -- creation_date
775 IF p_comp_prod_rec.creation_date = FND_API.g_miss_date THEN
776 x_complete_rec.creation_date := l_comp_prod_rec_type_rec.creation_date;
777 END IF;
778
779 -- created_by
780 IF p_comp_prod_rec.created_by = FND_API.g_miss_num THEN
781 x_complete_rec.created_by := l_comp_prod_rec_type_rec.created_by;
782 END IF;
783
784 -- last_update_login
785 IF p_comp_prod_rec.last_update_login = FND_API.g_miss_num THEN
789 -- competitor_party_id
786 x_complete_rec.last_update_login := l_comp_prod_rec_type_rec.last_update_login;
787 END IF;
788
790 IF p_comp_prod_rec.competitor_party_id = FND_API.g_miss_num THEN
791 x_complete_rec.competitor_party_id := l_comp_prod_rec_type_rec.competitor_party_id;
792 END IF;
793
794 -- competitor_product_code
795 IF p_comp_prod_rec.competitor_product_code = FND_API.g_miss_char THEN
796 x_complete_rec.competitor_product_code := l_comp_prod_rec_type_rec.competitor_product_code;
797 END IF;
798
799 -- interest_type_id
800 IF p_comp_prod_rec.interest_type_id = FND_API.g_miss_num THEN
801 x_complete_rec.interest_type_id := l_comp_prod_rec_type_rec.interest_type_id;
802 END IF;
803
804 -- inventory_item_id
805 IF p_comp_prod_rec.inventory_item_id = FND_API.g_miss_num THEN
806 x_complete_rec.inventory_item_id := l_comp_prod_rec_type_rec.inventory_item_id;
807 END IF;
808
809 -- organization_id
810 IF p_comp_prod_rec.organization_id = FND_API.g_miss_num THEN
811 x_complete_rec.organization_id := l_comp_prod_rec_type_rec.organization_id;
812 END IF;
813
814 -- comp_product_url
815 IF p_comp_prod_rec.comp_product_url = FND_API.g_miss_char THEN
816 x_complete_rec.comp_product_url := l_comp_prod_rec_type_rec.comp_product_url;
817 END IF;
818
819 -- original_system_ref
820 IF p_comp_prod_rec.original_system_ref = FND_API.g_miss_char THEN
821 x_complete_rec.original_system_ref := l_comp_prod_rec_type_rec.original_system_ref;
822 END IF;
823 /*
824 -- security_group_id
825 IF p_comp_prod_rec.security_group_id = FND_API.g_miss_num THEN
826 x_complete_rec.security_group_id := l_comp_prod_rec_type_rec.security_group_id;
827 END IF;
828 */
829 -- competitor_product_name
830 IF p_comp_prod_rec.competitor_product_name = FND_API.g_miss_char THEN
831 x_complete_rec.competitor_product_name := l_comp_prod_rec_type_rec.competitor_product_name;
832 END IF;
833
834 -- description
835 IF p_comp_prod_rec.description = FND_API.g_miss_char THEN
836 x_complete_rec.description := l_comp_prod_rec_type_rec.description;
837 END IF;
838
839 IF p_comp_prod_rec.start_date = FND_API.g_miss_date THEN
840 x_complete_rec.start_date := l_comp_prod_rec_type_rec.start_date;
841 END IF;
842
843 IF p_comp_prod_rec.end_date = FND_API.g_miss_date THEN
844 x_complete_rec.end_date := l_comp_prod_rec_type_rec.end_date;
845 END IF;
846
847 IF p_comp_prod_rec.category_id = FND_API.g_miss_NUM THEN
848 x_complete_rec.category_id := l_comp_prod_rec_type_rec.category_id;
849 END IF;
850
851 IF p_comp_prod_rec.category_set_id = FND_API.g_miss_NUM THEN
852 x_complete_rec.category_set_id := l_comp_prod_rec_type_rec.category_set_id;
853 END IF;
854
855 -- to handle any business specific requirements.
856 END Complete_comp_prod_rec;
857
858 PROCEDURE check_comp_prod_uk_items(
859 p_comp_prod_rec IN comp_prod_rec_type,
860 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
861 x_return_status OUT NOCOPY VARCHAR2)
862 IS
863 l_valid_flag VARCHAR2(1);
864
865 BEGIN
866 x_return_status := FND_API.g_ret_sts_success;
867 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
868 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
869 'AMS_COMPETITOR_PRODUCTS_B',
870 'competitor_product_id = '|| p_comp_prod_rec.competitor_product_id
871 );
872 ELSE
873 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
874 'AMS_COMPETITOR_PRODUCTS_B',
875 'competitor_product_id = ''' || p_comp_prod_rec.competitor_product_id ||
876 ''' AND competitor_product_id <> ' || p_comp_prod_rec.competitor_product_id
877 );
878 END IF;
879
880 IF l_valid_flag = FND_API.g_false THEN
881 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_competitor_product_id_DUPLICATE');
882 x_return_status := FND_API.g_ret_sts_error;
883 RETURN;
884 END IF;
885
886
887
888 END check_comp_prod_uk_items;
889
890 PROCEDURE check_comp_Prod_req_items(
891 p_comp_Prod_rec IN comp_Prod_rec_type,
892 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
893 x_return_status OUT NOCOPY VARCHAR2
894 )
895 IS
896 BEGIN
897 x_return_status := FND_API.g_ret_sts_success;
898
899 IF (AMS_DEBUG_HIGH_ON)
900 THEN
901 AMS_UTILITY_PVT.debug_message('INSIDE THE check_comp_Prod_req_items and p_validation_mode is:'||p_validation_mode);
902 END IF;
903
904 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
905
906 IF p_comp_Prod_rec.competitor_party_id = FND_API.g_miss_num OR p_comp_Prod_rec.competitor_party_id IS NULL THEN
907 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_comp_Prod_NO_competitor_party_id');
908 x_return_status := FND_API.g_ret_sts_error;
909 RETURN;
910 END IF;
911
912 IF p_comp_Prod_rec.competitor_product_name = FND_API.g_miss_char OR p_comp_Prod_rec.competitor_product_name IS NULL THEN
913 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_comp_Prod_NO_competitor_product_name');
917
914 x_return_status := FND_API.g_ret_sts_error;
915 RETURN;
916 END IF;
918 IF (AMS_DEBUG_HIGH_ON)
919 THEN
920 AMS_UTILITY_PVT.debug_message('before error msf');
921 IF( p_comp_Prod_rec.inventory_item_id=FND_API.g_miss_num
922 AND p_comp_prod_rec.category_id=FND_API.g_miss_num)
923 THEN
924 AMS_UTILITY_PVT.debug_message('inv_id and cat id is g_miss num');
925 ELSE
926 AMS_UTILITY_PVT.debug_message(' in the else part before erroring out');
927 AMS_UTILITY_PVT.debug_message('p_comp_Prod_rec.inventory_item_id:'||p_comp_Prod_rec.inventory_item_id);
928 AMS_UTILITY_PVT.debug_message('p_comp_Prod_rec.category_id:'||p_comp_Prod_rec.category_id);
929 END IF;
930 END IF;
931
932 IF ((p_comp_Prod_rec.inventory_item_id=FND_API.g_miss_num AND p_comp_prod_rec.category_id=FND_API.g_miss_num)
933 OR
934 ((p_comp_Prod_rec.inventory_item_id IS NULL) AND (p_comp_prod_rec.category_id IS NULL)))
935 THEN
936 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_INV_AND_CAT_NULL');
937 x_return_status := FND_API.g_ret_sts_error;
938 RETURN;
939 END IF;
940 ELSE
941
942 IF p_comp_Prod_rec.competitor_product_id IS NULL THEN
943 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_comp_Prod_NO_competitor_product_id');
944 x_return_status := FND_API.g_ret_sts_error;
945 RETURN;
946 END IF;
947
948 IF (( p_comp_Prod_rec.inventory_item_id IS NULL ) AND (p_comp_prod_rec.category_id IS NULL)) --interest_type_id
949 THEN
950 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_INV_AND_CAT_NULL');
951 x_return_status := FND_API.g_ret_sts_error;
952 RETURN;
953 END IF;
954
955 IF p_comp_Prod_rec.competitor_party_id IS NULL THEN
956 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_comp_Prod_NO_competitor_party_id');
957 x_return_status := FND_API.g_ret_sts_error;
958 RETURN;
959 END IF;
960
961 IF p_comp_Prod_rec.competitor_product_name IS NULL THEN
962 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_comp_Prod_NO_competitor_product_name');
963 x_return_status := FND_API.g_ret_sts_error;
964 RETURN;
965 END IF;
966
967
968 /***
969
970 IF p_comp_Prod_rec.inventory_item_id IS NULL THEN
971 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_comp_Prod_NO_inventory_item_id');
972 x_return_status := FND_API.g_ret_sts_error;
973 RETURN;
974 END IF;
975
976
977 IF p_comp_Prod_rec.organization_id IS NULL THEN
978 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_comp_Prod_NO_organization_id');
979 x_return_status := FND_API.g_ret_sts_error;
980 RETURN;
981 END IF;
982 ***/
983
984
985 END IF;
986
987 END check_comp_Prod_req_items;
988
989 PROCEDURE check_comp_prod_FK_items(
990 p_comp_prod_rec IN comp_prod_rec_type,
991 x_return_status OUT NOCOPY VARCHAR2
992 )
993 IS
994 BEGIN
995 x_return_status := FND_API.g_ret_sts_success;
996
997 --- checking the inventory_item_id
998 IF p_comp_prod_rec.inventory_item_id <> FND_API.g_miss_num
999 AND p_comp_prod_rec.inventory_item_id IS NOT NULL
1000 THEN
1001 IF AMS_Utility_PVT.check_fk_exists(
1002 'mtl_system_items_b',
1003 'inventory_item_id',
1004 p_comp_prod_rec.inventory_item_id ) = FND_API.g_false
1005 THEN
1006 AMS_Utility_PVT.Error_Message('AMS_PCMP_BAD_ITEM_ID');
1007 x_return_status := FND_API.g_ret_sts_error;
1008 RETURN;
1009 END IF;
1010 END IF;
1011
1012 --- checking the organization_id
1013 IF p_comp_prod_rec.organization_id <> FND_API.G_MISS_NUM
1014 AND p_comp_prod_rec.organization_id IS NOT NULL
1015 THEN
1016 IF AMS_Utility_PVT.check_fk_exists(
1017 'mtl_system_items_b'
1018 ,'organization_id'
1019 ,p_comp_prod_rec.organization_id) = FND_API.g_false
1020 THEN
1021 AMS_Utility_PVT.Error_Message('AMS_PCMP_BAD_ORG_ID');
1022 x_return_status := FND_API.g_ret_sts_error;
1023 RETURN;
1024 END IF;
1025 END IF;
1026
1027 -- checking the competitor_party_id
1028 IF p_comp_prod_rec.competitor_party_id <> FND_API.g_miss_num
1029 AND p_comp_prod_rec.competitor_party_id IS NOT NULL
1030 THEN
1031 IF AMS_Utility_PVT.check_fk_exists(
1032 'hz_parties'
1033 ,'party_id '
1034 ,p_comp_prod_rec.competitor_party_id) = FND_API.g_false
1035 THEN
1036 AMS_Utility_PVT.Error_Message('AMS_PCMP_BAD_PARTY_ID');
1037 x_return_status := FND_API.g_ret_sts_error;
1038 RETURN;
1039 END IF;
1040 END IF;
1041 /*
1042 -- checking the interest_Type_id
1043 IF p_comp_prod_rec.interest_type_id <> FND_API.G_MISS_NUM
1044 AND p_comp_prod_rec.interest_type_id IS NOT NULL
1045 THEN
1046 IF AMS_Utility_PVT.check_fk_exists(
1047 'as_interest_types_v'
1048 ,'interest_type_id'
1052 x_return_status := FND_API.g_ret_sts_error;
1049 ,p_comp_prod_rec.interest_type_id) = FND_API.g_false
1050 THEN
1051 AMS_Utility_PVT.Error_Message('AMS_PCMP_BAD_INTEREST_ID');
1053 RETURN;
1054 END IF;
1055 END IF;
1056 */
1057 END check_comp_prod_FK_items;
1058
1059 PROCEDURE Check_comp_prod_Items (
1060 p_comp_prod_rec IN comp_prod_rec_type,
1061 p_validation_mode IN VARCHAR2,
1062 x_return_status OUT NOCOPY VARCHAR2
1063 )
1064 IS
1065 BEGIN
1066
1067 -- Check Items Uniqueness API calls
1068 IF (AMS_DEBUG_HIGH_ON) THEN
1069 AMS_UTILITY_PVT.debug_message('Check_Comp_prod_Items - is first the return status '||x_return_status);
1070 END IF;
1071 -- Initialize API return status to SUCCESS
1072 x_return_status := FND_API.G_RET_STS_SUCCESS;
1073
1074 check_comp_prod_uk_items(
1075 p_comp_prod_rec => p_comp_prod_rec,
1076 p_validation_mode => p_validation_mode,
1077 x_return_status => x_return_status);
1078 IF x_return_status <> FND_API.g_ret_sts_success THEN
1079 RETURN;
1080 END IF;
1081
1082 -- Check Items Required/NOT NULL API calls
1083 IF (AMS_DEBUG_HIGH_ON) THEN
1084
1085 AMS_UTILITY_PVT.debug_message('Check_Comp_prod_Items - return status after uk_items :'||x_return_status);
1086 END IF;
1087
1088 check_comp_prod_req_items(
1089 p_comp_prod_rec => p_comp_prod_rec,
1090 p_validation_mode => p_validation_mode,
1091 x_return_status => x_return_status);
1092 IF x_return_status <> FND_API.g_ret_sts_success THEN
1093 RETURN;
1094 END IF;
1095
1096 IF (AMS_DEBUG_HIGH_ON) THEN
1097
1098
1099
1100 AMS_UTILITY_PVT.debug_message('Check_Comp_prod_Items - return status after reg_items :'||x_return_status);
1101
1102 END IF;
1103 -- Check Items Foreign Keys API calls
1104
1105 check_comp_prod_FK_items(
1106 p_comp_prod_rec => p_comp_prod_rec,
1107 x_return_status => x_return_status);
1108 IF x_return_status <> FND_API.g_ret_sts_success THEN
1109 RETURN;
1110 END IF;
1111
1112 IF (AMS_DEBUG_HIGH_ON) THEN
1113
1114
1115
1116 AMS_UTILITY_PVT.debug_message('Check_Comp_prod_Items - is sucess the return status '||x_return_status);
1117
1118 END IF;
1119
1120 END Check_Comp_prod_Items;
1121
1122
1123 PROCEDURE Validate_comp_prod_rec(
1124 p_api_version_number IN NUMBER,
1125 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1126 x_return_status OUT NOCOPY VARCHAR2,
1127 x_msg_count OUT NOCOPY NUMBER,
1128 x_msg_data OUT NOCOPY VARCHAR2,
1129 p_comp_prod_rec IN comp_prod_rec_type
1130 )
1131 IS
1132
1133 l_api_name varchar2(30) := 'Validate_comp_prod_rec';
1134 BEGIN
1135
1136 -- Initialize message list if p_init_msg_list is set to TRUE.
1137 IF FND_API.to_Boolean( p_init_msg_list )
1138 THEN
1139 FND_MSG_PUB.initialize;
1140 END IF;
1141
1142 -- Initialize API return status to SUCCESS
1143 x_return_status := FND_API.G_RET_STS_SUCCESS;
1144
1145 -- Hint: Validate data
1146 -- If data not valid
1147 -- THEN
1148 -- x_return_status := FND_API.G_RET_STS_ERROR;
1149
1150 -- Debug Message
1151 IF(p_comp_prod_rec.start_date > p_comp_prod_rec.end_date)
1152 THEN
1153 Ams_Utility_Pvt.debug_message('The End date is greater than Start date');
1154 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1155 THEN
1156 Fnd_Message.set_name('AMS', 'AMS_DATE_FROM_AFTER_DATE_TO');
1157 Fnd_Msg_Pub.ADD;
1158 END IF;
1159 RAISE FND_API.g_exc_error;
1160 END IF; -- (p_agenda_rec.start_date_time > p_agenda_rec.end_date_time)
1161
1162
1163 -- Standard call to get message count and if count is 1, get message info.
1164 FND_MSG_PUB.Count_And_Get
1165 (p_count => x_msg_count,
1166 p_data => x_msg_data
1167 );
1168
1169 END Validate_comp_prod_rec;
1170
1171 PROCEDURE Validate_comp_prod(
1172 p_api_version_number IN NUMBER,
1173 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1174 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1175 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1176 p_comp_prod_rec IN comp_prod_rec_type,
1177 x_return_status OUT NOCOPY VARCHAR2,
1178 x_msg_count OUT NOCOPY NUMBER,
1179 x_msg_data OUT NOCOPY VARCHAR2
1180 )
1181 IS
1182 L_API_NAME CONSTANT VARCHAR2(50) := 'Validate_Ams_Comp_Product';
1183 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1184 l_object_version_number NUMBER;
1185 l_comp_prod_rec_type_rec Ams_Competitor_Product_Pvt.comp_prod_rec_type;
1186
1187 BEGIN
1188 -- Standard Start of API savepoint
1189 SAVEPOINT VALIDATE_Ams_Comp_Product_;
1190
1191 -- Debug Message
1192 IF (AMS_DEBUG_HIGH_ON) THEN
1193
1194 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
1195 END IF;
1196
1200
1197 -- Initialize API return status to SUCCESS
1198 x_return_status := FND_API.G_RET_STS_SUCCESS;
1199
1201 -- Standard call to check for call compatibility.
1202 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1203 p_api_version_number,
1204 l_api_name,
1205 G_PKG_NAME)
1206 THEN
1207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1208 END IF;
1209
1210 -- Initialize message list if p_init_msg_list is set to TRUE.
1211 IF FND_API.to_Boolean( p_init_msg_list )
1212 THEN
1213 FND_MSG_PUB.initialize;
1214 END IF;
1215
1216
1217 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1218 Check_comp_prod_Items(
1219 p_comp_prod_rec => p_comp_prod_rec,
1220 p_validation_mode => p_validation_mode, --JTF_PLSQL_API.g_update,
1221 x_return_status => x_return_status
1222 );
1223
1224 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1225 RAISE FND_API.G_EXC_ERROR;
1226 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1228 END IF;
1229 END IF;
1230
1231 Complete_comp_prod_rec(
1232 p_comp_prod_rec => p_comp_prod_rec,
1233 x_complete_rec => l_comp_prod_rec_type_rec
1234 );
1235
1236 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1237 Validate_comp_prod_rec(
1238 p_api_version_number => 1.0,
1239 p_init_msg_list => FND_API.G_FALSE,
1240 x_return_status => x_return_status,
1241 x_msg_count => x_msg_count,
1242 x_msg_data => x_msg_data,
1243 p_comp_prod_rec => l_comp_prod_rec_type_rec);
1244
1245 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1246 RAISE FND_API.G_EXC_ERROR;
1247 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1249 END IF;
1250 END IF;
1251
1252 -- Debug Message
1253 IF (AMS_DEBUG_HIGH_ON) THEN
1254
1255 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1256 END IF;
1257
1258 -- Standard call to get message count and if count is 1, get message info.
1259 FND_MSG_PUB.Count_And_Get
1260 (p_count => x_msg_count,
1261 p_data => x_msg_data
1262 );
1263 EXCEPTION
1264
1265 WHEN AMS_Utility_PVT.resource_locked THEN
1266 x_return_status := FND_API.g_ret_sts_error;
1267 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1268
1269 WHEN FND_API.G_EXC_ERROR THEN
1270 ROLLBACK TO VALIDATE_Ams_Comp_Product_;
1271 x_return_status := FND_API.G_RET_STS_ERROR;
1272 -- Standard call to get message count and if count=1, get the message
1273 FND_MSG_PUB.Count_And_Get (
1274 p_encoded => FND_API.G_FALSE,
1275 p_count => x_msg_count,
1276 p_data => x_msg_data
1277 );
1278
1279 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1280 ROLLBACK TO VALIDATE_Ams_Comp_Product_;
1281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1282 -- Standard call to get message count and if count=1, get the message
1283 FND_MSG_PUB.Count_And_Get (
1284 p_encoded => FND_API.G_FALSE,
1285 p_count => x_msg_count,
1286 p_data => x_msg_data
1287 );
1288
1289 WHEN OTHERS THEN
1290 ROLLBACK TO VALIDATE_Ams_Comp_Product_;
1291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1292 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1293 THEN
1294 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1295 END IF;
1296 -- Standard call to get message count and if count=1, get the message
1297 FND_MSG_PUB.Count_And_Get (
1298 p_encoded => FND_API.G_FALSE,
1299 p_count => x_msg_count,
1300 p_data => x_msg_data
1301 );
1302 End Validate_comp_prod;
1303
1304 End Ams_Competitor_Product_Pvt;