[Home] [Help]
PACKAGE BODY: APPS.IBC_DIRECTORY_NODES_PKG
Source
1 PACKAGE BODY Ibc_Directory_Nodes_Pkg AS
2 /* $Header: ibctdndb.pls 120.4 2006/06/22 09:30:08 sharma ship $*/
3
4 -- Purpose: Table Handler for Ibc_Directory_Nodes table.
5
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- Sri Rangarajan 01/06/2002 Created Package
10 -- vicho 11/04/2002 Remove G_MISS defaulting on UPDATE_ROW
11 -- Edward Nunez New columns NODE_STATUS and DIRECTORY_PATH,
12 -- overloaded methods for BC4J compliance.
13 -- Edward Nunez New columns AVAILABLE_DATE, EXPIRATION_DATE
14 -- and HIDDEN_FLAG
15 -- Kiran 09/02/2003 Added new procedure(INSERT_ROW_CP) to call from Java CP
16 -- Edward Nunez 12/08/2003 No use of OVN locking for update_row
17 -- Sri Rangarajan 06/22/2004 Removed the logic of NULL from Update.Bug#3657744
18 -- Edward Nunez 06/23/2004 Added check for uniqueness during update_row
19 -- Sharma 07/04/2005 Modified LOAD_ROW, TRANSLATE_ROW and created
20 -- LOAD_SEED_ROW for R12 LCT standards bug 4411674
21
22
23 PROCEDURE INSERT_ROW (
24 x_ROWID OUT NOCOPY VARCHAR2,
25 px_DIRECTORY_NODE_ID IN OUT NOCOPY NUMBER,
26 p_DIRECTORY_NODE_CODE IN VARCHAR2,
27 p_NODE_STATUS IN VARCHAR2,
28 p_DIRECTORY_PATH IN VARCHAR2,
29 p_AVAILABLE_DATE IN DATE,
30 p_EXPIRATION_DATE IN DATE,
31 p_HIDDEN_FLAG IN VARCHAR2,
32 p_NODE_TYPE IN VARCHAR2,
33 p_OBJECT_VERSION_NUMBER IN NUMBER,
34 p_DIRECTORY_NODE_NAME IN VARCHAR2,
35 p_DESCRIPTION IN VARCHAR2,
36 p_CREATION_DATE IN DATE,
37 p_CREATED_BY IN NUMBER,
38 p_LAST_UPDATE_DATE IN DATE,
39 p_LAST_UPDATED_BY IN NUMBER,
40 p_LAST_UPDATE_LOGIN IN NUMBER
41 ) IS
42 CURSOR C IS SELECT ROWID FROM IBC_DIRECTORY_NODES_B
43 WHERE DIRECTORY_NODE_ID = px_DIRECTORY_NODE_ID
44 ;
45 CURSOR c2 IS SELECT ibc_directory_nodes_b_s1.NEXTVAL FROM dual;
46
47 BEGIN
48
49 -- Primary key validation check
50
51 IF ((px_DIRECTORY_NODE_ID IS NULL) OR
52 (px_DIRECTORY_NODE_ID = FND_API.G_MISS_NUM))
53 THEN
54 OPEN c2;
55 FETCH c2 INTO px_DIRECTORY_NODE_ID;
56 CLOSE c2;
57 END IF;
58
59 INSERT INTO IBC_DIRECTORY_NODES_B (
60 DIRECTORY_NODE_ID,
61 DIRECTORY_NODE_CODE,
62 NODE_STATUS,
63 DIRECTORY_PATH,
64 AVAILABLE_DATE,
65 EXPIRATION_DATE,
66 HIDDEN_FLAG,
67 NODE_TYPE,
68 OBJECT_VERSION_NUMBER,
69 CREATION_DATE,
70 CREATED_BY,
71 LAST_UPDATE_DATE,
72 LAST_UPDATED_BY,
73 LAST_UPDATE_LOGIN
74 ) VALUES (
75 px_DIRECTORY_NODE_ID,
76 p_DIRECTORY_NODE_CODE,
77 DECODE(p_NODE_STATUS,FND_API.G_MISS_CHAR,NULL,p_NODE_STATUS),
78 DECODE(p_DIRECTORY_PATH,FND_API.G_MISS_CHAR,NULL,p_DIRECTORY_PATH),
79 DECODE(p_AVAILABLE_DATE,FND_API.G_MISS_DATE,NULL,p_AVAILABLE_DATE),
80 DECODE(p_EXPIRATION_DATE,FND_API.G_MISS_DATE,NULL,p_EXPIRATION_DATE),
81 DECODE(p_HIDDEN_FLAG,FND_API.G_MISS_CHAR,NULL,p_HIDDEN_FLAG),
82 DECODE(p_NODE_TYPE,FND_API.G_MISS_CHAR,NULL,p_NODE_TYPE),
83 DECODE(p_OBJECT_VERSION_NUMBER,FND_API.G_MISS_NUM,1,p_object_version_number),
84 DECODE(p_creation_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
85 p_creation_date),
86 DECODE(p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
87 NULL, FND_GLOBAL.user_id, p_created_by),
88 DECODE(p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
89 p_last_update_date),
90 DECODE(p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
91 NULL, FND_GLOBAL.user_id, p_last_updated_by),
92 DECODE(p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.login_id,
93 NULL, FND_GLOBAL.login_id, p_last_update_login)
94 );
95
96 INSERT INTO IBC_DIRECTORY_NODES_TL (
97 DIRECTORY_NODE_ID,
98 DIRECTORY_NODE_NAME,
99 DESCRIPTION,
100 CREATED_BY,
101 CREATION_DATE,
102 LAST_UPDATED_BY,
103 LAST_UPDATE_DATE,
104 LAST_UPDATE_LOGIN,
105 LANGUAGE,
106 SOURCE_LANG
107 ) SELECT
108 px_DIRECTORY_NODE_ID,
109 p_DIRECTORY_NODE_NAME,
110 DECODE(p_DESCRIPTION,FND_API.G_MISS_CHAR,NULL,p_DESCRIPTION),
111 DECODE(p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
112 NULL, FND_GLOBAL.user_id, p_created_by),
113 DECODE(p_creation_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
114 p_creation_date) ,
115 DECODE(p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
116 NULL, FND_GLOBAL.user_id, p_last_updated_by),
117 DECODE(p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
118 p_last_update_date),
119 DECODE(p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.login_id,
120 NULL, FND_GLOBAL.login_id, p_last_update_login),
121 L.LANGUAGE_CODE,
122 USERENV('LANG')
123 FROM FND_LANGUAGES L
124 WHERE L.INSTALLED_FLAG IN ('I', 'B')
125 AND NOT EXISTS
126 (SELECT NULL
127 FROM IBC_DIRECTORY_NODES_TL T
128 WHERE T.DIRECTORY_NODE_ID = px_DIRECTORY_NODE_ID
129 AND T.LANGUAGE = L.LANGUAGE_CODE);
130
131 OPEN c;
132 FETCH c INTO x_ROWID;
133 IF (c%NOTFOUND) THEN
134 CLOSE c;
135 RAISE NO_DATA_FOUND;
136 END IF;
137 CLOSE c;
138
139 END INSERT_ROW;
140
141 PROCEDURE INSERT_ROW (
142 x_ROWID OUT NOCOPY VARCHAR2,
143 x_DIRECTORY_NODE_ID IN OUT NOCOPY NUMBER,
144 x_DIRECTORY_NODE_CODE IN VARCHAR2,
145 x_NODE_STATUS IN VARCHAR2,
146 x_DIRECTORY_PATH IN VARCHAR2,
147 x_AVAILABLE_DATE IN DATE,
148 x_EXPIRATION_DATE IN DATE,
149 x_HIDDEN_FLAG IN VARCHAR2,
150 x_NODE_TYPE IN VARCHAR2,
151 x_OBJECT_VERSION_NUMBER IN NUMBER,
152 x_DIRECTORY_NODE_NAME IN VARCHAR2,
153 x_DESCRIPTION IN VARCHAR2,
154 x_CREATION_DATE IN DATE,
155 x_CREATED_BY IN NUMBER,
156 x_LAST_UPDATE_DATE IN DATE,
157 x_LAST_UPDATED_BY IN NUMBER,
158 x_LAST_UPDATE_LOGIN IN NUMBER
159 ) IS
160 BEGIN
161
162 INSERT_ROW (
163 x_ROWID => x_rowid,
164 px_DIRECTORY_NODE_ID => x_directory_node_id,
165 p_DIRECTORY_NODE_CODE => x_directory_node_code,
166 p_NODE_STATUS => x_node_status,
167 p_DIRECTORY_PATH => x_directory_path,
168 p_AVAILABLE_DATE => x_available_date,
169 p_EXPIRATION_DATE => x_expiration_date,
170 p_HIDDEN_FLAG => x_HIDDEN_FLAG,
171 p_NODE_TYPE => x_node_type,
172 p_OBJECT_VERSION_NUMBER => x_object_version_number,
173 p_DIRECTORY_NODE_NAME => x_directory_node_name,
174 p_DESCRIPTION => x_description,
175 p_CREATION_DATE => x_creation_date,
176 p_CREATED_BY => x_created_by,
177 p_LAST_UPDATE_DATE => x_last_update_date,
178 p_LAST_UPDATED_BY => x_last_updated_by,
179 p_LAST_UPDATE_LOGIN => x_last_update_login
180 );
181
182 END INSERT_ROW;
183
184 PROCEDURE INSERT_ROW_CP (
185 x_ROWID OUT NOCOPY VARCHAR2,
186 x_DIRECTORY_NODE_ID IN OUT NOCOPY NUMBER,
187 x_DIRECTORY_NODE_CODE IN VARCHAR2,
188 x_NODE_STATUS IN VARCHAR2,
189 x_DIRECTORY_PATH IN VARCHAR2,
190 x_AVAILABLE_DATE IN DATE,
191 x_EXPIRATION_DATE IN DATE,
192 x_HIDDEN_FLAG IN VARCHAR2,
193 x_NODE_TYPE IN VARCHAR2,
194 x_OBJECT_VERSION_NUMBER IN NUMBER,
195 x_DIRECTORY_NODE_NAME IN VARCHAR2,
196 x_DESCRIPTION IN VARCHAR2,
197 x_CREATION_DATE IN DATE,
198 x_CREATED_BY IN NUMBER,
199 x_LAST_UPDATE_DATE IN DATE,
200 x_LAST_UPDATED_BY IN NUMBER,
201 x_LAST_UPDATE_LOGIN IN NUMBER
202 ) IS
203 BEGIN
204
205 INSERT_ROW (
206 x_ROWID => x_rowid,
207 px_DIRECTORY_NODE_ID => x_directory_node_id,
208 p_DIRECTORY_NODE_CODE => x_directory_node_code,
209 p_NODE_STATUS => x_node_status,
210 p_DIRECTORY_PATH => x_directory_path,
211 p_AVAILABLE_DATE => x_available_date,
212 p_EXPIRATION_DATE => x_expiration_date,
213 p_HIDDEN_FLAG => x_HIDDEN_FLAG,
214 p_NODE_TYPE => x_node_type,
215 p_OBJECT_VERSION_NUMBER => x_object_version_number,
216 p_DIRECTORY_NODE_NAME => x_directory_node_name,
217 p_DESCRIPTION => x_description,
218 p_CREATION_DATE => x_creation_date,
219 p_CREATED_BY => x_created_by,
220 p_LAST_UPDATE_DATE => x_last_update_date,
221 p_LAST_UPDATED_BY => x_last_updated_by,
222 p_LAST_UPDATE_LOGIN => x_last_update_login
223 );
224
225 END INSERT_ROW_CP;
226
227
228 PROCEDURE LOCK_ROW (
229 p_DIRECTORY_NODE_ID IN NUMBER,
230 p_DIRECTORY_NODE_CODE IN VARCHAR2,
231 p_NODE_TYPE IN VARCHAR2,
232 p_NODE_STATUS IN VARCHAR2,
233 p_DIRECTORY_PATH IN VARCHAR2,
234 p_AVAILABLE_DATE IN DATE,
235 p_EXPIRATION_DATE IN DATE,
236 p_HIDDEN_FLAG IN VARCHAR2,
237 p_OBJECT_VERSION_NUMBER IN NUMBER,
238 p_DIRECTORY_NODE_NAME IN VARCHAR2,
239 p_DESCRIPTION IN VARCHAR2
240 ) IS
241 CURSOR c IS SELECT
242 DIRECTORY_NODE_CODE,
243 NODE_TYPE,
244 NODE_STATUS,
245 DIRECTORY_PATH,
246 AVAILABLE_DATE,
247 EXPIRATION_DATE,
248 HIDDEN_FLAG,
249 OBJECT_VERSION_NUMBER
250 FROM IBC_DIRECTORY_NODES_B
251 WHERE DIRECTORY_NODE_ID = p_DIRECTORY_NODE_ID
252 FOR UPDATE OF DIRECTORY_NODE_ID NOWAIT;
253 recinfo c%ROWTYPE;
254
255 CURSOR c1 IS SELECT
256 DIRECTORY_NODE_NAME,
257 DESCRIPTION,
258 DECODE(LANGUAGE, USERENV('LANG'), 'Y', 'N') BASELANG
259 FROM IBC_DIRECTORY_NODES_TL
260 WHERE DIRECTORY_NODE_ID = p_DIRECTORY_NODE_ID
261 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
262 FOR UPDATE OF DIRECTORY_NODE_ID NOWAIT;
263 BEGIN
264 OPEN c;
265 FETCH c INTO recinfo;
266 IF (c%NOTFOUND) THEN
267 CLOSE c;
268 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
269 app_exception.raise_exception;
270 END IF;
271 CLOSE c;
272 IF ( ((recinfo.NODE_TYPE = p_NODE_TYPE)
273 OR ((recinfo.NODE_TYPE IS NULL) AND (p_NODE_TYPE IS NULL)))
274 AND ((recinfo.DIRECTORY_NODE_CODE = p_DIRECTORY_NODE_CODE)
275 OR ((recinfo.DIRECTORY_NODE_CODE IS NULL) AND (p_DIRECTORY_NODE_CODE IS NULL)))
276 AND ((recinfo.NODE_STATUS = p_NODE_STATUS)
277 OR ((recinfo.NODE_STATUS IS NULL) AND (p_NODE_STATUS IS NULL)))
278 AND ((recinfo.DIRECTORY_PATH = p_DIRECTORY_PATH)
279 OR ((recinfo.DIRECTORY_PATH IS NULL) AND (p_DIRECTORY_PATH IS NULL)))
280 AND ((recinfo.AVAILABLE_DATE = p_AVAILABLE_DATE)
281 OR ((recinfo.AVAILABLE_DATE IS NULL) AND (p_AVAILABLE_DATE IS NULL)))
282 AND ((recinfo.EXPIRATION_DATE = p_EXPIRATION_DATE)
283 OR ((recinfo.EXPIRATION_DATE IS NULL) AND (p_EXPIRATION_DATE IS NULL)))
284 AND ((recinfo.HIDDEN_FLAG = p_HIDDEN_FLAG)
285 OR ((recinfo.HIDDEN_FLAG IS NULL) AND (p_HIDDEN_FLAG IS NULL)))
286 AND (recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
287 ) THEN
288 NULL;
289 ELSE
290 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
291 app_exception.raise_exception;
292 END IF;
293
294 FOR tlinfo IN c1 LOOP
295 IF (tlinfo.BASELANG = 'Y') THEN
296 IF ( (tlinfo.DIRECTORY_NODE_NAME = p_DIRECTORY_NODE_NAME)
297 AND ((tlinfo.DESCRIPTION = p_DESCRIPTION)
298 OR ((tlinfo.DESCRIPTION IS NULL) AND (p_DESCRIPTION IS NULL)))
299 ) THEN
300 NULL;
301 ELSE
302 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
303 app_exception.raise_exception;
304 END IF;
305 END IF;
306 END LOOP;
307 RETURN;
308 END LOCK_ROW;
309
310 PROCEDURE LOCK_ROW (
311 x_DIRECTORY_NODE_ID IN NUMBER,
312 x_DIRECTORY_NODE_CODE IN VARCHAR2,
313 x_NODE_TYPE IN VARCHAR2,
314 x_NODE_STATUS IN VARCHAR2,
315 x_DIRECTORY_PATH IN VARCHAR2,
316 x_AVAILABLE_DATE IN DATE,
317 x_EXPIRATION_DATE IN DATE,
318 x_HIDDEN_FLAG IN VARCHAR2,
319 x_OBJECT_VERSION_NUMBER IN NUMBER,
320 x_DIRECTORY_NODE_NAME IN VARCHAR2,
321 x_DESCRIPTION IN VARCHAR2
322 ) IS
323 BEGIN
324 LOCK_ROW (
325 p_DIRECTORY_NODE_ID => x_directory_node_id,
326 p_DIRECTORY_NODE_CODE => x_directory_node_code,
327 p_NODE_TYPE => x_node_type,
328 p_NODE_STATUS => x_node_status,
329 p_DIRECTORY_PATH => x_directory_path,
330 p_AVAILABLE_DATE => x_available_date,
331 p_EXPIRATION_DATE => x_expiration_date,
332 p_HIDDEN_FLAG => x_hidden_flag,
333 p_OBJECT_VERSION_NUMBER => x_object_version_number,
334 p_DIRECTORY_NODE_NAME => x_directory_node_name,
335 p_DESCRIPTION => x_description
336 );
337 END LOCK_ROW;
338
339 PROCEDURE UPDATE_ROW (
340 p_DIRECTORY_NODE_ID IN NUMBER,
341 p_DIRECTORY_NODE_CODE IN VARCHAR2,
342 p_DESCRIPTION IN VARCHAR2,
343 p_DIRECTORY_NODE_NAME IN VARCHAR2,
344 p_LAST_UPDATED_BY IN NUMBER,
345 p_LAST_UPDATE_DATE IN DATE,
346 p_LAST_UPDATE_LOGIN IN NUMBER,
347 p_NODE_STATUS IN VARCHAR2,
348 p_DIRECTORY_PATH IN VARCHAR2,
349 p_AVAILABLE_DATE IN DATE,
350 p_EXPIRATION_DATE IN DATE,
351 p_HIDDEN_FLAG IN VARCHAR2,
352 p_NODE_TYPE IN VARCHAR2,
353 p_OBJECT_VERSION_NUMBER IN NUMBER
354 ) IS
355
356 CURSOR c_parent_dirnode(p_dir_node_id NUMBER) IS
357 SELECT parent_dir_node_id
358 FROM ibc_directory_node_rels
359 WHERE child_dir_node_id = p_dir_node_id;
360
361 l_object_type VARCHAR2(30);
362 l_object_id NUMBER;
363
364 BEGIN
365
366 -- Validating Uniqueness for Name in a particular directory
367 FOR r_parent_dirnode IN c_parent_dirnode(p_directory_node_id) LOOP
368 IF IBC_UTILITIES_PVT.is_name_already_used(
369 p_dir_node_id => r_parent_dirnode.parent_dir_node_id,
370 p_name => p_directory_node_code,
371 p_language => USERENV('lang'),
372 p_chk_dir_node_id => p_directory_node_id,
373 x_object_type => l_object_type,
374 x_object_id => l_object_id)
375 THEN
376 Fnd_Message.Set_Name('IBC', 'IBC_INVALID_FOLDER_NAME');
377 Fnd_Msg_Pub.ADD;
378 RAISE Fnd_Api.G_EXC_ERROR;
379 END IF;
380 END LOOP;
381
382 UPDATE IBC_DIRECTORY_NODES_B SET
383 DIRECTORY_NODE_CODE = DECODE(p_DIRECTORY_NODE_CODE,FND_API.G_MISS_CHAR,NULL,NULL,DIRECTORY_NODE_CODE,p_DIRECTORY_NODE_CODE),
384 NODE_STATUS = DECODE(p_NODE_STATUS,FND_API.G_MISS_CHAR,NULL,NULL,NODE_STATUS,p_NODE_STATUS),
385 DIRECTORY_PATH = DECODE(p_DIRECTORY_PATH,FND_API.G_MISS_CHAR,NULL,NULL,DIRECTORY_PATH,p_DIRECTORY_PATH),
386 AVAILABLE_DATE = DECODE(p_AVAILABLE_DATE,FND_API.G_MISS_DATE,NULL,p_AVAILABLE_DATE),
387 EXPIRATION_DATE = DECODE(p_EXPIRATION_DATE,FND_API.G_MISS_DATE,NULL,p_EXPIRATION_DATE),
388 HIDDEN_FLAG = DECODE(p_HIDDEN_FLAG,FND_API.G_MISS_CHAR,NULL,NULL,HIDDEN_FLAG,p_HIDDEN_FLAG),
389 NODE_TYPE = DECODE(p_NODE_TYPE,FND_API.G_MISS_CHAR,NULL,NULL,NODE_TYPE,p_NODE_TYPE),
390 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
391 last_update_date = DECODE(p_last_update_date, FND_API.G_MISS_DATE, SYSDATE,
392 NULL, SYSDATE, p_last_update_date),
393 last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM,
394 FND_GLOBAL.user_id, NULL, FND_GLOBAL.user_id,
395 p_last_updated_by),
396 last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM,
397 FND_GLOBAL.login_id, NULL, FND_GLOBAL.login_id,
398 p_last_update_login)
399 WHERE DIRECTORY_NODE_ID = p_DIRECTORY_NODE_ID;
400
401 -- AND object_version_number = DECODE(p_object_version_number,
402 -- FND_API.G_MISS_NUM, object_version_number,
403 -- NULL, object_version_number,
404 -- p_object_version_number);
405
406 IF (SQL%NOTFOUND) THEN
407 RAISE NO_DATA_FOUND;
408 END IF;
409
410 UPDATE IBC_DIRECTORY_NODES_TL SET
411 DIRECTORY_NODE_NAME = DECODE(p_DIRECTORY_NODE_NAME,FND_API.G_MISS_CHAR,NULL,NULL,DIRECTORY_NODE_NAME,p_DIRECTORY_NODE_NAME),
412 DESCRIPTION = DECODE(p_DESCRIPTION,FND_API.G_MISS_CHAR,NULL,p_DESCRIPTION),
413 last_update_date = DECODE(p_last_update_date, FND_API.G_MISS_DATE, SYSDATE,
414 NULL, SYSDATE, p_last_update_date),
415 last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM,
416 FND_GLOBAL.user_id, NULL, FND_GLOBAL.user_id,
417 p_last_updated_by),
418 last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM,
419 FND_GLOBAL.login_id, NULL, FND_GLOBAL.login_id,
420 p_last_update_login),
421 SOURCE_LANG = USERENV('LANG')
422 WHERE DIRECTORY_NODE_ID = p_DIRECTORY_NODE_ID
423 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
424
425 IF (SQL%NOTFOUND) THEN
426 RAISE NO_DATA_FOUND;
427 END IF;
428
429 END UPDATE_ROW;
430
431 PROCEDURE UPDATE_ROW (
432 x_DIRECTORY_NODE_ID IN NUMBER,
433 x_DIRECTORY_NODE_CODE IN VARCHAR2,
434 x_DESCRIPTION IN VARCHAR2,
435 x_DIRECTORY_NODE_NAME IN VARCHAR2,
436 x_LAST_UPDATED_BY IN NUMBER,
437 x_LAST_UPDATE_DATE IN DATE,
438 x_LAST_UPDATE_LOGIN IN NUMBER,
439 x_NODE_STATUS IN VARCHAR2,
440 x_DIRECTORY_PATH IN VARCHAR2,
441 x_AVAILABLE_DATE IN DATE,
442 x_EXPIRATION_DATE IN DATE,
443 x_HIDDEN_FLAG IN VARCHAR2,
444 x_NODE_TYPE IN VARCHAR2,
445 x_OBJECT_VERSION_NUMBER IN NUMBER
446 ) IS
447 BEGIN
448 UPDATE_ROW (
449 p_DIRECTORY_NODE_ID => x_directory_node_id,
450 p_DIRECTORY_NODE_CODE => x_directory_node_code,
451 p_DESCRIPTION => x_description,
452 p_DIRECTORY_NODE_NAME => x_directory_node_name,
453 p_LAST_UPDATED_BY => x_last_updated_by,
454 p_LAST_UPDATE_DATE => x_last_update_date,
455 p_LAST_UPDATE_LOGIN => x_last_update_login,
456 p_NODE_STATUS => x_node_status,
457 p_DIRECTORY_PATH => x_directory_path,
458 p_AVAILABLE_DATE => x_available_date,
459 p_EXPIRATION_DATE => x_expiration_date,
460 p_HIDDEN_FLAG => x_hidden_flag,
461 p_NODE_TYPE => x_node_type,
462 p_OBJECT_VERSION_NUMBER => x_object_version_number
463 );
464 END UPDATE_ROW;
465
466 PROCEDURE DELETE_ROW (
467 p_DIRECTORY_NODE_ID IN NUMBER
468 ) IS
469 BEGIN
470 DELETE FROM IBC_DIRECTORY_NODES_TL
471 WHERE DIRECTORY_NODE_ID = p_DIRECTORY_NODE_ID;
472
473 IF (SQL%NOTFOUND) THEN
474 RAISE NO_DATA_FOUND;
475 END IF;
476
477 DELETE FROM IBC_DIRECTORY_NODES_B
478 WHERE DIRECTORY_NODE_ID = p_DIRECTORY_NODE_ID;
479
480 IF (SQL%NOTFOUND) THEN
481 RAISE NO_DATA_FOUND;
482 END IF;
483 END DELETE_ROW;
484
485 PROCEDURE DELETE_ROW (
486 x_DIRECTORY_NODE_ID IN NUMBER
487 ) IS
488 BEGIN
489 DELETE_ROW (
490 p_DIRECTORY_NODE_ID => x_directory_node_id
491 );
492 END DELETE_ROW;
493
494 PROCEDURE ADD_LANGUAGE
495 IS
496 BEGIN
497 DELETE FROM IBC_DIRECTORY_NODES_TL T
498 WHERE NOT EXISTS
499 (SELECT NULL
500 FROM IBC_DIRECTORY_NODES_B B
501 WHERE B.DIRECTORY_NODE_ID = T.DIRECTORY_NODE_ID
502 );
503
504 UPDATE IBC_DIRECTORY_NODES_TL T SET (
505 DIRECTORY_NODE_NAME,
506 DESCRIPTION
507 ) = (SELECT
508 B.DIRECTORY_NODE_NAME,
509 B.DESCRIPTION
510 FROM IBC_DIRECTORY_NODES_TL B
511 WHERE B.DIRECTORY_NODE_ID = T.DIRECTORY_NODE_ID
512 AND B.LANGUAGE = T.SOURCE_LANG)
513 WHERE (
514 T.DIRECTORY_NODE_ID,
515 T.LANGUAGE
516 ) IN (SELECT
517 SUBT.DIRECTORY_NODE_ID,
518 SUBT.LANGUAGE
519 FROM IBC_DIRECTORY_NODES_TL SUBB, IBC_DIRECTORY_NODES_TL SUBT
520 WHERE SUBB.DIRECTORY_NODE_ID = SUBT.DIRECTORY_NODE_ID
521 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
522 AND (SUBB.DIRECTORY_NODE_NAME <> SUBT.DIRECTORY_NODE_NAME
523 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
524 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
525 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
526 ));
527
528 INSERT INTO IBC_DIRECTORY_NODES_TL (
529 DIRECTORY_NODE_ID,
530 DIRECTORY_NODE_NAME,
531 DESCRIPTION,
532 CREATED_BY,
533 CREATION_DATE,
534 LAST_UPDATED_BY,
535 LAST_UPDATE_DATE,
536 LAST_UPDATE_LOGIN,
537 LANGUAGE,
538 SOURCE_LANG
539 ) SELECT /*+ ORDERED */
540 B.DIRECTORY_NODE_ID,
541 B.DIRECTORY_NODE_NAME,
542 B.DESCRIPTION,
543 B.CREATED_BY,
544 B.CREATION_DATE,
545 B.LAST_UPDATED_BY,
546 B.LAST_UPDATE_DATE,
547 B.LAST_UPDATE_LOGIN,
548 L.LANGUAGE_CODE,
549 B.SOURCE_LANG
550 FROM IBC_DIRECTORY_NODES_TL B, FND_LANGUAGES L
551 WHERE L.INSTALLED_FLAG IN ('I', 'B')
552 AND B.LANGUAGE = USERENV('LANG')
553 AND NOT EXISTS
554 (SELECT NULL
555 FROM IBC_DIRECTORY_NODES_TL T
556 WHERE T.DIRECTORY_NODE_ID = B.DIRECTORY_NODE_ID
557 AND T.LANGUAGE = L.LANGUAGE_CODE);
558 END ADD_LANGUAGE;
559
560 PROCEDURE LOAD_ROW (
561 p_UPLOAD_MODE IN VARCHAR2,
562 p_DIRECTORY_NODE_ID IN NUMBER,
563 p_NODE_TYPE IN VARCHAR2,
564 p_NODE_STATUS IN VARCHAR2,
565 p_DIRECTORY_PATH IN VARCHAR2,
566 p_AVAILABLE_DATE IN DATE,
567 p_EXPIRATION_DATE IN DATE,
568 p_HIDDEN_FLAG IN VARCHAR2,
569 p_DIRECTORY_NODE_CODE IN VARCHAR2,
570 p_DIRECTORY_NODE_NAME IN VARCHAR2,
571 p_DESCRIPTION IN VARCHAR2,
572 p_OWNER IN VARCHAR2,
573 p_last_update_date IN VARCHAR2) IS
574 BEGIN
575 DECLARE
576 l_user_id NUMBER := 0;
577 l_last_update_date DATE;
578
579 db_user_id NUMBER := 0;
580 db_last_update_date DATE;
581
582 l_row_id VARCHAR2(64);
583
584 BEGIN
585 --get last updated by user id
586 l_user_id := FND_LOAD_UTIL.OWNER_ID(p_OWNER);
587
588 --translate data type VARCHAR2 to DATE for last_update_date
589 l_last_update_date := nvl(TO_DATE(p_last_update_date, 'YYYY/MM/DD'),SYSDATE);
590
591 -- get updatedby and update_date values if existing in db
592 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE INTO db_user_id, db_last_update_date
593 FROM IBC_DIRECTORY_NODES_B
594 WHERE DIRECTORY_NODE_ID = p_DIRECTORY_NODE_ID;
595
596 IF (FND_LOAD_UTIL.UPLOAD_TEST(l_user_id, l_last_update_date,
597 db_user_id, db_last_update_date, p_upload_mode )) THEN
598
599 Ibc_Directory_Nodes_Pkg.UPDATE_ROW (
600 p_DIRECTORY_NODE_ID => p_DIRECTORY_NODE_ID,
601 p_NODE_TYPE => nvl(p_NODE_TYPE,FND_API.G_MISS_CHAR),
602 p_NODE_STATUS => nvl(p_NODE_STATUS,FND_API.G_MISS_CHAR),
603 p_DIRECTORY_PATH => nvl(p_DIRECTORY_PATH,FND_API.G_MISS_CHAR),
604 p_AVAILABLE_DATE => nvl(p_AVAILABLE_DATE, FND_API.G_MISS_DATE),
605 p_EXPIRATION_DATE => nvl(p_EXPIRATION_DATE, FND_API.G_MISS_DATE),
606 p_HIDDEN_FLAG => nvl(p_HIDDEN_FLAG, FND_API.g_MISS_CHAR),
607 p_DIRECTORY_NODE_CODE => p_DIRECTORY_NODE_CODE,
608 p_DIRECTORY_NODE_NAME => nvl(p_DIRECTORY_NODE_NAME,FND_API.G_MISS_CHAR),
609 p_DESCRIPTION => nvl(p_DESCRIPTION,FND_API.G_MISS_CHAR),
610 p_LAST_UPDATED_BY => l_user_id,
611 p_LAST_UPDATE_DATE => l_last_update_date,
612 p_LAST_UPDATE_LOGIN => 0,
613 p_OBJECT_VERSION_NUMBER => NULL
614 );
615 END IF;
616
617
618 EXCEPTION
619 WHEN NO_DATA_FOUND THEN
620 DECLARE
621 lx_rowid VARCHAR2(240);
622 l_DIRECTORY_NODE_ID NUMBER := p_DIRECTORY_NODE_ID;
623
624 BEGIN
625 Ibc_Directory_Nodes_Pkg.INSERT_ROW (
626 x_rowid => lx_rowid,
627 px_DIRECTORY_NODE_ID => l_DIRECTORY_NODE_ID,
628 p_NODE_TYPE => p_NODE_TYPE,
629 p_NODE_STATUS => p_NODE_STATUS,
630 p_DIRECTORY_PATH => p_DIRECTORY_PATH,
631 p_AVAILABLE_DATE => p_AVAILABLE_DATE,
632 p_EXPIRATION_DATE => p_EXPIRATION_DATE,
633 p_HIDDEN_FLAG => p_HIDDEN_FLAG,
634 p_DIRECTORY_NODE_CODE => p_DIRECTORY_NODE_CODE,
635 p_DIRECTORY_NODE_NAME => p_DIRECTORY_NODE_NAME,
636 p_DESCRIPTION => p_DESCRIPTION,
637 p_CREATION_DATE => l_last_update_date,
638 p_CREATED_BY => l_user_id,
639 p_LAST_UPDATE_DATE => l_last_update_date,
640 p_LAST_UPDATED_BY => l_user_id,
641 p_LAST_UPDATE_LOGIN => 0,
642 p_OBJECT_VERSION_NUMBER => 1);
643 END;
644
645 END;
646 END LOAD_ROW;
647
648 PROCEDURE TRANSLATE_ROW (
649 p_UPLOAD_MODE IN VARCHAR2,
650 p_DIRECTORY_NODE_ID IN NUMBER,
651 p_DIRECTORY_NODE_NAME IN VARCHAR2,
652 p_DESCRIPTION IN VARCHAR2,
653 p_OWNER IN VARCHAR2,
654 p_last_update_date IN VARCHAR2) IS
655 BEGIN
656 DECLARE
657 l_user_id NUMBER := 0;
658 l_last_update_date DATE;
659
660 db_user_id NUMBER := 0;
661 db_last_update_date DATE;
662
663 BEGIN
664 --get last updated by user id
665 l_user_id := FND_LOAD_UTIL.OWNER_ID(p_OWNER);
666
667 --translate data type VARCHAR2 to DATE for last_update_date
668 l_last_update_date := nvl(TO_DATE(p_last_update_date, 'YYYY/MM/DD'),SYSDATE);
669
670 -- get updatedby and update_date values if existing in db
671 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE INTO db_user_id, db_last_update_date
672 FROM ibc_directory_nodes_tl
673 WHERE DIRECTORY_NODE_ID = p_DIRECTORY_NODE_ID
674 AND USERENV('LANG') IN (LANGUAGE, source_lang);
675
676 IF (FND_LOAD_UTIL.UPLOAD_TEST(l_user_id, l_last_update_date,
677 db_user_id, db_last_update_date, p_upload_mode )) THEN
678
679 -- Only update rows which have not been altered by user
680 UPDATE ibc_directory_nodes_tl t SET
681 DIRECTORY_NODE_NAME = p_DIRECTORY_NODE_NAME,
682 description = p_description,
683 source_lang = USERENV('LANG'),
684 last_update_date = l_last_update_date,
685 last_updated_by = l_user_id,
686 last_update_login = 0
687 WHERE DIRECTORY_NODE_ID = p_DIRECTORY_NODE_ID
688 AND USERENV('LANG') IN (LANGUAGE, source_lang);
689
690 END IF;
691
692 EXCEPTION
693 WHEN NO_DATA_FOUND THEN
694 NULL;
695 END;
696 END TRANSLATE_ROW;
697
698 PROCEDURE LOAD_SEED_ROW (
699 p_UPLOAD_MODE IN VARCHAR2,
700 p_DIRECTORY_NODE_ID IN NUMBER,
701 p_NODE_TYPE IN VARCHAR2,
702 p_NODE_STATUS IN VARCHAR2,
703 p_DIRECTORY_PATH IN VARCHAR2,
704 p_AVAILABLE_DATE IN DATE,
705 p_EXPIRATION_DATE IN DATE,
706 p_HIDDEN_FLAG IN VARCHAR2,
707 p_DIRECTORY_NODE_CODE IN VARCHAR2,
708 p_DIRECTORY_NODE_NAME IN VARCHAR2,
709 p_DESCRIPTION IN VARCHAR2,
710 p_OWNER IN VARCHAR2,
711 p_LAST_UPDATE_DATE IN VARCHAR2) IS
712 BEGIN
713 IF (p_UPLOAD_MODE = 'NLS') THEN
714 Ibc_Directory_Nodes_Pkg.TRANSLATE_ROW (
715 p_UPLOAD_MODE => p_UPLOAD_MODE,
716 p_DIRECTORY_NODE_ID => p_DIRECTORY_NODE_ID,
717 p_DIRECTORY_NODE_NAME => p_DIRECTORY_NODE_NAME,
718 p_DESCRIPTION => p_DESCRIPTION,
719 p_OWNER => p_OWNER,
720 p_last_update_date => p_LAST_UPDATE_DATE);
721 ELSE
722 Ibc_Directory_Nodes_Pkg.LOAD_ROW(
723 p_UPLOAD_MODE => p_UPLOAD_MODE,
724 p_DIRECTORY_NODE_ID => p_DIRECTORY_NODE_ID,
725 p_NODE_TYPE => p_NODE_TYPE,
726 p_NODE_STATUS => p_NODE_STATUS,
727 p_DIRECTORY_PATH => p_DIRECTORY_PATH,
728 p_AVAILABLE_DATE => p_AVAILABLE_DATE,
729 p_EXPIRATION_DATE => p_EXPIRATION_DATE,
730 p_HIDDEN_FLAG => p_HIDDEN_FLAG,
731 p_DIRECTORY_NODE_CODE => p_DIRECTORY_NODE_CODE,
732 p_DIRECTORY_NODE_NAME => p_DIRECTORY_NODE_NAME,
733 p_DESCRIPTION =>p_DESCRIPTION,
734 p_OWNER => p_OWNER,
735 p_last_update_date => p_LAST_UPDATE_DATE);
736 END IF;
737 END LOAD_SEED_ROW;
738
739 END Ibc_Directory_Nodes_Pkg;