[Home] [Help]
PACKAGE BODY: APPS.IBC_LABELS_GRP
Source
1 PACKAGE BODY Ibc_Labels_GRP AS
2 /* $Header: ibcglabb.pls 115.3 2002/11/13 23:45:35 vicho ship $ */
3
4 -- Purpose: API to Populate Association Type.
5
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- Sri Rangarajan 01/06/2002 Created Package
10
11
12 -- Package name : Ibc_Labels_GRP
13 -- Purpose :
14 -- History :
15 -- NOTE :
16 -- End of Comments
17
18
19 G_PKG_NAME CONSTANT VARCHAR2(30):= 'Ibc_Labels_GRP';
20 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ibcglabb.pls';
21
22 FUNCTION Query_Label_Row (
23 p_Label_code IN VARCHAR2
24 ) RETURN Ibc_Labels_GRP.Label_Rec_Type;
25
26
27 PROCEDURE Update_Label(
28 P_Api_Version_Number IN NUMBER,
29 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
30 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
31 P_Validation_Level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
32 P_Label_Rec IN Ibc_Labels_GRP.Label_Rec_Type,
33 X_Return_Status OUT NOCOPY VARCHAR2,
34 X_Msg_Count OUT NOCOPY NUMBER,
35 X_Msg_Data OUT NOCOPY VARCHAR2
36 );
37
38
39 FUNCTION IsLabelRecordEmpty(
40 P_Label_Rec IN Ibc_Labels_GRP.Label_Rec_Type)
41 RETURN BOOLEAN IS
42
43 BEGIN
44
45 IF ((p_Label_rec.Label_code IS NULL
46 OR p_Label_rec.Label_code = FND_API.G_MISS_CHAR)
47 AND (p_Label_rec.Label_code IS NULL
48 OR p_Label_rec.Label_code = FND_API.G_MISS_CHAR))
49 THEN
50
51 RETURN TRUE;
52
53 ELSE
54 RETURN FALSE;
55
56 END IF;
57
58 END IsLabelRecordEmpty;
59
60
61 PROCEDURE Validate_Label_Tbl(
62 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
63 p_Label_code IN VARCHAR2,
64 P_Label_Tbl IN Ibc_Labels_GRP.Label_Tbl_Type,
65 X_Return_Status OUT NOCOPY VARCHAR2,
66 X_Msg_Count OUT NOCOPY NUMBER,
67 X_Msg_Data OUT NOCOPY VARCHAR2)
68 IS
69
70 l_Label_rec Ibc_Labels_GRP.Label_Rec_Type;
71 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
72
73 BEGIN
74
75 -- Initialize API return status to success
76 x_return_status := FND_API.G_RET_STS_SUCCESS;
77
78
79 IF p_Label_tbl.COUNT = 0 THEN
80 RETURN;
81 END IF;
82
83 FOR i IN p_Label_tbl.FIRST..p_Label_tbl.LAST LOOP
84
85 IF p_Label_tbl.EXISTS(i) AND NOT IsLabelRecordEmpty(p_Label_tbl(i))
86 THEN
87
88 l_Label_rec := p_Label_tbl(i);
89
90 -- Check for all the NOT NULL Columns
91 -- Label_Code Cannot be NULL
92 IBC_VALIDATE_PVT.VALidate_NotNULL_VARCHAR2 (
93 p_init_msg_list => FND_API.G_FALSE,
94 p_column_name => 'Label_Code',
95 p_notnull_column=> l_Label_rec.Label_code,
96 x_return_status => x_return_status,
97 x_msg_count => x_msg_count,
98 x_msg_data => x_msg_data);
99
100 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
101 l_return_status := x_return_status;
102 END IF;
103
104
105 --
106 -- Validate the Association Type Code in Association Rec
107 IF (l_Label_rec.Label_code IS NULL OR
108 l_Label_rec.Label_code = FND_API.G_MISS_CHAR) THEN
109 l_Label_rec.Label_code := p_Label_CODE;
110 ELSE
111 IF l_Label_rec.Label_code <> p_Label_CODE THEN
112 x_return_status := FND_API.G_RET_STS_ERROR;
113 FND_MESSAGE.Set_Name('IBC', 'Invalid Association TYPE');
114 FND_MESSAGE.Set_Token('COLUMN', 'Label_Code', FALSE);
115 FND_MSG_PUB.ADD;
116 END IF;
117 END IF;
118
119 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
120 l_return_status := x_return_status;
121 END IF;
122
123
124 -- Label_Name Cannot be NULL
125 IBC_VALIDATE_PVT.VALidate_NotNULL_VARCHAR2 (
126 p_init_msg_list => FND_API.G_FALSE,
127 p_column_name => 'Label_Name',
128 p_notnull_column=> l_Label_rec.Label_Name,
129 x_return_status => x_return_status,
130 x_msg_count => x_msg_count,
131 x_msg_data => x_msg_data);
132 END IF;
133
134 END LOOP;
135
136 x_return_status := l_return_status;
137
138 END validate_Label_tbl;
139
140
141 PROCEDURE Create_Label(
142 P_Api_Version_Number IN NUMBER,
143 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
144 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
145 P_Validation_Level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
146 P_Label_Rec IN Ibc_Labels_GRP.Label_Rec_Type,
147 X_Return_Status OUT NOCOPY VARCHAR2,
148 X_Msg_Count OUT NOCOPY NUMBER,
149 X_Msg_Data OUT NOCOPY VARCHAR2
150 )
151 IS
152
153 CURSOR C_Label(p_Label_Code IN VARCHAR2) IS
154 SELECT Label_Code
155 FROM ibc_Labels_b
156 WHERE Label_Code = p_Label_Code;
157
158 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
159
160 l_api_version_number NUMBER := 1.0;
161 l_api_name VARCHAR2(50) := 'Create_Label';
162 lx_rowid VARCHAR2(240);
163 l_Label_code VARCHAR2(100);
164
165 l_Label_rec Ibc_Labels_GRP.Label_Rec_Type := P_Label_Rec;
166
167 BEGIN
168
169 -- Initialize API return status to SUCCESS
170 x_return_status := FND_API.G_RET_STS_SUCCESS;
171
172 -- Begin Validation for Association Type Record
173
174 -- Check for all the NOT NULL Columns
175 -- Label_Code Cannot be NULL
176 IBC_VALIDATE_PVT.VALidate_NotNULL_VARCHAR2 (
177 p_init_msg_list => FND_API.G_FALSE,
178 p_column_name => 'Label_Code',
179 p_notnull_column=> l_Label_rec.Label_code,
180 x_return_status => x_return_status,
181 x_msg_count => x_msg_count,
182 x_msg_data => x_msg_data);
183
184 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
185 l_return_status := x_return_status;
186 END IF;
187
188 -- Label_Name Cannot be NULL
189 IBC_VALIDATE_PVT.VALidate_NotNULL_VARCHAR2 (
190 p_init_msg_list => FND_API.G_FALSE,
191 p_column_name => 'Label_Name',
192 p_notnull_column=> l_Label_rec.Label_Name,
193 x_return_status => x_return_status,
194 x_msg_count => x_msg_count,
195 x_msg_data => x_msg_data);
196
197 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
198 l_return_status := x_return_status;
199 END IF;
200
201 -- Validate Object Version Number
202 IF l_Label_rec.OBJECT_VERSION_NUMBER IS NULL
203 OR l_Label_rec.OBJECT_VERSION_NUMBER = FND_API.G_MISS_NUM THEN
204 l_Label_rec.OBJECT_VERSION_NUMBER := 1;
205 END IF;
206
207 -- Check for Uniqueness
208 OPEN C_Label(p_Label_Code => l_Label_rec.Label_code);
209 FETCH C_Label INTO l_Label_Code;
210 IF C_Label%FOUND THEN
211 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
212 x_return_status := FND_API.G_RET_STS_ERROR;
213 FND_MESSAGE.Set_Name('IBC', 'RECORD Already EXISTS');
214 FND_MESSAGE.Set_Token('COLUMN', 'Label_Code',FALSE);
215 FND_MSG_PUB.ADD;
216 END IF;
217 END IF;
218
219 CLOSE C_Label;
220
221 IF l_return_status<>FND_API.G_RET_STS_SUCCESS
222 OR x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
223 RAISE FND_API.G_EXC_ERROR;
224 END IF;
225
226
227 Ibc_Labels_Pkg.insert_row (
228 x_rowid => lx_rowid,
229 p_Label_CODE => l_Label_rec.Label_CODE,
230 p_Label_NAME => l_Label_rec.Label_NAME,
231 p_CREATED_BY => l_Label_rec.CREATED_BY,
232 p_CREATION_DATE => l_Label_rec.CREATION_DATE,
233 p_DESCRIPTION => l_Label_rec.DESCRIPTION,
234 p_LAST_UPDATED_BY => l_Label_rec.LAST_UPDATED_BY,
235 p_LAST_UPDATE_DATE => l_Label_rec.LAST_UPDATE_DATE,
236 p_LAST_UPDATE_LOGIN => l_Label_rec.LAST_UPDATE_LOGIN,
237 p_OBJECT_VERSION_NUMBER => l_Label_rec.OBJECT_VERSION_NUMBER
238 );
239
240
241 END create_Label;
242
243
244 PROCEDURE Create_Labels(
245 P_Api_Version_Number IN NUMBER,
246 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
247 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
248 P_Validation_Level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
249 P_Label_Tbl IN Ibc_Labels_GRP.Label_Tbl_Type := Ibc_Labels_GRP.G_Miss_Label_Tbl,
250 x_Label_Tbl OUT NOCOPY Ibc_Labels_GRP.Label_Tbl_Type,
251 X_Return_Status OUT NOCOPY VARCHAR2,
252 X_Msg_Count OUT NOCOPY NUMBER,
253 X_Msg_Data OUT NOCOPY VARCHAR2
254 )
255 IS
256 l_temp CHAR(1);
257 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
258
259 l_api_version_number NUMBER := 1.0;
260 l_api_name VARCHAR2(50) := 'Create_Label';
261 l_Label_Code VARCHAR2(100);
262 lx_rowid VARCHAR2(240);
263
264 l_Label_Tbl Ibc_Labels_GRP.Label_Tbl_Type := p_Label_Tbl;
265 l_Label_rec Ibc_Labels_GRP.Label_Rec_Type;
266
267 BEGIN
268
269 -- Initialize API return status to SUCCESS
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271
272 --
273 -- API body
274 --
275
276 -- ******************************************************************
277 -- Validate Environment
278 -- ******************************************************************
279 IF FND_GLOBAL.User_Id IS NULL
280 THEN
281 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
282 THEN
283 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
284 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
285 FND_MSG_PUB.ADD;
286 END IF;
287 RAISE FND_API.G_EXC_ERROR;
288 END IF;
289
290 --dbms_output.put_line('Insert into Association Type Success');
291 -- Insert The Corresponding Associations in ibc_Labels_b table
292
293 IF l_Label_tbl.COUNT <> 0 THEN
294 FOR i IN l_Label_tbl.FIRST..l_Label_tbl.LAST LOOP
295
296 IF l_Label_tbl.EXISTS(i) AND NOT IsLabelRecordEmpty(l_Label_Tbl(i))
297 THEN
298
299 -- l_Label_Tbl(i).Label_code := l_Label_rec.Label_CODE;
300
301 Create_Label(
302 P_Api_Version_Number =>P_Api_Version_Number,
303 P_Init_Msg_List =>P_Init_Msg_List,
304 P_Commit =>P_Commit,
305 P_Validation_Level =>FND_API.G_VALID_LEVEL_FULL,
306 P_Label_Rec =>l_Label_Tbl(i),
307 X_Return_Status =>X_Return_Status,
308 X_Msg_Count =>X_Msg_Count,
309 X_Msg_Data =>X_Msg_Data);
310
311 x_Label_Tbl(i) := Query_Label_Row (p_Label_code=>l_Label_Tbl(i).label_code);
312
313 END IF;
314
315 END LOOP;
316
317 END IF;
318
319 ---
320 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
321 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
322 FND_MESSAGE.Set_Name('IBC', 'IBC_INSERT_ERROR');
323 FND_MSG_PUB.ADD;
324 END IF;
325
326 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
328 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
329 RAISE FND_API.G_EXC_ERROR;
330 END IF;
331 END IF;
332
333 --
334 -- End of API body
335 --
336
337 -- Standard check for p_commit
338 IF FND_API.to_Boolean( p_commit )
339 THEN
340 COMMIT WORK;
341 END IF;
342
343 -- Standard call to get message count and if count is 1, get message info.
344 FND_MSG_PUB.Count_And_Get
345 ( p_count => x_msg_count,
346 p_data => x_msg_data
347 );
348
349
350 EXCEPTION
351 WHEN FND_API.G_EXC_ERROR THEN
352 ROLLBACK;
353 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
354 P_API_NAME => L_API_NAME
355 ,P_PKG_NAME => G_PKG_NAME
356 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
357 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
358 ,P_SQLCODE => SQLCODE
359 ,P_SQLERRM => SQLERRM
360 ,X_MSG_COUNT => X_MSG_COUNT
361 ,X_MSG_DATA => X_MSG_DATA
362 ,X_RETURN_STATUS => X_RETURN_STATUS);
363
364 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
365 ROLLBACK;
366 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
367 P_API_NAME => L_API_NAME
368 ,P_PKG_NAME => G_PKG_NAME
369 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
370 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
371 ,P_SQLCODE => SQLCODE
372 ,P_SQLERRM => SQLERRM
373 ,X_MSG_COUNT => X_MSG_COUNT
374 ,X_MSG_DATA => X_MSG_DATA
375 ,X_RETURN_STATUS => X_RETURN_STATUS);
376
377 WHEN OTHERS THEN
378 ROLLBACK;
379 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
380 P_API_NAME => L_API_NAME
381 ,P_PKG_NAME => G_PKG_NAME
382 ,P_EXCEPTION_LEVEL => Ibc_Utilities_Pvt.G_EXC_OTHERS
383 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
384 ,P_SQLCODE => SQLCODE
385 ,P_SQLERRM => SQLERRM
386 ,X_MSG_COUNT => X_MSG_COUNT
387 ,X_MSG_DATA => X_MSG_DATA
388 ,X_RETURN_STATUS => X_RETURN_STATUS);
389
390 END Create_Labels;
391
392
393 PROCEDURE Update_Labels(
394 P_Api_Version_Number IN NUMBER,
395 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
396 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
397 P_Validation_Level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
398 P_Label_Tbl IN Ibc_Labels_GRP.Label_Tbl_Type := Ibc_Labels_GRP.G_Miss_Label_Tbl,
399 x_Label_Tbl OUT NOCOPY Ibc_Labels_GRP.Label_Tbl_Type,
400 X_Return_Status OUT NOCOPY VARCHAR2,
401 X_Msg_Count OUT NOCOPY NUMBER,
402 X_Msg_Data OUT NOCOPY VARCHAR2
403 )
404 IS
405
406 l_temp CHAR(1);
407 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
408
409 l_api_version_number NUMBER := 1.0;
410 l_api_name VARCHAR2(50) := 'Update_Label';
411 l_Label_Code VARCHAR2(100);
412 lx_rowid VARCHAR2(240);
413
414 l_Label_Tbl Ibc_Labels_GRP.Label_Tbl_Type := p_Label_Tbl;
415 l_Label_rec Ibc_Labels_GRP.Label_Rec_Type;
416
417 BEGIN
418
419 -- Initialize API return status to SUCCESS
420 x_return_status := FND_API.G_RET_STS_SUCCESS;
421
422 --
423 -- API body
424 --
425
426 -- ******************************************************************
427 -- Validate Environment
428 -- ******************************************************************
429 IF FND_GLOBAL.User_Id IS NULL
430 THEN
431 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
432 THEN
433 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
434 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
435 FND_MSG_PUB.ADD;
436 END IF;
437 RAISE FND_API.G_EXC_ERROR;
438 END IF;
439
440 -- Update The Corresponding Associations in ibc_Labels_b table
441
442 IF l_Label_tbl.COUNT <> 0 THEN
443
444 FOR i IN l_Label_tbl.FIRST..l_Label_tbl.LAST LOOP
445
446 IF l_Label_tbl.EXISTS(i) AND NOT IsLabelRecordEmpty(l_Label_Tbl(i))
447 THEN
448
449 Update_Label(
450 P_Api_Version_Number =>P_Api_Version_Number,
451 P_Init_Msg_List =>P_Init_Msg_List,
452 P_Commit =>P_Commit,
453 P_Validation_Level =>FND_API.G_VALID_LEVEL_FULL,
454 P_Label_Rec =>l_Label_Tbl(i),
455 X_Return_Status =>X_Return_Status,
456 X_Msg_Count =>X_Msg_Count,
457 X_Msg_Data =>X_Msg_Data);
458
459 x_Label_Tbl(i) := Query_Label_Row (p_Label_code=>l_Label_Tbl(i).label_code);
460
461 END IF;
462
463 END LOOP;
464
465 END IF;
466
467 ---
468 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
469 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
470 FND_MESSAGE.Set_Name('IBC', 'IBC_UPDATE_ERROR');
471 FND_MSG_PUB.ADD;
472 END IF;
473
474 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
476 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
477 RAISE FND_API.G_EXC_ERROR;
478 END IF;
479 END IF;
480
481 --
482 -- End of API body
483 --
484
485 -- Standard check for p_commit
486 IF FND_API.to_Boolean( p_commit )
487 THEN
488 COMMIT WORK;
489 END IF;
490
491 -- Standard call to get message count and if count is 1, get message info.
492 FND_MSG_PUB.Count_And_Get
493 ( p_count => x_msg_count,
494 p_data => x_msg_data
495 );
496
497
498 EXCEPTION
499 WHEN FND_API.G_EXC_ERROR THEN
500 ROLLBACK;
501 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
502 P_API_NAME => L_API_NAME
503 ,P_PKG_NAME => G_PKG_NAME
504 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
505 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
506 ,P_SQLCODE => SQLCODE
507 ,P_SQLERRM => SQLERRM
508 ,X_MSG_COUNT => X_MSG_COUNT
509 ,X_MSG_DATA => X_MSG_DATA
510 ,X_RETURN_STATUS => X_RETURN_STATUS);
511
512 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
513 ROLLBACK;
514 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
515 P_API_NAME => L_API_NAME
516 ,P_PKG_NAME => G_PKG_NAME
517 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
518 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
519 ,P_SQLCODE => SQLCODE
520 ,P_SQLERRM => SQLERRM
521 ,X_MSG_COUNT => X_MSG_COUNT
522 ,X_MSG_DATA => X_MSG_DATA
523 ,X_RETURN_STATUS => X_RETURN_STATUS);
524
525 WHEN OTHERS THEN
526 ROLLBACK;
527 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
528 P_API_NAME => L_API_NAME
529 ,P_PKG_NAME => G_PKG_NAME
530 ,P_EXCEPTION_LEVEL => Ibc_Utilities_Pvt.G_EXC_OTHERS
531 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
532 ,P_SQLCODE => SQLCODE
533 ,P_SQLERRM => SQLERRM
534 ,X_MSG_COUNT => X_MSG_COUNT
535 ,X_MSG_DATA => X_MSG_DATA
536 ,X_RETURN_STATUS => X_RETURN_STATUS);
537
538 END Update_Labels;
539
540
541 PROCEDURE delete_Label(
542 P_Api_Version_Number IN NUMBER,
543 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
544 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
545 P_Validation_Level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
546 P_Label_Code IN VARCHAR2,
547 X_Return_Status OUT NOCOPY VARCHAR2,
548 X_Msg_Count OUT NOCOPY NUMBER,
549 X_Msg_Data OUT NOCOPY VARCHAR2
550 )
551 IS
552
553 CURSOR C_Label IS
554 SELECT
555 Label_Code
556 FROM ibc_Labels_b
557 WHERE Label_Code = p_Label_Code;
558
559
560 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
561
562 l_api_version_number NUMBER := 1.0;
563 l_api_name VARCHAR2(50) := 'Delete_Label';
564 l_Label_Code VARCHAR2(100);
565
566 BEGIN
567
568 -- Initialize API return status to SUCCESS
569 x_return_status := FND_API.G_RET_STS_SUCCESS;
570
571 --
572 -- API body
573 --
574
575 -- ******************************************************************
576 -- Validate Environment
577 -- ******************************************************************
578 IF FND_GLOBAL.User_Id IS NULL
579 THEN
580 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
581 THEN
582 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
583 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
584 FND_MSG_PUB.ADD;
585 END IF;
586 RAISE FND_API.G_EXC_ERROR;
587 END IF;
588
589
590 -- Check for all the NOT NULL Columns
591 -- Label_Code Cannot be NULL
592 IBC_VALIDATE_PVT.VALidate_NotNULL_VARCHAR2 (
593 p_init_msg_list => FND_API.G_FALSE,
594 p_column_name => 'Label_Code',
595 p_notnull_column=> p_Label_code,
596 x_return_status => x_return_status,
597 x_msg_count => x_msg_count,
598 x_msg_data => x_msg_data);
599
600 -- Don't RAISE the EXCEPTION Yet. RUN ALL the validation procedures
601 -- and show Exceptions all at once.
602 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
603 l_return_status := x_return_status;
604 END IF;
605
606
607 -- Check If the Record Exists
608 OPEN C_Label;
609 FETCH C_Label INTO l_Label_Code;
610 IF C_Label%NOTFOUND THEN
611 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
612 FND_MESSAGE.Set_Name('IBC', 'Cannot Find Record to be Deleted');
613 FND_MESSAGE.Set_Token('COLUMN', 'Label_Code', FALSE);
614 FND_MSG_PUB.ADD;
615 END IF;
616 CLOSE C_Label;
617 RAISE FND_API.G_EXC_ERROR;
618 END IF;
619 CLOSE C_Label;
620
621 OPEN C_Label;
622 FETCH C_Label INTO l_Label_Code;
623 IF C_Label%FOUND THEN
624 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
625 FND_MESSAGE.Set_Name('IBC', 'References_ExistAssociationTypeCode');
626 FND_MESSAGE.Set_Token('COLUMN', 'Label_Code', FALSE);
627 FND_MSG_PUB.ADD;
628 END IF;
629 CLOSE C_Label;
630 RAISE FND_API.G_EXC_ERROR;
631 END IF;
632 CLOSE C_Label;
633
634
635 IF x_return_status<>FND_API.G_RET_STS_SUCCESS
636 OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
637 RAISE FND_API.G_EXC_ERROR;
638 END IF;
639
640 --
641 -- Table Handler to Delete Row from IBC_LabelS
642 --
643
644 Ibc_Labels_Pkg.delete_row (
645 p_Label_code =>p_Label_code
646 );
647
648
649 Ibc_Labels_Pkg.DELETE_ROW (
650 p_Label_CODE =>p_Label_CODE);
651
652
653 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
654 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
655 FND_MESSAGE.Set_Name('IBC', 'IBC_DELETE_ERROR');
656 FND_MSG_PUB.ADD;
657 END IF;
658
659 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
661 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
662 RAISE FND_API.G_EXC_ERROR;
663 END IF;
664 END IF;
665
666 --
667 -- End of API body
668 --
669
670 -- Standard check for p_commit
671 IF FND_API.to_Boolean( p_commit )
672 THEN
673 COMMIT WORK;
674 END IF;
675
676 -- Standard call to get message count and if count is 1, get message info.
677 FND_MSG_PUB.Count_And_Get
678 ( p_count => x_msg_count,
679 p_data => x_msg_data
680 );
681
682
683 EXCEPTION
684 WHEN FND_API.G_EXC_ERROR THEN
685 ROLLBACK;
686 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
687 P_API_NAME => L_API_NAME
688 ,P_PKG_NAME => G_PKG_NAME
689 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
690 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
691 ,P_SQLCODE => SQLCODE
692 ,P_SQLERRM => SQLERRM
693 ,X_MSG_COUNT => X_MSG_COUNT
694 ,X_MSG_DATA => X_MSG_DATA
695 ,X_RETURN_STATUS => X_RETURN_STATUS);
696
697 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
698 ROLLBACK;
699 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
700 P_API_NAME => L_API_NAME
701 ,P_PKG_NAME => G_PKG_NAME
702 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
703 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
704 ,P_SQLCODE => SQLCODE
705 ,P_SQLERRM => SQLERRM
706 ,X_MSG_COUNT => X_MSG_COUNT
707 ,X_MSG_DATA => X_MSG_DATA
708 ,X_RETURN_STATUS => X_RETURN_STATUS);
709
710 WHEN OTHERS THEN
711 ROLLBACK;
712 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
713 P_API_NAME => L_API_NAME
714 ,P_PKG_NAME => G_PKG_NAME
715 ,P_EXCEPTION_LEVEL => Ibc_Utilities_Pvt.G_EXC_OTHERS
716 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
717 ,P_SQLCODE => SQLCODE
718 ,P_SQLERRM => SQLERRM
719 ,X_MSG_COUNT => X_MSG_COUNT
720 ,X_MSG_DATA => X_MSG_DATA
721 ,X_RETURN_STATUS => X_RETURN_STATUS);
722
723 END delete_Label;
724
725 PROCEDURE Update_Label(
726 P_Api_Version_Number IN NUMBER,
727 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
728 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
729 P_Validation_Level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
730 P_Label_Rec IN Ibc_Labels_GRP.Label_Rec_Type,
731 X_Return_Status OUT NOCOPY VARCHAR2,
732 X_Msg_Count OUT NOCOPY NUMBER,
733 X_Msg_Data OUT NOCOPY VARCHAR2
734 )
735 IS
736
737 CURSOR C_Label(p_Label_Code IN VARCHAR2) IS
738 SELECT Label_Code
739 FROM ibc_Labels_b
740 WHERE Label_Code = p_Label_Code;
741
742 l_temp CHAR(1);
743 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
744
745 l_api_version_number NUMBER := 1.0;
746 l_api_name VARCHAR2(50) := 'Update_Label';
747 lx_rowid VARCHAR2(240);
748 l_Label_code VARCHAR2(100);
749
750 l_Label_rec Ibc_Labels_GRP.Label_Rec_Type := P_Label_Rec;
751 l_old_Label_rec Ibc_Labels_GRP.Label_Rec_Type;
752
753 BEGIN
754
755 -- Initialize API return status to SUCCESS
756 x_return_status := FND_API.G_RET_STS_SUCCESS;
757
758 -- Check If Row exists
759 l_old_Label_rec := Query_Label_Row
760 (p_Label_Code => l_Label_rec.Label_code);
761
762
763 -- Begin Validation for Association Type Record
764 -- Check for all the NOT NULL Columns
765 -- Label_Code Cannot be NULL
766 IBC_VALIDATE_PVT.VALidate_NotNULL_VARCHAR2 (
767 p_init_msg_list => FND_API.G_FALSE,
768 p_column_name => 'Label_Code',
769 p_notnull_column=> l_Label_rec.Label_code,
770 x_return_status => x_return_status,
771 x_msg_count => x_msg_count,
772 x_msg_data => x_msg_data);
773
774 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
775 l_return_status := x_return_status;
776 END IF;
777
778
779 IF l_Label_rec.Label_Name <> FND_API.G_MISS_CHAR THEN
780 -- Label_Name Cannot be NULL
781 IBC_VALIDATE_PVT.VALidate_NotNULL_VARCHAR2 (
782 p_init_msg_list => FND_API.G_FALSE,
783 p_column_name => 'Label_Name',
784 p_notnull_column=> l_Label_rec.Label_Name,
785 x_return_status => x_return_status,
786 x_msg_count => x_msg_count,
787 x_msg_data => x_msg_data);
788
789 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
790 l_return_status := x_return_status;
791 END IF;
792
793 END IF;
794
795
796 -- End Validation for Association Type Record
797
798 Ibc_Labels_Pkg.Update_row (
799 p_Label_CODE => l_Label_rec.Label_CODE,
800 p_Label_NAME => l_Label_rec.Label_NAME,
801 p_DESCRIPTION => l_Label_rec.DESCRIPTION,
802 p_LAST_UPDATED_BY => l_Label_rec.LAST_UPDATED_BY,
803 p_LAST_UPDATE_DATE => l_Label_rec.LAST_UPDATE_DATE,
804 p_LAST_UPDATE_LOGIN => l_Label_rec.LAST_UPDATE_LOGIN,
805 p_OBJECT_VERSION_NUMBER => l_Label_rec.OBJECT_VERSION_NUMBER
806 );
807
808 END Update_Label;
809
810
811 PROCEDURE delete_Label(
812 P_Api_Version_Number IN NUMBER,
813 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
814 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
815 P_Validation_Level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
816 P_Label_Rec IN Ibc_Labels_GRP.Label_Rec_Type,
817 X_Return_Status OUT NOCOPY VARCHAR2,
818 X_Msg_Count OUT NOCOPY NUMBER,
819 X_Msg_Data OUT NOCOPY VARCHAR2
820 )
821 IS
822
823 CURSOR C_Label(p_Label_Code IN VARCHAR2) IS
824 SELECT Label_Code
825 FROM ibc_Labels_b
826 WHERE Label_Code = p_Label_Code;
827
828 l_temp CHAR(1);
829 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
830
831 l_api_version_number NUMBER := 1.0;
832 l_api_name VARCHAR2(50) := 'delete_Label';
833 lx_rowid VARCHAR2(240);
834 l_Label_code VARCHAR2(100);
835
836 l_Label_rec Ibc_Labels_GRP.Label_Rec_Type := P_Label_Rec;
837 l_old_Label_rec Ibc_Labels_GRP.Label_Rec_Type;
838
839 BEGIN
840
841 -- Initialize API return status to SUCCESS
842 x_return_status := FND_API.G_RET_STS_SUCCESS;
843
844 -- Check If Row exists
845 l_old_Label_rec := Query_Label_Row
846 (p_Label_Code => l_Label_rec.Label_code);
847
848
849 -- Begin Validation for Association Type Record
850 -- Check for all the NOT NULL Columns
851 -- Label_Code Cannot be NULL
852 IBC_VALIDATE_PVT.VALidate_NotNULL_VARCHAR2 (
853 p_init_msg_list => FND_API.G_FALSE,
854 p_column_name => 'Label_Code',
855 p_notnull_column=> l_Label_rec.Label_code,
856 x_return_status => x_return_status,
857 x_msg_count => x_msg_count,
858 x_msg_data => x_msg_data);
859
860 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
861 l_return_status := x_return_status;
862 END IF;
863
864 --
865 -- Validate the Association Type Code in Association Rec
866 IBC_VALIDATE_PVT.VALidate_NotNULL_VARCHAR2 (
867 p_init_msg_list => FND_API.G_FALSE,
868 p_column_name => 'Label_Code',
869 p_notnull_column=> l_Label_rec.Label_Code,
870 x_return_status => x_return_status,
871 x_msg_count => x_msg_count,
872 x_msg_data => x_msg_data);
873
874 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
875 l_return_status := x_return_status;
876 END IF;
877
878 -- End Validation for Association Type Record
879
880 Ibc_Labels_Pkg.delete_row (
881 p_Label_code =>l_Label_rec.Label_code);
882
883 END delete_Label;
884
885
886 FUNCTION Query_Label_Row (
887 p_Label_code IN VARCHAR2)
888 RETURN Ibc_Labels_GRP.Label_Rec_Type
889 IS
890 l_Label_Rec Ibc_Labels_GRP.Label_Rec_Type;
891 BEGIN
892 SELECT
893 Label_CODE
894 ,CREATED_BY
895 ,CREATION_DATE
896 ,LAST_UPDATED_BY
897 ,LAST_UPDATE_DATE
898 ,LAST_UPDATE_LOGIN
899 ,OBJECT_VERSION_NUMBER
900 ,Label_NAME
901 ,DESCRIPTION
902 INTO
903 l_Label_Rec.Label_CODE ,
904 l_Label_Rec.CREATED_BY ,
905 l_Label_Rec.CREATION_DATE ,
906 l_Label_Rec.LAST_UPDATED_BY ,
907 l_Label_Rec.LAST_UPDATE_DATE ,
908 l_Label_Rec.LAST_UPDATE_LOGIN ,
909 l_Label_Rec.OBJECT_VERSION_NUMBER ,
910 l_Label_Rec.Label_NAME ,
911 l_Label_Rec.DESCRIPTION
912 FROM IBC_LabelS_VL
913 WHERE Label_code = p_Label_code;
914
915 RETURN l_Label_rec;
916
917 EXCEPTION
918 WHEN NO_DATA_FOUND THEN
919 RAISE NO_DATA_FOUND;
920 WHEN OTHERS THEN
921 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
922 FND_MESSAGE.Set_Name('IBC', 'Association TYPE RECORD Error');
923 FND_MSG_PUB.ADD;
924 END IF;
925 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
926 END Query_Label_Row;
927
928 FUNCTION get_label_rec RETURN Ibc_Labels_GRP.Label_rec_type
929 IS
930 TMP_REC Ibc_Labels_GRP.Label_rec_type;
931 BEGIN
932 RETURN TMP_REC;
933 END get_label_rec;
934
935 END Ibc_Labels_GRP;