[Home] [Help]
PACKAGE BODY: APPS.IEM_KB_CATEGORIES_PVT
Source
1 PACKAGE BODY IEM_KB_CATEGORIES_PVT as
2 /* $Header: iemvkbcb.pls 115.3 2002/12/02 23:52:40 sboorela shipped $ */
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_KB_CATEGORIES_PVT ';
5
6 PROCEDURE create_item (p_api_version_number IN NUMBER,
7 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8 p_commit IN VARCHAR2 := FND_API.G_FALSE,
9 p_kb_category_id NUMBER,
10 p_kb_parent_category_id IN NUMBER,
11 p_display_name IN VARCHAR2,
12 p_category_code IN VARCHAR2,
13 p_is_repos IN VARCHAR2,
14 p_category_order IN NUMBER,
15 p_category_desc IN varchar2,
16 p_CREATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
17 p_CREATION_DATE DATE:=SYSDATE,
18 p_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ,
19 p_LAST_UPDATE_DATE DATE:=SYSDATE,
20 p_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ,
21 p_ATTRIBUTE1 VARCHAR2,
22 p_ATTRIBUTE2 VARCHAR2,
23 p_ATTRIBUTE3 VARCHAR2,
24 p_ATTRIBUTE4 VARCHAR2,
25 p_ATTRIBUTE5 VARCHAR2,
26 p_ATTRIBUTE6 VARCHAR2,
27 p_ATTRIBUTE7 VARCHAR2,
28 p_ATTRIBUTE8 VARCHAR2,
29 p_ATTRIBUTE9 VARCHAR2,
30 p_ATTRIBUTE10 VARCHAR2,
31 p_ATTRIBUTE11 VARCHAR2,
32 p_ATTRIBUTE12 VARCHAR2,
33 p_ATTRIBUTE13 VARCHAR2,
34 p_ATTRIBUTE14 VARCHAR2,
35 p_ATTRIBUTE15 VARCHAR2,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2
39 ) is
40 l_api_name VARCHAR2(255):='create_item';
41 l_api_version_number NUMBER:=1.0;
42 l_seq_id number;
43 l_grp_cnt number;
44 l_CREATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID'));
45 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
46 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
47
48 BEGIN
49 -- Standard Start of API savepoint
50 SAVEPOINT create_item_PVT;
51 -- Standard call to check for call compatibility.
52 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
53 p_api_version_number,
54 l_api_name,
55 G_PKG_NAME)
56 THEN
57 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
58 END IF;
59 -- Initialize message list if p_init_msg_list is set to TRUE.
60 IF FND_API.to_Boolean( p_init_msg_list )
61 THEN
62 FND_MSG_PUB.initialize;
63 END IF;
64 -- Initialize API return status to SUCCESS
65 x_return_status := FND_API.G_RET_STS_SUCCESS;
66
67 SELECT IEM_KB_CATEGORIES_s1.nextval
68 INTO l_seq_id
69 FROM dual;
70
71 INSERT INTO IEM_KB_CATEGORIES
72 (
73 KB_CATEGORY_ID,
74 KB_PARENT_CATEGORY_ID,
75 DISPLAY_NAME ,
76 CATEGORY_CODE ,
77 IS_REPOSITORY ,
78 CATEGORY_ORDER,
79 CATEGORY_DESC ,
80 CREATED_BY,
81 CREATION_DATE,
82 LAST_UPDATED_BY,
83 LAST_UPDATE_DATE,
84 LAST_UPDATE_LOGIN,
85 ATTRIBUTE1,
86 ATTRIBUTE2,
87 ATTRIBUTE3,
88 ATTRIBUTE4,
89 ATTRIBUTE5,
90 ATTRIBUTE6,
91 ATTRIBUTE7,
92 ATTRIBUTE8,
93 ATTRIBUTE9,
94 ATTRIBUTE10,
95 ATTRIBUTE11,
96 ATTRIBUTE12,
97 ATTRIBUTE13,
98 ATTRIBUTE14,
99 ATTRIBUTE15
100 )
101 VALUES
102 (l_seq_id,
103 p_kb_parent_category_id,
104 p_display_name,
105 p_category_code,
106 p_is_repos,
107 p_category_order,
108 decode(p_category_desc,FND_API.G_MISS_CHAR,NULL,p_category_desc),
109 decode(l_CREATED_BY,null,-1,l_CREATED_BY),
110 sysdate,
111 decode(l_LAST_UPDATED_BY,null,-1,l_LAST_UPDATED_BY),
112 sysdate,
113 decode(l_LAST_UPDATE_LOGIN,null,-1,l_LAST_UPDATE_LOGIN),
114 p_ATTRIBUTE1,
115 p_ATTRIBUTE2,
116 p_ATTRIBUTE3,
117 p_ATTRIBUTE4,
118 p_ATTRIBUTE5,
119 p_ATTRIBUTE6,
120 p_ATTRIBUTE7,
121 p_ATTRIBUTE8,
122 p_ATTRIBUTE9,
123 p_ATTRIBUTE10,
124 p_ATTRIBUTE11,
125 p_ATTRIBUTE12,
126 p_ATTRIBUTE13,
127 p_ATTRIBUTE14,
128 p_ATTRIBUTE15
129 );
130
131 -- Standard callto get message count and if count is 1, get message info.
132 FND_MSG_PUB.Count_And_Get
133 ( p_count => x_msg_count,
134 p_data => x_msg_data
135 );
136 EXCEPTION
137 WHEN FND_API.G_EXC_ERROR THEN
138 ROLLBACK TO create_item_PVT;
139 x_return_status := FND_API.G_RET_STS_ERROR ;
140 FND_MSG_PUB.Count_And_Get
141 ( p_count => x_msg_count,
142 p_data => x_msg_data
143 );
144 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
145 ROLLBACK TO create_item_PVT;
146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
147 FND_MSG_PUB.Count_And_Get
148 ( p_count => x_msg_count,
149 p_data => x_msg_data
150 );
151 WHEN OTHERS THEN
152 ROLLBACK TO create_item_PVT;
153 x_return_status := FND_API.G_RET_STS_ERROR;
154 IF FND_MSG_PUB.Check_Msg_Level
155 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
156 THEN
157 FND_MSG_PUB.Add_Exc_Msg
158 ( G_PKG_NAME ,
159 l_api_name
160 );
161 END IF;
162 FND_MSG_PUB.Count_And_Get
163 ( p_count => x_msg_count ,
164 p_data => x_msg_data
165 );
166
167 END create_item;
168
169 PROCEDURE delete_item (p_api_version_number IN NUMBER,
170 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
171 p_commit IN VARCHAR2 := FND_API.G_FALSE,
172 p_kb_category_id in NUMBER,
173 p_kb_parent_category_id IN NUMBER,
174 p_display_name IN VARCHAR2 ,
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 IF p_kb_category_id =FND_API.G_MISS_NUM THEN
202 DELETE FROM IEM_KB_CATEGORIES
203 WHERE display_name=p_display_name AND
204 kb_parent_category_id=p_kb_parent_category_id;
205 else
206 DELETE FROM IEM_KB_CATEGORIES
207 where kb_category_id=p_kb_category_id;
208 end if;
209
210 -- Standard Check Of p_commit.
211 IF FND_API.To_Boolean(p_commit) THEN
212 COMMIT WORK;
213 END IF;
214 -- Standard callto get message count and if count is 1, get message info.
215 FND_MSG_PUB.Count_And_Get
216 ( p_count => x_msg_count,
217 p_data => x_msg_data
218 );
219 EXCEPTION
220 WHEN FND_API.G_EXC_ERROR THEN
221 ROLLBACK TO delete_item_PVT;
222 x_return_status := FND_API.G_RET_STS_ERROR ;
223 FND_MSG_PUB.Count_And_Get
224 ( p_count => x_msg_count,
225 p_data => x_msg_data
226 );
227 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
228 ROLLBACK TO delete_item_PVT;
229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
230 FND_MSG_PUB.Count_And_Get
231 ( p_count => x_msg_count,
232 p_data => x_msg_data
233 );
234 WHEN OTHERS THEN
235 ROLLBACK TO delete_item_PVT;
236 x_return_status := FND_API.G_RET_STS_ERROR;
237 IF FND_MSG_PUB.Check_Msg_Level
238 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
239 THEN
240 FND_MSG_PUB.Add_Exc_Msg
241 ( G_PKG_NAME ,
242 l_api_name
243 );
244 END IF;
245 FND_MSG_PUB.Count_And_Get
246 ( p_count => x_msg_count ,
247 p_data => x_msg_data
248 );
249
250 END delete_item;
251
252 PROCEDURE update_item (p_api_version_number IN NUMBER,
253 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
254 p_commit IN VARCHAR2 := FND_API.G_FALSE,
255 p_kb_category_id IN NUMBER,
256 p_kb_parent_category_id IN number,
257 p_display_name IN varchar2,
258 p_category_code IN varchar2,
259 p_is_repos IN VARCHAR2,
260 p_category_order IN number,
261 p_category_desc varchar2,
262 p_CREATED_BY NUMBER,
263 p_CREATION_DATE DATE,
264 p_LAST_UPDATED_BY NUMBER,
265 p_LAST_UPDATE_DATE DATE,
266 p_LAST_UPDATE_LOGIN NUMBER,
267 p_ATTRIBUTE1 VARCHAR2,
268 p_ATTRIBUTE2 VARCHAR2,
269 p_ATTRIBUTE3 VARCHAR2,
270 p_ATTRIBUTE4 VARCHAR2,
271 p_ATTRIBUTE5 VARCHAR2,
272 p_ATTRIBUTE6 VARCHAR2,
273 p_ATTRIBUTE7 VARCHAR2,
274 p_ATTRIBUTE8 VARCHAR2,
275 p_ATTRIBUTE9 VARCHAR2,
276 p_ATTRIBUTE10 VARCHAR2,
277 p_ATTRIBUTE11 VARCHAR2,
278 p_ATTRIBUTE12 VARCHAR2,
279 p_ATTRIBUTE13 VARCHAR2,
280 p_ATTRIBUTE14 VARCHAR2,
281 p_ATTRIBUTE15 VARCHAR2,
282 x_return_status OUT NOCOPY VARCHAR2,
283 x_msg_count OUT NOCOPY NUMBER,
284 x_msg_data OUT NOCOPY VARCHAR2
285 ) is
286 l_api_name VARCHAR2(255):='update_item';
287 l_api_version_number NUMBER:=1.0;
288 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
289 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
290
291 BEGIN
292 -- Standard Start of API savepoint
293 SAVEPOINT update_item_PVT;
294 -- Standard call to check for call compatibility.
295 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
296 p_api_version_number,
297 l_api_name,
298 G_PKG_NAME)
299 THEN
300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
301 END IF;
302 -- Initialize message list if p_init_msg_list is set to TRUE.
303 IF FND_API.to_Boolean( p_init_msg_list )
304 THEN
305 FND_MSG_PUB.initialize;
306 END IF;
307 -- Initialize API return status to SUCCESS
308 x_return_status := FND_API.G_RET_STS_SUCCESS;
309
310 update IEM_KB_CATEGORIES
311 set kb_parent_category_id=decode(p_kb_parent_category_id,FND_API.G_MISS_NUM,kb_parent_category_id,p_kb_parent_category_id),
312 display_name=decode(p_display_name,FND_API.G_MISS_CHAR,display_name,p_display_name),
313 category_code=decode(p_category_code,FND_API.G_MISS_CHAR,category_code,p_category_code),
314 is_repository=decode(p_is_repos,FND_API.G_MISS_CHAR,is_repository,p_is_repos),
315 category_order=decode(p_category_order,FND_API.G_MISS_NUM,category_order,p_category_order),
316 category_desc=decode(p_category_desc,FND_API.G_MISS_CHAR,category_desc,p_category_desc),
317 LAST_UPDATE_DATE = sysdate,
318 LAST_UPDATED_BY = decode(l_LAST_UPDATED_BY, null,-1,l_LAST_UPDATED_BY),
319 LAST_UPDATE_LOGIN = decode( l_LAST_UPDATE_LOGIN,null,l_LAST_UPDATE_LOGIN),
320 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, p_ATTRIBUTE1),
321 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, p_ATTRIBUTE2),
322 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, p_ATTRIBUTE3),
323 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, p_ATTRIBUTE4),
324 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, p_ATTRIBUTE5),
325 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, p_ATTRIBUTE6),
326 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, p_ATTRIBUTE7),
327 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, p_ATTRIBUTE8),
328 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, p_ATTRIBUTE9),
329 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, p_ATTRIBUTE10),
330 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, p_ATTRIBUTE11),
331 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, p_ATTRIBUTE12),
332 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, p_ATTRIBUTE13),
333 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, p_ATTRIBUTE14),
334 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, p_ATTRIBUTE15)
335 where kb_category_id=p_kb_category_id;
336
337 -- Standard Check Of p_commit.
338 IF FND_API.To_Boolean(p_commit) THEN
339 COMMIT WORK;
340 END IF;
341 -- Standard callto get message count and if count is 1, get message info.
342 FND_MSG_PUB.Count_And_Get
343 ( p_count => x_msg_count,
344 p_data => x_msg_data
345 );
346 EXCEPTION
347 WHEN FND_API.G_EXC_ERROR THEN
348 ROLLBACK TO update_item_PVT;
349 x_return_status := FND_API.G_RET_STS_ERROR ;
350 FND_MSG_PUB.Count_And_Get
351 ( p_count => x_msg_count,
352 p_data => x_msg_data
353 );
354 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
355 ROLLBACK TO update_item_PVT;
356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
357 FND_MSG_PUB.Count_And_Get
358 ( p_count => x_msg_count,
359 p_data => x_msg_data
360 );
361 WHEN OTHERS THEN
362 ROLLBACK TO update_item_PVT;
363 x_return_status := FND_API.G_RET_STS_ERROR;
364 IF FND_MSG_PUB.Check_Msg_Level
365 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
366 THEN
367 FND_MSG_PUB.Add_Exc_Msg
368 ( G_PKG_NAME ,
369 l_api_name
370 );
371 END IF;
372 FND_MSG_PUB.Count_And_Get
373 ( p_count => x_msg_count ,
374 p_data => x_msg_data
375 );
376
377 END update_item;
378 END IEM_KB_CATEGORIES_PVT ;