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