[Home] [Help]
PACKAGE BODY: APPS.IEM_EMAIL_CATEGORY_MAPS_PVT
Source
1 PACKAGE BODY IEM_EMAIL_CATEGORY_MAPS_PVT as
2 /* $Header: iemvcatb.pls 115.8 2002/12/03 02:17:55 sboorela shipped $ */
3 G_PKG_NAME CONSTANT varchar2(30) :='IEM_EMAIL_CATEGORY_MAPS_PVT ';
4
5 PROCEDURE create_item (p_api_version_number IN NUMBER,
6 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
7 p_commit IN VARCHAR2 := FND_API.G_FALSE,
8 p_email_account_id IN NUMBER,
9 p_kb_category_id IN NUMBER,
10 p_CREATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
11 p_CREATION_DATE DATE:=SYSDATE,
12 p_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ,
13 p_LAST_UPDATE_DATE DATE:=SYSDATE,
14 p_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ,
15 p_ATTRIBUTE1 VARCHAR2,
16 p_ATTRIBUTE2 VARCHAR2,
17 p_ATTRIBUTE3 VARCHAR2,
18 p_ATTRIBUTE4 VARCHAR2,
19 p_ATTRIBUTE5 VARCHAR2,
20 p_ATTRIBUTE6 VARCHAR2,
21 p_ATTRIBUTE7 VARCHAR2,
22 p_ATTRIBUTE8 VARCHAR2,
23 p_ATTRIBUTE9 VARCHAR2,
24 p_ATTRIBUTE10 VARCHAR2,
25 p_ATTRIBUTE11 VARCHAR2,
26 p_ATTRIBUTE12 VARCHAR2,
27 p_ATTRIBUTE13 VARCHAR2,
28 p_ATTRIBUTE14 VARCHAR2,
29 p_ATTRIBUTE15 VARCHAR2,
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_msg_count OUT NOCOPY NUMBER,
32 x_msg_data OUT NOCOPY VARCHAR2
33 ) is
34 l_api_name VARCHAR2(255):='create_item';
35 l_api_version_number NUMBER:=1.0;
36 l_cnt number;
37 l_CREATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID'));
38 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
39 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
40 BEGIN
41 -- Standard Start of API savepoint
42 SAVEPOINT create_item_PVT;
43 -- Standard call to check for call compatibility.
44 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
45 p_api_version_number,
46 l_api_name,
47 G_PKG_NAME)
48 THEN
49 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
50 END IF;
51 -- Initialize message list if p_init_msg_list is set to TRUE.
52 IF FND_API.to_Boolean( p_init_msg_list )
53 THEN
54 FND_MSG_PUB.initialize;
55 END IF;
56 -- Initialize API return status to SUCCESS
57 x_return_status := FND_API.G_RET_STS_SUCCESS;
58
59 /*Check For Existing Email Account Id*/
60
61 Select count(*) into l_cnt from iem_email_accounts
62 where email_account_id=p_email_account_id
63 and rownum=1;
64 IF l_cnt = 0 then
65 FND_MESSAGE.SET_NAME('IEM','IEM_NON_EXISTENT_EMAIL_ACCT_ID');
66 APP_EXCEPTION.RAISE_EXCEPTION;
67 END IF;
68
69 /*Check For Existing KB Category Id */
70
71 Select count(*) into l_cnt from iem_kb_categories
72 where kb_category_id=p_kb_category_id
73 and rownum=1;
74 IF l_cnt = 0 then
75 FND_MESSAGE.SET_NAME('IEM','IEM_NON_EXISTENT_KB_CAT_ID');
76 APP_EXCEPTION.RAISE_EXCEPTION;
77 END IF;
78 INSERT INTO iem_email_category_maps
79 (
80 EMAIL_ACCOUNT_ID,
81 KB_CATEGORY_ID,
82 CREATED_BY,
83 CREATION_DATE,
84 LAST_UPDATED_BY,
85 LAST_UPDATE_DATE,
86 LAST_UPDATE_LOGIN,
87 ATTRIBUTE1,
88 ATTRIBUTE2,
89 ATTRIBUTE3,
90 ATTRIBUTE4,
91 ATTRIBUTE5,
92 ATTRIBUTE6,
93 ATTRIBUTE7,
94 ATTRIBUTE8,
95 ATTRIBUTE9,
96 ATTRIBUTE10,
97 ATTRIBUTE11,
98 ATTRIBUTE12,
99 ATTRIBUTE13,
100 ATTRIBUTE14,
101 ATTRIBUTE15
102 )
103 VALUES
104 (
105 p_email_account_id ,
106 p_kb_category_id ,
107 decode(l_CREATED_BY,null,-1,l_CREATED_BY),
108 sysdate,
109 decode(l_LAST_UPDATED_BY,null,-1,l_LAST_UPDATED_BY),
110 sysdate,
111 decode(l_LAST_UPDATE_LOGIN,null,-1,l_LAST_UPDATE_LOGIN),
112 decode(p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
113 decode(p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
114 decode(p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
115 decode(p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
116 decode(p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
117 decode(p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
118 decode(p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
119 decode(p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
120 decode(p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
121 decode(p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
122 decode(p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
123 decode(p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
124 decode(p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
125 decode(p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
126 decode(p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
127 );
128 -- Standard Check Of p_commit.
129 IF FND_API.To_Boolean(p_commit) THEN
130 COMMIT WORK;
131 END IF;
132 -- Standard callto get message count and if count is 1, get message info.
133 FND_MSG_PUB.Count_And_Get
134 ( p_count => x_msg_count,
135 p_data => x_msg_data
136 );
137 EXCEPTION
138 WHEN FND_API.G_EXC_ERROR THEN
139 ROLLBACK TO create_item_PVT;
140 x_return_status := FND_API.G_RET_STS_ERROR ;
141 FND_MSG_PUB.Count_And_Get
142 ( p_count => x_msg_count,
143 p_data => x_msg_data
144 );
145 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
146 ROLLBACK TO create_item_PVT;
147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
148 FND_MSG_PUB.Count_And_Get
149 ( p_count => x_msg_count,
150 p_data => x_msg_data
151 );
152 WHEN OTHERS THEN
153 ROLLBACK TO create_item_PVT;
154 x_return_status := FND_API.G_RET_STS_ERROR;
155 IF FND_MSG_PUB.Check_Msg_Level
156 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
157 THEN
158 FND_MSG_PUB.Add_Exc_Msg
159 ( G_PKG_NAME ,
160 l_api_name
161 );
162 END IF;
163 FND_MSG_PUB.Count_And_Get
164 ( p_count => x_msg_count ,
165 p_data => x_msg_data
166 );
167
168 END create_item;
169
170 PROCEDURE delete_item (p_api_version_number IN NUMBER,
171 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
172 p_commit IN VARCHAR2 := FND_API.G_FALSE,
173 p_email_account_id IN NUMBER,
174 p_kb_category_id IN NUMBER,
175 x_return_status OUT NOCOPY VARCHAR2,
176 x_msg_count OUT NOCOPY NUMBER,
177 x_msg_data OUT NOCOPY VARCHAR2
178 ) is
179 l_api_name VARCHAR2(255):='delete_item';
180 l_api_version_number NUMBER:=1.0;
181
182 BEGIN
183 -- Standard Start of API savepoint
184 SAVEPOINT delete_item_PVT;
185 -- Standard call to check for call compatibility.
186 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
187 p_api_version_number,
188 l_api_name,
189 G_PKG_NAME)
190 THEN
191 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
192 END IF;
193 -- Initialize message list if p_init_msg_list is set to TRUE.
194 IF FND_API.to_Boolean( p_init_msg_list )
195 THEN
196 FND_MSG_PUB.initialize;
197 END IF;
198 -- Initialize API return status to SUCCESS
199 x_return_status := FND_API.G_RET_STS_SUCCESS;
200
201 DELETE FROM IEM_EMAIL_CATEGORY_MAPS
202 where email_account_id=p_email_account_id
203 and kb_category_id=p_kb_category_id;
204
205 -- Standard Check Of p_commit.
206 IF FND_API.To_Boolean(p_commit) THEN
207 COMMIT WORK;
208 END IF;
209 -- Standard callto get message count and if count is 1, get message info.
210 FND_MSG_PUB.Count_And_Get
211 ( p_count => x_msg_count,
212 p_data => x_msg_data
213 );
214 EXCEPTION
215 WHEN FND_API.G_EXC_ERROR THEN
216 ROLLBACK TO delete_item_PVT;
217 x_return_status := FND_API.G_RET_STS_ERROR ;
218 FND_MSG_PUB.Count_And_Get
219 ( p_count => x_msg_count,
220 p_data => x_msg_data
221 );
222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
223 ROLLBACK TO delete_item_PVT;
224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
225 FND_MSG_PUB.Count_And_Get
226 ( p_count => x_msg_count,
227 p_data => x_msg_data
228 );
229 WHEN OTHERS THEN
230 ROLLBACK TO delete_item_PVT;
231 x_return_status := FND_API.G_RET_STS_ERROR;
232 IF FND_MSG_PUB.Check_Msg_Level
233 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
234 THEN
235 FND_MSG_PUB.Add_Exc_Msg
236 ( G_PKG_NAME ,
237 l_api_name
238 );
239 END IF;
240 FND_MSG_PUB.Count_And_Get
241 ( p_count => x_msg_count ,
242 p_data => x_msg_data
243 );
244
245 END delete_item;
246 END IEM_EMAIL_CATEGORY_MAPS_PVT ;