[Home] [Help]
PACKAGE BODY: APPS.IBC_CV_LABEL_GRP
Source
1 PACKAGE BODY Ibc_Cv_Label_Grp AS
2 /* $Header: ibcgcvlb.pls 115.5 2002/11/15 00:48:46 svatsa ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IBC_CV_LABEL_GRP';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ibcgcvlb.pls';
6
7 FUNCTION Query_CV_Label_Row (
8 p_content_item_id IN NUMBER,
9 p_Label_code IN VARCHAR2
10 ) RETURN Ibc_Cv_Label_Grp.CV_Label_Rec_Type;
11
12
13
14 PROCEDURE Create_CV_Label(
15 P_Api_Version_Number IN NUMBER,
16 P_Init_Msg_List IN VARCHAR2 ,--:= Fnd_Api.G_FALSE,
17 P_Commit IN VARCHAR2 ,--:= Fnd_Api.G_FALSE,
18 P_Validation_Level IN NUMBER ,--:= Fnd_Api.G_VALID_LEVEL_FULL,
19 P_CV_Label_Rec IN Ibc_Cv_Label_Grp.CV_Label_Rec_Type ,--:= Ibc_Cv_Label_Grp.G_MISS_CV_Label_Rec,
20 x_CV_Label_Rec OUT NOCOPY Ibc_Cv_Label_Grp.CV_Label_Rec_Type,
21 X_Return_Status OUT NOCOPY VARCHAR2,
22 X_Msg_Count OUT NOCOPY NUMBER,
23 X_Msg_Data OUT NOCOPY VARCHAR2
24 )
25 IS
26 CURSOR C_CV_Label IS
27 SELECT
28 label_code
29 FROM IBC_CITEM_VERSION_LABELS
30 WHERE Label_Code = P_CV_Label_Rec.Label_Code
31 AND content_item_id = P_CV_Label_Rec.content_item_id;
32
33 l_api_version_number NUMBER := 1.0;
34 l_api_name VARCHAR2(50) := 'Create_CV_Label';
35 l_CV_Label_Code VARCHAR2(100);
36 lx_rowid VARCHAR2(240);
37
38 l_CV_Label_Rec Ibc_Cv_Label_Grp.CV_Label_Rec_Type := p_CV_Label_Rec;
39
40 BEGIN
41
42 -- Initialize API return status to SUCCESS
43 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
44
45 --
46 -- API body
47 --
48
49 -- ******************************************************************
50 -- Validate Environment
51 -- ******************************************************************
52 IF Fnd_Global.User_Id IS NULL
53 THEN
54 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
55 THEN
56 Fnd_Message.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
57 Fnd_Message.Set_Token('PROFILE', 'USER_ID', FALSE);
58 Fnd_Msg_Pub.ADD;
59 END IF;
60 RAISE Fnd_Api.G_EXC_ERROR;
61 END IF;
62
63 -- Check for all the NOT NULL Columns
64 -- CV_Label_Code Cannot be NULL and must exist in IBC_LABELS
65 IF (Ibc_Validate_Pvt.isValidLabel(l_CV_Label_rec.Label_code) = Fnd_Api.g_false) THEN
66 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
67 Fnd_Message.Set_Name('IBC', 'INVALID_LABEL_CODE');
68 Fnd_Message.Set_token('LABEL_CODE', l_CV_Label_rec.Label_code);
69 Fnd_Msg_Pub.ADD;
70 END IF;
71 -- Don't RAISE the EXCEPTION Yet. RUN ALL the validation procedures
72 -- and show Exceptions all at once.
73 x_return_status := Fnd_Api.G_RET_STS_ERROR;
74 END IF;
75
76 -- Check if Content_item_id is Valid
77 IF (Ibc_Validate_Pvt.isValidCitem(l_CV_Label_rec.content_item_id) = Fnd_Api.g_false) THEN
78 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
79 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_ID');
80 Fnd_Message.Set_token('CITEM_ID', l_CV_Label_rec.content_item_id);
81 Fnd_Msg_Pub.ADD;
82 END IF;
83 x_return_status := Fnd_Api.G_RET_STS_ERROR;
84 END IF;
85
86 -- Check if Citem_Version_ID exists
87 IF (Ibc_Validate_Pvt.isValidCitemVer(l_CV_Label_rec.citem_version_id) = Fnd_Api.g_false) THEN
88 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
89 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_VERSION_ID');
90 Fnd_Message.Set_token('CITEM_VERSION_ID', l_CV_Label_rec.CITEM_VERSION_id);
91 Fnd_Msg_Pub.ADD;
92 END IF;
93 x_return_status := Fnd_Api.G_RET_STS_ERROR;
94 END IF;
95
96
97
98
99 -- Check for Uniqueness
100 OPEN C_CV_Label;
101 FETCH C_CV_Label INTO l_CV_Label_Code;
102 IF C_CV_Label%FOUND THEN
103 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
104 Fnd_Message.Set_Name('IBC', 'Label Code Already EXISTS');
105 Fnd_Message.Set_Token('COLUMN', 'Label_Code', FALSE);
106 Fnd_Msg_Pub.ADD;
107 END IF;
108 x_return_status := Fnd_Api.G_RET_STS_ERROR;
109 END IF;
110
111 CLOSE C_CV_Label;
112
113
114 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
115 RAISE Fnd_Api.G_EXC_ERROR;
116 END IF;
117
118
119 -- Validate Object Version Number
120 IF l_CV_Label_rec.OBJECT_VERSION_NUMBER IS NULL
121 OR l_CV_Label_rec.OBJECT_VERSION_NUMBER = Fnd_Api.G_MISS_NUM THEN
122 l_CV_Label_rec.OBJECT_VERSION_NUMBER := 1;
123 END IF;
124
125 --
126 -- Table Handler to Insert Row into IBC_CV_LabelS
127 --
128 Ibc_Citem_Version_Labels_Pkg.INSERT_ROW (
129 x_ROWID => lx_rowid
130 ,p_content_item_id => l_CV_Label_rec.content_item_id
131 ,p_Label_code => l_CV_Label_rec.Label_code
132 ,p_citem_version_id => l_CV_Label_rec.citem_version_id
133 ,p_CREATED_BY => l_CV_Label_rec.CREATED_BY
134 ,p_CREATION_DATE => l_CV_Label_rec.CREATION_DATE
135 ,p_LAST_UPDATED_BY => l_CV_Label_rec.LAST_UPDATED_BY
136 ,p_LAST_UPDATE_DATE => l_CV_Label_rec.LAST_UPDATE_DATE
137 ,p_LAST_UPDATE_LOGIN => l_CV_Label_rec.LAST_UPDATE_LOGIN
138 ,p_OBJECT_VERSION_NUMBER => l_CV_Label_rec.OBJECT_VERSION_NUMBER);
139
140
141 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
142 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
143 Fnd_Message.Set_Name('IBC', 'IBC_INSERT_ERROR');
144 Fnd_Msg_Pub.ADD;
145 END IF;
146
147 IF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR) THEN
148 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
149 ELSIF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
150 RAISE Fnd_Api.G_EXC_ERROR;
151 END IF;
152 END IF;
153
154 --
155 -- End of API body
156 --
157
158 -- Standard check for p_commit
159 IF Fnd_Api.to_Boolean( p_commit )
160 THEN
161 COMMIT WORK;
162 END IF;
163
164 -- Standard call to get message count and if count is 1, get message info.
165 Fnd_Msg_Pub.Count_And_Get
166 ( p_count => x_msg_count,
167 p_data => x_msg_data
168 );
169
170
171 EXCEPTION
172 WHEN Fnd_Api.G_EXC_ERROR THEN
173 ROLLBACK;
174 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
175 P_API_NAME => L_API_NAME
176 ,P_PKG_NAME => G_PKG_NAME
177 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
178 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
179 ,P_SQLCODE => SQLCODE
180 ,P_SQLERRM => SQLERRM
181 ,X_MSG_COUNT => X_MSG_COUNT
182 ,X_MSG_DATA => X_MSG_DATA
183 ,X_RETURN_STATUS => X_RETURN_STATUS);
184
185 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
186 ROLLBACK;
187 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
188 P_API_NAME => L_API_NAME
189 ,P_PKG_NAME => G_PKG_NAME
190 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
191 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
192 ,P_SQLCODE => SQLCODE
193 ,P_SQLERRM => SQLERRM
194 ,X_MSG_COUNT => X_MSG_COUNT
195 ,X_MSG_DATA => X_MSG_DATA
196 ,X_RETURN_STATUS => X_RETURN_STATUS);
197
198 WHEN OTHERS THEN
199 ROLLBACK;
200 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
201 P_API_NAME => L_API_NAME
202 ,P_PKG_NAME => G_PKG_NAME
203 ,P_EXCEPTION_LEVEL => Ibc_Utilities_Pvt.G_EXC_OTHERS
204 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
205 ,P_SQLCODE => SQLCODE
206 ,P_SQLERRM => SQLERRM
207 ,X_MSG_COUNT => X_MSG_COUNT
208 ,X_MSG_DATA => X_MSG_DATA
209 ,X_RETURN_STATUS => X_RETURN_STATUS);
210
211 END Create_CV_Label;
212
213
214 PROCEDURE Update_CV_Label(
215 P_Api_Version_Number IN NUMBER,
216 P_Init_Msg_List IN VARCHAR2 ,--:= Fnd_Api.G_FALSE,
217 P_Commit IN VARCHAR2 ,--:= Fnd_Api.G_FALSE,
218 P_Validation_Level IN NUMBER ,--:= Fnd_Api.G_VALID_LEVEL_FULL,
219 P_CV_Label_Rec IN Ibc_Cv_Label_Grp.CV_Label_Rec_Type ,--:= Ibc_Cv_Label_Grp.G_MISS_CV_Label_Rec,
220 x_CV_Label_Rec OUT NOCOPY Ibc_Cv_Label_Grp.CV_Label_Rec_Type,
221 X_Return_Status OUT NOCOPY VARCHAR2,
222 X_Msg_Count OUT NOCOPY NUMBER,
223 X_Msg_Data OUT NOCOPY VARCHAR2
224 )
225 IS
226 CURSOR C_CV_Label IS
227 SELECT
228 label_code
229 FROM IBC_CITEM_VERSION_LABELS
230 WHERE Label_Code = P_CV_Label_Rec.Label_Code
231 AND content_item_id = P_CV_Label_Rec.content_item_id;
232
233 l_temp CHAR(1);
234 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
235
236 l_api_version_number NUMBER := 1.0;
237 l_api_name VARCHAR2(50) := 'Update_CV_Label';
238 l_CV_Label_Code VARCHAR2(100);
239 lx_rowid VARCHAR2(240);
240
241 lx_CV_Label_REL_ID NUMBER;
242
243
244 l_CV_Label_Rec Ibc_Cv_Label_Grp.CV_Label_Rec_Type := p_CV_Label_Rec;
245
246 BEGIN
247
248 -- Initialize API return status to SUCCESS
249 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
250
251 --
252 -- API body
253 --
254
255 -- ******************************************************************
256 -- Validate Environment
257 -- ******************************************************************
258 IF Fnd_Global.User_Id IS NULL
259 THEN
260 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
261 THEN
262 Fnd_Message.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
263 Fnd_Message.Set_Token('PROFILE', 'USER_ID', FALSE);
264 Fnd_Msg_Pub.ADD;
265 END IF;
266 RAISE Fnd_Api.G_EXC_ERROR;
267 END IF;
268
269 -- Check for all the NOT NULL Columns
270 -- CV_Label_Code Cannot be NULL and must exist in IBC_LABELS
271 IF (Ibc_Validate_Pvt.isValidLabel(l_CV_Label_rec.Label_code) = Fnd_Api.g_false) THEN
272 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
273 Fnd_Message.Set_Name('IBC', 'INVALID_LABEL_CODE');
274 Fnd_Message.Set_token('LABEL_CODE', l_CV_Label_rec.Label_code);
275 Fnd_Msg_Pub.ADD;
276 END IF;
277 -- Don't RAISE the EXCEPTION Yet. RUN ALL the validation procedures
278 -- and show Exceptions all at once.
279 x_return_status := Fnd_Api.G_RET_STS_ERROR;
280 END IF;
281
282 -- Check if Content_item_id is Valid
283 IF (Ibc_Validate_Pvt.isValidCitem(l_CV_Label_rec.content_item_id) = Fnd_Api.g_false) THEN
284 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
285 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_ID');
286 Fnd_Message.Set_token('CITEM_ID', l_CV_Label_rec.content_item_id);
287 Fnd_Msg_Pub.ADD;
288 END IF;
289 x_return_status := Fnd_Api.G_RET_STS_ERROR;
290 END IF;
291
292 -- Check if Citem_Version_ID exists
293 IF (Ibc_Validate_Pvt.isValidCitemVer(l_CV_Label_rec.citem_version_id) = Fnd_Api.g_false) THEN
294 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
295 Fnd_Message.Set_Name('IBC', 'INVALID_CITEM_VERSION_ID');
296 Fnd_Message.Set_token('CITEM_VERSION_ID', l_CV_Label_rec.CITEM_VERSION_id);
297 Fnd_Msg_Pub.ADD;
298 END IF;
299 x_return_status := Fnd_Api.G_RET_STS_ERROR;
300 END IF;
301
302
303
304
305 -- Check if the label code exists
306 OPEN C_CV_Label;
307 FETCH C_CV_Label INTO l_CV_Label_Code;
308 IF C_CV_Label%NOTFOUND THEN
309 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
310 Fnd_Message.Set_Name('IBC', 'Label Code doesnot EXIST');
311 Fnd_Message.Set_Token('COLUMN', 'Label_Code', FALSE);
312 Fnd_Msg_Pub.ADD;
313 END IF;
314 x_return_status := Fnd_Api.G_RET_STS_ERROR;
315 END IF;
316 CLOSE C_CV_Label;
317
318 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
319 RAISE Fnd_Api.G_EXC_ERROR;
320 END IF;
321
322 -- -- Validate Object Version Number
323 -- IF l_CV_Label_rec.OBJECT_VERSION_NUMBER IS NULL THEN
324 -- l_CV_Label_rec.OBJECT_VERSION_NUMBER := FND_API.G_MISS_NUM;
325 -- END IF;
326
327 --
328 -- Table Handler to Update Row into IBC_CV_LabelS
329 --
330 Ibc_Citem_Version_Labels_Pkg.UPDATE_ROW (
331 p_content_item_id => l_CV_Label_rec.content_item_id
332 ,p_Label_code => l_CV_Label_rec.Label_code
333 ,p_citem_version_id => l_CV_Label_rec.citem_version_id
334 ,p_LAST_UPDATED_BY => l_CV_Label_rec.LAST_UPDATED_BY
335 ,p_LAST_UPDATE_DATE => l_CV_Label_rec.LAST_UPDATE_DATE
336 ,p_LAST_UPDATE_LOGIN => l_CV_Label_rec.LAST_UPDATE_LOGIN
337 ,p_OBJECT_VERSION_NUMBER => l_CV_Label_rec.OBJECT_VERSION_NUMBER);
338
339
340
341 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
342 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
343 Fnd_Message.Set_Name('IBC', 'IBC_UPDATE_ERROR');
344 Fnd_Msg_Pub.ADD;
345 END IF;
346
347 IF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR) THEN
348 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
349 ELSIF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
350 RAISE Fnd_Api.G_EXC_ERROR;
351 END IF;
352 END IF;
353
354 --
355 -- End of API body
356 --
357
358 -- Standard check for p_commit
359 IF Fnd_Api.to_Boolean( p_commit )
360 THEN
361 COMMIT WORK;
362 END IF;
363
364 -- Standard call to get message count and if count is 1, get message info.
365 Fnd_Msg_Pub.Count_And_Get
366 ( p_count => x_msg_count,
367 p_data => x_msg_data
368 );
369
370
371 EXCEPTION
372 WHEN Fnd_Api.G_EXC_ERROR THEN
373 ROLLBACK;
374 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
375 P_API_NAME => L_API_NAME
376 ,P_PKG_NAME => G_PKG_NAME
377 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
378 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
379 ,P_SQLCODE => SQLCODE
380 ,P_SQLERRM => SQLERRM
381 ,X_MSG_COUNT => X_MSG_COUNT
382 ,X_MSG_DATA => X_MSG_DATA
383 ,X_RETURN_STATUS => X_RETURN_STATUS);
384
385 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
386 ROLLBACK;
387 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
388 P_API_NAME => L_API_NAME
389 ,P_PKG_NAME => G_PKG_NAME
390 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
391 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
392 ,P_SQLCODE => SQLCODE
393 ,P_SQLERRM => SQLERRM
394 ,X_MSG_COUNT => X_MSG_COUNT
395 ,X_MSG_DATA => X_MSG_DATA
396 ,X_RETURN_STATUS => X_RETURN_STATUS);
397
398 WHEN OTHERS THEN
399 ROLLBACK;
400 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
401 P_API_NAME => L_API_NAME
402 ,P_PKG_NAME => G_PKG_NAME
403 ,P_EXCEPTION_LEVEL => Ibc_Utilities_Pvt.G_EXC_OTHERS
404 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
405 ,P_SQLCODE => SQLCODE
406 ,P_SQLERRM => SQLERRM
407 ,X_MSG_COUNT => X_MSG_COUNT
408 ,X_MSG_DATA => X_MSG_DATA
409 ,X_RETURN_STATUS => X_RETURN_STATUS);
410
411 END Update_CV_Label;
412
413
417 P_Commit IN VARCHAR2 ,--:= Fnd_Api.G_FALSE,
414 PROCEDURE delete_CV_Label(
415 P_Api_Version_Number IN NUMBER,
416 P_Init_Msg_List IN VARCHAR2 ,--:= Fnd_Api.G_FALSE,
418 P_Validation_Level IN NUMBER ,--:= Fnd_Api.G_VALID_LEVEL_FULL,
419 P_Label_Code IN VARCHAR2,
420 P_content_item_id IN NUMBER,
421 X_Return_Status OUT NOCOPY VARCHAR2,
422 X_Msg_Count OUT NOCOPY NUMBER,
423 X_Msg_Data OUT NOCOPY VARCHAR2
424 )
425 IS
426
427 CURSOR C_CV_Label IS
428 SELECT
429 label_code
430 FROM IBC_CITEM_VERSION_LABELS
431 WHERE Label_Code = P_Label_Code
432 AND content_item_id = P_content_item_id;
433
434 l_temp CHAR(1);
435 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
436
437 l_api_version_number NUMBER := 1.0;
438 l_api_name VARCHAR2(50) := 'Delete_CV_Label';
439 l_CV_Label_Code VARCHAR2(100);
440 lx_rowid VARCHAR2(240);
441
442 lx_CV_Label_REL_ID NUMBER;
443
444
445 BEGIN
446
447 -- Initialize API return status to SUCCESS
448 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
449
450 --
451 -- API body
452 --
453
454 -- ******************************************************************
455 -- Validate Environment
456 -- ******************************************************************
457 IF Fnd_Global.User_Id IS NULL
458 THEN
459 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
460 THEN
461 Fnd_Message.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
462 Fnd_Message.Set_Token('PROFILE', 'USER_ID', FALSE);
463 Fnd_Msg_Pub.ADD;
464 END IF;
465 RAISE Fnd_Api.G_EXC_ERROR;
466 END IF;
467
468
469 --
470 -- Table Handler to Insert Row into IBC_CV_LabelS
471 --
472 Ibc_Citem_Version_Labels_Pkg.DELETE_ROW (
473 p_content_item_id => p_content_item_id
474 ,p_Label_code => p_Label_code);
475
476
477
478 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
479 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
480 Fnd_Message.Set_Name('IBC', 'IBC_DELETE_ERROR');
481 Fnd_Msg_Pub.ADD;
482 END IF;
483
484 IF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR) THEN
485 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
486 ELSIF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
487 RAISE Fnd_Api.G_EXC_ERROR;
488 END IF;
489 END IF;
490
491 --
492 -- End of API body
493 --
494
495 -- Standard check for p_commit
496 IF Fnd_Api.to_Boolean( p_commit )
497 THEN
498 COMMIT WORK;
499 END IF;
500
501 -- Standard call to get message count and if count is 1, get message info.
502 Fnd_Msg_Pub.Count_And_Get
503 ( p_count => x_msg_count,
504 p_data => x_msg_data
505 );
506
507
508 EXCEPTION
509 WHEN Fnd_Api.G_EXC_ERROR THEN
510 ROLLBACK;
511 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
512 P_API_NAME => L_API_NAME
513 ,P_PKG_NAME => G_PKG_NAME
514 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
515 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
516 ,P_SQLCODE => SQLCODE
517 ,P_SQLERRM => SQLERRM
518 ,X_MSG_COUNT => X_MSG_COUNT
519 ,X_MSG_DATA => X_MSG_DATA
520 ,X_RETURN_STATUS => X_RETURN_STATUS);
521
522 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
523 ROLLBACK;
524 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
525 P_API_NAME => L_API_NAME
526 ,P_PKG_NAME => G_PKG_NAME
527 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
528 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
529 ,P_SQLCODE => SQLCODE
530 ,P_SQLERRM => SQLERRM
531 ,X_MSG_COUNT => X_MSG_COUNT
532 ,X_MSG_DATA => X_MSG_DATA
533 ,X_RETURN_STATUS => X_RETURN_STATUS);
534
535 WHEN OTHERS THEN
536 ROLLBACK;
537 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
538 P_API_NAME => L_API_NAME
539 ,P_PKG_NAME => G_PKG_NAME
540 ,P_EXCEPTION_LEVEL => Ibc_Utilities_Pvt.G_EXC_OTHERS
541 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
542 ,P_SQLCODE => SQLCODE
543 ,P_SQLERRM => SQLERRM
544 ,X_MSG_COUNT => X_MSG_COUNT
545 ,X_MSG_DATA => X_MSG_DATA
546 ,X_RETURN_STATUS => X_RETURN_STATUS);
547
548 END delete_CV_Label;
549
550 FUNCTION Query_CV_Label_Row (
551 p_content_item_id IN NUMBER,
552 p_Label_code IN VARCHAR2
553 ) RETURN Ibc_Cv_Label_Grp.CV_Label_Rec_Type
554 IS
555 l_CV_Label_Rec Ibc_Cv_Label_Grp.CV_Label_Rec_Type;
556 BEGIN
557
558 SELECT
559 CONTENT_ITEM_ID
560 ,CITEM_VERSION_ID
561 ,LABEL_CODE
562 ,CREATED_BY
563 ,CREATION_DATE
564 ,LAST_UPDATED_BY
565 ,LAST_UPDATE_DATE
566 ,LAST_UPDATE_LOGIN
567 ,OBJECT_VERSION_NUMBER
568 INTO
569 l_CV_Label_Rec.CONTENT_ITEM_ID
570 ,l_CV_Label_Rec.CITEM_VERSION_ID
571 ,l_CV_Label_Rec.LABEL_CODE
572 ,l_CV_Label_Rec.CREATED_BY
573 ,l_CV_Label_Rec.CREATION_DATE
574 ,l_CV_Label_Rec.LAST_UPDATED_BY
575 ,l_CV_Label_Rec.LAST_UPDATE_DATE
576 ,l_CV_Label_Rec.LAST_UPDATE_LOGIN
577 ,l_CV_Label_Rec.OBJECT_VERSION_NUMBER
578 FROM IBC_CITEM_VERSION_LABELS
579 WHERE CONTENT_ITEM_ID = p_content_item_id
580 AND LABEL_CODE = p_label_code;
581
582 RETURN l_CV_Label_rec;
583
584 EXCEPTION
585 WHEN NO_DATA_FOUND THEN
586 RAISE NO_DATA_FOUND;
587 WHEN OTHERS THEN
588 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
589 Fnd_Message.Set_Name('IBC', 'CItem Version Label RECORD Error');
590 Fnd_Msg_Pub.ADD;
591 END IF;
592 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
593 END Query_CV_Label_Row;
594
595 FUNCTION get_CV_Label_rec RETURN Ibc_Cv_Label_Grp.CV_Label_rec_type
596 IS
597 TMP_REC Ibc_Cv_Label_Grp.CV_Label_rec_type;
598 BEGIN
599 RETURN TMP_REC;
600 END get_CV_Label_rec;
601
602 --
603 -- Upsert into CV Labels Table
604 --
605 PROCEDURE Upsert_Cv_Labels(
606 p_label_code IN VARCHAR2
607 ,p_content_item_ids IN JTF_NUMBER_TABLE
608 ,p_citem_version_ids IN JTF_NUMBER_TABLE
609 ,p_version_number IN JTF_NUMBER_TABLE
610 ,p_commit IN VARCHAR2
611 ,p_api_version_number IN NUMBER
612 ,p_init_msg_list IN VARCHAR2 --DEFAULT Fnd_Api.G_FALSE
613 ,x_return_status OUT NOCOPY VARCHAR2
614 ,x_msg_count OUT NOCOPY NUMBER
615 ,x_msg_data OUT NOCOPY VARCHAR2
616 )
617 IS
618
619 l_api_name CONSTANT VARCHAR2(30) := 'UPSERT_CV_LABELS';
620 l_api_version_number CONSTANT NUMBER := 1; --G_API_VERSION_DEFAULT;
621
622 lx_rowid VARCHAR2(240);
623 l_ins_content_item_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
627 l_citem_version_ids JTF_NUMBER_TABLE := p_citem_version_ids;
624 l_ins_citem_version_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
625 l_cnt INTEGER := 1;
626 l_temp INTEGER := 1;
628
629
630 BEGIN
631
632 --DBMS_OUTPUT.put_line('----- ' || l_api_name || ' -----');
633
634
635 SAVEPOINT SVPT_UPSERT_CV_LABELS;
636
637 IF (p_init_msg_list = Fnd_Api.g_true) THEN --|**|
638 Fnd_Msg_Pub.initialize; --|**|
639 END IF; --|**|
640 --|**|
641 -- Standard call to check for call compatibility. --|**|
642 IF NOT Fnd_Api.Compatible_API_Call ( --|**|
643 l_api_version_number --|**|
644 ,p_api_version_number --|**|
645 ,l_api_name --|**|
646 ,G_PKG_NAME --|**|
647 )THEN --|**|
648 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR; --|**|
649 END IF; --|**|
650 --|**|
651 -- Initialize API return status to SUCCESS --|**|
652 x_return_status := Fnd_Api.G_RET_STS_SUCCESS; --|**|
653
654
655 IF l_citem_version_ids.COUNT <> 0 THEN
656 BEGIN
657 --
658 -- Validate and make sure that the content_item_id and citem_version_id
659 -- Exists in database
660 --
661 FOR i IN p_content_item_ids.FIRST..p_content_item_ids.LAST
662 LOOP
663 SELECT '1' INTO l_temp FROM ibc_citem_versions_b
664 WHERE content_item_id = p_content_item_ids(i)
665 AND citem_version_id = l_citem_version_ids(i);
666 END LOOP;
667 EXCEPTION WHEN NO_DATA_FOUND THEN
668 --DBMS_OUTPUT.put_line('CitemVersionId Invalid....');
669 x_return_status := Fnd_Api.G_RET_STS_ERROR;
670 Fnd_Message.Set_Name('IBC', 'API_INVALID_ID');
671 Fnd_Message.Set_Token('COLUMN','p_citem_version_ids', FALSE);
672 Fnd_Message.Set_Token('VALUE','p_citem_version_ids(i)', FALSE);
673 Fnd_Msg_Pub.ADD;
674 RAISE Fnd_Api.G_EXC_ERROR;
675 END;
676 ELSIF p_version_number.COUNT <> 0 THEN
677 BEGIN
678 -- get the citem_version id for all the content item id and
679 -- the version numbers into l_citem_version_id
680 --
681 FOR i IN p_content_item_ids.FIRST..p_content_item_ids.LAST
682 LOOP
683 l_citem_version_ids.extend;
684 SELECT citem_version_id INTO l_citem_version_ids(i)
685 FROM ibc_citem_versions_b
686 WHERE content_item_id = p_content_item_ids(i)
687 AND version_number = p_version_number(i);
688 END LOOP;
689 EXCEPTION WHEN NO_DATA_FOUND THEN
690 --DBMS_OUTPUT.put_line('CitemVersionId Invalid....');
691 x_return_status := Fnd_Api.G_RET_STS_ERROR;
692 Fnd_Message.Set_Name('IBC', 'API_INVALID_ID');
693 Fnd_Message.Set_Token('COLUMN','p_version_number', FALSE);
694 Fnd_Message.Set_Token('VALUE','p_version_number(i)',FALSE);
695 Fnd_Msg_Pub.ADD;
696 RAISE Fnd_Api.G_EXC_ERROR;
697 END;
698 END IF;
699
700 --
701 -- Validate if the Label Code passed exists in the Database
702 --
703 BEGIN
704 SELECT '1' INTO l_temp FROM IBC_LABELS_B
705 WHERE label_code = p_label_code;
706 EXCEPTION WHEN NO_DATA_FOUND THEN
707 --DBMS_OUTPUT.put_line('Label Code is Invalid....');
708 x_return_status := Fnd_Api.G_RET_STS_ERROR;
709 Fnd_Message.Set_Name('IBC', 'API_INVALID_ID');
710 Fnd_Message.Set_Token('COLUMN','p_label_code', FALSE);
711 Fnd_Message.Set_Token('VALUE',p_label_code, FALSE);
712 Fnd_Msg_Pub.ADD;
713 RAISE Fnd_Api.G_EXC_ERROR;
714 END;
715
716 --
717 -- Update
718 FORALL i IN p_content_item_ids.FIRST..p_content_item_ids.LAST
719 UPDATE IBC_CITEM_VERSION_LABELS SET
720 CITEM_VERSION_ID = l_citem_version_ids(i),
721 OBJECT_VERSION_NUMBER = 1,
722 LAST_UPDATE_DATE = SYSDATE,
723 LAST_UPDATED_BY = Fnd_Global.user_id,
724 LAST_UPDATE_LOGIN = Fnd_Global.login_id
725 WHERE label_code = p_label_code
726 AND content_item_id = p_content_item_ids(i);
727
728 --DBMS_OUTPUT.put_line('Update Successful....');
729 --
730 -- Collect all those that could not be updated
731 -- Will insert them.
732 FOR i IN p_content_item_ids.FIRST..p_content_item_ids.LAST
733 LOOP
734 IF SQL%BULK_ROWCOUNT(i) = 0 THEN
735 l_ins_content_item_ids.extend;
736 l_ins_citem_version_ids.extend;
737 l_ins_content_item_ids(l_cnt) := p_content_item_ids(i);
738 l_ins_citem_version_ids(l_cnt) := l_citem_version_ids(i);
739 l_cnt := l_cnt + 1;
740 END IF;
741 END LOOP;
742
743 --DBMS_OUTPUT.put_line('Bulk Count ....');
744
745 -- Insert
746 --
747 IF l_ins_content_item_ids.EXISTS(l_ins_content_item_ids.FIRST) THEN
748
749 FORALL i IN l_ins_content_item_ids.FIRST..l_ins_content_item_ids.LAST
750 INSERT INTO IBC_CITEM_VERSION_LABELS (
751 CONTENT_ITEM_ID,
752 LABEL_CODE,
753 CITEM_VERSION_ID,
754 OBJECT_VERSION_NUMBER,
755 CREATION_DATE,
756 CREATED_BY,
757 LAST_UPDATE_DATE,
758 LAST_UPDATED_BY,
759 LAST_UPDATE_LOGIN
763 l_ins_citem_version_ids(i),
760 ) VALUES (
761 l_ins_content_item_ids(i),
762 p_LABEL_CODE,
764 1,
765 SYSDATE,
766 Fnd_Global.user_id,
767 SYSDATE,
768 Fnd_Global.user_id,
769 Fnd_Global.login_id
770 );
771
772 END IF;
773
774
775
776 -- COMMIT?
777 IF ( (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) AND (p_commit = Fnd_Api.g_true) ) THEN
778 COMMIT;
779 END IF;
780
781 -- Standard call to get message count and if count is 1, get message info.
782 Fnd_Msg_Pub.Count_And_Get(
783 p_count => x_msg_count,
784 p_data => x_msg_data
785 );
786
787 EXCEPTION
788 WHEN Fnd_Api.G_EXC_ERROR THEN
789 ROLLBACK TO SVPT_UPSERT_CV_LABELS;
790 --DBMS_OUTPUT.put_line('Expected Error');
791 Ibc_Utilities_Pvt.handle_exceptions(
792 p_api_name => L_API_NAME
793 ,p_pkg_name => G_PKG_NAME
794 ,p_exception_level => Fnd_Msg_Pub.G_MSG_LVL_ERROR
795 ,p_package_type => Ibc_Utilities_Pvt.G_PVT
796 ,p_sqlcode => SQLCODE
797 ,p_sqlerrm => SQLERRM
798 ,x_msg_count => x_msg_count
799 ,x_msg_data => x_msg_data
800 ,x_return_status => x_return_status
801 );
802
803 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
804 ROLLBACK TO SVPT_UPSERT_CV_LABELS;
805 --DBMS_OUTPUT.put_line('Unexpected error');
806 Ibc_Utilities_Pvt.handle_exceptions(
807 p_api_name => L_API_NAME
808 ,p_pkg_name => G_PKG_NAME
809 ,p_exception_level => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
810 ,p_package_type => Ibc_Utilities_Pvt.G_PVT
811 ,p_sqlcode => SQLCODE
812 ,p_sqlerrm => SQLERRM
813 ,x_msg_count => x_msg_count
814 ,x_msg_data => x_msg_data
815 ,x_return_status => x_return_status
816 );
817
818 WHEN OTHERS THEN
819 ROLLBACK TO SVPT_UPSERT_CV_LABELS;
820 --DBMS_OUTPUT.put_line('Other error');
821 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
822 p_api_name => L_API_NAME
823 ,p_pkg_name => G_PKG_NAME
824 ,p_exception_level => Ibc_Utilities_Pvt.G_EXC_OTHERS
825 ,p_package_type => Ibc_Utilities_Pvt.G_PVT
826 ,p_sqlcode => SQLCODE
827 ,p_sqlerrm => SQLERRM
828 ,x_msg_count => x_msg_count
829 ,x_msg_data => x_msg_data
830 ,x_return_status => x_return_status
831 );
832 END Upsert_Cv_Labels;
833
834 --
835 -- Upsert into CV Labels Table
836 --
837 PROCEDURE Upsert_Cv_Labels(
838 p_label_code IN VARCHAR2
839 ,p_content_item_ids IN JTF_NUMBER_TABLE
840 ,p_citem_version_ids IN JTF_NUMBER_TABLE
841 ,p_commit IN VARCHAR2
842 ,p_api_version_number IN NUMBER
843 ,p_init_msg_list IN VARCHAR2 --DEFAULT Fnd_Api.G_FALSE
844 ,x_return_status OUT NOCOPY VARCHAR2
845 ,x_msg_count OUT NOCOPY NUMBER
846 ,x_msg_data OUT NOCOPY VARCHAR2
847 )
848 IS
849
850
851
852
853 BEGIN
854
855 --
856 -- Validate and make sure that the content_item_id and citem_version_id
857 -- Exists in database
858 --
859
860 Upsert_Cv_Labels(
861 p_label_code => p_label_code
862 ,p_content_item_ids => p_content_item_ids
863 ,p_citem_version_ids => p_citem_version_ids
864 ,p_version_number => JTF_NUMBER_TABLE()
865 ,p_commit => p_commit
866 ,p_api_version_number => p_api_version_number
867 ,p_init_msg_list => p_init_msg_list
868 ,x_return_status => x_return_status
869 ,x_msg_count => x_msg_count
870 ,x_msg_data => x_msg_data);
871
872
873 END Upsert_Cv_Labels;
874
875
876 --
877 -- Upsert into CV Labels Table
878 --
879 PROCEDURE Upsert_Cv_Labels(
880 p_label_code IN VARCHAR2
881 ,p_content_item_ids IN JTF_NUMBER_TABLE
882 ,p_version_number IN JTF_NUMBER_TABLE
883 ,p_commit IN VARCHAR2
884 ,p_api_version_number IN NUMBER
885 ,p_init_msg_list IN VARCHAR2 --DEFAULT Fnd_Api.G_FALSE
886 ,x_return_status OUT NOCOPY VARCHAR2
887 ,x_msg_count OUT NOCOPY NUMBER
888 ,x_msg_data OUT NOCOPY VARCHAR2
889 )
890 IS
891
892 l_citem_version_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
893
894
895 BEGIN
896
897 --
898 -- Validate and make sure that the content_item_id and citem_version_id
899 -- Exists in database
900 --
901
902 Upsert_Cv_Labels(
903 p_label_code => p_label_code
904 ,p_content_item_ids => p_content_item_ids
905 ,p_version_number => p_version_number
906 ,p_citem_version_ids => JTF_NUMBER_TABLE()
907 ,p_commit => p_commit
908 ,p_api_version_number => p_api_version_number
909 ,p_init_msg_list => p_init_msg_list
910 ,x_return_status => x_return_status
911 ,x_msg_count => x_msg_count
912 ,x_msg_data => x_msg_data);
913
917 -- Delete into CV Labels Table
914 END Upsert_Cv_Labels;
915
916 --
918 --
919 PROCEDURE Delete_Cv_Labels(
920 p_label_code IN VARCHAR2
921 ,p_content_item_ids IN JTF_NUMBER_TABLE
922 ,p_commit IN VARCHAR2
923 ,p_api_version_number IN NUMBER
924 ,p_init_msg_list IN VARCHAR2 --DEFAULT Fnd_Api.G_FALSE
925 ,x_return_status OUT NOCOPY VARCHAR2
926 ,x_msg_count OUT NOCOPY NUMBER
927 ,x_msg_data OUT NOCOPY VARCHAR2
928 )
929 IS
930
931 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CV_LABELS';
932 l_api_version_number CONSTANT NUMBER := 1; --G_API_VERSION_DEFAULT;
933
934 BEGIN
935
936 --DBMS_OUTPUT.put_line('----- ' || l_api_name || ' -----');
937
938
939 SAVEPOINT SVPT_DELETE_CV_LABELS;
940
941 IF (p_init_msg_list = Fnd_Api.g_true) THEN --|**|
942 Fnd_Msg_Pub.initialize; --|**|
943 END IF; --|**|
944 --|**|
945 -- Standard call to check for call compatibility. --|**|
946 IF NOT Fnd_Api.Compatible_API_Call ( --|**|
947 l_api_version_number --|**|
948 ,p_api_version_number --|**|
949 ,l_api_name --|**|
950 ,G_PKG_NAME --|**|
951 )THEN --|**|
952 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR; --|**|
953 END IF; --|**|
954 --|**|
955 -- Initialize API return status to SUCCESS --|**|
956 x_return_status := Fnd_Api.G_RET_STS_SUCCESS; --|**|
957
958
959 --
960 -- Delete
961 FORALL i IN p_content_item_ids.FIRST..p_content_item_ids.LAST
962 DELETE FROM IBC_CITEM_VERSION_LABELS
963 WHERE label_code = p_label_code
964 AND content_item_id = p_content_item_ids(i);
965
966 --DBMS_OUTPUT.put_line('Delete Successful....');
967
968 -- COMMIT?
969 IF ( (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) AND (p_commit = Fnd_Api.g_true) ) THEN
970 COMMIT;
971 END IF;
972
973 -- Standard call to get message count and if count is 1, get message info.
974 Fnd_Msg_Pub.Count_And_Get(
975 p_count => x_msg_count,
976 p_data => x_msg_data
977 );
978
979 EXCEPTION
980 WHEN Fnd_Api.G_EXC_ERROR THEN
981 ROLLBACK TO SVPT_UPSERT_CV_LABELS;
982 --DBMS_OUTPUT.put_line('Expected Error');
983 Ibc_Utilities_Pvt.handle_exceptions(
984 p_api_name => L_API_NAME
985 ,p_pkg_name => G_PKG_NAME
986 ,p_exception_level => Fnd_Msg_Pub.G_MSG_LVL_ERROR
987 ,p_package_type => Ibc_Utilities_Pvt.G_PVT
988 ,p_sqlcode => SQLCODE
989 ,p_sqlerrm => SQLERRM
990 ,x_msg_count => x_msg_count
991 ,x_msg_data => x_msg_data
992 ,x_return_status => x_return_status
993 );
994
995 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
996 ROLLBACK TO SVPT_UPSERT_CV_LABELS;
997 --DBMS_OUTPUT.put_line('Unexpected error');
998 Ibc_Utilities_Pvt.handle_exceptions(
999 p_api_name => L_API_NAME
1000 ,p_pkg_name => G_PKG_NAME
1001 ,p_exception_level => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
1002 ,p_package_type => Ibc_Utilities_Pvt.G_PVT
1003 ,p_sqlcode => SQLCODE
1004 ,p_sqlerrm => SQLERRM
1005 ,x_msg_count => x_msg_count
1006 ,x_msg_data => x_msg_data
1007 ,x_return_status => x_return_status
1008 );
1009
1010 WHEN OTHERS THEN
1011 ROLLBACK TO SVPT_UPSERT_CV_LABELS;
1012 --DBMS_OUTPUT.put_line('Other error');
1013 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
1014 p_api_name => L_API_NAME
1015 ,p_pkg_name => G_PKG_NAME
1016 ,p_exception_level => Ibc_Utilities_Pvt.G_EXC_OTHERS
1017 ,p_package_type => Ibc_Utilities_Pvt.G_PVT
1018 ,p_sqlcode => SQLCODE
1019 ,p_sqlerrm => SQLERRM
1020 ,x_msg_count => x_msg_count
1021 ,x_msg_data => x_msg_data
1022 ,x_return_status => x_return_status
1023 );
1024 END Delete_Cv_Labels;
1025
1026
1027 END Ibc_Cv_Label_Grp;