eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.AMS_CNT_POINT_PVT

Source


1 PACKAGE BODY AMS_Cnt_Point_PVT as
2 /* $Header: amsvconb.pls 120.0 2005/05/31 22:01:31 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 --          AMS_Cnt_Point_PVT
7 -- Purpose
8 --
9 -- History
10 --     30-aug-2001    soagrawa    Modified names of all error messages, whose length was
11 --                                > 30 chars. Added those messages to seed.
12 --     31-oct-2001    soagrawa    Modified check_conact_point_fk_rec for bug# 2074740.
13 --     27-dec-2001    aranka      Modified check_conact_point_fk_rec, contact_point_value size from 30 to 256 for bug# 2163252
14 --     21-jan-2003    soagrawa    Fixed validation of inbounud outbound script - bug# 2757856
15 --     20-may-2005    musman      Added contact_point_value_id column for webadi collaboration script usage
16 
17 -- NOTE
18 --
19 -- End of Comments
20 -- ===============================================================
21 
22 
23 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Cnt_Point_PVT';
24 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvconb.pls';
25 
26 -- Hint: Primary key needs to be returned.
27 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
28 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
29 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
30 
31 PROCEDURE Create_Cnt_Point(
32     p_api_version_number         IN   NUMBER,
33     p_init_msg_list              IN   VARCHAR2     := FND_API.G_FALSE,
34     p_commit                     IN   VARCHAR2     := FND_API.G_FALSE,
35     p_validation_level           IN   NUMBER       := FND_API.G_VALID_LEVEL_FULL,
36 
37     x_return_status              OUT NOCOPY  VARCHAR2,
38     x_msg_count                  OUT NOCOPY  NUMBER,
39     x_msg_data                   OUT NOCOPY  VARCHAR2,
40 
41     p_cnt_point_rec               IN   cnt_point_rec_type  := g_miss_cnt_point_rec,
42     x_contact_point_id                   OUT NOCOPY  NUMBER
43      )
44 
45  IS
46 L_API_NAME                  CONSTANT VARCHAR2(30) := 'Create_Cnt_Point';
47 L_API_VERSION_NUMBER        CONSTANT NUMBER   := 1.0;
48    l_return_status_full        VARCHAR2(1);
49    l_object_version_number     NUMBER := 1;
50    l_org_id                    NUMBER := FND_API.G_MISS_NUM;
51    l_CONTACT_POINT_ID                  NUMBER;
52    l_dummy       NUMBER;
53 
54    CURSOR c_id IS
55       SELECT AMS_ACT_CONTACT_POINTS_s.NEXTVAL
56       FROM dual;
57 
58    CURSOR c_id_exists (l_id IN NUMBER) IS
59       SELECT 1 FROM dual
60       WHERE EXISTS (SELECT 1 FROM AMS_ACT_CONTACT_POINTS
61                     WHERE CONTACT_POINT_ID = l_id);
62 
63 BEGIN
64      IF (AMS_DEBUG_HIGH_ON) THEN
65 
66      AMS_UTILITY_PVT.debug_message('Inside Procedure Create_cnt_point');
67      END IF;
68         -- Standard Start of API savepoint
69       SAVEPOINT CREATE_Cnt_Point_PVT;
70         IF (AMS_DEBUG_HIGH_ON) THEN
71 
72         AMS_UTILITY_PVT.debug_message('Save point : CREATE_Cnt_Point_PVT created');
73         END IF;
74          IF (AMS_DEBUG_HIGH_ON) THEN
75 
76          AMS_UTILITY_PVT.debug_message('Save point : CREATE_Cnt_Point_PVT created');
77          END IF;
78 
79       -- Standard call to check for call compatibility.
80       IF NOT FND_API.Compatible_API_Call ( L_API_VERSION_NUMBER,
81                                            p_api_version_number,
82                                            l_api_name,
83                                            G_PKG_NAME)
84       THEN
85           RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
86       END IF;
87 
88       -- Initialize message list if p_init_msg_list is set to TRUE.
89       IF FND_API.to_Boolean( p_init_msg_list )
90       THEN
91          FND_MSG_PUB.initialize;
92       END IF;
93 
94       -- Debug Message
95       IF (AMS_DEBUG_HIGH_ON) THEN
96 
97       AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
98       END IF;
99       IF (AMS_DEBUG_HIGH_ON) THEN
100 
101       AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
102       END IF;
103 
104 
105       -- Initialize API return status to SUCCESS
106       x_return_status := FND_API.G_RET_STS_SUCCESS;
107 
108    -- Local variable initialization
109 
110    IF p_cnt_point_rec.CONTACT_POINT_ID IS NULL OR p_cnt_point_rec.CONTACT_POINT_ID = FND_API.g_miss_num THEN
111       LOOP
112          l_dummy := NULL;
113          OPEN c_id;
114          FETCH c_id INTO l_CONTACT_POINT_ID;
115          CLOSE c_id;
116 
117 
118          OPEN c_id_exists(l_CONTACT_POINT_ID);
119          FETCH c_id_exists INTO l_dummy;
120 
121          CLOSE c_id_exists;
122          EXIT WHEN l_dummy IS NULL;
123       END LOOP;
124    END IF;
125 
126       IF (AMS_DEBUG_HIGH_ON) THEN
127 
128 
129 
130       AMS_UTILITY_PVT.debug_message('Retreived pk');
131 
132       END IF;
133       -- =========================================================================
134       -- Validate Environment
135       -- =========================================================================
136 
137 
138       IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
139          THEN
140           -- Debug message
141           IF (AMS_DEBUG_HIGH_ON) THEN
142 
143           AMS_UTILITY_PVT.debug_message('Private API: Validate_Cnt_Point');
144           END IF;
145           IF (AMS_DEBUG_HIGH_ON) THEN
146 
147           AMS_UTILITY_PVT.debug_message('Private API: Validate_Cnt_Point');
148           END IF;
149           -- Invoke validation procedures
150 
151           Validate_cnt_point(
152             p_api_version_number     => 1.0,
153             p_init_msg_list    => FND_API.G_FALSE,
154             p_validation_level => p_validation_level,
155             p_validation_mode  => JTF_PLSQL_API.g_create,
156             p_cnt_point_rec    =>  p_cnt_point_rec,
157             x_return_status    => x_return_status,
158             x_msg_count        => x_msg_count,
159             x_msg_data         => x_msg_data);
160 
161         END IF;
162 
163       IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
164           RAISE FND_API.G_EXC_ERROR;
165       END IF;
166 
167 
168       -- Debug Message
169       IF (AMS_DEBUG_HIGH_ON) THEN
170 
171       AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
172       END IF;
173 
174       -- Invoke table handler(AMS_ACT_CONTACT_POINTS_PKG.Insert_Row)
175       AMS_ACT_CONTACT_POINTS_PKG.Insert_Row(
176           px_contact_point_id       => l_contact_point_id,
177           p_last_update_date        => SYSDATE,
178           p_last_updated_by         => FND_GLOBAL.user_id,
179           p_creation_date           => SYSDATE,
180           p_created_by              => FND_GLOBAL.user_id,
181           p_last_update_login       => FND_GLOBAL.conc_login_id,
182           px_object_version_number  => l_object_version_number,
183           p_arc_contact_used_by     => p_cnt_point_rec.arc_contact_used_by,
184           p_act_contact_used_by_id  => p_cnt_point_rec.act_contact_used_by_id,
185           p_contact_point_type      => p_cnt_point_rec.contact_point_type,
186           p_contact_point_value     => p_cnt_point_rec.contact_point_value,
187           p_city                    => p_cnt_point_rec.city,
188           p_country                 => p_cnt_point_rec.country,
189           p_zipcode                 => p_cnt_point_rec.zipcode,
190           p_attribute_category      => p_cnt_point_rec.attribute_category,
191           p_attribute1              => p_cnt_point_rec.attribute1,
192           p_attribute2              => p_cnt_point_rec.attribute2,
193           p_attribute3              => p_cnt_point_rec.attribute3,
194           p_attribute4              => p_cnt_point_rec.attribute4,
195           p_attribute5              => p_cnt_point_rec.attribute5,
196           p_attribute6              => p_cnt_point_rec.attribute6,
197           p_attribute7              => p_cnt_point_rec.attribute7,
198           p_attribute8              => p_cnt_point_rec.attribute8,
199           p_attribute9              => p_cnt_point_rec.attribute9,
200           p_attribute10             => p_cnt_point_rec.attribute10,
201           p_attribute11             => p_cnt_point_rec.attribute11,
202           p_attribute12             => p_cnt_point_rec.attribute12,
203           p_attribute13             => p_cnt_point_rec.attribute13,
204           p_attribute14             => p_cnt_point_rec.attribute14,
205           p_attribute15             => p_cnt_point_rec.attribute15,
206          p_contact_point_value_id => p_cnt_point_rec.contact_point_value_id
207           );
208       IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
209           RAISE FND_API.G_EXC_ERROR;
210       ELSE
211 	x_contact_point_id := l_contact_point_id;
212       END IF;
213 --
214 -- End of API body
215 --
216 
217       -- Standard check for p_commit
218       IF FND_API.to_Boolean( p_commit )
219       THEN
220          COMMIT WORK;
221       END IF;
222 
223 
224       -- Debug Message
225       IF (AMS_DEBUG_HIGH_ON) THEN
226 
227       AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
228       END IF;
229 
230       -- Standard call to get message count and if count is 1, get message info.
231       FND_MSG_PUB.Count_And_Get
232         (p_count          =>   x_msg_count,
233          p_data           =>   x_msg_data
234       );
235 EXCEPTION
236 
237    WHEN AMS_Utility_PVT.resource_locked THEN
238      x_return_status := FND_API.g_ret_sts_error;
239  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
240 
241    WHEN FND_API.G_EXC_ERROR THEN
242      ROLLBACK TO CREATE_Cnt_Point_PVT;
243      x_return_status := FND_API.G_RET_STS_ERROR;
244      -- Standard call to get message count and if count=1, get the message
245      FND_MSG_PUB.Count_And_Get (
246             p_encoded => FND_API.G_FALSE,
247             p_count   => x_msg_count,
248             p_data    => x_msg_data
249      );
250 
251    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
252      ROLLBACK TO CREATE_Cnt_Point_PVT;
253      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
254      -- Standard call to get message count and if count=1, get the message
255      FND_MSG_PUB.Count_And_Get (
256             p_encoded => FND_API.G_FALSE,
257             p_count => x_msg_count,
258             p_data  => x_msg_data
259      );
260 
261    WHEN OTHERS THEN
262      ROLLBACK TO CREATE_Cnt_Point_PVT;
263      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
264      IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
265      THEN
266         FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
267      END IF;
268      -- Standard call to get message count and if count=1, get the message
269      FND_MSG_PUB.Count_And_Get (
270             p_encoded => FND_API.G_FALSE,
271             p_count => x_msg_count,
272             p_data  => x_msg_data
273      );
274 End Create_Cnt_Point;
275 
276 
277 PROCEDURE Update_Cnt_Point(
278     p_api_version_number         IN   NUMBER,
279     p_init_msg_list              IN   VARCHAR2     := FND_API.G_FALSE,
280     p_commit                     IN   VARCHAR2     := FND_API.G_FALSE,
281     p_validation_level           IN  NUMBER       := FND_API.G_VALID_LEVEL_FULL,
282 
283 
284     x_return_status              OUT NOCOPY  VARCHAR2,
285     x_msg_count                  OUT NOCOPY  NUMBER,
286     x_msg_data                   OUT NOCOPY  VARCHAR2,
287 
288     p_cnt_point_rec               IN    cnt_point_rec_type,
289     x_object_version_number      OUT NOCOPY  NUMBER
290     )
291 
292  IS
293 
294 CURSOR c_get_cnt_point(contact_point_id NUMBER) IS
295     SELECT *
296     FROM  AMS_ACT_CONTACT_POINTS
297     WHERE contact_point_id = p_cnt_point_rec.contact_point_id;
298     -- Hint: Developer need to provide Where clause
299 
300 L_API_NAME                  CONSTANT VARCHAR2(30) := 'Update_Cnt_Point';
301 L_API_VERSION_NUMBER        CONSTANT NUMBER   := 1.0;
302 -- Local Variables
303 l_object_version_number     NUMBER;
304 l_CONTACT_POINT_ID    NUMBER;
305 l_ref_cnt_point_rec  c_get_Cnt_Point%ROWTYPE ;
306 l_tar_cnt_point_rec  AMS_Cnt_Point_PVT.cnt_point_rec_type := P_cnt_point_rec;
307 l_rowid  ROWID;
308 
309  BEGIN
310       IF (AMS_DEBUG_HIGH_ON) THEN
311 
312       AMS_UTILITY_PVT.debug_message('Inside Update_cnt_point');
313       END IF;
314       -- Standard Start of API savepoint
315       SAVEPOINT UPDATE_Cnt_Point_PVT;
316 
317       -- Standard call to check for call compatibility.
318       IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
319                                            p_api_version_number,
320                                            l_api_name,
321                                            G_PKG_NAME)
322       THEN
323           RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
324       END IF;
325 
326       -- Initialize message list if p_init_msg_list is set to TRUE.
327       IF FND_API.to_Boolean( p_init_msg_list )
328       THEN
329          FND_MSG_PUB.initialize;
330       END IF;
331 
332       -- Debug Message
333       IF (AMS_DEBUG_HIGH_ON) THEN
334 
335       AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
336       END IF;
337 
338 
339       -- Initialize API return status to SUCCESS
340       x_return_status := FND_API.G_RET_STS_SUCCESS;
341 
342       -- Debug Message
343       IF (AMS_DEBUG_HIGH_ON) THEN
344 
345       AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
346       END IF;
347 
348 
349       OPEN c_get_Cnt_Point( l_tar_cnt_point_rec.contact_point_id);
350 
351       FETCH c_get_Cnt_Point INTO l_ref_cnt_point_rec  ;
352 
353        If ( c_get_Cnt_Point%NOTFOUND) THEN
354   AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
355    p_token_name   => 'INFO',
356  p_token_value  => 'Cnt_Point') ;
357            RAISE FND_API.G_EXC_ERROR;
358        END IF;
359        -- Debug Message
360        IF (AMS_DEBUG_HIGH_ON) THEN
361 
362        AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
363        END IF;
364        CLOSE     c_get_Cnt_Point;
365 
366 
367 
368       If (l_tar_cnt_point_rec.object_version_number is NULL or
369           l_tar_cnt_point_rec.object_version_number = FND_API.G_MISS_NUM ) Then
370   AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
371    p_token_name   => 'COLUMN',
372  p_token_value  => 'Last_Update_Date') ;
373           raise FND_API.G_EXC_ERROR;
374       End if;
375       -- Check Whether record has been changed by someone else
376       If (l_tar_cnt_point_rec.object_version_number <> l_ref_cnt_point_rec.object_version_number) Then
377   AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
378    p_token_name   => 'INFO',
379  p_token_value  => 'Cnt_Point') ;
380           raise FND_API.G_EXC_ERROR;
381       End if;
382       IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
383       THEN
384           -- Debug message
385           IF (AMS_DEBUG_HIGH_ON) THEN
386 
387           AMS_UTILITY_PVT.debug_message('Private API: Validate_Cnt_Point  '|| x_return_status);
388           END IF;
389 
390           -- Invoke validation procedures
391           Validate_cnt_point(
392             p_api_version_number     => 1.0,
393             p_init_msg_list    => FND_API.G_FALSE,
394             p_validation_level => p_validation_level,
395             p_validation_mode  => JTF_PLSQL_API.g_update,
396             p_cnt_point_rec    =>  p_cnt_point_rec,
397             x_return_status    => x_return_status,
398             x_msg_count        => x_msg_count,
399             x_msg_data         => x_msg_data);
400       END IF;
401 
402       IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
403           RAISE FND_API.G_EXC_ERROR;
404       END IF;
405 
406 
407       -- Debug Message
408       IF (AMS_DEBUG_HIGH_ON) THEN
409 
410       AMS_UTILITY_PVT.debug_message( 'Private API: Calling update table handler');
411       END IF;
412 
413       -- Invoke table handler(AMS_ACT_CONTACT_POINTS_PKG.Update_Row)
414       AMS_ACT_CONTACT_POINTS_PKG.Update_Row(
415           p_contact_point_id  => p_cnt_point_rec.contact_point_id,
416           p_last_update_date  => SYSDATE,
417           p_last_updated_by  => FND_GLOBAL.user_id,
418           p_last_update_login  => FND_GLOBAL.conc_login_id,
419           p_object_version_number  => p_cnt_point_rec.object_version_number,
420           p_arc_contact_used_by  => p_cnt_point_rec.arc_contact_used_by,
421           p_act_contact_used_by_id  => p_cnt_point_rec.act_contact_used_by_id,
422           p_contact_point_type  => p_cnt_point_rec.contact_point_type,
423           p_contact_point_value  => p_cnt_point_rec.contact_point_value,
424           p_city  => p_cnt_point_rec.city,
425           p_country  => p_cnt_point_rec.country,
426           p_zipcode  => p_cnt_point_rec.zipcode,
427           p_attribute_category  => p_cnt_point_rec.attribute_category,
428           p_attribute1  => p_cnt_point_rec.attribute1,
429           p_attribute2  => p_cnt_point_rec.attribute2,
430           p_attribute3  => p_cnt_point_rec.attribute3,
431           p_attribute4  => p_cnt_point_rec.attribute4,
432           p_attribute5  => p_cnt_point_rec.attribute5,
433           p_attribute6  => p_cnt_point_rec.attribute6,
434           p_attribute7  => p_cnt_point_rec.attribute7,
435           p_attribute8  => p_cnt_point_rec.attribute8,
436           p_attribute9  => p_cnt_point_rec.attribute9,
437           p_attribute10  => p_cnt_point_rec.attribute10,
438           p_attribute11  => p_cnt_point_rec.attribute11,
439           p_attribute12  => p_cnt_point_rec.attribute12,
440           p_attribute13  => p_cnt_point_rec.attribute13,
441           p_attribute14  => p_cnt_point_rec.attribute14,
442           p_attribute15  => p_cnt_point_rec.attribute15
443          ,p_contact_point_value_id => p_cnt_point_rec.contact_point_value_id
444           );
445       --
446       -- End of API body.
447       --
448 
449       -- Standard check for p_commit
450       IF FND_API.to_Boolean( p_commit )
451       THEN
452          COMMIT WORK;
453       END IF;
454 
455 
456       -- Debug Message
457       IF (AMS_DEBUG_HIGH_ON) THEN
458 
459       AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
460       END IF;
461 
462       -- Standard call to get message count and if count is 1, get message info.
463       FND_MSG_PUB.Count_And_Get
464         (p_count          =>   x_msg_count,
465          p_data           =>   x_msg_data
466       );
467 EXCEPTION
468 
469    WHEN AMS_Utility_PVT.resource_locked THEN
470      x_return_status := FND_API.g_ret_sts_error;
471  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
472 
473    WHEN FND_API.G_EXC_ERROR THEN
474      ROLLBACK TO UPDATE_Cnt_Point_PVT;
475      x_return_status := FND_API.G_RET_STS_ERROR;
476      -- Standard call to get message count and if count=1, get the message
477      FND_MSG_PUB.Count_And_Get (
478             p_encoded => FND_API.G_FALSE,
479             p_count   => x_msg_count,
480             p_data    => x_msg_data
481      );
482 
483    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
484      ROLLBACK TO UPDATE_Cnt_Point_PVT;
485      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
486      -- Standard call to get message count and if count=1, get the message
487      FND_MSG_PUB.Count_And_Get (
488             p_encoded => FND_API.G_FALSE,
489             p_count => x_msg_count,
490             p_data  => x_msg_data
491      );
492 
493    WHEN OTHERS THEN
494      ROLLBACK TO UPDATE_Cnt_Point_PVT;
495      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
496      IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
497      THEN
498         FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
499      END IF;
500      -- Standard call to get message count and if count=1, get the message
501      FND_MSG_PUB.Count_And_Get (
502             p_encoded => FND_API.G_FALSE,
503             p_count => x_msg_count,
504             p_data  => x_msg_data
505      );
506 End Update_Cnt_Point;
507 
508 
509 PROCEDURE Delete_Cnt_Point(
510     p_api_version_number         IN   NUMBER,
511     p_init_msg_list              IN   VARCHAR2     := FND_API.G_FALSE,
512     p_commit                     IN   VARCHAR2     := FND_API.G_FALSE,
513     p_validation_level           IN   NUMBER       := FND_API.G_VALID_LEVEL_FULL,
514     x_return_status              OUT NOCOPY  VARCHAR2,
515     x_msg_count                  OUT NOCOPY  NUMBER,
516     x_msg_data                   OUT NOCOPY  VARCHAR2,
517     p_contact_point_id                   IN  NUMBER,
518     p_object_version_number      IN   NUMBER
519     )
520 
521  IS
522 
523  CURSOR c_Cnt_Point IS
524    SELECT CONTACT_POINT_ID
525    FROM AMS_ACT_CONTACT_POINTS
526    WHERE CONTACT_POINT_ID = p_CONTACT_POINT_ID
527    AND object_version_number = p_object_version_number ;
528 
529 L_API_NAME                  CONSTANT VARCHAR2(30) := 'Delete_Cnt_Point';
530 L_API_VERSION_NUMBER        CONSTANT NUMBER   := 1.0;
531 l_object_version_number     NUMBER;
532 l_contact_point_id          NUMBER;
533 
534  BEGIN
535       IF (AMS_DEBUG_HIGH_ON) THEN
536 
537       AMS_UTILITY_PVT.debug_message('Inside Delete_cnt_point');
538       END IF;
539       -- Standard Start of API savepoint
540       SAVEPOINT DELETE_Cnt_Point_PVT;
541 
542       -- Standard call to check for call compatibility.
543       IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
544                                            p_api_version_number,
545                                            l_api_name,
546                                            G_PKG_NAME)
547       THEN
548           RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
549       END IF;
550 
551       -- Initialize message list if p_init_msg_list is set to TRUE.
552       IF FND_API.to_Boolean( p_init_msg_list )
553       THEN
554          FND_MSG_PUB.initialize;
555       END IF;
556 
557       -- Debug Message
558       IF (AMS_DEBUG_HIGH_ON) THEN
559 
560       AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
561       END IF;
562 
563        OPEN c_Cnt_Point;
564 
565       FETCH c_Cnt_Point INTO l_CONTACT_POINT_ID ;
566 
567       IF (c_Cnt_Point%NOTFOUND) THEN
568       CLOSE c_Cnt_Point;
569       AMS_Utility_PVT.Error_Message(p_message_name =>  'AMS_API_RECORD_NOT_FOUND');
570       RAISE FND_API.g_exc_error;
571      END IF;
572 
573      CLOSE c_Cnt_Point;
574 
575 
576       -- Initialize API return status to SUCCESS
577       x_return_status := FND_API.G_RET_STS_SUCCESS;
578 
579       --
580       -- Api body
581       --
582       -- Debug Message
583       IF (AMS_DEBUG_HIGH_ON) THEN
584 
585       AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
586       END IF;
587 
588       -- Invoke table handler(AMS_ACT_CONTACT_POINTS_PKG.Delete_Row)
589       AMS_ACT_CONTACT_POINTS_PKG.Delete_Row(
590           p_CONTACT_POINT_ID  => p_CONTACT_POINT_ID);
591       --
592       -- End of API body
593       --
594 
595       -- Standard check for p_commit
596       IF FND_API.to_Boolean( p_commit )
597       THEN
598          COMMIT WORK;
599       END IF;
600 
601 
602 
603 
604       -- Debug Message
605       IF (AMS_DEBUG_HIGH_ON) THEN
606 
607       AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
608       END IF;
609 
610       -- Standard call to get message count and if count is 1, get message info.
611       FND_MSG_PUB.Count_And_Get
612         (p_count          =>   x_msg_count,
613          p_data           =>   x_msg_data
614       );
615 EXCEPTION
616 
617    WHEN AMS_Utility_PVT.resource_locked THEN
618      x_return_status := FND_API.g_ret_sts_error;
619  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
620 
621    WHEN FND_API.G_EXC_ERROR THEN
622      ROLLBACK TO DELETE_Cnt_Point_PVT;
623      x_return_status := FND_API.G_RET_STS_ERROR;
624      -- Standard call to get message count and if count=1, get the message
625      FND_MSG_PUB.Count_And_Get (
626             p_encoded => FND_API.G_FALSE,
627             p_count   => x_msg_count,
628             p_data    => x_msg_data
629      );
630 
631    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
632      ROLLBACK TO DELETE_Cnt_Point_PVT;
633      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
634      -- Standard call to get message count and if count=1, get the message
635      FND_MSG_PUB.Count_And_Get (
636             p_encoded => FND_API.G_FALSE,
637             p_count => x_msg_count,
638             p_data  => x_msg_data
639      );
640 
641    WHEN OTHERS THEN
642      ROLLBACK TO DELETE_Cnt_Point_PVT;
643      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644      IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
645      THEN
646         FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
647      END IF;
648      -- Standard call to get message count and if count=1, get the message
649      FND_MSG_PUB.Count_And_Get (
650             p_encoded => FND_API.G_FALSE,
651             p_count => x_msg_count,
652             p_data  => x_msg_data
653      );
654 End Delete_Cnt_Point;
655 
656 
657 
658 -- Hint: Primary key needs to be returned.
659 PROCEDURE Lock_Cnt_Point(
660     p_api_version_number         IN   NUMBER,
661     p_init_msg_list              IN   VARCHAR2     := FND_API.G_FALSE,
662 
663     x_return_status              OUT NOCOPY  VARCHAR2,
664     x_msg_count                  OUT NOCOPY  NUMBER,
665     x_msg_data                   OUT NOCOPY  VARCHAR2,
666 
667     p_contact_point_id                   IN  NUMBER,
668     p_object_version             IN  NUMBER
669     )
670 
671  IS
672 L_API_NAME                  CONSTANT VARCHAR2(30) := 'Lock_Cnt_Point';
673 L_API_VERSION_NUMBER        CONSTANT NUMBER   := 1.0;
674 L_FULL_NAME                 CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
675 l_CONTACT_POINT_ID                  NUMBER;
676 
677 CURSOR c_Cnt_Point IS
678    SELECT CONTACT_POINT_ID
679    FROM AMS_ACT_CONTACT_POINTS
680    WHERE CONTACT_POINT_ID = p_CONTACT_POINT_ID
681    AND object_version_number = p_object_version
682    FOR UPDATE NOWAIT;
683 
684 BEGIN
685 
686       IF (AMS_DEBUG_HIGH_ON) THEN
687 
688 
689 
690       AMS_UTILITY_PVT.debug_message('Inside LockCnt_point');
691 
692       END IF;
693       -- Debug Message
694       IF (AMS_DEBUG_HIGH_ON) THEN
695 
696       AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
697       END IF;
698 
699       -- Initialize message list if p_init_msg_list is set to TRUE.
700       IF FND_API.to_Boolean( p_init_msg_list )
701       THEN
702          FND_MSG_PUB.initialize;
703       END IF;
704 
705       -- Standard call to check for call compatibility.
706       IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
707                                            p_api_version_number,
708                                            l_api_name,
709                                            G_PKG_NAME)
710       THEN
711           RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
712       END IF;
713 
714 
715       -- Initialize API return status to SUCCESS
716       x_return_status := FND_API.G_RET_STS_SUCCESS;
717 
718 
719 ------------------------ lock -------------------------
720 
721   IF (AMS_DEBUG_HIGH_ON) THEN
722 
723 
724 
725   AMS_Utility_PVT.debug_message(l_full_name||': start');
726 
727   END IF;
728   OPEN c_Cnt_Point;
729 
730   FETCH c_Cnt_Point INTO l_CONTACT_POINT_ID;
731 
732   IF (c_Cnt_Point%NOTFOUND) THEN
733     CLOSE c_Cnt_Point;
734     IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
735        FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
736        FND_MSG_PUB.add;
737     END IF;
738     RAISE FND_API.g_exc_error;
739   END IF;
740 
741   CLOSE c_Cnt_Point;
742 
743  -------------------- finish --------------------------
744   FND_MSG_PUB.count_and_get(
745     p_encoded => FND_API.g_false,
746     p_count   => x_msg_count,
747     p_data    => x_msg_data);
748   IF (AMS_DEBUG_HIGH_ON) THEN
749 
750   AMS_Utility_PVT.debug_message(l_full_name ||': end');
751   END IF;
752 EXCEPTION
753 
754    WHEN AMS_Utility_PVT.resource_locked THEN
755      x_return_status := FND_API.g_ret_sts_error;
756  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
757 
758    WHEN FND_API.G_EXC_ERROR THEN
759      ROLLBACK TO LOCK_Cnt_Point_PVT;
760      x_return_status := FND_API.G_RET_STS_ERROR;
761      -- Standard call to get message count and if count=1, get the message
762      FND_MSG_PUB.Count_And_Get (
763             p_encoded => FND_API.G_FALSE,
764             p_count   => x_msg_count,
765             p_data    => x_msg_data
766      );
767 
768    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
769      ROLLBACK TO LOCK_Cnt_Point_PVT;
770      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
771      -- Standard call to get message count and if count=1, get the message
772      FND_MSG_PUB.Count_And_Get (
773             p_encoded => FND_API.G_FALSE,
774             p_count => x_msg_count,
775             p_data  => x_msg_data
776      );
777 
778    WHEN OTHERS THEN
779      ROLLBACK TO LOCK_Cnt_Point_PVT;
780      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
781      IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
782      THEN
783         FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
784      END IF;
785      -- Standard call to get message count and if count=1, get the message
786      FND_MSG_PUB.Count_And_Get (
787             p_encoded => FND_API.G_FALSE,
788             p_count => x_msg_count,
789             p_data  => x_msg_data
790      );
791 End Lock_Cnt_Point;
792 
793 
794 PROCEDURE check_cnt_point_uk_items(
795     p_cnt_point_rec               IN   cnt_point_rec_type,
796     p_validation_mode            IN  VARCHAR2 := JTF_PLSQL_API.g_create,
797     x_return_status              OUT NOCOPY VARCHAR2)
798 IS
799 l_valid_flag  VARCHAR2(1);
800 
801 BEGIN
802       x_return_status := FND_API.g_ret_sts_success;
803       IF (AMS_DEBUG_HIGH_ON) THEN
804 
805       AMS_UTILITY_PVT.debug_message('Inside check_uk_items');
806       END IF;
807       IF (AMS_DEBUG_HIGH_ON) THEN
808 
809       AMS_UTILITY_PVT.debug_message(p_cnt_point_rec.arc_contact_used_by);
810       END IF;
811       IF p_validation_mode = JTF_PLSQL_API.g_create THEN
812          l_valid_flag := AMS_Utility_PVT.check_uniqueness(
813          'AMS_ACT_CONTACT_POINTS',
814          'CONTACT_POINT_ID = ' || p_cnt_point_rec.CONTACT_POINT_ID
815          );
816         IF l_valid_flag = FND_API.g_false THEN
817           AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CONTACT_POINT_ID_DUPLICATE');
818           x_return_status := FND_API.g_ret_sts_error;
819          RETURN;
820       END IF;
821          -- code added by me
822          /*l_valid_flag := AMS_Utility_PVT.check_uniqueness(
823                                                          'AMS_ACT_CONTACT_POINTS'
824                                                          , '''ARC_CONTACT_USED_BY = ''' || p_cnt_point_rec.ARC_CONTACT_USED_BY  ||
825                                                           ''' AND ACT_CONTACT_USED_BY_ID  = ' || p_cnt_point_rec.ACT_CONTACT_USED_BY_ID ||
826                                                           ''' AND CONTACT_POINT_TYPE = ''' || p_cnt_point_rec.CONTACT_POINT_TYPE ||
827                                                           ''' AND CONTACT_POINT_VALUE  = ''' || p_cnt_point_rec.CONTACT_POINT_VALUE
828                                                           ''' AND CONTACT_POINT_ID  <> ''' || p_cnt_point_rec.CONTACT_POINT_TYPE
829                                                          );*/
830 
831 
832          -- end code added by me
833 
834 
835       /*ELSE
836          l_valid_flag := AMS_Utility_PVT.check_uniqueness(
837          'AMS_ACT_CONTACT_POINTS',
838          'CONTACT_POINT_ID =  ' || p_cnt_point_rec.CONTACT_POINT_ID
839          );
840 
841 
842         -- ||
843          --''' AND CONTACT_POINT_ID <> ' || p_cnt_point_rec.CONTACT_POINT_ID
844          --);
845           IF l_valid_flag = FND_API.g_false THEN
846           AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CONTACT_POINT_ID_DUPLICATE');
847           x_return_status := FND_API.g_ret_sts_error;
848          RETURN;
849       END IF;*/
850 
851 
852       END IF;
853        IF (AMS_DEBUG_HIGH_ON) THEN
854 
855        AMS_UTILITY_PVT.debug_message(p_cnt_point_rec.arc_contact_used_by);
856        END IF;
857 
858 END check_cnt_point_uk_items;
859 
860 
861 /**
862   --  PURPOSE
863             Checks whether the required fields are not null
864   -- HISTORY
865             rssharma
866             01/29/2001  Removed check from all the who fields
867             and object version Number as these values are
868             supplied by Table API in case they are missing
869   */
870 
871 
872 PROCEDURE check_cnt_point_req_items(
873     p_cnt_point_rec               IN  cnt_point_rec_type,
874     p_validation_mode             IN VARCHAR2 := JTF_PLSQL_API.g_create,
875     x_return_status            OUT NOCOPY VARCHAR2
876 )
877 IS
878 BEGIN
879 
880   IF (AMS_DEBUG_HIGH_ON) THEN
881 
882 
883 
884   AMS_UTILITY_PVT.debug_message('Inside check_cnt_point_req_items');
885 
886   END IF;
887 
888    x_return_status := FND_API.g_ret_sts_success;
889 
890    IF p_validation_mode = JTF_PLSQL_API.g_create THEN
891 
892    /*
893       IF p_cnt_point_rec.contact_point_id = FND_API.g_miss_num OR p_cnt_point_rec.contact_point_id IS NULL THEN
894         AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CNT_POINT_NO_ID');
895          x_return_status := FND_API.g_ret_sts_error;
896 
897          RETURN;
898       END IF;
899    */
900 
901       /*IF p_cnt_point_rec.last_update_date = FND_API.g_miss_date OR p_cnt_point_rec.last_update_date IS NULL THEN
902  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_cnt_point_NO_last_update_date');
903          x_return_status := FND_API.g_ret_sts_error;
904          RETURN;
905       END IF;
906 
907 
908       IF p_cnt_point_rec.last_updated_by = FND_API.g_miss_num OR p_cnt_point_rec.last_updated_by IS NULL THEN
909  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_cnt_point_NO_last_updated_by');
910          x_return_status := FND_API.g_ret_sts_error;
911          RETURN;
912       END IF;
913 
914 
915       IF p_cnt_point_rec.creation_date = FND_API.g_miss_date OR p_cnt_point_rec.creation_date IS NULL THEN
916  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_cnt_point_NO_creation_date');
917          x_return_status := FND_API.g_ret_sts_error;
918          RETURN;
919       END IF;
920 
921 
922       IF p_cnt_point_rec.created_by = FND_API.g_miss_num OR p_cnt_point_rec.created_by IS NULL THEN
923  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_cnt_point_NO_created_by');
924          x_return_status := FND_API.g_ret_sts_error;
925          RETURN;
926       END IF;
927 
928 
929       IF p_cnt_point_rec.object_version_number = FND_API.g_miss_num OR p_cnt_point_rec.object_version_number IS NULL THEN
930  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_cnt_point_NO_object_version_number');
931          x_return_status := FND_API.g_ret_sts_error;
932          RETURN;
933       END IF;  */
934 
935 
936       IF p_cnt_point_rec.arc_contact_used_by = FND_API.g_miss_char OR p_cnt_point_rec.arc_contact_used_by IS NULL THEN
937  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CNT_POINT_NO_USED_BY');
938          x_return_status := FND_API.g_ret_sts_error;
939          RETURN;
940       END IF;
941 
942 
943       IF p_cnt_point_rec.act_contact_used_by_id = FND_API.g_miss_num OR p_cnt_point_rec.act_contact_used_by_id IS NULL THEN
944  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CNT_POINT_NO_USED_BY_ID');
945          x_return_status := FND_API.g_ret_sts_error;
946          RETURN;
947       END IF;
948 
949 
950       IF p_cnt_point_rec.contact_point_type = FND_API.g_miss_char OR p_cnt_point_rec.contact_point_type IS NULL THEN
951  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CNT_POINT_NO_TYPE');
952          x_return_status := FND_API.g_ret_sts_error;
953          RETURN;
954       END IF;
955 
956 
957       IF p_cnt_point_rec.contact_point_value = FND_API.g_miss_char OR p_cnt_point_rec.contact_point_value IS NULL THEN
958  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CNT_POINT_NO_VALUE');
959          x_return_status := FND_API.g_ret_sts_error;
960          RETURN;
961       END IF;
962    ELSE
963 
964 
965       IF p_cnt_point_rec.contact_point_id IS NULL THEN
966  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CNT_POINT_NO_ID');
967          x_return_status := FND_API.g_ret_sts_error;
968          RETURN;
969       END IF;
970 
971 
972       /*IF p_cnt_point_rec.last_update_date IS NULL THEN
973  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_cnt_point_NO_last_update_date');
974          x_return_status := FND_API.g_ret_sts_error;
975          RETURN;
976       END IF;
977 
978 
979       IF p_cnt_point_rec.last_updated_by IS NULL THEN
980  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_cnt_point_NO_last_updated_by');
981          x_return_status := FND_API.g_ret_sts_error;
982          RETURN;
983       END IF;
984 
985 
986       IF p_cnt_point_rec.creation_date IS NULL THEN
987  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_cnt_point_NO_creation_date');
988          x_return_status := FND_API.g_ret_sts_error;
989          RETURN;
990       END IF;
991 
992 
993       IF p_cnt_point_rec.created_by IS NULL THEN
994  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_cnt_point_NO_created_by');
995          x_return_status := FND_API.g_ret_sts_error;
996          RETURN;
997       END IF;
998 
999 
1000       IF p_cnt_point_rec.object_version_number IS NULL THEN
1001  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_cnt_point_NO_object_version_number');
1002          x_return_status := FND_API.g_ret_sts_error;
1003          RETURN;
1004       END IF;  */
1005 
1006 
1007       IF p_cnt_point_rec.arc_contact_used_by IS NULL THEN
1008  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CNT_POINT_NO_USED_BY');
1009          x_return_status := FND_API.g_ret_sts_error;
1010          RETURN;
1011       END IF;
1012 
1013 
1014       IF p_cnt_point_rec.act_contact_used_by_id IS NULL THEN
1015  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CNT_POINT_NO_USED_BY_ID');
1016          x_return_status := FND_API.g_ret_sts_error;
1017          RETURN;
1018       END IF;
1019 
1020 
1021       IF p_cnt_point_rec.contact_point_type IS NULL THEN
1022  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CNT_POINT_NO_TYPE');
1023          x_return_status := FND_API.g_ret_sts_error;
1024          RETURN;
1025       END IF;
1026 
1027 
1028       IF p_cnt_point_rec.contact_point_value IS NULL THEN
1029  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CNT_POINT_NO_VALUE');
1030          x_return_status := FND_API.g_ret_sts_error;
1031          RETURN;
1032       END IF;
1033    END IF;
1034 
1035 IF (AMS_DEBUG_HIGH_ON) THEN
1036 
1037 
1038 
1039 AMS_UTILITY_PVT.debug_message(' Exiting check_cnt_point_req_items');
1040 
1041 END IF;
1042 
1043 END check_cnt_point_req_items;
1044 
1045 PROCEDURE check_country_exists(
1046     p_cnt_point_rec   IN  cnt_point_rec_type
1047     , x_return_status  OUT NOCOPY VARCHAR2
1048     )
1049     IS
1050     l_table_name VARCHAR2(30):= 'jtf_loc_hierarchies_vl';
1051     l_pk_name VARCHAR2(30) := 'location_hierarchy_id';
1052     l_pk_value NUMBER := p_cnt_point_rec.country;
1053     l_pk_data_type NUMBER := AMS_Utility_PVT.g_number;
1054 
1055 
1056     BEGIN
1057      IF (AMS_DEBUG_HIGH_ON) THEN
1058 
1059      AMS_UTILITY_PVT.debug_message('Inside   check_country_exists');
1060      END IF;
1061       -- initialise the return status to success
1062       x_return_status := FND_API.g_ret_sts_success ;
1063 
1064       -- if country is null or missing then perform the check
1065       IF p_cnt_point_rec.country <> FND_API.g_miss_num
1066        AND p_cnt_point_rec.country IS NOT NULL  THEN
1067       IF
1068        AMS_Utility_PVT.check_fk_exists(
1069                                        p_table_name  => l_table_name
1070                                        , p_pk_name   => l_pk_name
1071                                        , p_pk_value  => l_pk_value
1072                                        , p_pk_data_type => l_pk_data_type
1073                                        ,  p_additional_where_clause => NULL
1074                                        ) = FND_API.g_false
1075          THEN
1076          AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CNT_POINT_BAD_COUNTRY');
1077          x_return_status := FND_API.g_ret_sts_error;
1078          RETURN ;
1079        END IF;
1080        END IF;
1081 
1082     END check_country_exists;
1083 
1084 PROCEDURE check_cnt_point_FK_items(
1085     p_cnt_point_rec IN cnt_point_rec_type,
1086     x_return_status OUT NOCOPY VARCHAR2
1087 )
1088 IS
1089 BEGIN
1090 --
1091 -- Initialize the OUT parameter
1092 --
1093   x_return_status := FND_API.g_ret_sts_success ;
1094 -- code added by me
1095 -- Check arc_contact_used_by
1096    IF (AMS_DEBUG_HIGH_ON) THEN
1097 
1098    AMS_UTILITY_PVT.debug_message('Inside check_cnt_point_fk_items');
1099    END IF;
1100 
1101    IF p_cnt_point_rec.arc_contact_used_by <> FND_API.G_MISS_CHAR THEN
1102         IF p_cnt_point_rec.arc_contact_used_by <> 'CAMP' AND
1103            p_cnt_point_rec.arc_contact_used_by <> 'CSCH' AND
1104                  p_cnt_point_rec.arc_contact_used_by <> 'EVEH' AND
1105                  p_cnt_point_rec.arc_contact_used_by <> 'EVEO'
1106         THEN
1107       -- invalid item
1108            AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CONTACT_INVALID_USED_BY'); -- TO CHECK THE ERROR MESSAGE TO BE RAISED*/
1109          /*IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1110          THEN -- MMSG
1111 --             IF (AMS_DEBUG_HIGH_ON) THEN                          AMS_UTILITY_PVT.debug_message('Foreign Key Does not Exist');             END IF;
1112             FND_MESSAGE.Set_Name('AMS', 'AMS_TRIG_INVALID_CREATED_FOR');
1113                 FND_MSG_PUB.Add;
1114         END IF;*/
1115 
1116 
1117          x_return_status := FND_API.G_RET_STS_ERROR;
1118         -- If any errors happen abort API/Procedure.
1119         RETURN;
1120       END IF;
1121    END IF;
1122 
1123    IF p_cnt_point_rec.country <> FND_API.G_MISS_NUM AND  p_cnt_point_rec.country IS NOT NULL  THEN
1124    check_country_exists(
1125                         p_cnt_point_rec  => p_cnt_point_rec
1126                         , x_return_status => x_return_status
1127                         );
1128 
1129       IF x_return_status <>  FND_API.g_ret_sts_success THEN
1130         AMS_Utility_PVT.Error_Message(p_message_name => 'API_CONTACT_INVALID_COUNTRY');
1131       RETURN;
1132       END IF;
1133     END IF;
1134 
1135 
1136    -- end code added by me
1137 
1138    -- Enter custom code here
1139 
1140 
1141 
1142 END check_cnt_point_FK_items;
1143 
1144 PROCEDURE check_cnt_point_Lookup_items(
1145     p_cnt_point_rec IN cnt_point_rec_type,
1146     x_return_status OUT NOCOPY VARCHAR2
1147 )
1148 IS
1149 BEGIN
1150    IF (AMS_DEBUG_HIGH_ON) THEN
1151 
1152    AMS_UTILITY_PVT.debug_message('Inside Ckeck lookups');
1153    END IF;
1154     IF (AMS_DEBUG_HIGH_ON) THEN
1155 
1156     AMS_UTILITY_PVT.debug_message(p_cnt_point_rec.arc_contact_used_by);
1157     END IF;
1158    x_return_status := FND_API.g_ret_sts_success;
1159 -- code added by me
1160  IF p_cnt_point_rec.CONTACT_POINT_TYPE <> FND_API.G_MISS_CHAR
1161   THEN
1162        IF (AMS_DEBUG_HIGH_ON) THEN
1163 
1164        AMS_UTILITY_PVT.debug_message(' LookUp type   lookup code  = '''|| p_cnt_point_rec.contact_point_value);
1165        END IF;
1166    IF AMS_Utility_PVT.Check_Lookup_Exists
1167       ( p_lookup_table_name   => 'AMS_LOOKUPS'
1168              ,p_lookup_type         => 'AMS_CONTACT_POINT_TYPE'
1169         ,p_lookup_code         => p_cnt_point_rec.contact_point_type
1170         ) = FND_API.G_FALSE then
1171 
1172       -- invalid item
1173       /*   IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1174         THEN -- MMSG
1175 --            IF (AMS_DEBUG_HIGH_ON) THEN                        AMS_UTILITY_PVT.debug_message('Triggering Type is invalid');            END IF;
1176 
1177           FND_MSG_PUB.Add;
1178         END IF;*/
1179         AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CONTACT_INVALID_TYPE');
1180       x_return_status := FND_API.G_RET_STS_ERROR;
1181       -- If any errors happen abort API/Procedure.
1182       RETURN;
1183    END IF;
1184   END IF;
1185   -- end code added by me
1186    -- Enter custom code here
1187        IF (AMS_DEBUG_HIGH_ON) THEN
1188 
1189        AMS_UTILITY_PVT.debug_message(p_cnt_point_rec.arc_contact_used_by);
1190        END IF;
1191 END check_cnt_point_Lookup_items;
1192 
1193 PROCEDURE Check_cnt_point_Items (
1194     P_cnt_point_rec     IN    cnt_point_rec_type,
1195     p_validation_mode  IN    VARCHAR2,
1196     x_return_status    OUT NOCOPY   VARCHAR2
1197     )
1198 IS
1199 BEGIN
1200 
1201    -- Check Items Uniqueness API calls
1202    IF (AMS_DEBUG_HIGH_ON) THEN
1203 
1204    AMS_UTILITY_PVT.debug_message('Inside Check_cnt_point_items');
1205    END IF;
1206   IF (AMS_DEBUG_HIGH_ON) THEN
1207 
1208   AMS_UTILITY_PVT.debug_message('Before Calling uk Items');
1209   END IF;
1210    IF (AMS_DEBUG_HIGH_ON) THEN
1211 
1212    AMS_UTILITY_PVT.debug_message('Validation mode is ' || p_validation_mode);
1213    END IF;
1214    check_cnt_point_uk_items(
1215       p_cnt_point_rec => p_cnt_point_rec,
1216       p_validation_mode => p_validation_mode,
1217       x_return_status => x_return_status);
1218    IF x_return_status <> FND_API.g_ret_sts_success THEN
1219       RETURN;
1220    END IF;
1221    IF (AMS_DEBUG_HIGH_ON) THEN
1222 
1223    AMS_UTILITY_PVT.debug_message('After Calling uk Items '||x_return_status);
1224    END IF;
1225 
1226    -- Check Items Required/NOT NULL API calls
1227    IF (AMS_DEBUG_HIGH_ON) THEN
1228 
1229    AMS_UTILITY_PVT.debug_message('Before Calling req Items');
1230    END IF;
1231    IF (AMS_DEBUG_HIGH_ON) THEN
1232 
1233    AMS_UTILITY_PVT.debug_message('Validation mode is ' || p_validation_mode);
1234    END IF;
1235    check_cnt_point_req_items(
1236       p_cnt_point_rec => p_cnt_point_rec,
1237       p_validation_mode => p_validation_mode,
1238       x_return_status => x_return_status);
1239    IF x_return_status <> FND_API.g_ret_sts_success THEN
1240       RETURN;
1241    END IF;
1242    IF (AMS_DEBUG_HIGH_ON) THEN
1243 
1244    AMS_UTILITY_PVT.debug_message('After Calling req Items'|| x_return_status);
1245    END IF;
1246 
1247 /* Commented by nrengasw 05/07/2001. No validation required on contact point used by.
1248    Country is a free format column. Do not validate against geographies
1249 
1250    -- Check Items Foreign Keys API calls
1251     IF (AMS_DEBUG_HIGH_ON) THEN
1252 
1253     AMS_UTILITY_PVT.debug_message('Before Calling fk Items');
1254     END IF;
1255    check_cnt_point_FK_items(
1256       p_cnt_point_rec => p_cnt_point_rec,
1257       x_return_status => x_return_status);
1258    IF x_return_status <> FND_API.g_ret_sts_success THEN
1259       RETURN;
1260    END IF;
1261    IF (AMS_DEBUG_HIGH_ON) THEN
1262 
1263    AMS_UTILITY_PVT.debug_message('After Calling fk Items' || x_return_status);
1264    END IF;
1265 
1266 End of comments by nrengasw
1267 */
1268    -- Check Items Lookups
1269    IF (AMS_DEBUG_HIGH_ON) THEN
1270 
1271    AMS_UTILITY_PVT.debug_message('Before Calling Lk Items');
1272    END IF;
1273    check_cnt_point_Lookup_items(
1274       p_cnt_point_rec => p_cnt_point_rec,
1275       x_return_status => x_return_status);
1276    IF x_return_status <> FND_API.g_ret_sts_success THEN
1277       RETURN;
1278    END IF;
1279    IF (AMS_DEBUG_HIGH_ON) THEN
1280 
1281    AMS_UTILITY_PVT.debug_message('After Calling lk Items'|| x_return_status);
1282    END IF;
1283 END Check_cnt_point_Items;
1284 
1285 
1286 PROCEDURE Complete_cnt_point_Rec (
1287     P_cnt_point_rec     IN    cnt_point_rec_type,
1288      x_complete_rec        OUT NOCOPY    cnt_point_rec_type
1289     )
1290 IS
1291 BEGIN
1292 
1293       --
1294       -- Check Items API calls
1295       NULL;
1296       --
1297 
1298 END Complete_cnt_point_Rec;
1299 
1300 
1301 PROCEDURE Validate_cnt_point(
1302     p_api_version_number         IN   NUMBER,
1303     p_init_msg_list              IN   VARCHAR2     := FND_API.G_FALSE,
1304     p_validation_level           IN   NUMBER := FND_API.G_VALID_LEVEL_FULL,
1305     p_validation_mode            IN   VARCHAR2,
1306     p_cnt_point_rec              IN   cnt_point_rec_type,
1307     x_return_status              OUT NOCOPY  VARCHAR2,
1308     x_msg_count                  OUT NOCOPY  NUMBER,
1309     x_msg_data                   OUT NOCOPY  VARCHAR2
1310     )
1311 
1312  IS
1313 L_API_NAME                  CONSTANT VARCHAR2(30) := 'Validate_Cnt_Point';
1314 L_API_VERSION_NUMBER        CONSTANT NUMBER   := 1.0;
1315 l_object_version_number     NUMBER;
1316 l_cnt_point_rec  AMS_Cnt_Point_PVT.cnt_point_rec_type;
1317 
1318  BEGIN
1319        IF (AMS_DEBUG_HIGH_ON) THEN
1320 
1321        AMS_UTILITY_PVT.debug_message('Inside Validate_cnt_points');
1322        END IF;
1323 
1324       -- Standard Start of API savepoint
1325       SAVEPOINT VALIDATE_Cnt_Point_;
1326 
1327       -- Standard call to check for call compatibility.
1328       IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1329                                            p_api_version_number,
1330                                            l_api_name,
1331                                            G_PKG_NAME)
1332       THEN
1333           RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1334       END IF;
1335 
1336       IF (AMS_DEBUG_HIGH_ON) THEN
1337 
1338 
1339 
1340       AMS_UTILITY_PVT.debug_message('Validate Api is compatible');
1341 
1342       END IF;
1343       -- Initialize message list if p_init_msg_list is set to TRUE.
1344       IF FND_API.to_Boolean( p_init_msg_list )
1345       THEN
1346          FND_MSG_PUB.initialize;
1347       END IF;
1348 
1349      IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1350          IF (AMS_DEBUG_HIGH_ON) THEN
1351 
1352          AMS_UTILITY_PVT.debug_message('Calling Check_cnt_point');
1353          END IF;
1354          IF (AMS_DEBUG_HIGH_ON) THEN
1355 
1356          AMS_UTILITY_PVT.debug_message('Validation mode is ' || p_validation_mode);
1357          END IF;
1358               Check_cnt_point_Items(
1359                  p_cnt_point_rec        => p_cnt_point_rec,
1360                  p_validation_mode   => p_validation_mode,
1361                  x_return_status     => x_return_status
1362               );
1363 
1364            IF (AMS_DEBUG_HIGH_ON) THEN
1365 
1366 
1367 
1368            AMS_UTILITY_PVT.debug_message('DOne calling check_cnt_point '||p_cnt_point_rec.arc_contact_used_by|| x_return_status);
1369 
1370            END IF;
1371 
1372               IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1373                   RAISE FND_API.G_EXC_ERROR;
1374               ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1375                   RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1376               END IF;
1377     END IF;
1378 
1379       IF (AMS_DEBUG_HIGH_ON) THEN
1380 
1381 
1382 
1383       AMS_UTILITY_PVT.debug_message('Callin Complete rec');
1384 
1385       END IF;
1386       Complete_cnt_point_Rec(
1387          p_cnt_point_rec        => p_cnt_point_rec,
1388          x_complete_rec        => l_cnt_point_rec
1389       );
1390 
1391       IF (AMS_DEBUG_HIGH_ON) THEN
1392 
1393 
1394 
1395       AMS_UTILITY_PVT.debug_message('DOne calling Complete_rec '|| x_return_status);
1396 
1397       END IF;
1398 
1399 
1400 
1401       IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1402          IF (AMS_DEBUG_HIGH_ON) THEN
1403 
1404          AMS_UTILITY_PVT.debug_message('Calling Validate rec'||p_cnt_point_rec.arc_contact_used_by|| x_return_status);
1405          END IF;
1406          Validate_cnt_point_Rec(
1407            p_api_version_number     => 1.0,
1408            p_validation_mode        => p_validation_mode,
1409            p_init_msg_list          => FND_API.G_FALSE,
1410 
1411            x_return_status          => x_return_status,
1412            x_msg_count              => x_msg_count,
1413            x_msg_data               => x_msg_data,
1414            p_cnt_point_rec           =>    p_cnt_point_rec);
1415           IF (AMS_DEBUG_HIGH_ON) THEN
1416 
1417           AMS_UTILITY_PVT.debug_message('DOne calling Validate rec ' ||p_cnt_point_rec.arc_contact_used_by|| x_return_status);
1418           END IF;
1419 
1420               IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1421                  RAISE FND_API.G_EXC_ERROR;
1422               ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1423                  RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1424               END IF;
1425       END IF;
1426 
1427 
1428       -- Debug Message
1429       IF (AMS_DEBUG_HIGH_ON) THEN
1430 
1431       AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start '||x_return_status);
1432       END IF;
1433 
1434 
1435       -- Initialize API return status to SUCCESS
1436       x_return_status := FND_API.G_RET_STS_SUCCESS;
1437 
1438 
1439       -- Debug Message
1440       IF (AMS_DEBUG_HIGH_ON) THEN
1441 
1442       AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end  ' || x_return_status);
1443       END IF;
1444 
1445       -- Standard call to get message count and if count is 1, get message info.
1446       FND_MSG_PUB.Count_And_Get
1447         (p_encoded => FND_API.G_FALSE,
1448          p_count          =>   x_msg_count,
1449          p_data           =>   x_msg_data
1450       );
1451 EXCEPTION
1452 
1453    WHEN AMS_Utility_PVT.resource_locked THEN
1454      x_return_status := FND_API.g_ret_sts_error;
1455  AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1456 
1457    WHEN FND_API.G_EXC_ERROR THEN
1458      ROLLBACK TO VALIDATE_Cnt_Point_;
1459      x_return_status := FND_API.G_RET_STS_ERROR;
1460      -- Standard call to get message count and if count=1, get the message
1461      FND_MSG_PUB.Count_And_Get (
1462             p_encoded => FND_API.G_FALSE,
1463             p_count   => x_msg_count,
1464             p_data    => x_msg_data
1465      );
1466 
1467    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1468      ROLLBACK TO VALIDATE_Cnt_Point_;
1469      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1470      -- Standard call to get message count and if count=1, get the message
1471      FND_MSG_PUB.Count_And_Get (
1472             p_encoded => FND_API.G_FALSE,
1473             p_count => x_msg_count,
1474             p_data  => x_msg_data
1475      );
1476 
1477    WHEN OTHERS THEN
1478      ROLLBACK TO VALIDATE_Cnt_Point_;
1479      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1480      IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1481      THEN
1482         FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1483      END IF;
1484      -- Standard call to get message count and if count=1, get the message
1485      FND_MSG_PUB.Count_And_Get (
1486             p_encoded => FND_API.G_FALSE,
1487             p_count => x_msg_count,
1488             p_data  => x_msg_data
1489      );
1490 End Validate_Cnt_Point;
1491 
1492 -- procedure added by me
1493 PROCEDURE check_cnt_point_uk_rec(
1494     p_cnt_point_rec            IN cnt_point_rec_type,
1495     p_validation_mode          IN VARCHAR2,
1496     x_return_status            OUT NOCOPY VARCHAR2
1497     )
1498     IS
1499     l_contact_point_id  NUMBER ;
1500     CURSOR c_check_cnt_point_uk_rec_cr IS
1501     SELECT contact_point_id
1502     FROM ams_act_contact_points
1503     WHERE arc_contact_used_by = p_cnt_point_rec.arc_contact_used_by
1504     AND   act_contact_used_by_id = p_cnt_point_rec.act_contact_used_by_id
1505     AND   contact_point_type = p_cnt_point_rec.contact_point_type
1506     AND   contact_point_value = p_cnt_point_rec.contact_point_value
1507     AND   contact_point_value_id  = p_cnt_point_rec.contact_point_value_id;
1508 
1509     CURSOR c_check_cnt_point_uk_rec_up IS
1510     SELECT contact_point_id
1511     FROM ams_act_contact_points
1512     WHERE arc_contact_used_by = p_cnt_point_rec.arc_contact_used_by
1513     AND   act_contact_used_by_id = p_cnt_point_rec.act_contact_used_by_id
1514     AND   contact_point_type = p_cnt_point_rec.contact_point_type
1515     AND   contact_point_value = p_cnt_point_rec.contact_point_value
1516     AND contact_point_id <> p_cnt_point_rec.contact_point_id
1517     AND   contact_point_value_id  = p_cnt_point_rec.contact_point_value_id;
1518 
1519     BEGIN
1520 
1521      -- Initialize API return status to SUCCESS
1522       x_return_status := FND_API.G_RET_STS_SUCCESS;
1523 
1524       IF(p_validation_mode = JTF_PLSQL_API.g_create)
1525       THEN
1526            OPEN c_check_cnt_point_uk_rec_cr ;
1527            FETCH c_check_cnt_point_uk_rec_cr INTO l_contact_point_id;
1528            IF  c_check_cnt_point_uk_rec_cr%FOUND THEN
1529              x_return_status := FND_API.G_RET_STS_ERROR;
1530              AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CONTACT_POINT_ID_DUPLICATE');
1531              CLOSE c_check_cnt_point_uk_rec_cr ;
1532              RETURN;
1533            END IF;
1534            CLOSE c_check_cnt_point_uk_rec_cr ;
1535       END IF;
1536 
1537       IF(p_validation_mode = JTF_PLSQL_API.g_update) THEN
1538        OPEN c_check_cnt_point_uk_rec_up ;
1539        FETCH c_check_cnt_point_uk_rec_up INTO l_contact_point_id;
1540 
1541       IF  c_check_cnt_point_uk_rec_up%FOUND THEN
1542         x_return_status := FND_API.G_RET_STS_ERROR;
1543         AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CONTACT_POINT_ID_DUPLICATE');
1544         CLOSE c_check_cnt_point_uk_rec_up ;
1545         RETURN;
1546       END IF;
1547       CLOSE c_check_cnt_point_uk_rec_up ;
1548       END IF;
1549 
1550     END check_cnt_point_uk_rec;
1551 -- end procedure added by me
1552 
1553 
1554 
1555 -- procedure added by me
1556 PROCEDURE check_cnt_point_fk_rec(
1557           p_cnt_point_rec  IN    cnt_point_rec_type
1558           ,x_return_status  OUT NOCOPY   VARCHAR2
1559                                  )
1560      IS
1561 
1562     l_arc_contact_used_by VARCHAR2(30) := p_cnt_point_rec.arc_contact_used_by ;
1563     l_table_name  VARCHAR2(30);
1564     l_pk_name  VARCHAR2(30)  ;
1565     l_pk_value NUMBER;
1566     l_pk_data_type NUMBER;
1567     l_additional_where_clause VARCHAR2(100);
1568 
1569     --added by soagrawa on 31-oct-2001; bug# 2074740
1570     l_contact_point_type  VARCHAR2(30) := p_cnt_point_rec.contact_point_type;
1571 
1572 -- added by aranka on 27-dec-2001; bug# 2163252
1573 --    l_contact_point_value VARCHAR2(30) := p_cnt_point_rec.contact_point_value;
1574     l_contact_point_value VARCHAR2(256) := p_cnt_point_rec.contact_point_value;
1575 -- added by aranka on 27-dec-2001; bug# 2163252
1576 
1577     BEGIN
1578       /* check if the arc_contact_point_id is valid */
1579       IF (AMS_DEBUG_HIGH_ON) THEN
1580 
1581       AMS_UTILITY_PVT.debug_message('Calling  Get_Qual_Table_Name_And_PK');
1582       END IF;
1583       IF (AMS_DEBUG_HIGH_ON) THEN
1584 
1585       AMS_UTILITY_PVT.debug_message(l_arc_contact_used_by || '     '|| p_cnt_point_rec.arc_contact_used_by );
1586       END IF;
1587          AMS_Utility_PVT.Get_Qual_Table_Name_And_PK (
1588            p_sys_qual                     => l_arc_contact_used_by,
1589            x_return_status                => x_return_status,
1590            x_table_name                   => l_table_name,
1591            x_pk_name                      => l_pk_name
1592         );
1593 
1594       IF (AMS_DEBUG_HIGH_ON) THEN
1595 
1596 
1597 
1598       AMS_UTILITY_PVT.debug_message('Done Calling  Get_Qual_Table_Name_And_PK ' || l_arc_contact_used_by ||l_table_name|| l_pk_name || x_return_status);
1599 
1600       END IF;
1601 
1602       l_pk_value                 := p_cnt_point_rec.ACT_CONTACT_USED_BY_ID ; -- findout what to assign
1603       l_pk_data_type             := AMS_Utility_PVT.G_NUMBER;
1604       l_additional_where_clause  := NULL;
1605       IF (AMS_DEBUG_HIGH_ON) THEN
1606 
1607       AMS_UTILITY_PVT.debug_message(' Calling  Check FK Exists  '|| l_table_name || l_pk_name || l_pk_value || l_pk_data_type || x_return_status );
1608       END IF;
1609       IF AMS_Utility_PVT.Check_FK_Exists (
1610              p_table_name                   => l_table_name
1611             ,p_pk_name                      => l_pk_name
1612             ,p_pk_value                     => l_pk_value
1613             --,p_pk_data_type                 => l_pk_data_type
1614             --,p_additional_where_clause      => l_additional_where_clause
1615          ) = FND_API.G_FALSE
1616       THEN
1617 
1618            AMS_Utility_PVT.Error_Message(p_message_name =>  'AMS_TRIG_INVALID_CREATED_FOR');
1619 
1620 
1621             x_return_status := FND_API.G_RET_STS_ERROR;
1622             IF (AMS_DEBUG_HIGH_ON) THEN
1623 
1624             AMS_UTILITY_PVT.debug_message(' Done Calling  Check FK Exists  '|| l_table_name || l_pk_name || l_pk_value || l_pk_data_type || x_return_status );
1625             END IF;
1626           RETURN;
1627       END IF;
1628 
1629       /* in case of inbound/outbound script - check if the name is valid
1630          added by soagrawa on 31-oct-2001 : bug# 2074740 */
1631       IF l_contact_point_type IS NOT NULL AND l_contact_point_value IS NOT null
1632       THEN
1633          IF l_contact_point_type = 'INBOUND_SCRIPT' OR l_contact_point_type = 'OUTBOUND_SCRIPT'
1634          THEN
1635 
1636             -- soagrawa 21-jan-2003 fixed bug# 2757856
1637             -- now comparing trimmed values
1638             IF AMS_Utility_PVT.Check_FK_Exists (
1639                    p_table_name                   => 'ies_deployed_scripts'
1640                   ,p_pk_name                      => 'ltrim(rtrim(dscript_name))'
1641                   ,p_pk_value                     => ltrim(rtrim(l_contact_point_value))
1642                ) = FND_API.G_FALSE
1643             THEN
1644                  IF l_contact_point_type = 'INBOUND_SCRIPT'
1645                  THEN
1646                     AMS_Utility_PVT.Error_Message(p_message_name =>  'AMS_CSCH_BAD_INBOUND_DSCRIPT');
1647                  ELSIF  l_contact_point_type = 'OUTBOUND_SCRIPT'
1648                  THEN
1649                     AMS_Utility_PVT.Error_Message(p_message_name =>  'AMS_CSCH_BAD_OUTBOUND_DSCRIPT');
1650                  END IF;
1651 
1652                  x_return_status := FND_API.G_RET_STS_ERROR;
1653                  IF (AMS_DEBUG_HIGH_ON) THEN
1654 
1655                  AMS_UTILITY_PVT.debug_message(' Done Calling  Check FK Exists  '|| l_table_name || l_pk_name || l_pk_value || l_pk_data_type || x_return_status );
1656                  END IF;
1657                  RETURN;
1658             END IF;
1659 
1660          END IF;
1661       END IF;
1662 
1663 
1664 
1665  END check_cnt_point_fk_rec;
1666 
1667     -- end procedure added by me
1668 
1669 PROCEDURE Validate_cnt_point_rec(
1670     p_api_version_number         IN   NUMBER,
1671     p_validation_mode            IN   VARCHAR2,
1672     p_init_msg_list              IN   VARCHAR2     := FND_API.G_FALSE,
1673     x_return_status              OUT NOCOPY  VARCHAR2,
1674     x_msg_count                  OUT NOCOPY  NUMBER,
1675     x_msg_data                   OUT NOCOPY  VARCHAR2,
1676     p_cnt_point_rec               IN    cnt_point_rec_type
1677     )
1678 IS
1679     l_arc_contact_used_by VARCHAR2(30) := p_cnt_point_rec.arc_contact_used_by ;
1680     l_table_name  VARCHAR2(30);
1681     l_pk_name  VARCHAR2(30)  ;
1682     l_pk_value NUMBER;
1683     l_pk_data_type NUMBER;
1684     l_additional_where_clause VARCHAR2(100);
1685 BEGIN
1686       -- Initialize message list if p_init_msg_list is set to TRUE.
1687       IF FND_API.to_Boolean( p_init_msg_list )
1688       THEN
1689          FND_MSG_PUB.initialize;
1690       END IF;
1691 
1692       IF (AMS_DEBUG_HIGH_ON) THEN
1693 
1694 
1695 
1696       AMS_UTILITY_PVT.debug_message('Inside Validate rec'||p_cnt_point_rec.arc_contact_used_by|| x_return_status);
1697 
1698       END IF;
1699       -- Initialize API return status to SUCCESS
1700       x_return_status := FND_API.G_RET_STS_SUCCESS;
1701 
1702       -- Hint: Validate data
1703       -- If data not valid
1704       -- THEN
1705       -- x_return_status := FND_API.G_RET_STS_ERROR;
1706      -- code added by rssharma
1707      /* check_cnt_point_FK_items(
1708       p_cnt_point_rec =>  p_cnt_point_rec,
1709       x_return_status => x_return_status
1710        );
1711 
1712        IF x_return_status <> FND_API.g_ret_sts_success THEN
1713       RETURN;
1714          END IF;*/
1715          IF (AMS_DEBUG_HIGH_ON) THEN
1716 
1717          AMS_UTILITY_PVT.debug_message('Calling check_cnt_point_fk_rec'||p_cnt_point_rec.arc_contact_used_by|| x_return_status);
1718          END IF;
1719 
1720           check_cnt_point_fk_rec(
1721            p_cnt_point_rec => p_cnt_point_rec
1722            ,x_return_status => x_return_status
1723            );
1724           IF (AMS_DEBUG_HIGH_ON) THEN
1725 
1726           AMS_UTILITY_PVT.debug_message(' Done calling check_cnt_point_fk_rec '||p_cnt_point_rec.arc_contact_used_by|| x_return_status);
1727           END IF;
1728          IF x_return_status <> FND_API.g_ret_sts_success THEN
1729       RETURN;
1730          END IF;
1731 
1732 
1733          IF (AMS_DEBUG_HIGH_ON) THEN
1734 
1735 
1736 
1737 
1738 
1739          AMS_UTILITY_PVT.debug_message('Calling check_cnt_point_uk_rec');
1740 
1741 
1742          END IF;
1743       check_cnt_point_uk_rec(
1744          p_cnt_point_rec       =>  p_cnt_point_rec,
1745          p_validation_mode     => p_validation_mode,
1746          x_return_status       =>  x_return_status);
1747          IF (AMS_DEBUG_HIGH_ON) THEN
1748 
1749          AMS_UTILITY_PVT.debug_message(' Done calling check_cnt_point_uk_rec '||x_return_status);
1750          END IF;
1751 
1752           IF x_return_status <> FND_API.g_ret_sts_success THEN
1753       RETURN;
1754          END IF;
1755       -- end code added by me
1756 
1757 
1758 
1759 
1760       -- Debug Message
1761 END Validate_cnt_point_Rec;
1762 
1763 
1764 
1765 END AMS_Cnt_Point_PVT;