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