[Home] [Help]
PACKAGE BODY: APPS.IBC_DIRECTORY_NODE_RELS_PKG
Source
1 PACKAGE BODY Ibc_Directory_Node_Rels_Pkg AS
2 /* $Header: ibctdrlb.pls 120.2 2005/08/08 14:10:52 appldev ship $*/
3
4 -- Purpose: Table Handler for Ibc_Directory_Node_Rels table.
5
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- Sri Rangarajan 01/06/2002 Created Package
10 -- vicho 11/05/2002 Remove G_MISS defaulting on UPDATE_ROW
11
12 PROCEDURE INSERT_ROW (
13 x_ROWID OUT NOCOPY VARCHAR2,
14 px_Directory_Node_Rel_ID IN OUT NOCOPY NUMBER,
15 p_CHILD_DIR_NODE_ID IN NUMBER,
16 p_PARENT_DIR_NODE_ID IN NUMBER,
17 p_OBJECT_VERSION_NUMBER IN NUMBER,
18 p_CREATION_DATE IN DATE,
19 p_CREATED_BY IN NUMBER,
20 p_LAST_UPDATE_DATE IN DATE,
21 p_LAST_UPDATED_BY IN NUMBER,
22 p_LAST_UPDATE_LOGIN IN NUMBER
23 ) IS
24 CURSOR C IS SELECT ROWID FROM IBC_DIRECTORY_NODE_RELS
25 WHERE Directory_Node_Rel_ID = px_Directory_Node_Rel_ID;
26
27 CURSOR c2 IS SELECT ibc_Directory_Node_Rels_s1.NEXTVAL FROM dual;
28
29 CURSOR c_dirnode(p_dir_node_id NUMBER) IS
30 SELECT directory_node_code
31 FROM ibc_directory_nodes_b
32 WHERE directory_node_id = p_dir_node_id;
33
34 l_object_type VARCHAR2(30);
35 l_object_id NUMBER;
36
37 BEGIN
38
39 -- Validating Uniqueness for Name in a particular directory
40 FOR r_dirnode IN c_dirnode(p_child_dir_node_id) LOOP
41 IF IBC_UTILITIES_PVT.is_name_already_used(
42 p_dir_node_id => p_parent_dir_node_id,
43 p_name => r_dirnode.directory_node_code,
44 p_language => USERENV('lang'),
45 p_chk_dir_node_id => p_child_dir_node_id,
46 x_object_type => l_object_type,
47 x_object_id => l_object_id)
48 THEN
49 Fnd_Message.Set_Name('IBC', 'IBC_INVALID_FOLDER_NAME');
50 Fnd_Msg_Pub.ADD;
51 RAISE Fnd_Api.G_EXC_ERROR;
52 END IF;
53 END LOOP;
54
55 -- Primary key validation check
56
57 IF ((px_Directory_Node_Rel_ID IS NULL) OR
58 (px_Directory_Node_Rel_ID = FND_API.G_MISS_NUM))
59 THEN
60 OPEN c2;
61 FETCH c2 INTO px_Directory_Node_Rel_ID;
62 CLOSE c2;
63 END IF;
64
65 INSERT INTO IBC_DIRECTORY_NODE_RELS (
66 Directory_Node_Rel_ID,
67 CHILD_DIR_NODE_ID,
68 PARENT_DIR_NODE_ID,
69 OBJECT_VERSION_NUMBER,
70 CREATION_DATE,
71 CREATED_BY,
72 LAST_UPDATE_DATE,
73 LAST_UPDATED_BY,
74 LAST_UPDATE_LOGIN
75 ) VALUES (
76 px_Directory_Node_Rel_ID ,
77 p_CHILD_DIR_NODE_ID,
78 p_PARENT_DIR_NODE_ID,
79 p_OBJECT_VERSION_NUMBER,
80 DECODE(p_creation_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
81 p_creation_date),
82 DECODE(p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
83 NULL, FND_GLOBAL.user_id, p_created_by),
84 DECODE(p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
85 p_last_update_date),
86 DECODE(p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
87 NULL, FND_GLOBAL.user_id, p_last_updated_by),
88 DECODE(p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.login_id,
89 NULL, FND_GLOBAL.login_id, p_last_update_login)
90 );
91
92 OPEN c;
93 FETCH c INTO x_ROWID;
94 IF (c%NOTFOUND) THEN
95 CLOSE c;
96 RAISE NO_DATA_FOUND;
97 END IF;
98 CLOSE c;
99
100 END INSERT_ROW;
101
102 PROCEDURE LOCK_ROW (
103 p_Directory_Node_Rel_ID IN NUMBER,
104 p_OBJECT_VERSION_NUMBER IN NUMBER
105 ) IS
106 CURSOR c IS SELECT
107 OBJECT_VERSION_NUMBER
108 FROM IBC_DIRECTORY_NODE_RELS
109 WHERE Directory_Node_Rel_ID = p_Directory_Node_Rel_ID
110 FOR UPDATE OF CHILD_DIR_NODE_ID NOWAIT;
111 recinfo c%ROWTYPE;
112
113 BEGIN
114 OPEN c;
115 FETCH c INTO recinfo;
116 IF (c%NOTFOUND) THEN
117 CLOSE c;
118 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
119 app_exception.raise_exception;
120 END IF;
121 CLOSE c;
122 IF ( (recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
123 ) THEN
124 NULL;
125 ELSE
126 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
127 app_exception.raise_exception;
128 END IF;
129
130 END LOCK_ROW;
131
132 PROCEDURE UPDATE_ROW (
133 p_Directory_Node_Rel_ID IN NUMBER,
134 p_CHILD_DIR_NODE_ID IN NUMBER,
135 p_LAST_UPDATED_BY IN NUMBER,
136 p_LAST_UPDATE_DATE IN DATE,
137 p_LAST_UPDATE_LOGIN IN NUMBER,
138 p_OBJECT_VERSION_NUMBER IN NUMBER,
139 p_PARENT_DIR_NODE_ID IN NUMBER
140 ) IS
141
142 CURSOR c_dirnode(p_dir_node_id NUMBER) IS
143 SELECT directory_node_code
144 FROM ibc_directory_nodes_b
145 WHERE directory_node_id = p_dir_node_id;
146
147 l_object_type VARCHAR2(30);
148 l_object_id NUMBER;
149
150 BEGIN
151
152 -- Validating Uniqueness for Name in a particular directory
153 FOR r_dirnode IN c_dirnode(p_child_dir_node_id) LOOP
154 IF IBC_UTILITIES_PVT.is_name_already_used(
155 p_dir_node_id => p_parent_dir_node_id,
156 p_name => r_dirnode.directory_node_code,
157 p_language => USERENV('lang'),
158 p_chk_dir_node_id => p_child_dir_node_id,
159 x_object_type => l_object_type,
160 x_object_id => l_object_id)
161 THEN
162 Fnd_Message.Set_Name('IBC', 'IBC_INVALID_FOLDER_NAME');
163 Fnd_Msg_Pub.ADD;
164 RAISE Fnd_Api.G_EXC_ERROR;
165 END IF;
166 END LOOP;
167
168 UPDATE IBC_DIRECTORY_NODE_RELS SET
169 CHILD_DIR_NODE_ID = DECODE(p_CHILD_DIR_NODE_ID,FND_API.G_MISS_NUM,NULL,NULL,CHILD_DIR_NODE_ID,p_CHILD_DIR_NODE_ID),
170 PARENT_DIR_NODE_ID = DECODE(p_PARENT_DIR_NODE_ID,FND_API.G_MISS_NUM,NULL,NULL,PARENT_DIR_NODE_ID,p_PARENT_DIR_NODE_ID),
171 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
172 last_update_date = DECODE(p_last_update_date, FND_API.G_MISS_DATE, SYSDATE,
173 NULL, SYSDATE, p_last_update_date),
174 last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM,
175 FND_GLOBAL.user_id, NULL, FND_GLOBAL.user_id,
176 p_last_updated_by),
177 last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM,
178 FND_GLOBAL.login_id, NULL, FND_GLOBAL.login_id,
179 p_last_update_login)
180 WHERE Directory_Node_Rel_ID = p_Directory_Node_Rel_ID
181 AND object_version_number = DECODE(p_object_version_number,
182 FND_API.G_MISS_NUM,object_version_number,
183 NULL,object_version_number,
184 p_object_version_number);
185
186 IF (SQL%NOTFOUND) THEN
187 RAISE NO_DATA_FOUND;
188 END IF;
189
190 END UPDATE_ROW;
191
192 PROCEDURE DELETE_ROW (
193 p_Directory_Node_Rel_ID IN NUMBER
194 ) IS
195 BEGIN
196
197 DELETE FROM IBC_DIRECTORY_NODE_RELS
198 WHERE Directory_Node_Rel_ID = p_Directory_Node_Rel_ID;
199
200 IF (SQL%NOTFOUND) THEN
201 RAISE NO_DATA_FOUND;
202 END IF;
203 END DELETE_ROW;
204
205 PROCEDURE LOAD_ROW (
206 p_UPLOAD_MODE IN VARCHAR2,
207 p_DIRECTORY_NODE_REL_ID IN NUMBER,
208 p_child_dir_NODE_ID IN NUMBER,
209 p_pARENT_dir_NODE_ID IN NUMBER,
210 p_OWNER IN VARCHAR2,
211 p_LAST_UPDATE_DATE IN VARCHAR2) IS
212
213 CURSOR c_dirnode(p_dir_node_id NUMBER) IS
214 SELECT directory_node_code
215 FROM ibc_directory_nodes_b
216 WHERE directory_node_id = p_dir_node_id;
217
218 l_object_type VARCHAR2(30);
219 l_object_id NUMBER;
220
221 BEGIN
222
223 -- Validating Uniqueness for Name in a particular directory
224 FOR r_dirnode IN c_dirnode(p_child_dir_node_id) LOOP
225 IF IBC_UTILITIES_PVT.is_name_already_used(
226 p_dir_node_id => p_parent_dir_node_id,
227 p_name => r_dirnode.directory_node_code,
228 p_language => USERENV('lang'),
229 p_chk_dir_node_id => p_child_dir_node_id,
230 x_object_type => l_object_type,
231 x_object_id => l_object_id)
232 THEN
233 Fnd_Message.Set_Name('IBC', 'IBC_INVALID_FOLDER_NAME');
234 Fnd_Msg_Pub.ADD;
235 RAISE Fnd_Api.G_EXC_ERROR;
236 END IF;
237 END LOOP;
238
239 DECLARE
240 l_user_id NUMBER := 0;
241 l_last_update_date DATE;
242 l_row_id VARCHAR2(64);
243 lx_DIRECTORY_NODE_REL_ID NUMBER := p_DIRECTORY_NODE_REL_ID;
244
245 db_user_id NUMBER := 0;
246 db_last_update_date DATE;
247
248 BEGIN
249 --get last updated by user id
250 l_user_id := FND_LOAD_UTIL.OWNER_ID(p_OWNER);
251
252 --translate data type VARCHAR2 to DATE for last_update_date
253 l_last_update_date := nvl(TO_DATE(p_last_update_date, 'YYYY/MM/DD'),SYSDATE);
254
255 -- get updatedby and update_date values if existing in db
256 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE INTO db_user_id, db_last_update_date
257 FROM IBC_DIRECTORY_NODE_RELS
258 WHERE Directory_Node_Rel_ID = p_Directory_Node_Rel_ID
259 AND object_version_number = DECODE(object_version_number,
260 FND_API.G_MISS_NUM,object_version_number,
261 NULL,object_version_number,
262 object_version_number);
263
264 IF (FND_LOAD_UTIL.UPLOAD_TEST(l_user_id, l_last_update_date,
265 db_user_id, db_last_update_date, p_upload_mode )) THEN
266
267 Ibc_Directory_Node_Rels_Pkg.UPDATE_ROW (
268 p_DIRECTORY_NODE_REL_ID => p_DIRECTORY_NODE_REL_ID ,
269 p_CHILD_DIR_NODE_ID => nvl(p_child_dir_NODE_ID,FND_API.G_MISS_NUM),
270 p_PARENT_DIR_NODE_ID => nvl(p_pARENT_dir_NODE_ID,FND_API.G_MISS_NUM),
271 p_LAST_UPDATED_BY => l_user_id,
272 p_LAST_UPDATE_DATE => sysdate,
273 p_LAST_UPDATE_LOGIN => 0,
274 p_OBJECT_VERSION_NUMBER => NULL);
275
276 END IF;
277 EXCEPTION
278 WHEN NO_DATA_FOUND THEN
279 Ibc_Directory_Node_Rels_Pkg.INSERT_ROW (
280 x_ROWID => l_row_id,
281 px_DIRECTORY_NODE_REL_ID => lx_DIRECTORY_NODE_REL_ID ,
282 p_CHILD_DIR_NODE_ID => p_child_dir_node_id,
283 p_PARENT_DIR_NODE_ID => p_PARENT_DIR_NODE_ID ,
284 p_CREATED_BY => l_user_id ,
285 p_CREATION_DATE => SYSDATE,
286 p_LAST_UPDATED_BY => l_user_id ,
287 p_LAST_UPDATE_DATE => sysdate ,
288 p_LAST_UPDATE_LOGIN => 0,
289 p_OBJECT_VERSION_NUMBER => 1);
290
291 END;
292
293 -- Security Inheritance Logic.
294 DECLARE
295 x_return_status VARCHAR2(30);
296 x_msg_count NUMBER;
297 x_msg_data VARCHAR2(4096);
298 l_citem_oid NUMBER :=
299 IBC_DATA_SECURITY_PVT.get_object_id('IBC_CONTENT_ITEM');
300 l_directory_oid NUMBER :=
301 IBC_DATA_SECURITY_PVT.get_object_id('IBC_DIRECTORY_NODE');
302 BEGIN
303 -- Establish of inheritance for Security
304 IBC_DATA_SECURITY_PVT.establish_inheritance(
305 p_instance_object_id => l_directory_oid
306 ,p_instance_pk1_value => p_child_dir_NODE_ID
307 ,p_container_object_id => l_directory_oid
308 ,p_container_pk1_value => p_PARENT_DIR_NODE_ID
309 ,x_return_status => x_return_status
310 ,x_msg_count => x_msg_count
311 ,x_msg_data => x_msg_data
312 );
313 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
314 RAISE FND_API.G_EXC_ERROR;
315 END IF;
316 END;
317
318 END LOAD_ROW;
319
320 PROCEDURE LOAD_SEED_ROW (
321 p_UPLOAD_MODE IN VARCHAR2,
322 p_DIRECTORY_NODE_REL_ID IN NUMBER,
323 p_CHILD_DIR_NODE_ID IN NUMBER,
324 p_PARENT_DIR_NODE_ID IN NUMBER,
325 p_OWNER IN VARCHAR2,
326 p_LAST_UPDATE_DATE IN VARCHAR2) IS
327 BEGIN
328 IF (p_UPLOAD_MODE = 'NLS') THEN
329 NULL;
330 ELSE
331 Ibc_Directory_Node_Rels_Pkg.LOAD_ROW (
332 p_UPLOAD_MODE => p_UPLOAD_MODE,
333 p_DIRECTORY_NODE_REL_ID => p_DIRECTORY_NODE_REL_ID,
334 p_CHILD_DIR_NODE_ID => p_CHILD_DIR_NODE_ID,
335 p_PARENT_DIR_NODE_ID => p_PARENT_DIR_NODE_ID,
336 p_OWNER => p_OWNER,
337 p_last_update_date => p_LAST_UPDATE_DATE);
338 END IF;
339 END LOAD_SEED_ROW;
340
341 END Ibc_Directory_Node_Rels_Pkg;