1 package body AHL_PC_NODES_PKG as
2 /* $Header: AHLLPCNB.pls 115.2 2002/12/04 10:09:27 bachandr noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_PC_NODE_ID in NUMBER,
6 X_ATTRIBUTE11 in VARCHAR2,
7 X_ATTRIBUTE12 in VARCHAR2,
8 X_ATTRIBUTE13 in VARCHAR2,
9 X_ATTRIBUTE14 in VARCHAR2,
10 X_ATTRIBUTE15 in VARCHAR2,
11 X_ATTRIBUTE_CATEGORY in VARCHAR2,
12 X_ATTRIBUTE1 in VARCHAR2,
13 X_ATTRIBUTE2 in VARCHAR2,
14 X_OBJECT_VERSION_NUMBER in NUMBER,
15 X_NAME in VARCHAR2,
16 X_PARENT_NODE_ID in NUMBER,
17 X_CHILD_COUNT in NUMBER,
18 X_OPERATION_STATUS_FLAG in VARCHAR2,
19 X_DRAFT_FLAG in VARCHAR2,
20 X_LINK_TO_NODE_ID in NUMBER,
21 X_SECURITY_GROUP_ID in NUMBER,
22 X_ATTRIBUTE3 in VARCHAR2,
23 X_ATTRIBUTE4 in VARCHAR2,
24 X_ATTRIBUTE5 in VARCHAR2,
25 X_ATTRIBUTE6 in VARCHAR2,
26 X_ATTRIBUTE7 in VARCHAR2,
27 X_ATTRIBUTE8 in VARCHAR2,
28 X_ATTRIBUTE9 in VARCHAR2,
29 X_ATTRIBUTE10 in VARCHAR2,
30 X_PC_HEADER_ID in NUMBER,
31 X_DESCRIPTION in VARCHAR2,
32 X_CREATION_DATE in DATE,
33 X_CREATED_BY in NUMBER,
34 X_LAST_UPDATE_DATE in DATE,
35 X_LAST_UPDATED_BY in NUMBER,
36 X_LAST_UPDATE_LOGIN in NUMBER
37 ) is
38 cursor C is select ROWID from AHL_PC_NODES_B
39 where PC_NODE_ID = X_PC_NODE_ID
40 ;
41 begin
42 insert into AHL_PC_NODES_B (
43 ATTRIBUTE11,
44 ATTRIBUTE12,
45 ATTRIBUTE13,
46 ATTRIBUTE14,
47 ATTRIBUTE15,
48 ATTRIBUTE_CATEGORY,
49 ATTRIBUTE1,
50 ATTRIBUTE2,
51 PC_NODE_ID,
52 OBJECT_VERSION_NUMBER,
53 NAME,
54 PARENT_NODE_ID,
55 CHILD_COUNT,
56 OPERATION_STATUS_FLAG,
57 DRAFT_FLAG,
58 LINK_TO_NODE_ID,
59 SECURITY_GROUP_ID,
60 ATTRIBUTE3,
61 ATTRIBUTE4,
62 ATTRIBUTE5,
63 ATTRIBUTE6,
64 ATTRIBUTE7,
65 ATTRIBUTE8,
66 ATTRIBUTE9,
67 ATTRIBUTE10,
68 PC_HEADER_ID,
69 CREATION_DATE,
70 CREATED_BY,
71 LAST_UPDATE_DATE,
72 LAST_UPDATED_BY,
73 LAST_UPDATE_LOGIN
74 ) values (
75 X_ATTRIBUTE11,
76 X_ATTRIBUTE12,
77 X_ATTRIBUTE13,
78 X_ATTRIBUTE14,
79 X_ATTRIBUTE15,
80 X_ATTRIBUTE_CATEGORY,
81 X_ATTRIBUTE1,
82 X_ATTRIBUTE2,
83 X_PC_NODE_ID,
84 X_OBJECT_VERSION_NUMBER,
85 X_NAME,
86 X_PARENT_NODE_ID,
87 X_CHILD_COUNT,
88 X_OPERATION_STATUS_FLAG,
89 X_DRAFT_FLAG,
90 X_LINK_TO_NODE_ID,
91 X_SECURITY_GROUP_ID,
92 X_ATTRIBUTE3,
93 X_ATTRIBUTE4,
94 X_ATTRIBUTE5,
95 X_ATTRIBUTE6,
96 X_ATTRIBUTE7,
97 X_ATTRIBUTE8,
98 X_ATTRIBUTE9,
99 X_ATTRIBUTE10,
100 X_PC_HEADER_ID,
101 X_CREATION_DATE,
102 X_CREATED_BY,
103 X_LAST_UPDATE_DATE,
104 X_LAST_UPDATED_BY,
105 X_LAST_UPDATE_LOGIN
106 );
107
108 insert into AHL_PC_NODES_TL (
109 PC_NODE_ID,
110 DESCRIPTION,
111 LAST_UPDATE_DATE,
112 LAST_UPDATED_BY,
113 CREATION_DATE,
114 CREATED_BY,
115 LAST_UPDATE_LOGIN,
116 SECURITY_GROUP_ID,
117 LANGUAGE,
118 SOURCE_LANG
119 ) select
120 X_PC_NODE_ID,
121 X_DESCRIPTION,
122 X_LAST_UPDATE_DATE,
123 X_LAST_UPDATED_BY,
124 X_CREATION_DATE,
125 X_CREATED_BY,
126 X_LAST_UPDATE_LOGIN,
127 X_SECURITY_GROUP_ID,
128 L.LANGUAGE_CODE,
129 userenv('LANG')
130 from FND_LANGUAGES L
131 where L.INSTALLED_FLAG in ('I', 'B')
132 and not exists
133 (select NULL
134 from AHL_PC_NODES_TL T
135 where T.PC_NODE_ID = X_PC_NODE_ID
136 and T.LANGUAGE = L.LANGUAGE_CODE);
137
138 open c;
139 fetch c into X_ROWID;
140 if (c%notfound) then
141 close c;
142 raise no_data_found;
143 end if;
144 close c;
145
146 end INSERT_ROW;
147
148 procedure LOCK_ROW (
149 X_PC_NODE_ID in NUMBER,
150 X_ATTRIBUTE11 in VARCHAR2,
151 X_ATTRIBUTE12 in VARCHAR2,
152 X_ATTRIBUTE13 in VARCHAR2,
153 X_ATTRIBUTE14 in VARCHAR2,
154 X_ATTRIBUTE15 in VARCHAR2,
155 X_ATTRIBUTE_CATEGORY in VARCHAR2,
156 X_ATTRIBUTE1 in VARCHAR2,
157 X_ATTRIBUTE2 in VARCHAR2,
158 X_OBJECT_VERSION_NUMBER in NUMBER,
159 X_NAME in VARCHAR2,
160 X_PARENT_NODE_ID in NUMBER,
161 X_CHILD_COUNT in NUMBER,
162 X_OPERATION_STATUS_FLAG in VARCHAR2,
163 X_DRAFT_FLAG in VARCHAR2,
164 X_LINK_TO_NODE_ID in NUMBER,
165 X_SECURITY_GROUP_ID in NUMBER,
166 X_ATTRIBUTE3 in VARCHAR2,
167 X_ATTRIBUTE4 in VARCHAR2,
168 X_ATTRIBUTE5 in VARCHAR2,
169 X_ATTRIBUTE6 in VARCHAR2,
170 X_ATTRIBUTE7 in VARCHAR2,
171 X_ATTRIBUTE8 in VARCHAR2,
172 X_ATTRIBUTE9 in VARCHAR2,
173 X_ATTRIBUTE10 in VARCHAR2,
174 X_PC_HEADER_ID in NUMBER,
175 X_DESCRIPTION in VARCHAR2
176 ) is
177 cursor c is select
178 ATTRIBUTE11,
179 ATTRIBUTE12,
180 ATTRIBUTE13,
181 ATTRIBUTE14,
182 ATTRIBUTE15,
183 ATTRIBUTE_CATEGORY,
184 ATTRIBUTE1,
185 ATTRIBUTE2,
186 OBJECT_VERSION_NUMBER,
187 NAME,
188 PARENT_NODE_ID,
189 CHILD_COUNT,
190 OPERATION_STATUS_FLAG,
191 DRAFT_FLAG,
192 LINK_TO_NODE_ID,
193 SECURITY_GROUP_ID,
194 ATTRIBUTE3,
195 ATTRIBUTE4,
196 ATTRIBUTE5,
197 ATTRIBUTE6,
198 ATTRIBUTE7,
199 ATTRIBUTE8,
200 ATTRIBUTE9,
201 ATTRIBUTE10,
202 PC_HEADER_ID
203 from AHL_PC_NODES_B
204 where PC_NODE_ID = X_PC_NODE_ID
205 for update of PC_NODE_ID nowait;
206 recinfo c%rowtype;
207
208 cursor c1 is select
209 DESCRIPTION,
210 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
211 from AHL_PC_NODES_TL
212 where PC_NODE_ID = X_PC_NODE_ID
213 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
214 for update of PC_NODE_ID nowait;
215 begin
216 open c;
217 fetch c into recinfo;
218 if (c%notfound) then
219 close c;
220 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
221 app_exception.raise_exception;
222 end if;
223 close c;
224 if ( ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
225 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
226 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
227 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
228 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
229 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
230 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
231 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
232 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
233 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
234 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
235 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
236 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
237 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
238 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
239 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
240 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
241 AND (recinfo.NAME = X_NAME)
242 AND (recinfo.PARENT_NODE_ID = X_PARENT_NODE_ID)
243 AND (recinfo.CHILD_COUNT = X_CHILD_COUNT)
244 AND ((recinfo.OPERATION_STATUS_FLAG = X_OPERATION_STATUS_FLAG)
245 OR ((recinfo.OPERATION_STATUS_FLAG is null) AND (X_OPERATION_STATUS_FLAG is null)))
246 AND (recinfo.DRAFT_FLAG = X_DRAFT_FLAG)
247 AND ((recinfo.LINK_TO_NODE_ID = X_LINK_TO_NODE_ID)
248 OR ((recinfo.LINK_TO_NODE_ID is null) AND (X_LINK_TO_NODE_ID is null)))
249 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
250 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
251 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
252 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
253 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
254 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
255 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
256 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
257 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
258 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
259 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
260 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
261 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
262 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
263 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
264 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
265 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
266 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
267 AND (recinfo.PC_HEADER_ID = X_PC_HEADER_ID)
268 ) then
269 null;
270 else
271 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
272 app_exception.raise_exception;
273 end if;
274
275 for tlinfo in c1 loop
276 if (tlinfo.BASELANG = 'Y') then
277 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
278 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
279 ) then
280 null;
281 else
282 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
283 app_exception.raise_exception;
284 end if;
285 end if;
286 end loop;
287 return;
288 end LOCK_ROW;
289
290 procedure UPDATE_ROW (
291 X_PC_NODE_ID in NUMBER,
292 X_ATTRIBUTE11 in VARCHAR2,
293 X_ATTRIBUTE12 in VARCHAR2,
294 X_ATTRIBUTE13 in VARCHAR2,
295 X_ATTRIBUTE14 in VARCHAR2,
296 X_ATTRIBUTE15 in VARCHAR2,
297 X_ATTRIBUTE_CATEGORY in VARCHAR2,
298 X_ATTRIBUTE1 in VARCHAR2,
299 X_ATTRIBUTE2 in VARCHAR2,
300 X_OBJECT_VERSION_NUMBER in NUMBER,
301 X_NAME in VARCHAR2,
302 X_PARENT_NODE_ID in NUMBER,
303 X_CHILD_COUNT in NUMBER,
304 X_OPERATION_STATUS_FLAG in VARCHAR2,
305 X_DRAFT_FLAG in VARCHAR2,
306 X_LINK_TO_NODE_ID in NUMBER,
307 X_SECURITY_GROUP_ID in NUMBER,
308 X_ATTRIBUTE3 in VARCHAR2,
309 X_ATTRIBUTE4 in VARCHAR2,
310 X_ATTRIBUTE5 in VARCHAR2,
311 X_ATTRIBUTE6 in VARCHAR2,
312 X_ATTRIBUTE7 in VARCHAR2,
313 X_ATTRIBUTE8 in VARCHAR2,
314 X_ATTRIBUTE9 in VARCHAR2,
315 X_ATTRIBUTE10 in VARCHAR2,
316 X_PC_HEADER_ID in NUMBER,
317 X_DESCRIPTION in VARCHAR2,
318 X_LAST_UPDATE_DATE in DATE,
319 X_LAST_UPDATED_BY in NUMBER,
320 X_LAST_UPDATE_LOGIN in NUMBER
321 ) is
322 begin
323 update AHL_PC_NODES_B set
324 ATTRIBUTE11 = X_ATTRIBUTE11,
325 ATTRIBUTE12 = X_ATTRIBUTE12,
326 ATTRIBUTE13 = X_ATTRIBUTE13,
327 ATTRIBUTE14 = X_ATTRIBUTE14,
328 ATTRIBUTE15 = X_ATTRIBUTE15,
329 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
330 ATTRIBUTE1 = X_ATTRIBUTE1,
331 ATTRIBUTE2 = X_ATTRIBUTE2,
332 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
333 NAME = X_NAME,
334 PARENT_NODE_ID = X_PARENT_NODE_ID,
335 CHILD_COUNT = X_CHILD_COUNT,
336 OPERATION_STATUS_FLAG = X_OPERATION_STATUS_FLAG,
337 DRAFT_FLAG = X_DRAFT_FLAG,
338 LINK_TO_NODE_ID = X_LINK_TO_NODE_ID,
339 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
340 ATTRIBUTE3 = X_ATTRIBUTE3,
341 ATTRIBUTE4 = X_ATTRIBUTE4,
342 ATTRIBUTE5 = X_ATTRIBUTE5,
343 ATTRIBUTE6 = X_ATTRIBUTE6,
344 ATTRIBUTE7 = X_ATTRIBUTE7,
345 ATTRIBUTE8 = X_ATTRIBUTE8,
346 ATTRIBUTE9 = X_ATTRIBUTE9,
347 ATTRIBUTE10 = X_ATTRIBUTE10,
348 PC_HEADER_ID = X_PC_HEADER_ID,
349 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
350 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
351 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
352 where PC_NODE_ID = X_PC_NODE_ID;
353
354 if (sql%notfound) then
355 raise no_data_found;
356 end if;
357
358 update AHL_PC_NODES_TL set
359 DESCRIPTION = X_DESCRIPTION,
360 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
361 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
362 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
363 SOURCE_LANG = userenv('LANG')
364 where PC_NODE_ID = X_PC_NODE_ID
365 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
366
367 if (sql%notfound) then
368 raise no_data_found;
369 end if;
370 end UPDATE_ROW;
371
372 procedure DELETE_ROW (
373 X_PC_NODE_ID in NUMBER
374 ) is
375 begin
376 delete from AHL_PC_NODES_TL
377 where PC_NODE_ID = X_PC_NODE_ID;
378
379 if (sql%notfound) then
380 raise no_data_found;
381 end if;
382
383 delete from AHL_PC_NODES_B
384 where PC_NODE_ID = X_PC_NODE_ID;
385
386 if (sql%notfound) then
387 raise no_data_found;
388 end if;
389 end DELETE_ROW;
390
391 procedure ADD_LANGUAGE
392 is
393 begin
394 delete from AHL_PC_NODES_TL T
395 where not exists
396 (select NULL
397 from AHL_PC_NODES_B B
398 where B.PC_NODE_ID = T.PC_NODE_ID
399 );
400
401 update AHL_PC_NODES_TL T set (
402 DESCRIPTION
403 ) = (select
404 B.DESCRIPTION
405 from AHL_PC_NODES_TL B
406 where B.PC_NODE_ID = T.PC_NODE_ID
407 and B.LANGUAGE = T.SOURCE_LANG)
408 where (
409 T.PC_NODE_ID,
410 T.LANGUAGE
411 ) in (select
412 SUBT.PC_NODE_ID,
413 SUBT.LANGUAGE
414 from AHL_PC_NODES_TL SUBB, AHL_PC_NODES_TL SUBT
415 where SUBB.PC_NODE_ID = SUBT.PC_NODE_ID
416 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
417 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
418 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
419 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
420 ));
421
422 insert into AHL_PC_NODES_TL (
423 PC_NODE_ID,
424 DESCRIPTION,
425 LAST_UPDATE_DATE,
426 LAST_UPDATED_BY,
427 CREATION_DATE,
428 CREATED_BY,
429 LAST_UPDATE_LOGIN,
430 SECURITY_GROUP_ID,
431 LANGUAGE,
432 SOURCE_LANG
433 ) select
434 B.PC_NODE_ID,
435 B.DESCRIPTION,
436 B.LAST_UPDATE_DATE,
440 B.LAST_UPDATE_LOGIN,
437 B.LAST_UPDATED_BY,
438 B.CREATION_DATE,
439 B.CREATED_BY,
441 B.SECURITY_GROUP_ID,
442 L.LANGUAGE_CODE,
443 B.SOURCE_LANG
444 from AHL_PC_NODES_TL B, FND_LANGUAGES L
445 where L.INSTALLED_FLAG in ('I', 'B')
446 and B.LANGUAGE = userenv('LANG')
447 and not exists
448 (select NULL
449 from AHL_PC_NODES_TL T
450 where T.PC_NODE_ID = B.PC_NODE_ID
451 and T.LANGUAGE = L.LANGUAGE_CODE);
452 end ADD_LANGUAGE;
453
454 end AHL_PC_NODES_PKG;
[Home] [Help]