[Home] [Help]
PACKAGE BODY: APPS.IBC_CTYPE_PVT
Source
1 PACKAGE BODY Ibc_Ctype_Pvt AS
2 /* $Header: ibcvctyb.pls 120.2 2005/06/01 23:50:00 appldev $ */
3
4 -- Purpose: API to Populate Content 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 for NOCOPY
11 -- Sri Rangarajan 01/06/2004 Added the Method get_sql_from_flex
12
13 -- Package Name : IBC_Ctype_Pvt
14 -- Purpose :
15 -- History : 05/18/2005 Sharma GSCC NOCOPY issue fixed
16 -- NOTE :
17 -- End of Comments
18
19
20 G_PKG_Name CONSTANT VARCHAR2(30):= 'Ibc_Ctype_Pvt';
21 G_FILE_Name CONSTANT VARCHAR2(12) := 'ibcvctyb.pls';
22
23 PROCEDURE Update_Attribute_Type(
24 P_Api_Version_Number IN NUMBER,
25 P_Init_Msg_List IN VARCHAR2 ,--:= FND_API.G_FALSE,
26 P_Commit IN VARCHAR2 ,--:= FND_API.G_FALSE,
27 P_Validation_Level IN NUMBER ,--:= FND_API.G_VALID_LEVEL_FULL,
28 P_Attribute_Type_Rec IN Ibc_Ctype_Pvt.Attribute_Type_Rec_Type,
29 X_Return_Status OUT NOCOPY VARCHAR2,
30 X_Msg_Count OUT NOCOPY NUMBER,
31 X_Msg_Data OUT NOCOPY VARCHAR2
32 );
33
34 PROCEDURE Delete_Attribute_Type(
35 P_Api_Version_Number IN NUMBER,
36 P_Init_Msg_List IN VARCHAR2 ,--:= FND_API.G_FALSE,
37 P_Commit IN VARCHAR2 ,--:= FND_API.G_FALSE,
38 P_Validation_Level IN NUMBER ,--:= FND_API.G_VALID_LEVEL_FULL,
39 P_Attribute_Type_Rec IN Ibc_Ctype_Pvt.Attribute_Type_Rec_Type,
40 X_Return_Status OUT NOCOPY VARCHAR2,
41 X_Msg_Count OUT NOCOPY NUMBER,
42 X_Msg_Data OUT NOCOPY VARCHAR2
43 );
44
45 FUNCTION Query_Attribute_type_Row (
46 p_attribute_type_code IN VARCHAR2,
47 p_content_type_code IN VARCHAR2
48 ) RETURN Ibc_Ctype_Pvt.Attribute_Type_Rec_Type;
49
50
51 FUNCTION IsATypeRecordEmpty(
52 P_Attribute_Type_Rec IN Ibc_Ctype_Pvt.Attribute_Type_Rec_Type)
53 RETURN BOOLEAN IS
54
55 BEGIN
56
57 IF ((p_attribute_type_rec.content_type_code IS NULL
58 OR p_attribute_type_rec.content_type_code = Fnd_Api.G_MISS_CHAR)
59 AND (p_attribute_type_rec.attribute_type_code IS NULL
60 OR p_attribute_type_rec.attribute_type_code = Fnd_Api.G_MISS_CHAR))
61 THEN
62
63 RETURN TRUE;
64 ELSE
65 RETURN FALSE;
66 END IF;
67
68 END IsATypeRecordEmpty;
69
70
71 FUNCTION IsCTypeRecordEmpty(
72 P_content_Type_Rec IN Ibc_Ctype_Pvt.Content_Type_Rec_Type)
73 RETURN BOOLEAN IS
74
75 BEGIN
76
77 IF ((P_content_Type_Rec.content_type_code IS NULL
78 OR P_content_Type_Rec.content_type_code = Fnd_Api.G_MISS_CHAR)
79 AND (P_content_Type_Rec.content_type_status IS NULL
80 OR P_content_Type_Rec.content_type_status = Fnd_Api.G_MISS_CHAR))
81 THEN
82
83 RETURN TRUE;
84
85 ELSE
86 RETURN FALSE;
87
88 END IF;
89
90 END IsCTypeRecordEmpty;
91
92 PROCEDURE Create_Content_Type(
93 P_Api_Version_Number IN NUMBER,
94 P_Init_Msg_List IN VARCHAR2 ,--:= FND_API.G_FALSE,
95 P_Commit IN VARCHAR2 ,--:= FND_API.G_FALSE,
96 P_Validation_Level IN NUMBER ,--:= FND_API.G_VALID_LEVEL_FULL,
97 P_Content_Type_Rec IN Ibc_Ctype_Pvt.Content_Type_Rec_Type ,--:= Ibc_Ctype_Pvt.G_MISS_Content_Type_Rec,
98 P_Attribute_Type_Tbl IN Ibc_Ctype_Pvt.Attribute_Type_Tbl_Type ,--:= Ibc_Ctype_Pvt.G_Miss_Attribute_Type_Tbl,
99 X_Return_Status OUT NOCOPY VARCHAR2,
100 X_Msg_Count OUT NOCOPY NUMBER,
101 X_Msg_Data OUT NOCOPY VARCHAR2
102 )
103 IS
104 CURSOR C_Content_Type(p_Content_Type_Code IN VARCHAR2) IS
105 SELECT
106 Content_Type_Code
107 FROM ibc_content_types_b
108 WHERE Content_Type_Code = p_Content_Type_Code;
109
110
111 CURSOR C_Attribute_Type(p_content_type_code IN VARCHAR2
112 ,p_attribute_type_code IN VARCHAR2)
113 IS
114 SELECT '1'
115 FROM IBC_ATTRIBUTE_TYPES_B
116 WHERE content_type_code = p_content_type_code
117 AND attribute_type_code = p_attribute_type_code;
118
119 l_temp CHAR(1);
120 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
121
122 l_api_version_number NUMBER := 1.0;
123 l_api_name VARCHAR2(50) := 'Create_Content_Type';
124 l_Content_Type_Code VARCHAR2(100);
125 lx_rowid VARCHAR2(240);
126
127 l_Content_Type_Rec Ibc_Ctype_Pvt.Content_Type_Rec_Type := p_Content_Type_Rec;
128 l_Attribute_Type_Tbl Ibc_Ctype_Pvt.Attribute_Type_Tbl_Type := p_Attribute_Type_Tbl;
129 l_attribute_type_rec Ibc_Ctype_Pvt.Attribute_Type_Rec_Type;
130
131 l_Does_name_exist BOOLEAN := FALSE;
132 l_Does_Description_exist BOOLEAN := FALSE;
133
134 BEGIN
135 -- Standard Start of API savepoint
136 DBMS_TRANSACTION.SAVEPOINT(l_api_name);
137 --SAVEPOINT l_api_name;
138 -- Initialize API return status to SUCCESS
139 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
140
141 --
142 -- API body
143 --
144
145 -- ******************************************************************
146 -- Validate Environment
147 -- ******************************************************************
148 IF Fnd_Global.User_Id IS NULL
149 THEN
150 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
151 THEN
152 Fnd_Message.Set_Name(' + appShortname +', 'UT_CANNOT_GET_PROFILE_VALUE');
153 Fnd_Message.Set_Token('PROFILE', 'USER_ID', FALSE);
154 Fnd_Msg_Pub.ADD;
155 END IF;
156 RAISE Fnd_Api.G_EXC_ERROR;
157 END IF;
158
159 -- Check for all the NOT NULL Columns
160 -- Content_TYpe_Code Cannot be NULL
161 Ibc_Validate_Pvt.validate_NotNULL_VARCHAR2 (
162 p_init_msg_list => Fnd_Api.G_FALSE,
163 p_column_name => 'Content_Type_Code',
164 p_notnull_column=> l_content_type_rec.content_type_code,
165 x_return_status => x_return_status,
166 x_msg_count => x_msg_count,
167 x_msg_data => x_msg_data);
168
169 -- Don't RAISE the EXCEPTION Yet. RUN ALL the validation procedures
170 -- and show Exceptions all at once.
171 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
172 l_return_status := x_return_status;
173 END IF;
174
175 -- Content_TYpe_Status Cannot be NULL
176 Ibc_Validate_Pvt.validate_NotNULL_VARCHAR2 (
177 p_init_msg_list => Fnd_Api.G_FALSE,
178 p_column_name => 'Content_Type_Status',
179 p_notnull_column=> l_content_type_rec.content_type_Status,
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 -- Content_TYpe_name Cannot be NULL
189 Ibc_Validate_Pvt.validate_NotNULL_VARCHAR2 (
190 p_init_msg_list => Fnd_Api.G_FALSE,
191 p_column_name => 'Content_Type_name',
192 p_notnull_column=> l_content_type_rec.content_type_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
202 -- Check for Uniqueness
203 OPEN C_Content_Type(p_Content_Type_Code => l_content_type_rec.content_type_code);
204 FETCH C_Content_Type INTO l_Content_Type_Code;
205 IF C_Content_Type%FOUND THEN
206 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
207 Fnd_Message.Set_name('IBC', 'IBC_ALREADY_EXISTS');
208 Fnd_Message.Set_Token('DUPLICATE_OBJECT_TOKEN', 'Content_Type_Code', FALSE);
209 Fnd_Msg_Pub.ADD;
210 END IF;
211 CLOSE C_Content_Type;
212 RAISE Fnd_Api.G_EXC_ERROR;
213 END IF;
214 CLOSE C_Content_Type;
215
216
217 -- Validate Content Type Status
218 Ibc_Validate_Pvt.validate_Content_Type_Status(
219 p_init_msg_list => Fnd_Api.G_FALSE,
220 p_Content_Type_Status => l_content_type_rec.content_type_status,
221 x_return_status => x_return_status,
222 x_msg_count => x_msg_count,
223 x_msg_data => x_msg_data);
224
225 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
226 l_return_status := x_return_status;
227 END IF;
228
229
230 -- Validate Application Id
231 Ibc_Validate_Pvt.validate_application_id(
232 p_init_msg_list => Fnd_Api.G_FALSE,
233 p_application_id => l_content_type_rec.application_id,
234 x_return_status => x_return_status,
235 x_msg_count => x_msg_count,
236 x_msg_data => x_msg_data);
237
238 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS
239 OR l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
240 RAISE Fnd_Api.G_EXC_ERROR;
241 END IF;
242
243 -- Validate Object Version Number
244 IF l_content_type_rec.OBJECT_VERSION_NUMBER IS NULL
245 OR l_content_type_rec.OBJECT_VERSION_NUMBER = Fnd_Api.G_MISS_NUM THEN
246 l_content_type_rec.OBJECT_VERSION_NUMBER := 1;
247 END IF;
248
249 --dbms_output.put_line('Validation complete FOR content TYPE');
250
251
252 --
253 -- Table Handler to Insert Row into IBC_CONTENT_TYPES
254 --
255 Ibc_Content_Types_Pkg.INSERT_ROW (
256 x_ROWID =>lx_rowid,
257 p_CONTENT_TYPE_CODE =>l_content_type_rec.CONTENT_TYPE_CODE,
258 p_CONTENT_TYPE_STATUS =>l_content_type_rec.CONTENT_TYPE_STATUS,
259 p_APPLICATION_ID =>l_content_type_rec.APPLICATION_ID,
260 p_REQUEST_ID =>l_content_type_rec.REQUEST_ID,
261 p_OBJECT_VERSION_NUMBER =>l_content_type_rec.oBJECT_VERSION_NUMBER,
262 p_CONTENT_TYPE_Name =>l_content_type_rec.CONTENT_TYPE_name,
263 p_DESCRIPTION =>l_content_type_rec.DESCRIPTION,
264 p_CREATION_DATE =>l_content_type_rec.CREATION_DATE,
265 p_CREATED_BY =>l_content_type_rec.CREATED_BY,
266 p_LAST_UPDATE_DATE =>l_content_type_rec.LAST_UPDATE_DATE,
267 p_LAST_UPDATED_BY =>l_content_type_rec.LAST_UPDATED_BY,
268 p_LAST_UPDATE_LOGIN =>l_content_type_rec.LAST_UPDATE_LOGIN);
269
270
271 -- Insert The Corresponding Attributes in ibc_attribute_types_b table
272 IF l_attribute_type_tbl.COUNT <> 0 THEN
273 FOR i IN l_attribute_type_tbl.FIRST..l_attribute_type_tbl.LAST LOOP
274
275 IF l_attribute_type_tbl.EXISTS(i) AND NOT IsATypeRecordEmpty(l_Attribute_Type_Tbl(i))
276 THEN
277 --
278 -- Check to see if name and Description are already a part of the Attribute_Tbl
279 -- if NOT then Create name and DESCRIPTION attribute Types by default
280 --
281 -- Check if name exists in the Attribute Types
282 IF UPPER(l_Attribute_Type_Tbl(i).attribute_type_code) = G_NAME THEN
283 l_Does_name_exist := TRUE;
284 END IF;
285 -- Check if Description exists in the Attribute Types
286 IF UPPER(l_Attribute_Type_Tbl(i).attribute_type_code) = G_DESCRIPTION THEN
287 l_Does_Description_exist := TRUE;
288 END IF;
289
290 Create_Attribute_Type(
291 P_Api_Version_Number =>P_Api_Version_Number,
292 P_Init_Msg_List =>P_Init_Msg_List,
293 P_Commit =>P_Commit,
294 P_Validation_Level =>Fnd_Api.G_VALID_LEVEL_FULL,
295 P_Attribute_Type_Rec =>l_Attribute_Type_Tbl(i),
296 X_Return_Status =>X_Return_Status,
297 X_Msg_Count =>X_Msg_Count,
298 X_Msg_Data =>X_Msg_Data);
299
300 END IF;
301
302 END LOOP;
303
304 END IF;
305
306 IF NOT l_Does_Description_exist THEN
307 -- Create Default Description Attribs
308 l_ATTRIBUTE_TYPE_rec.ATTRIBUTE_TYPE_CODE := G_DESCRIPTION;
309 l_ATTRIBUTE_TYPE_rec.ATTRIBUTE_TYPE_name := 'Description';
310 l_ATTRIBUTE_TYPE_rec.DESCRIPTION := 'Description OF the ' || l_content_type_rec.CONTENT_TYPE_CODE||'.';
311 l_ATTRIBUTE_TYPE_rec.CONTENT_TYPE_CODE := l_content_type_rec.CONTENT_TYPE_CODE;
312 l_ATTRIBUTE_TYPE_rec.DATA_TYPE_CODE := 'string';
313 l_ATTRIBUTE_TYPE_rec.DATA_LENGTH := 2000;
314 l_ATTRIBUTE_TYPE_rec.MIN_INSTANCES := 1;
315 l_ATTRIBUTE_TYPE_rec.MAX_INSTANCES := 1;
316 l_ATTRIBUTE_TYPE_rec.UPDATEABLE_FLAG := 'T';
317
318 Create_Attribute_Type(
319 P_Api_Version_Number =>P_Api_Version_Number,
320 P_Init_Msg_List =>P_Init_Msg_List,
321 P_Commit =>P_Commit,
322 P_Validation_Level =>Fnd_Api.G_VALID_LEVEL_FULL,
323 P_Attribute_Type_Rec =>l_Attribute_Type_rec,
324 X_Return_Status =>X_Return_Status,
325 X_Msg_Count =>X_Msg_Count,
326 X_Msg_Data =>X_Msg_Data);
327
328 END IF;
329
330
331
332 IF NOT l_Does_name_exist THEN
333 -- Create Default name Attribs
334 l_ATTRIBUTE_TYPE_TBL(1).ATTRIBUTE_TYPE_CODE := G_NAME;
335 l_ATTRIBUTE_TYPE_TBL(1).ATTRIBUTE_TYPE_name := 'Name';
336 l_ATTRIBUTE_TYPE_TBL(1).DESCRIPTION := 'Name of the ' || l_content_type_rec.CONTENT_TYPE_CODE ||'.';
337 l_ATTRIBUTE_TYPE_TBL(1).CONTENT_TYPE_CODE := l_content_type_rec.CONTENT_TYPE_CODE;
338 l_ATTRIBUTE_TYPE_TBL(1).DATA_TYPE_CODE := 'string';
339 l_ATTRIBUTE_TYPE_TBL(1).DATA_LENGTH := 240;
340 l_ATTRIBUTE_TYPE_TBL(1).MIN_INSTANCES := 0;
341 l_ATTRIBUTE_TYPE_TBL(1).MAX_INSTANCES := 1;
342 l_ATTRIBUTE_TYPE_TBL(1).UPDATEABLE_FLAG := 'T';
343 Create_Attribute_Type(
344 P_Api_Version_Number =>P_Api_Version_Number,
345 P_Init_Msg_List =>P_Init_Msg_List,
346 P_Commit =>P_Commit,
347 P_Validation_Level =>Fnd_Api.G_VALID_LEVEL_FULL,
348 P_Attribute_Type_Rec =>l_Attribute_Type_Tbl(1),
349 X_Return_Status =>X_Return_Status,
350 X_Msg_Count =>X_Msg_Count,
351 X_Msg_Data =>X_Msg_Data);
352
353 END IF;
354
355
356 -- SELECT A.*
357 -- INTO l_temp
358 -- FROM TABLE(CAST(l_atype_code AS JTF_VARCHAR2_TABLE_100)) A;
359
360
361 -- dbms_output.put_line('COUNT ' || l_temp);
362 ---
363 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
364 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
365 Fnd_Message.Set_name('IBC', 'IBC_INSERT_ERROR');
366 Fnd_Msg_Pub.ADD;
367 END IF;
368
369 IF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR) THEN
370 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
371 ELSIF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
372 RAISE Fnd_Api.G_EXC_ERROR;
373 END IF;
374 END IF;
375
376 --
377 -- End of API body
378 --
379
380 -- Standard check for p_commit
381 IF Fnd_Api.to_Boolean( p_commit )
382 THEN
383 COMMIT WORK;
384 END IF;
385
386 -- Standard call to get message count and if count is 1, get message info.
387 Fnd_Msg_Pub.Count_And_Get
388 ( p_count => x_msg_count,
389 p_data => x_msg_data
390 );
391
392 EXCEPTION
393 WHEN Fnd_Api.G_EXC_ERROR THEN
394 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name);
395 --ROLLBACK TO SAVEPOINT l_api_name;
396 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
397 P_API_name => L_API_name
398 ,P_PKG_name => G_PKG_name
399 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
400 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
401 ,P_SQLCODE => SQLCODE
402 ,P_SQLERRM => SQLERRM
403 ,X_MSG_COUNT => X_MSG_COUNT
404 ,X_MSG_DATA => X_MSG_DATA
405 ,X_RETURN_STATUS => X_RETURN_STATUS);
406
407 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
408 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name);
409 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
410 P_API_name => L_API_name
411 ,P_PKG_name => G_PKG_name
412 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
413 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
414 ,P_SQLCODE => SQLCODE
415 ,P_SQLERRM => SQLERRM
416 ,X_MSG_COUNT => X_MSG_COUNT
417 ,X_MSG_DATA => X_MSG_DATA
418 ,X_RETURN_STATUS => X_RETURN_STATUS);
419
420 WHEN OTHERS THEN
421 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name);
422 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
423 P_API_name => L_API_name
424 ,P_PKG_name => G_PKG_name
425 ,P_EXCEPTION_LEVEL => Ibc_Utilities_Pvt.G_EXC_OTHERS
426 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
427 ,P_SQLCODE => SQLCODE
428 ,P_SQLERRM => SQLERRM
429 ,X_MSG_COUNT => X_MSG_COUNT
430 ,X_MSG_DATA => X_MSG_DATA
431 ,X_RETURN_STATUS => X_RETURN_STATUS);
432
433 END Create_Content_Type;
434
435
436 PROCEDURE Create_Attribute_Type(
437 P_Api_Version_Number IN NUMBER,
438 P_Init_Msg_List IN VARCHAR2 ,--:= FND_API.G_FALSE,
439 P_Commit IN VARCHAR2 ,--:= FND_API.G_FALSE,
440 P_Validation_Level IN NUMBER ,--:= FND_API.G_VALID_LEVEL_FULL,
441 P_Attribute_Type_Rec IN Ibc_Ctype_Pvt.Attribute_Type_Rec_Type,
442 X_Return_Status OUT NOCOPY VARCHAR2,
443 X_Msg_Count OUT NOCOPY NUMBER,
444 X_Msg_Data OUT NOCOPY VARCHAR2
445 )
446 IS
447
448 CURSOR C_Content_Type(p_Content_Type_Code IN VARCHAR2) IS
449 SELECT Content_Type_Code
450 FROM ibc_content_types_b
451 WHERE Content_Type_Code = p_Content_Type_Code;
452
453 CURSOR C_Attribute_Type(p_content_type_code IN VARCHAR2
454 ,p_attribute_type_code IN VARCHAR2)
455 IS
456 SELECT '1'
457 FROM IBC_ATTRIBUTE_TYPES_B
458 WHERE content_type_code = p_content_type_code
459 AND attribute_type_code = p_attribute_type_code;
460
461 l_temp CHAR(1);
462 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
463
464 l_api_version_number NUMBER := 1.0;
465 l_api_name VARCHAR2(50) := 'Create_Attribute_Type';
466 lx_rowid VARCHAR2(240);
467 l_content_type_code VARCHAR2(100);
468
469 l_attribute_type_rec Ibc_Ctype_Pvt.Attribute_Type_Rec_Type := P_Attribute_Type_Rec;
470
471 BEGIN
472
473 -- Initialize API return status to SUCCESS
474 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
475
476 -- Begin Validation for Attribute Type Record
477
478 -- Check for all the NOT NULL Columns
479 -- Attribute_Type_Code Cannot be NULL
480 Ibc_Validate_Pvt.validate_NotNULL_VARCHAR2 (
481 p_init_msg_list => Fnd_Api.G_FALSE,
482 p_column_name => 'Attribute_Type_Code',
483 p_notnull_column=> l_attribute_type_rec.attribute_type_code,
484 x_return_status => x_return_status,
485 x_msg_count => x_msg_count,
486 x_msg_data => x_msg_data);
487
488 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
489 l_return_status := x_return_status;
490 END IF;
491
492
493 --
494 -- Validate the Content Type Code in Attribute Rec
495 Ibc_Validate_Pvt.validate_NotNULL_VARCHAR2 (
496 p_init_msg_list => Fnd_Api.G_FALSE,
497 p_column_name => 'Content_Type_Code',
498 p_notnull_column=> l_attribute_type_rec.Content_Type_Code,
499 x_return_status => x_return_status,
500 x_msg_count => x_msg_count,
501 x_msg_data => x_msg_data);
502
503 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
504 l_return_status := x_return_status;
505 END IF;
506
507
508 -- Check for Content Type Code exits in Content Type Table
509 OPEN C_Content_Type(p_Content_Type_Code => l_attribute_type_rec.Content_Type_Code);
510 FETCH C_Content_Type INTO l_Content_Type_Code;
511 IF C_Content_Type%NOTFOUND THEN
512 x_return_status := Fnd_Api.G_RET_STS_ERROR;
513 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
514 Fnd_Message.Set_name('IBC', 'INVALID_CONTENT_TYPE_CODE');
515 Fnd_Message.Set_Token('COLUMN', 'Content_Type_Code', FALSE);
516 Fnd_Msg_Pub.ADD;
517 END IF;
518 CLOSE C_Content_Type;
519 RAISE Fnd_Api.G_EXC_ERROR;
520 END IF;
521 CLOSE C_Content_Type;
522
523 -- Attribute_Type_name Cannot be NULL
524 Ibc_Validate_Pvt.validate_NotNULL_VARCHAR2 (
525 p_init_msg_list => Fnd_Api.G_FALSE,
526 p_column_name => 'Attribute_Type_Name',
527 p_notnull_column=> l_attribute_type_rec.Attribute_type_name,
528 x_return_status => x_return_status,
529 x_msg_count => x_msg_count,
530 x_msg_data => x_msg_data);
531
532 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
533 l_return_status := x_return_status;
534 END IF;
535
536 -- Data_Type_Code Cannot be NULL
537 Ibc_Validate_Pvt.validate_NotNULL_VARCHAR2 (
538 p_init_msg_list => Fnd_Api.G_FALSE,
539 p_column_name => 'Data_Type_code',
540 p_notnull_column=> l_attribute_type_rec.Data_Type_code,
541 x_return_status => x_return_status,
542 x_msg_count => x_msg_count,
543 x_msg_data => x_msg_data);
544
545 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
546 l_return_status := x_return_status;
547 END IF;
548
549
550 -- Validate Data Type Code
551 Ibc_Validate_Pvt.validate_Data_Type_Code(
552 p_init_msg_list => Fnd_Api.G_FALSE,
553 p_data_Type_Code => l_attribute_type_rec.Data_Type_code,
554 x_return_status => x_return_status,
555 x_msg_count => x_msg_count,
556 x_msg_data => x_msg_data);
557
558 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
559 l_return_status := x_return_status;
560 END IF;
561
562 -- Validate Default Value
563 Ibc_Validate_Pvt.validate_Default_Value(
564 p_init_msg_list => Fnd_Api.G_FALSE,
565 p_data_type_code => l_attribute_type_rec.data_type_code,
566 p_default_Value => l_attribute_type_rec.Default_value,
567 x_return_status => x_return_status,
568 x_msg_count => x_msg_count,
569 x_msg_data => x_msg_data);
570
571 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
572 l_return_status := x_return_status;
573 END IF;
574
575 -- Validate Reference code
576 Ibc_Validate_Pvt.validate_Reference_Code(
577 p_init_msg_list => Fnd_Api.G_FALSE,
578 p_data_type_Code => l_attribute_type_rec.data_type_Code,
579 p_Reference_Code => l_attribute_type_rec.Reference_Code,
580 x_return_status => x_return_status,
581 x_msg_count => x_msg_count,
582 x_msg_data => x_msg_data);
583
584 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
585 l_return_status := x_return_status;
586 END IF;
587
588 -- Default Values for MIN and MAX Instances
589 IF l_attribute_type_rec.min_instances IS NULL
590 OR l_attribute_type_rec.min_instances = Fnd_Api.G_MISS_NUM THEN
591 l_attribute_type_rec.min_instances := 0;
592 END IF;
593
594 -- IF l_attribute_type_rec.max_instances IS NULL
595 -- OR l_attribute_type_rec.max_instances = FND_API.G_MISS_NUM THEN
596 -- l_attribute_type_rec.max_instances := 1;
597 -- END IF;
598
599 -- Validate Min Max Instances
600 Ibc_Validate_Pvt.validate_Min_Max_Instances(
601 p_init_msg_list => Fnd_Api.G_FALSE,
602 p_Min_Instances => l_attribute_type_rec.Min_instances,
603 p_Max_Instances => l_attribute_type_rec.Max_instances,
604 x_return_status => x_return_status,
605 x_msg_count => x_msg_count,
606 x_msg_data => x_msg_data);
607
608 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
609 l_return_status := x_return_status;
610 END IF;
611
612
613 -- End Validation for Attribute Type Record
614
615
616 -- Validate Object Version Number
617 IF l_attribute_type_rec.OBJECT_VERSION_NUMBER IS NULL
618 OR l_attribute_type_rec.OBJECT_VERSION_NUMBER = Fnd_Api.G_MISS_NUM THEN
619 l_attribute_type_rec.OBJECT_VERSION_NUMBER := 1;
620 END IF;
621
622 -- Check for Uniqueness
623 OPEN C_Attribute_Type(p_Content_Type_Code => l_attribute_type_rec.content_type_code
624 ,p_attribute_Type_Code => l_attribute_type_rec.Attribute_type_code);
625 FETCH C_Attribute_Type INTO l_temp;
626 IF C_Attribute_Type%FOUND THEN
627 x_return_status := Fnd_Api.G_RET_STS_ERROR;
628 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
629 Fnd_Message.Set_name('IBC', 'IBC_ALREADY_EXISTS');
630 Fnd_Message.Set_Token('DUPLICATE_OBJECT_TOKEN', 'Attribute_Type_Code',FALSE);
631 Fnd_Msg_Pub.ADD;
632 END IF;
633 END IF;
634
635 CLOSE C_Attribute_Type;
636
637
638
639 IF l_return_status<>Fnd_Api.G_RET_STS_SUCCESS
640 OR x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
641 RAISE Fnd_Api.G_EXC_ERROR;
642 END IF;
643
644
645
646 Ibc_Attribute_Types_Pkg.insert_row (
647 x_rowid =>lx_rowid,
648 p_attribute_type_code =>l_attribute_type_rec.attribute_type_code,
649 p_content_type_code =>l_attribute_type_rec.content_type_code,
650 p_data_type_code =>l_attribute_type_rec.data_type_code,
651 p_data_length =>l_attribute_type_rec.data_length,
652 p_min_instances =>l_attribute_type_rec.min_instances,
653 p_max_instances =>l_attribute_type_rec.max_instances,
654 p_reference_code =>l_attribute_type_rec.reference_code,
655 p_default_value =>l_attribute_type_rec.default_value,
656 p_updateable_flag =>l_attribute_type_rec.updateable_flag,
657 p_object_version_number =>l_attribute_type_rec.object_version_number,
658 p_attribute_type_name =>l_attribute_type_rec.attribute_type_name,
659 p_description =>l_attribute_type_rec.description,
660 p_creation_date =>l_attribute_type_rec.creation_date,
661 p_created_by =>l_attribute_type_rec.created_by,
662 p_last_update_date =>l_attribute_type_rec.last_update_date,
663 p_last_updated_by =>l_attribute_type_rec.last_updated_by,
664 p_last_update_login =>l_attribute_type_rec.last_update_login
665 );
666
667
668 END create_attribute_type;
669
670
671 PROCEDURE Update_Content_Type(
672 P_Api_Version_Number IN NUMBER,
673 P_Init_Msg_List IN VARCHAR2 ,--:= FND_API.G_FALSE,
674 P_Commit IN VARCHAR2 ,--:= FND_API.G_FALSE,
675 P_Validation_Level IN NUMBER ,--:= FND_API.G_VALID_LEVEL_FULL,
676 P_Content_Type_Rec IN Ibc_Ctype_Pvt.Content_Type_Rec_Type ,--:= Ibc_Ctype_Pvt.G_MISS_Content_Type_Rec,
677 P_Attribute_Type_Tbl IN Ibc_Ctype_Pvt.Attribute_Type_Tbl_Type ,--:= Ibc_Ctype_Pvt.G_Miss_Attribute_Type_Tbl,
678 x_Content_Type_Rec OUT NOCOPY Ibc_Ctype_Pvt.Content_Type_Rec_Type,
679 x_Attribute_Type_Tbl OUT NOCOPY Ibc_Ctype_Pvt.Attribute_Type_Tbl_Type,
680 X_Return_Status OUT NOCOPY VARCHAR2,
681 X_Msg_Count OUT NOCOPY NUMBER,
682 X_Msg_Data OUT NOCOPY VARCHAR2
683 )
684 IS
685
686 CURSOR C_Content_Type(p_Content_Type_Code IN VARCHAR2) IS
687 SELECT
688 Content_Type_Code
689 FROM ibc_content_types_b
690 WHERE Content_Type_Code = p_Content_Type_Code;
691
692
693 CURSOR C_Attribute_Type(p_content_type_code IN VARCHAR2
694 ,p_attribute_type_code IN VARCHAR2)
695 IS
696 SELECT '1'
697 FROM IBC_ATTRIBUTE_TYPES_B
698 WHERE content_type_code = p_content_type_code
699 AND attribute_type_code = p_attribute_type_code;
700
701 l_temp CHAR(1);
702 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
703
704 l_api_version_number NUMBER := 1.0;
705 l_api_name VARCHAR2(50) := 'Update_Content_Type';
706 l_Content_Type_Code VARCHAR2(100);
707 lx_rowid VARCHAR2(240);
708
709 l_Content_Type_Rec Ibc_Ctype_Pvt.Content_Type_Rec_Type := p_Content_Type_Rec;
710 l_Attribute_Type_Tbl Ibc_Ctype_Pvt.Attribute_Type_Tbl_Type := p_Attribute_Type_Tbl;
711 l_attribute_type_rec Ibc_Ctype_Pvt.Attribute_Type_Rec_Type;
712
713 BEGIN
714
715 -- Initialize API return status to SUCCESS
716 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
717
718 --
719 -- API body
720 --
721
722 -- ******************************************************************
723 -- Validate Environment
724 -- ******************************************************************
725 IF Fnd_Global.User_Id IS NULL
726 THEN
727 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
728 THEN
729 Fnd_Message.Set_name(' + appShortname +', 'UT_CANNOT_GET_PROFILE_VALUE');
730 Fnd_Message.Set_Token('PROFILE', 'USER_ID', FALSE);
731 Fnd_Msg_Pub.ADD;
732 END IF;
733 RAISE Fnd_Api.G_EXC_ERROR;
734 END IF;
735
736
737 IF NOT IsCTypeRecordEmpty(l_content_type_rec) THEN
738
739 -- Check for all the NOT NULL Columns
740 -- Content_TYpe_Code Cannot be NULL
741 Ibc_Validate_Pvt.validate_NotNULL_VARCHAR2 (
742 p_init_msg_list => Fnd_Api.G_FALSE,
743 p_column_name => 'Content_Type_Code',
744 p_notnull_column=> l_content_type_rec.content_type_code,
745 x_return_status => x_return_status,
746 x_msg_count => x_msg_count,
747 x_msg_data => x_msg_data);
748
749 -- Don't RAISE the EXCEPTION Yet. RUN ALL the validation procedures
750 -- and show Exceptions all at once.
751 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
752 l_return_status := x_return_status;
753 END IF;
754
755
756 -- Check If the Record Exists
757 OPEN C_Content_Type(p_Content_Type_Code => l_content_type_rec.content_type_code);
758 FETCH C_Content_Type INTO l_Content_Type_Code;
759 IF C_Content_Type%NOTFOUND THEN
760 x_return_status := Fnd_Api.G_RET_STS_ERROR;
761 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
762 Fnd_Message.Set_name('IBC', 'INVALID_CONTENT_TYPE_CODE');
763 Fnd_Message.Set_Token('COLUMN', 'Content_Type_Code', FALSE);
764 Fnd_Msg_Pub.ADD;
765 END IF;
766 CLOSE C_Content_Type;
767 RAISE Fnd_Api.G_EXC_ERROR;
768 END IF;
769 CLOSE C_Content_Type;
770
771
772 IF l_content_type_rec.content_type_status <> Fnd_Api.G_MISS_CHAR THEN
773 -- Validate Content Type Status
774 Ibc_Validate_Pvt.validate_Content_Type_Status(
775 p_init_msg_list => Fnd_Api.G_FALSE,
776 p_Content_Type_Status => l_content_type_rec.content_type_status,
777 x_return_status => x_return_status,
778 x_msg_count => x_msg_count,
779 x_msg_data => x_msg_data);
780
781 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
782 l_return_status := x_return_status;
783 END IF;
784 END IF;
785
786
787 IF l_content_type_rec.application_id <> Fnd_Api.G_MISS_NUM THEN
788 -- Validate Application Id
789 Ibc_Validate_Pvt.validate_application_id(
790 p_init_msg_list => Fnd_Api.G_FALSE,
791 p_application_id => l_content_type_rec.application_id,
792 x_return_status => x_return_status,
793 x_msg_count => x_msg_count,
794 x_msg_data => x_msg_data);
795
796 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS
797 OR l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
798 RAISE Fnd_Api.G_EXC_ERROR;
799 END IF;
800 END IF;
801
802 --
803 -- Table Handler to Update Row into IBC_CONTENT_TYPES
804 --
805 Ibc_Content_Types_Pkg.UPDATE_ROW (
806 p_CONTENT_TYPE_CODE =>l_content_type_rec.CONTENT_TYPE_CODE,
807 p_CONTENT_TYPE_STATUS =>l_content_type_rec.CONTENT_TYPE_STATUS,
808 p_APPLICATION_ID =>l_content_type_rec.APPLICATION_ID,
809 p_REQUEST_ID =>l_content_type_rec.REQUEST_ID,
810 p_OBJECT_VERSION_NUMBER =>l_content_type_rec.oBJECT_VERSION_NUMBER,
811 p_CONTENT_TYPE_name =>l_content_type_rec.CONTENT_TYPE_name,
812 p_DESCRIPTION =>l_content_type_rec.DESCRIPTION,
813 p_LAST_UPDATE_DATE =>l_content_type_rec.LAST_UPDATE_DATE,
814 p_LAST_UPDATED_BY =>l_content_type_rec.LAST_UPDATED_BY,
815 p_LAST_UPDATE_LOGIN =>l_content_type_rec.LAST_UPDATE_LOGIN);
816
817
818 END IF;
819
820
821 -- Insert Or Update The Corresponding Attributes in ibc_attribute_types_b table
822
823 IF l_attribute_type_tbl.COUNT <> 0 THEN
824
825 FOR i IN l_attribute_type_tbl.FIRST..l_attribute_type_tbl.LAST LOOP
826
827 IF l_attribute_type_tbl.EXISTS(i) AND NOT IsATypeRecordEmpty(l_Attribute_Type_Tbl(i))
828 THEN
829
830 IF l_Attribute_Type_Tbl(i).OPERATION_CODE = 'CREATE' THEN
831
832 Create_Attribute_Type(
833 P_Api_Version_Number =>P_Api_Version_Number,
834 P_Init_Msg_List =>P_Init_Msg_List,
835 P_Commit =>P_Commit,
836 P_Validation_Level =>Fnd_Api.G_VALID_LEVEL_FULL,
837 P_Attribute_Type_Rec =>l_Attribute_Type_Tbl(i),
838 X_Return_Status =>X_Return_Status,
839 X_Msg_Count =>X_Msg_Count,
840 X_Msg_Data =>X_Msg_Data);
841
842 ELSIF l_Attribute_Type_Tbl(i).OPERATION_CODE = 'UPDATE' THEN
843
844 Update_Attribute_Type(
845 P_Api_Version_Number =>P_Api_Version_Number,
846 P_Init_Msg_List =>P_Init_Msg_List,
847 P_Commit =>P_Commit,
848 P_Validation_Level =>Fnd_Api.G_VALID_LEVEL_FULL,
849 P_Attribute_Type_Rec =>l_Attribute_Type_Tbl(i),
850 X_Return_Status =>X_Return_Status,
851 X_Msg_Count =>X_Msg_Count,
852 X_Msg_Data =>X_Msg_Data);
853
854 ELSIF l_Attribute_Type_Tbl(i).OPERATION_CODE = 'DELETE' THEN
855
856 Delete_Attribute_Type(
857 P_Api_Version_Number =>P_Api_Version_Number,
858 P_Init_Msg_List =>P_Init_Msg_List,
859 P_Commit =>P_Commit,
860 P_Validation_Level =>Fnd_Api.G_VALID_LEVEL_FULL,
861 P_Attribute_Type_Rec =>l_Attribute_Type_Tbl(i),
862 X_Return_Status =>X_Return_Status,
863 X_Msg_Count =>X_Msg_Count,
864 X_Msg_Data =>X_Msg_Data);
865
866 END IF;
867
868 END IF;
869
870 END LOOP;
871
872 END IF;
873
874 ---
875 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
876 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
877 Fnd_Message.Set_name('IBC', 'IBC_UPDATE_ERROR');
878 Fnd_Msg_Pub.ADD;
879 END IF;
880
881 IF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR) THEN
882 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
883 ELSIF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
884 RAISE Fnd_Api.G_EXC_ERROR;
885 END IF;
886 END IF;
887
888 --
889 -- End of API body
890 --
891
892 -- Standard check for p_commit
893 IF Fnd_Api.to_Boolean( p_commit )
894 THEN
895 COMMIT WORK;
896 END IF;
897
898 -- Standard call to get message count and if count is 1, get message info.
899 Fnd_Msg_Pub.Count_And_Get
900 ( p_count => x_msg_count,
901 p_data => x_msg_data
902 );
903
904
905 EXCEPTION
906 WHEN Fnd_Api.G_EXC_ERROR THEN
907 ROLLBACK;
908 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
909 P_API_name => L_API_name
910 ,P_PKG_name => G_PKG_name
911 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
912 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
913 ,P_SQLCODE => SQLCODE
914 ,P_SQLERRM => SQLERRM
915 ,X_MSG_COUNT => X_MSG_COUNT
916 ,X_MSG_DATA => X_MSG_DATA
917 ,X_RETURN_STATUS => X_RETURN_STATUS);
918
919 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
920 ROLLBACK;
921 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
922 P_API_name => L_API_name
923 ,P_PKG_name => G_PKG_name
924 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
925 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
926 ,P_SQLCODE => SQLCODE
927 ,P_SQLERRM => SQLERRM
928 ,X_MSG_COUNT => X_MSG_COUNT
929 ,X_MSG_DATA => X_MSG_DATA
930 ,X_RETURN_STATUS => X_RETURN_STATUS);
931
932 WHEN OTHERS THEN
933 ROLLBACK;
934 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
935 P_API_name => L_API_name
936 ,P_PKG_name => G_PKG_name
937 ,P_EXCEPTION_LEVEL => Ibc_Utilities_Pvt.G_EXC_OTHERS
938 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
939 ,P_SQLCODE => SQLCODE
940 ,P_SQLERRM => SQLERRM
941 ,X_MSG_COUNT => X_MSG_COUNT
942 ,X_MSG_DATA => X_MSG_DATA
943 ,X_RETURN_STATUS => X_RETURN_STATUS);
944
945 END Update_Content_type;
946
947
948 PROCEDURE Delete_Content_Type(
949 P_Api_Version_Number IN NUMBER,
950 P_Init_Msg_List IN VARCHAR2 ,--:= FND_API.G_FALSE,
951 P_Commit IN VARCHAR2 ,--:= FND_API.G_FALSE,
952 P_Validation_Level IN NUMBER ,--:= FND_API.G_VALID_LEVEL_FULL,
953 P_Content_Type_Code IN VARCHAR2,
954 X_Return_Status OUT NOCOPY VARCHAR2,
955 X_Msg_Count OUT NOCOPY NUMBER,
956 X_Msg_Data OUT NOCOPY VARCHAR2
957 )
958 IS
959
960 CURSOR C_Content_Type IS
961 SELECT
962 Content_Type_Code
963 FROM ibc_content_types_b
964 WHERE Content_Type_Code = p_Content_Type_Code;
965
966
967 CURSOR C_Attribute_Type
968 IS
969 SELECT attribute_Type_code
970 FROM IBC_ATTRIBUTE_TYPES_B
971 WHERE Reference_code = p_content_type_code;
972
973 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
974
975 l_api_version_number NUMBER := 1.0;
976 l_api_name VARCHAR2(50) := 'Delete_Content_Type';
977 l_Content_Type_Code VARCHAR2(100);
978 l_Attribute_Type_Code VARCHAR2(100);
979
980 BEGIN
981
982 -- Initialize API return status to SUCCESS
983 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
984
985 --
986 -- API body
987 --
988
989 -- ******************************************************************
990 -- Validate Environment
991 -- ******************************************************************
992 IF Fnd_Global.User_Id IS NULL
993 THEN
994 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
995 THEN
996 Fnd_Message.Set_name(' + appShortname +', 'UT_CANNOT_GET_PROFILE_VALUE');
997 Fnd_Message.Set_Token('PROFILE', 'USER_ID', FALSE);
998 Fnd_Msg_Pub.ADD;
999 END IF;
1000 RAISE Fnd_Api.G_EXC_ERROR;
1001 END IF;
1002
1003
1004 -- Check for all the NOT NULL Columns
1005 -- Content_TYpe_Code Cannot be NULL
1006 Ibc_Validate_Pvt.validate_NotNULL_VARCHAR2 (
1007 p_init_msg_list => Fnd_Api.G_FALSE,
1008 p_column_name => 'Content_Type_Code',
1009 p_notnull_column=> p_content_type_code,
1010 x_return_status => x_return_status,
1011 x_msg_count => x_msg_count,
1012 x_msg_data => x_msg_data);
1013
1014 -- Don't RAISE the EXCEPTION Yet. RUN ALL the validation procedures
1015 -- and show Exceptions all at once.
1016 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
1017 l_return_status := x_return_status;
1018 END IF;
1019
1020
1021 -- Check If the Record Exists
1022 OPEN C_Content_Type;
1023 FETCH C_Content_Type INTO l_Content_Type_Code;
1024 IF C_Content_Type%NOTFOUND THEN
1025 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1026 Fnd_Message.Set_name('IBC', 'Cannot Find Record to be Deleted');
1027 Fnd_Message.Set_Token('COLUMN', 'Content_Type_Code', FALSE);
1028 Fnd_Msg_Pub.ADD;
1029 END IF;
1030 CLOSE C_Content_Type;
1031 RAISE Fnd_Api.G_EXC_ERROR;
1032 END IF;
1033 CLOSE C_Content_Type;
1034
1035 OPEN C_Attribute_Type;
1036 FETCH C_Attribute_Type INTO l_Content_Type_Code;
1037 IF C_Attribute_Type%FOUND THEN
1038 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1039 Fnd_Message.Set_name('IBC', 'References_ExistContentTypeCode');
1040 Fnd_Message.Set_Token('COLUMN', 'Content_Type_Code', FALSE);
1041 Fnd_Msg_Pub.ADD;
1042 END IF;
1043 CLOSE C_Attribute_Type;
1044 RAISE Fnd_Api.G_EXC_ERROR;
1045 END IF;
1046 CLOSE C_Attribute_Type;
1047
1048
1049 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS
1050 OR l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1051 RAISE Fnd_Api.G_EXC_ERROR;
1052 END IF;
1053
1054 --
1055 -- Table Handler to Delete Row from IBC_ATTRIBUTE_TYPES
1056 -- If a Content Type does not have any attributes. Don't
1057 -- give Error
1058 --
1059 BEGIN
1060 Ibc_Attribute_Types_Pkg.delete_rows (
1061 p_content_type_code =>p_content_type_code
1062 );
1063 EXCEPTION WHEN NO_DATA_FOUND THEN
1064 NULL;
1065 END;
1066
1067 Ibc_Content_Types_Pkg.DELETE_ROW (
1068 p_CONTENT_TYPE_CODE =>p_CONTENT_TYPE_CODE);
1069
1070
1071 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
1072 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1073 Fnd_Message.Set_name('IBC', 'IBC_DELETE_ERROR');
1074 Fnd_Msg_Pub.ADD;
1075 END IF;
1076
1077 IF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR) THEN
1078 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1079 ELSIF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
1080 RAISE Fnd_Api.G_EXC_ERROR;
1081 END IF;
1082 END IF;
1083
1084 --
1085 -- End of API body
1086 --
1087
1088 -- Standard check for p_commit
1089 IF Fnd_Api.to_Boolean( p_commit )
1090 THEN
1091 COMMIT WORK;
1092 END IF;
1093
1094 -- Standard call to get message count and if count is 1, get message info.
1095 Fnd_Msg_Pub.Count_And_Get
1096 ( p_count => x_msg_count,
1097 p_data => x_msg_data
1098 );
1099
1100
1101 EXCEPTION
1102 WHEN Fnd_Api.G_EXC_ERROR THEN
1103 ROLLBACK;
1104 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
1105 P_API_name => L_API_name
1106 ,P_PKG_name => G_PKG_name
1107 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
1108 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
1109 ,P_SQLCODE => SQLCODE
1110 ,P_SQLERRM => SQLERRM
1111 ,X_MSG_COUNT => X_MSG_COUNT
1112 ,X_MSG_DATA => X_MSG_DATA
1113 ,X_RETURN_STATUS => X_RETURN_STATUS);
1114
1115 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1116 ROLLBACK;
1117 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
1118 P_API_name => L_API_name
1119 ,P_PKG_name => G_PKG_name
1120 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
1121 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
1122 ,P_SQLCODE => SQLCODE
1123 ,P_SQLERRM => SQLERRM
1124 ,X_MSG_COUNT => X_MSG_COUNT
1125 ,X_MSG_DATA => X_MSG_DATA
1126 ,X_RETURN_STATUS => X_RETURN_STATUS);
1127
1128 WHEN OTHERS THEN
1129 ROLLBACK;
1130 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
1131 P_API_name => L_API_name
1132 ,P_PKG_name => G_PKG_name
1133 ,P_EXCEPTION_LEVEL => Ibc_Utilities_Pvt.G_EXC_OTHERS
1134 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
1135 ,P_SQLCODE => SQLCODE
1136 ,P_SQLERRM => SQLERRM
1137 ,X_MSG_COUNT => X_MSG_COUNT
1138 ,X_MSG_DATA => X_MSG_DATA
1139 ,X_RETURN_STATUS => X_RETURN_STATUS);
1140
1141 END Delete_Content_Type;
1142
1143 PROCEDURE Update_Attribute_Type(
1144 P_Api_Version_Number IN NUMBER,
1145 P_Init_Msg_List IN VARCHAR2 ,--:= FND_API.G_FALSE,
1146 P_Commit IN VARCHAR2 ,--:= FND_API.G_FALSE,
1147 P_Validation_Level IN NUMBER ,--:= FND_API.G_VALID_LEVEL_FULL,
1148 P_Attribute_Type_Rec IN Ibc_Ctype_Pvt.Attribute_Type_Rec_Type,
1149 X_Return_Status OUT NOCOPY VARCHAR2,
1150 X_Msg_Count OUT NOCOPY NUMBER,
1151 X_Msg_Data OUT NOCOPY VARCHAR2
1152 )
1153 IS
1154
1155 CURSOR C_Content_Type(p_Content_Type_Code IN VARCHAR2) IS
1156 SELECT Content_Type_Code
1157 FROM ibc_content_types_b
1158 WHERE Content_Type_Code = p_Content_Type_Code;
1159
1160 CURSOR C_Attribute_Type(p_content_type_code IN VARCHAR2
1161 ,p_attribute_type_code IN VARCHAR2)
1162 IS
1163 SELECT '1'
1164 FROM IBC_ATTRIBUTE_TYPES_B
1165 WHERE content_type_code = p_content_type_code
1166 AND attribute_type_code = p_attribute_type_code;
1167
1168 l_temp CHAR(1);
1169 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
1170
1171 l_api_version_number NUMBER := 1.0;
1172 l_api_name VARCHAR2(50) := 'Update_Attribute_Type';
1173 lx_rowid VARCHAR2(240);
1174 l_content_type_code VARCHAR2(100);
1175
1176 l_attribute_type_rec Ibc_Ctype_Pvt.Attribute_Type_Rec_Type := P_Attribute_Type_Rec;
1177 l_old_attribute_type_rec Ibc_Ctype_Pvt.Attribute_Type_Rec_Type;
1178
1179 BEGIN
1180
1181 -- Initialize API return status to SUCCESS
1182 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1183
1184 -- Check If Row exists
1185 l_old_attribute_type_rec := Query_Attribute_Type_Row
1186 (p_Content_Type_Code => l_attribute_type_rec.content_type_code
1187 ,p_attribute_Type_Code => l_attribute_type_rec.Attribute_type_code);
1188
1189
1190 -- Begin Validation for Attribute Type Record
1191 -- Check for all the NOT NULL Columns
1192 -- Attribute_Type_Code Cannot be NULL
1193 Ibc_Validate_Pvt.validate_NotNULL_VARCHAR2 (
1194 p_init_msg_list => Fnd_Api.G_FALSE,
1195 p_column_name => 'Attribute_Type_Code',
1196 p_notnull_column=> l_attribute_type_rec.attribute_type_code,
1197 x_return_status => x_return_status,
1198 x_msg_count => x_msg_count,
1199 x_msg_data => x_msg_data);
1200
1201 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
1202 l_return_status := x_return_status;
1203 END IF;
1204
1205 --
1206 -- Validate the Content Type Code in Attribute Rec
1207 Ibc_Validate_Pvt.validate_NotNULL_VARCHAR2 (
1208 p_init_msg_list => Fnd_Api.G_FALSE,
1209 p_column_name => 'Content_Type_Code',
1210 p_notnull_column=> l_attribute_type_rec.Content_Type_Code,
1211 x_return_status => x_return_status,
1212 x_msg_count => x_msg_count,
1213 x_msg_data => x_msg_data);
1214
1215 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
1216 l_return_status := x_return_status;
1217 END IF;
1218
1219
1220 IF l_attribute_type_rec.Attribute_type_name <> Fnd_Api.G_MISS_CHAR THEN
1221 -- Content_TYpe_name Cannot be NULL
1222 Ibc_Validate_Pvt.validate_NotNULL_VARCHAR2 (
1223 p_init_msg_list => Fnd_Api.G_FALSE,
1224 p_column_name => 'Attribute_Type_name',
1225 p_notnull_column=> l_attribute_type_rec.Attribute_type_name,
1226 x_return_status => x_return_status,
1227 x_msg_count => x_msg_count,
1228 x_msg_data => x_msg_data);
1229
1230 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
1231 l_return_status := x_return_status;
1232 END IF;
1233
1234 END IF;
1235
1236
1237 IF l_attribute_type_rec.Data_Type_code <> Fnd_Api.G_MISS_CHAR THEN
1238 -- Data_Type_Code Cannot be NULL
1239 Ibc_Validate_Pvt.validate_NotNULL_VARCHAR2 (
1240 p_init_msg_list => Fnd_Api.G_FALSE,
1241 p_column_name => 'Data_Type_code',
1242 p_notnull_column=> l_attribute_type_rec.Data_Type_code,
1243 x_return_status => x_return_status,
1244 x_msg_count => x_msg_count,
1245 x_msg_data => x_msg_data);
1246
1247 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
1248 l_return_status := x_return_status;
1249 END IF;
1250
1251 END IF;
1252
1253
1254 IF l_attribute_type_rec.Data_Type_code <> Fnd_Api.G_MISS_CHAR THEN
1255 -- Validate Data Type Code
1256 Ibc_Validate_Pvt.validate_Data_Type_Code(
1257 p_init_msg_list => Fnd_Api.G_FALSE,
1258 p_data_Type_Code => l_attribute_type_rec.Data_Type_code,
1259 x_return_status => x_return_status,
1260 x_msg_count => x_msg_count,
1261 x_msg_data => x_msg_data);
1262
1263 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
1264 l_return_status := x_return_status;
1265 END IF;
1266 END IF;
1267
1268
1269 IF l_attribute_type_rec.Default_value <> Fnd_Api.G_MISS_CHAR THEN
1270
1271 IF l_attribute_type_rec.Data_Type_code = Fnd_Api.G_MISS_CHAR THEN
1272 l_attribute_type_rec.data_type_code := l_old_attribute_type_rec.data_type_code;
1273 END IF;
1274
1275 -- Validate Default Value
1276 Ibc_Validate_Pvt.validate_Default_Value(
1277 p_init_msg_list => Fnd_Api.G_FALSE,
1278 p_data_type_code => l_attribute_type_rec.data_type_code,
1279 p_default_Value => l_attribute_type_rec.Default_value,
1280 x_return_status => x_return_status,
1281 x_msg_count => x_msg_count,
1282 x_msg_data => x_msg_data);
1283
1284 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
1285 l_return_status := x_return_status;
1286 END IF;
1287 END IF;
1288
1289
1290 IF l_attribute_type_rec.Reference_Code <> Fnd_Api.G_MISS_CHAR THEN
1291
1292 IF l_attribute_type_rec.Data_Type_code = Fnd_Api.G_MISS_CHAR THEN
1293 l_attribute_type_rec.data_type_code := l_old_attribute_type_rec.data_type_code;
1294 END IF;
1295
1296 -- Validate Reference code
1297 Ibc_Validate_Pvt.validate_Reference_Code(
1298 p_init_msg_list => Fnd_Api.G_FALSE,
1299 p_data_type_Code => l_attribute_type_rec.data_type_Code,
1300 p_Reference_Code => l_attribute_type_rec.Reference_Code,
1301 x_return_status => x_return_status,
1302 x_msg_count => x_msg_count,
1303 x_msg_data => x_msg_data);
1304
1305 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
1306 l_return_status := x_return_status;
1307 END IF;
1308
1309 END IF;
1310
1311
1312
1313 IF l_attribute_type_rec.min_instances IS NULL THEN
1314 l_attribute_type_rec.min_instances := 0;
1315 END IF;
1316
1317 -- IF l_attribute_type_rec.max_instances IS NULL THEN
1318 -- l_attribute_type_rec.max_instances := 1;
1319 -- END IF;
1320
1321 IF l_attribute_type_rec.Min_instances = Fnd_Api.G_MISS_NUM THEN
1322 l_attribute_type_rec.Min_instances := l_old_attribute_type_rec.Min_instances;
1323 END IF;
1324
1325 IF l_attribute_type_rec.Max_instances = Fnd_Api.G_MISS_NUM THEN
1326 l_attribute_type_rec.Max_instances := l_old_attribute_type_rec.Max_instances;
1327 END IF;
1328
1329 -- Validate Min Max Instances
1330 Ibc_Validate_Pvt.validate_Min_Max_Instances(
1331 p_init_msg_list => Fnd_Api.G_FALSE,
1332 p_Min_Instances => l_attribute_type_rec.Min_instances,
1333 p_Max_Instances => l_attribute_type_rec.Max_instances,
1334 x_return_status => x_return_status,
1335 x_msg_count => x_msg_count,
1336 x_msg_data => x_msg_data);
1337
1338 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
1339 l_return_status := x_return_status;
1340 END IF;
1341
1342
1343 IF l_return_status<>Fnd_Api.G_RET_STS_SUCCESS
1344 OR x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
1345 x_return_status := l_return_status;
1346 RAISE Fnd_Api.G_EXC_ERROR;
1347 END IF;
1348
1349
1350 -- End Validation for Attribute Type Record
1351
1352 Ibc_Attribute_Types_Pkg.Update_row (
1353 p_attribute_type_code =>l_attribute_type_rec.attribute_type_code,
1354 p_content_type_code =>l_attribute_type_rec.content_type_code,
1355 p_data_type_code =>l_attribute_type_rec.data_type_code,
1356 p_data_length =>l_attribute_type_rec.data_length,
1357 p_min_instances =>l_attribute_type_rec.min_instances,
1358 p_max_instances =>l_attribute_type_rec.max_instances,
1359 p_reference_code =>l_attribute_type_rec.reference_code,
1360 p_default_value =>l_attribute_type_rec.default_value,
1361 p_updateable_flag =>l_attribute_type_rec.updateable_flag,
1362 p_object_version_number =>l_attribute_type_rec.object_version_number,
1363 p_attribute_type_name =>l_attribute_type_rec.attribute_type_name,
1364 p_description =>l_attribute_type_rec.description,
1365 p_last_update_date =>l_attribute_type_rec.last_update_date,
1366 p_last_updated_by =>l_attribute_type_rec.last_updated_by,
1367 p_last_update_login =>l_attribute_type_rec.last_update_login
1368 );
1369
1370 END Update_attribute_type;
1371
1372
1373 PROCEDURE Delete_Attribute_Type(
1374 P_Api_Version_Number IN NUMBER,
1375 P_Init_Msg_List IN VARCHAR2 ,--:= FND_API.G_FALSE,
1376 P_Commit IN VARCHAR2 ,--:= FND_API.G_FALSE,
1377 P_Validation_Level IN NUMBER ,--:= FND_API.G_VALID_LEVEL_FULL,
1378 P_Attribute_Type_Rec IN Ibc_Ctype_Pvt.Attribute_Type_Rec_Type,
1379 X_Return_Status OUT NOCOPY VARCHAR2,
1380 X_Msg_Count OUT NOCOPY NUMBER,
1381 X_Msg_Data OUT NOCOPY VARCHAR2
1382 )
1383 IS
1384 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
1385 l_api_name VARCHAR2(50) := 'Delete_Attribute_Type';
1386 l_attribute_type_rec Ibc_Ctype_Pvt.Attribute_Type_Rec_Type := P_Attribute_Type_Rec;
1387
1388 BEGIN
1389
1390 -- Initialize API return status to SUCCESS
1391 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1392
1393 IF l_attribute_type_rec.attribute_type_code IN (G_NAME,G_DESCRIPTION) THEN
1394 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1395 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1396 Fnd_Message.Set_name('IBC', 'Name OR Description Cannot be Deleted');
1397 Fnd_Msg_Pub.ADD;
1398 END IF;
1399 RAISE Fnd_Api.G_EXC_ERROR;
1400 END IF;
1401
1402 -- Begin Validation for Attribute Type Record
1403 -- Check for all the NOT NULL Columns
1404 -- Attribute_Type_Code Cannot be NULL
1405 Ibc_Validate_Pvt.validate_NotNULL_VARCHAR2 (
1406 p_init_msg_list => Fnd_Api.G_FALSE,
1407 p_column_name => 'Attribute_Type_Code',
1408 p_notnull_column=> l_attribute_type_rec.attribute_type_code,
1409 x_return_status => x_return_status,
1410 x_msg_count => x_msg_count,
1411 x_msg_data => x_msg_data);
1412
1413 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
1414 l_return_status := x_return_status;
1415 END IF;
1416
1417 --
1418 -- Validate the Content Type Code in Attribute Rec
1419 Ibc_Validate_Pvt.validate_NotNULL_VARCHAR2 (
1420 p_init_msg_list => Fnd_Api.G_FALSE,
1421 p_column_name => 'Content_Type_Code',
1422 p_notnull_column=> l_attribute_type_rec.Content_Type_Code,
1423 x_return_status => x_return_status,
1424 x_msg_count => x_msg_count,
1425 x_msg_data => x_msg_data);
1426
1427 IF x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
1428 l_return_status := x_return_status;
1429 END IF;
1430
1431
1432 IF l_return_status<>Fnd_Api.G_RET_STS_SUCCESS
1433 OR x_return_status<>Fnd_Api.G_RET_STS_SUCCESS THEN
1434 x_return_status := l_return_status;
1435 RAISE Fnd_Api.G_EXC_ERROR;
1436 END IF;
1437
1438
1439 Ibc_Attribute_Types_Pkg.delete_row (
1440 p_attribute_type_code =>l_attribute_type_rec.attribute_type_code,
1441 p_content_type_code =>l_attribute_type_rec.content_type_code
1442 );
1443
1444 END Delete_Attribute_Type;
1445
1446
1447
1448 PROCEDURE get_Attribute_Type_LOV(
1449 P_Api_Version_Number IN NUMBER
1450 ,P_Init_Msg_List IN VARCHAR2 --:= FND_API.G_FALSE
1451 ,p_content_type_code IN VARCHAR2 --1
1452 ,p_attribute_type_code IN VARCHAR2 --2
1453 ,x_code OUT NOCOPY JTF_VARCHAR2_TABLE_100 --4
1454 ,x_name OUT NOCOPY JTF_VARCHAR2_TABLE_300 -- 5
1455 ,x_description OUT NOCOPY JTF_VARCHAR2_TABLE_2000 --3
1456 ,X_Return_Status OUT NOCOPY VARCHAR2 --6
1457 ,X_Msg_Count OUT NOCOPY NUMBER -- 7
1458 ,X_Msg_Data OUT NOCOPY VARCHAR2 -- 8
1459 ) IS
1460
1461 CURSOR C_Attribs IS
1462 SELECT A.flex_value_set_id,validation_type
1463 FROM IBC_ATTRIBUTE_TYPES_B A, fnd_flex_value_sets F
1464 WHERE a.flex_value_set_id = F.flex_value_set_id
1465 AND A.attribute_type_code = p_attribute_type_code
1466 AND A.content_type_code = p_content_type_code;
1467
1468 l_value_set_id NUMBER;
1469 lx_select VARCHAR2(32000);
1470 lx_success VARCHAR2(10);
1471 lx_mapping_code VARCHAR2(32000);
1472 l_vset_type VARCHAR2(1);
1473 l_api_name VARCHAR2(30) := 'Get_Attribute_Type_LOV';
1474
1475 BEGIN
1476
1477 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1478
1479 OPEN C_attribs;
1480 FETCH C_attribs INTO l_value_set_id,l_vset_type;
1481
1482 IF C_attribs%NOTFOUND THEN
1483 CLOSE C_ATTRIBS;
1484 RETURN;
1485 END IF;
1486 -- FND_MESSAGE.Set_name('IBC', 'Invalid Flex Value SET');
1487 -- FND_MESSAGE.Set_Token('COLUMN',p_attribute_type_code, FALSE);
1488 -- FND_MSG_PUB.ADD;
1489 -- CLOSE C_attribs;
1490 -- RAISE FND_API.G_EXC_ERROR;
1491 -- END IF;
1492
1493 CLOSE C_ATTRIBS;
1494
1495 -- dbms_output.put_line(l_value_set_id);
1496 -- dbms_output.put_line(l_vset_type);
1497
1498 IF l_vset_type = 'F' THEN
1499 Fnd_Flex_Val_Api.get_table_vset_select
1500 (p_value_set_id => l_value_set_id,
1501 x_select =>lx_select,
1502 x_mapping_code =>lx_mapping_code,
1503 x_success =>lx_success);
1504 -- dbms_output.put_line(lx_success);
1505 ELSIF l_vset_type = 'I' THEN
1506 Fnd_Flex_Val_Api.get_independent_vset_select
1507 (p_value_set_id => l_value_set_id,
1508 x_select =>lx_select,
1509 x_mapping_code =>lx_mapping_code,
1510 x_success =>lx_success);
1511 -- dbms_output.put_line(lx_success);
1512 ELSE
1513 RETURN;
1514 END IF;
1515
1516
1517
1518 lx_select := REPLACE(UPPER(lx_select),'FROM','BULK COLLECT INTO :tab1,:tab2,:tab3 FROM ');
1519
1520 -- dbms_output.put_line(SUBSTR(lx_select,1,240));
1521 -- dbms_output.put_line(SUBSTR(lx_select,241,480));
1522
1523
1524 EXECUTE IMMEDIATE 'BEGIN ' || lx_select || '; END;'
1525 USING OUT x_code, OUT x_name, OUT x_description;
1526
1527 -- Standard call to get message count and if count is 1, get message info.
1528 Fnd_Msg_Pub.Count_And_Get
1529 ( p_count => x_msg_count,
1530 p_data => x_msg_data
1531 );
1532
1533
1534 EXCEPTION
1535 WHEN Fnd_Api.G_EXC_ERROR THEN
1536 ROLLBACK;
1537 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
1538 P_API_name => L_API_name
1539 ,P_PKG_name => G_PKG_name
1540 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
1541 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
1542 ,P_SQLCODE => SQLCODE
1543 ,P_SQLERRM => SQLERRM
1544 ,X_MSG_COUNT => X_MSG_COUNT
1545 ,X_MSG_DATA => X_MSG_DATA
1546 ,X_RETURN_STATUS => X_RETURN_STATUS);
1547
1548 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1549 ROLLBACK;
1550 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
1551 P_API_name => L_API_name
1552 ,P_PKG_name => G_PKG_name
1553 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
1554 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
1555 ,P_SQLCODE => SQLCODE
1556 ,P_SQLERRM => SQLERRM
1557 ,X_MSG_COUNT => X_MSG_COUNT
1558 ,X_MSG_DATA => X_MSG_DATA
1559 ,X_RETURN_STATUS => X_RETURN_STATUS);
1560
1561 WHEN OTHERS THEN
1562 ROLLBACK;
1563 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
1564 P_API_name => L_API_name
1565 ,P_PKG_name => G_PKG_name
1566 ,P_EXCEPTION_LEVEL => Ibc_Utilities_Pvt.G_EXC_OTHERS
1567 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
1568 ,P_SQLCODE => SQLCODE
1569 ,P_SQLERRM => SQLERRM
1570 ,X_MSG_COUNT => X_MSG_COUNT
1571 ,X_MSG_DATA => X_MSG_DATA
1572 ,X_RETURN_STATUS => X_RETURN_STATUS);
1573
1574 END get_Attribute_Type_LOV;
1575
1576
1577 PROCEDURE get_Content_Type(
1578 p_api_version_number IN NUMBER --DEFAULT 1.0
1579 ,p_init_msg_list IN VARCHAR2 --DEFAULT FND_API.g_false
1580 ,p_content_type_code IN VARCHAR2 -- 1
1581 ,x_content_type_name OUT NOCOPY VARCHAR2 -- 2
1582 ,x_content_type_description OUT NOCOPY VARCHAR2 -- 3
1583 ,x_content_type_status OUT NOCOPY VARCHAR2 -- 4
1584 ,X_ATTRIBUTE_TYPE_CODE OUT NOCOPY JTF_VARCHAR2_TABLE_100 -- 5
1585 ,x_ATTRIBUTE_TYPE_name OUT NOCOPY JTF_VARCHAR2_TABLE_300 -- 6
1586 ,x_DESCRIPTION OUT NOCOPY JTF_VARCHAR2_TABLE_2000 -- 7
1587 ,x_CONTENT_TYPE_CODE OUT NOCOPY JTF_VARCHAR2_TABLE_100 -- 8
1588 ,x_DATA_TYPE_CODE OUT NOCOPY JTF_VARCHAR2_TABLE_100 -- 9
1589 ,x_DATA_LENGTH OUT NOCOPY JTF_NUMBER_TABLE -- 10
1590 ,x_MIN_INSTANCES OUT NOCOPY JTF_NUMBER_TABLE -- 11
1591 ,x_MAX_INSTANCES OUT NOCOPY JTF_NUMBER_TABLE -- 12
1592 ,x_Flex_value_set_id OUT NOCOPY JTF_NUMBER_TABLE -- 13
1593 ,x_REFERENCE_CODE OUT NOCOPY JTF_VARCHAR2_TABLE_100 -- 14
1594 ,x_DEFAULT_VALUE OUT NOCOPY JTF_VARCHAR2_TABLE_300 -- 15
1595 ,x_UPDATEABLE_FLAG OUT NOCOPY JTF_VARCHAR2_TABLE_100 -- 16 Varchar2(1)
1596 ,x_CREATED_BY OUT NOCOPY JTF_NUMBER_TABLE -- 17
1597 ,x_CREATION_DATE OUT NOCOPY JTF_DATE_TABLE -- 18
1598 ,x_LAST_UPDATED_BY OUT NOCOPY JTF_NUMBER_TABLE --19
1599 ,x_LAST_UPDATE_DATE OUT NOCOPY JTF_DATE_TABLE -- 20
1600 ,x_LAST_UPDATE_LOGIN OUT NOCOPY JTF_NUMBER_TABLE --21
1601 ,x_OBJECT_VERSION_NUMBER OUT NOCOPY JTF_NUMBER_TABLE --22
1602 ,x_return_status OUT NOCOPY VARCHAR2 -- 23
1603 ,x_msg_count OUT NOCOPY INTEGER --24
1604 ,x_msg_data OUT NOCOPY VARCHAR2 --25
1605 ,p_language IN VARCHAR2 --26
1606 )
1607 IS
1608
1609 CURSOR Cur_Content_Type(l_language IN VARCHAR2) IS
1610 SELECT content_type_name
1611 ,description
1612 ,content_type_status
1613 FROM
1614 IBC_CONTENT_TYPES_TL T,
1615 IBC_CONTENT_TYPES_B B
1616 WHERE
1617 B.CONTENT_TYPE_CODE = T.CONTENT_TYPE_CODE AND
1618 B.CONTENT_TYPE_CODE = p_content_type_Code AND
1619 T.LANGUAGE = l_language;
1620
1621
1622 --
1623
1624 CURSOR Cur_Attributes(l_language IN VARCHAR2) IS
1625 SELECT B.ATTRIBUTE_TYPE_CODE
1626 ,ATTRIBUTE_TYPE_name
1627 ,DESCRIPTION
1628 ,B.CONTENT_TYPE_CODE
1629 ,DATA_TYPE_CODE
1630 ,DATA_LENGTH
1631 ,MIN_INSTANCES
1632 ,MAX_INSTANCES
1633 ,Flex_value_set_id
1634 ,REFERENCE_CODE
1635 ,DEFAULT_VALUE
1636 ,UPDATEABLE_FLAG
1637 ,B.CREATED_BY
1638 ,B.CREATION_DATE
1639 ,B.LAST_UPDATED_BY
1640 ,B.LAST_UPDATE_DATE
1641 ,B.LAST_UPDATE_LOGIN
1642 ,B.OBJECT_VERSION_NUMBER
1643 FROM IBC_ATTRIBUTE_TYPES_B B,IBC_ATTRIBUTE_TYPES_TL T
1644 WHERE B.CONTENT_TYPE_CODE = p_Content_type_code
1645 AND B.content_type_code = T.CONTENT_TYPE_CODE
1646 AND B.ATTRIBUTE_TYPE_CODE = T.ATTRIBUTE_TYPE_CODE
1647 AND LANGUAGE = l_language
1648 ORDER BY DISPLAY_ORDER;
1649
1650 CURSOR CUR_LANG IS
1651 SELECT '1' FROM FND_LANGUAGES
1652 WHERE LANGUAGE_CODE = p_language;
1653
1654 l_api_name VARCHAR2(50) := 'Get_Content_Type';
1655 l_language VARCHAR2(4) := p_language;
1656 l_temp CHAR(1);
1657
1658 BEGIN
1659
1660 IF l_language IS NULL OR l_language = Fnd_Api.G_MISS_CHAR THEN
1661 l_language := USERENV('LANG');
1662 ELSE
1663 OPEN CUR_LANG;
1664 FETCH CUR_LANG INTO l_temp;
1665 IF CUR_LANG%NOTFOUND THEN
1666 Fnd_Message.Set_name('IBC', 'IBC_INVALID_LANGUAGE_CODE');
1667 Fnd_Message.Set_Token('COLUMN', 'LANGUAGE', FALSE);
1668 Fnd_Msg_Pub.ADD;
1669 CLOSE CUR_LANG;
1670 RAISE Fnd_Api.G_EXC_ERROR;
1671 END IF;
1672 END IF;
1673
1674
1675
1676 -- Initialize API return status to SUCCESS
1677 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1678
1679 OPEN Cur_Content_Type(l_language);
1680 FETCH Cur_Content_Type INTO x_content_type_name
1681 ,x_content_type_description
1682 ,x_content_type_status;
1683 IF Cur_Content_Type%NOTFOUND THEN
1684 Fnd_Message.Set_name('IBC', 'Invalid Content TYPE Code');
1685 Fnd_Message.Set_Token('COLUMN', 'Content_Type_Code', FALSE);
1686 Fnd_Msg_Pub.ADD;
1687 CLOSE Cur_Content_Type;
1688 RAISE Fnd_Api.G_EXC_ERROR;
1689 END IF;
1690 CLOSE Cur_Content_Type;
1691
1692
1693 OPEN Cur_Attributes(l_language);
1694
1695 FETCH Cur_Attributes BULK COLLECT INTO X_ATTRIBUTE_TYPE_CODE
1696 ,x_ATTRIBUTE_TYPE_name
1697 ,x_DESCRIPTION
1698 ,x_CONTENT_TYPE_CODE
1699 ,x_DATA_TYPE_CODE
1700 ,x_DATA_LENGTH
1701 ,x_MIN_INSTANCES
1702 ,x_MAX_INSTANCES
1703 ,x_Flex_value_set_id
1704 ,x_REFERENCE_CODE
1705 ,x_DEFAULT_VALUE
1706 ,x_UPDATEABLE_FLAG
1707 ,x_CREATED_BY
1708 ,x_CREATION_DATE
1709 ,x_LAST_UPDATED_BY
1710 ,x_LAST_UPDATE_DATE
1711 ,x_LAST_UPDATE_LOGIN
1712 ,x_OBJECT_VERSION_NUMBER;
1713
1714 CLOSE Cur_Attributes;
1715
1716
1717 -- IF x_return_status = 'S' THEN
1718 -- dbms_output.put_line('================= OUT PUT ======================');
1719 -- FOR i IN x_attribute_type_code.first..x_attribute_type_code.last LOOP
1720 -- dbms_output.put_line('x_attribute_type_code('||i||') =>' ||x_attribute_type_code(i));
1721 -- dbms_output.put_line('x_ATTRIBUTE_TYPE_name ('||i||') =>'||x_ATTRIBUTE_TYPE_name (i));
1722 -- dbms_output.put_line('x_DESCRIPTION ('||i||') =>' ||x_DESCRIPTION (i));
1723 -- dbms_output.put_line('x_CONTENT_TYPE_CODE ('||i||') =>' ||x_CONTENT_TYPE_CODE(i));
1724 -- dbms_output.put_line('x_DATA_TYPE_CODE ('||i||') =>' ||x_DATA_TYPE_CODE(i));
1725 -- dbms_output.put_line('===================END =============================');
1726 -- END LOOP;
1727 -- END IF;
1728
1729 -- Standard call to get message count and if count is 1, get message info.
1730 Fnd_Msg_Pub.Count_And_Get
1731 ( p_count => x_msg_count,
1732 p_data => x_msg_data
1733 );
1734
1735
1736 EXCEPTION
1737 WHEN Fnd_Api.G_EXC_ERROR THEN
1738 ROLLBACK;
1739 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
1740 P_API_name => L_API_name
1741 ,P_PKG_name => G_PKG_name
1742 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
1743 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
1744 ,P_SQLCODE => SQLCODE
1745 ,P_SQLERRM => SQLERRM
1746 ,X_MSG_COUNT => X_MSG_COUNT
1747 ,X_MSG_DATA => X_MSG_DATA
1748 ,X_RETURN_STATUS => X_RETURN_STATUS);
1749
1750 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1751 ROLLBACK;
1752 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
1753 P_API_name => L_API_name
1754 ,P_PKG_name => G_PKG_name
1755 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
1756 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
1757 ,P_SQLCODE => SQLCODE
1758 ,P_SQLERRM => SQLERRM
1759 ,X_MSG_COUNT => X_MSG_COUNT
1760 ,X_MSG_DATA => X_MSG_DATA
1761 ,X_RETURN_STATUS => X_RETURN_STATUS);
1762
1763 WHEN OTHERS THEN
1764 ROLLBACK;
1765 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
1766 P_API_name => L_API_name
1767 ,P_PKG_name => G_PKG_name
1768 ,P_EXCEPTION_LEVEL => Ibc_Utilities_Pvt.G_EXC_OTHERS
1769 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
1770 ,P_SQLCODE => SQLCODE
1771 ,P_SQLERRM => SQLERRM
1772 ,X_MSG_COUNT => X_MSG_COUNT
1773 ,X_MSG_DATA => X_MSG_DATA
1774 ,X_RETURN_STATUS => X_RETURN_STATUS);
1775
1776 END get_Content_Type;
1777
1778
1779 FUNCTION Query_Attribute_type_Row (
1780 p_attribute_type_code IN VARCHAR2,
1781 p_content_type_code IN VARCHAR2
1782 ) RETURN Ibc_Ctype_Pvt.Attribute_Type_Rec_Type
1783
1784 IS
1785 l_Attribute_Type_Rec Ibc_Ctype_Pvt.Attribute_Type_Rec_Type;
1786 BEGIN
1787 SELECT
1788 ATTRIBUTE_TYPE_CODE,
1789 ATTRIBUTE_TYPE_name,
1790 CONTENT_TYPE_CODE,
1791 CREATED_BY,
1792 CREATION_DATE,
1793 DATA_LENGTH,
1794 DATA_TYPE_CODE,
1795 DEFAULT_VALUE,
1796 DESCRIPTION,
1797 LAST_UPDATED_BY,
1798 LAST_UPDATE_DATE,
1799 LAST_UPDATE_LOGIN,
1800 MAX_INSTANCES,
1801 MIN_INSTANCES,
1802 OBJECT_VERSION_NUMBER,
1803 REFERENCE_CODE,
1804 UPDATEABLE_FLAG
1805 INTO
1806 l_Attribute_Type_Rec.ATTRIBUTE_TYPE_CODE,
1807 l_Attribute_Type_Rec.ATTRIBUTE_TYPE_name,
1808 l_Attribute_Type_Rec.CONTENT_TYPE_CODE,
1809 l_Attribute_Type_Rec.CREATED_BY,
1810 l_Attribute_Type_Rec.CREATION_DATE,
1811 l_Attribute_Type_Rec.DATA_LENGTH,
1812 l_Attribute_Type_Rec.DATA_TYPE_CODE,
1813 l_Attribute_Type_Rec.DEFAULT_VALUE,
1814 l_Attribute_Type_Rec.DESCRIPTION,
1815 l_Attribute_Type_Rec.LAST_UPDATED_BY,
1816 l_Attribute_Type_Rec.LAST_UPDATE_DATE,
1817 l_Attribute_Type_Rec.LAST_UPDATE_LOGIN,
1818 l_Attribute_Type_Rec.MAX_INSTANCES,
1819 l_Attribute_Type_Rec.MIN_INSTANCES,
1820 l_Attribute_Type_Rec.OBJECT_VERSION_NUMBER,
1821 l_Attribute_Type_Rec.REFERENCE_CODE,
1822 l_Attribute_Type_Rec.UPDATEABLE_FLAG
1823 FROM IBC_ATTRIBUTE_TYPES_VL
1824 WHERE attribute_type_code = p_attribute_type_code
1825 AND content_type_code = p_content_type_code;
1826
1827 RETURN l_attribute_type_rec;
1828
1829 END Query_Attribute_Type_Row;
1830
1831 FUNCTION get_ctype_rec RETURN Ibc_Ctype_Pvt.content_type_rec_type
1832 IS
1833 TMP_REC Ibc_Ctype_Pvt.content_type_rec_type;
1834 BEGIN
1835 RETURN TMP_REC;
1836 END get_ctype_rec;
1837
1838
1839
1840
1841
1842
1843 PROCEDURE Is_Valid_Flex_Value(
1844 P_Api_Version_Number IN NUMBER
1845 ,P_Init_Msg_List IN VARCHAR2
1846 ,p_flex_value_set_id IN NUMBER
1847 ,p_flex_value_code IN VARCHAR2
1848 ,x_exists OUT NOCOPY VARCHAR2
1849 ,X_Return_Status OUT NOCOPY VARCHAR2
1850 ,X_Msg_Count OUT NOCOPY NUMBER
1851 ,X_Msg_Data OUT NOCOPY VARCHAR2
1852 ) IS
1853
1854 CURSOR C_FlexValueSet IS
1855 SELECT flex_value_set_id,validation_type
1856 FROM fnd_flex_value_sets
1857 WHERE flex_value_set_id = p_flex_value_set_id;
1858
1859 l_value_set_id NUMBER;
1860 lx_select VARCHAR2(32000);
1861 lx_success VARCHAR2(10);
1862 lx_mapping_code VARCHAR2(32000);
1863 l_vset_type VARCHAR2(1);
1864 l_api_name VARCHAR2(30) := 'IS_VALID_FLEX_VALUE';
1865
1866 l_code JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
1867 l_name JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
1868 l_description JTF_VARCHAR2_TABLE_3000 := JTF_VARCHAR2_TABLE_3000();
1869 l_temp CHAR(1);
1870
1871 l_meaning VARCHAR2(240);
1872 l_id VARCHAR2(240);
1873
1874 CURSOR C_Flex_Value_Code IS
1875 SELECT '1'
1876 FROM TABLE(CAST(l_code AS JTF_VARCHAR2_TABLE_300)) A
1877 WHERE A.COLUMN_VALUE = p_flex_value_code;
1878
1879 CURSOR C_flex_validation_tab IS
1880 SELECT id_column_name,meaning_column_name
1881 FROM fnd_flex_validation_tables
1882 WHERE flex_value_set_id=p_flex_value_set_id;
1883
1884 BEGIN
1885
1886 x_exists := Fnd_Api.G_TRUE;
1887 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1888
1889 OPEN C_FlexValueSet;
1890 FETCH C_FlexValueSet INTO l_value_set_id,l_vset_type;
1891 IF C_FlexValueSet%NOTFOUND THEN
1892 Fnd_Message.Set_name('IBC', 'BAD_INPUT_VALUE');
1893 Fnd_Message.Set_Token('INPUT',p_flex_value_set_id, FALSE);
1894 Fnd_Msg_Pub.ADD;
1895 CLOSE C_FlexValueSet;
1896 x_exists := Fnd_Api.G_FALSE;
1897 RAISE Fnd_Api.G_EXC_ERROR;
1898 END IF;
1899 CLOSE C_FlexValueSet;
1900
1901 -- dbms_output.put_line(l_value_set_id);
1902 -- dbms_output.put_line(l_vset_type);
1903
1904 IF l_vset_type = 'F' THEN
1905 Fnd_Flex_Val_Api.get_table_vset_select
1906 (p_value_set_id => l_value_set_id,
1907 x_select =>lx_select,
1908 x_mapping_code =>lx_mapping_code,
1909 x_success =>lx_success);
1910
1911
1912 -- dbms_output.put_line(lx_success);
1913 OPEN C_flex_validation_tab;
1914 FETCH C_flex_validation_tab INTO l_meaning,l_id;
1915 CLOSE C_flex_validation_tab;
1916
1917 IF l_meaning IS NOT NULL AND l_id IS NOT NULL THEN
1918 lx_select := REPLACE(UPPER(lx_select),'FROM','BULK COLLECT INTO :tab1,:tab2,:tab3 FROM ');
1919 EXECUTE IMMEDIATE 'BEGIN ' || lx_select || '; END;'
1920 USING OUT l_code,OUT l_name,OUT l_description;
1921 ELSIF l_meaning IS NULL AND l_id IS NULL THEN
1922 lx_select := REPLACE(UPPER(lx_select),'FROM','BULK COLLECT INTO :tab1 FROM ');
1923 EXECUTE IMMEDIATE 'BEGIN ' || lx_select || '; END;'
1924 USING OUT l_code;
1925 ELSE
1926 lx_select := REPLACE(UPPER(lx_select),'FROM','BULK COLLECT INTO :tab1,:tab2 FROM ');
1927 EXECUTE IMMEDIATE 'BEGIN ' || lx_select || '; END;'
1928 USING OUT l_code,OUT l_description;
1929 END IF;
1930
1931 ELSIF l_vset_type = 'I' THEN
1932 Fnd_Flex_Val_Api.get_independent_vset_select
1933 (p_value_set_id => l_value_set_id,
1934 x_select =>lx_select,
1935 x_mapping_code =>lx_mapping_code,
1936 x_success =>lx_success);
1937 lx_select := REPLACE(UPPER(lx_select),'FROM','BULK COLLECT INTO :tab1,:tab2,:tab3 FROM ');
1938 EXECUTE IMMEDIATE 'BEGIN ' || lx_select || '; END;'
1939 USING OUT l_code,OUT l_name,OUT l_description;
1940 ELSE
1941 x_exists := Fnd_Api.G_FALSE;
1942 RETURN;
1943 END IF;
1944
1945 OPEN C_Flex_Value_Code;
1946 FETCH C_Flex_Value_Code INTO l_temp;
1947
1948 IF C_Flex_Value_Code%NOTFOUND THEN
1949 x_exists := Fnd_Api.G_FALSE;
1950 END IF;
1951
1952 -- Standard call to get message count and if count is 1, get message info.
1953 Fnd_Msg_Pub.Count_And_Get
1954 ( p_count => x_msg_count,
1955 p_data => x_msg_data
1956 );
1957
1958 EXCEPTION
1959 WHEN Fnd_Api.G_EXC_ERROR THEN
1960 ROLLBACK;
1961 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
1962 P_API_name => L_API_name
1963 ,P_PKG_name => G_PKG_name
1964 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
1965 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
1966 ,P_SQLCODE => SQLCODE
1967 ,P_SQLERRM => SQLERRM
1968 ,X_MSG_COUNT => X_MSG_COUNT
1969 ,X_MSG_DATA => X_MSG_DATA
1970 ,X_RETURN_STATUS => X_RETURN_STATUS);
1971
1972 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1973 ROLLBACK;
1974 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
1975 P_API_name => L_API_name
1976 ,P_PKG_name => G_PKG_name
1977 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
1978 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
1979 ,P_SQLCODE => SQLCODE
1980 ,P_SQLERRM => SQLERRM
1981 ,X_MSG_COUNT => X_MSG_COUNT
1982 ,X_MSG_DATA => X_MSG_DATA
1983 ,X_RETURN_STATUS => X_RETURN_STATUS);
1984
1985 WHEN OTHERS THEN
1986 ROLLBACK;
1987 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
1988 P_API_name => L_API_name
1989 ,P_PKG_name => G_PKG_name
1990 ,P_EXCEPTION_LEVEL => Ibc_Utilities_Pvt.G_EXC_OTHERS
1991 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
1992 ,P_SQLCODE => SQLCODE
1993 ,P_SQLERRM => SQLERRM
1994 ,X_MSG_COUNT => X_MSG_COUNT
1995 ,X_MSG_DATA => X_MSG_DATA
1996 ,X_RETURN_STATUS => X_RETURN_STATUS);
1997 END Is_Valid_Flex_Value;
1998
1999
2000 PROCEDURE get_sql_from_flex(
2001 P_Api_Version_Number IN NUMBER
2002 ,P_Init_Msg_List IN VARCHAR2 --:= FND_API.G_FALSE
2003 ,p_flex_value_set_id IN NUMBER --1
2004 ,x_select OUT NOCOPY VARCHAR2 --4
2005 ,X_Return_Status OUT NOCOPY VARCHAR2 --6
2006 ,X_Msg_Count OUT NOCOPY NUMBER -- 7
2007 ,X_Msg_Data OUT NOCOPY VARCHAR2 -- 8
2008 ) IS
2009
2010 CURSOR C_flex IS
2011 SELECT flex_value_set_id,validation_type
2012 FROM fnd_flex_value_sets F
2013 WHERE F.flex_value_set_id = p_flex_value_set_id;
2014
2015 l_value_set_id NUMBER;
2016 lx_select VARCHAR2(32000);
2017 lx_success VARCHAR2(10);
2018 lx_mapping_code VARCHAR2(32000);
2019 l_vset_type VARCHAR2(1);
2020 l_api_name VARCHAR2(30) := 'GET_SQL_FROM_FLEX';
2021
2022 BEGIN
2023
2024 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2025
2026 OPEN C_flex;
2027 FETCH C_flex INTO l_value_set_id,l_vset_type;
2028
2029 IF C_flex%NOTFOUND THEN
2030 CLOSE C_flex;
2031 x_return_status := FND_API.G_RET_STS_ERROR;
2032 FND_MESSAGE.Set_Name('IBC', 'BAD_INPUT_VALUE');
2033 FND_MESSAGE.Set_Token('INPUT', 'FLEX_VALUE_SET_ID', FALSE);
2034 FND_MSG_PUB.ADD;
2035 RAISE FND_API.G_EXC_ERROR;
2036 END IF;
2037
2038
2039 CLOSE C_flex;
2040
2041 -- dbms_output.put_line(l_value_set_id);
2042 -- dbms_output.put_line(l_vset_type);
2043
2044 IF l_vset_type = 'F' THEN
2045 Fnd_Flex_Val_Api.get_table_vset_select
2046 (p_value_set_id => l_value_set_id,
2047 x_select =>lx_select,
2048 x_mapping_code =>lx_mapping_code,
2049 x_success =>lx_success);
2050 -- dbms_output.put_line(lx_success);
2051 ELSIF l_vset_type = 'I' THEN
2052 Fnd_Flex_Val_Api.get_independent_vset_select
2053 (p_value_set_id => l_value_set_id,
2054 x_select =>lx_select,
2055 x_mapping_code =>lx_mapping_code,
2056 x_success =>lx_success);
2057 -- dbms_output.put_line(lx_success);
2058 ELSE
2059 x_return_status := FND_API.G_RET_STS_ERROR;
2060 FND_MESSAGE.Set_Name('IBC', 'IBC_INVALID_FLEX_VALUE_SET');
2061 FND_MSG_PUB.ADD;
2062 RAISE FND_API.G_EXC_ERROR;
2063 END IF;
2064
2065
2066 x_select := lx_select;
2067
2068
2069 -- dbms_output.put_line(SUBSTR(x_select,1,240));
2070 -- dbms_output.put_line(SUBSTR(x_select,241,480));
2071
2072
2073 -- Standard call to get message count and if count is 1, get message info.
2074 Fnd_Msg_Pub.Count_And_Get
2075 ( p_count => x_msg_count,
2076 p_data => x_msg_data
2077 );
2078
2079
2080 EXCEPTION
2081 WHEN Fnd_Api.G_EXC_ERROR THEN
2082 ROLLBACK;
2083 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
2084 P_API_name => L_API_name
2085 ,P_PKG_name => G_PKG_name
2086 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
2087 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
2088 ,P_SQLCODE => SQLCODE
2089 ,P_SQLERRM => SQLERRM
2090 ,X_MSG_COUNT => X_MSG_COUNT
2091 ,X_MSG_DATA => X_MSG_DATA
2092 ,X_RETURN_STATUS => X_RETURN_STATUS);
2093
2094 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2095 ROLLBACK;
2096 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
2097 P_API_name => L_API_name
2098 ,P_PKG_name => G_PKG_name
2099 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
2100 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
2101 ,P_SQLCODE => SQLCODE
2102 ,P_SQLERRM => SQLERRM
2103 ,X_MSG_COUNT => X_MSG_COUNT
2104 ,X_MSG_DATA => X_MSG_DATA
2105 ,X_RETURN_STATUS => X_RETURN_STATUS);
2106
2107 WHEN OTHERS THEN
2108 ROLLBACK;
2109 Ibc_Utilities_Pvt.HANDLE_EXCEPTIONS(
2110 P_API_name => L_API_name
2111 ,P_PKG_name => G_PKG_name
2112 ,P_EXCEPTION_LEVEL => Ibc_Utilities_Pvt.G_EXC_OTHERS
2113 ,P_PACKAGE_TYPE => Ibc_Utilities_Pvt.G_PVT
2114 ,P_SQLCODE => SQLCODE
2115 ,P_SQLERRM => SQLERRM
2116 ,X_MSG_COUNT => X_MSG_COUNT
2117 ,X_MSG_DATA => X_MSG_DATA
2118 ,X_RETURN_STATUS => X_RETURN_STATUS);
2119
2120 END get_sql_from_flex;
2121
2122
2123
2124 END Ibc_Ctype_Pvt;