1 package body CS_SR_UWQ_NODES_PKG as
2 /* $Header: csnodesb.pls 120.0 2006/02/28 12:03:57 spusegao noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_NODE_ID in NUMBER,
6 X_NODE_VIEW in VARCHAR2,
7 X_DATA_SOURCE in VARCHAR2,
8 X_MEDIA_TYPE_ID in NUMBER,
9 X_WHERE_CLAUSE in VARCHAR2,
10 X_RES_CAT_ENUM_FLAG in VARCHAR2,
11 X_NODE_TYPE in VARCHAR2,
12 X_HIDE_IF_EMPTY in VARCHAR2,
13 X_NODE_DEPTH in NUMBER,
14 X_PARENT_ID in NUMBER,
15 X_NODE_QUERY in VARCHAR2,
16 X_CURSOR_SQL in VARCHAR2,
17 X_CURSOR_KEY_COL in VARCHAR2,
18 X_ENABLED_FLAG in VARCHAR2,
19 X_OBJECT_VERSION_NUMBER in NUMBER,
20 X_SECURITY_GROUP_ID in NUMBER,
21 X_NODE_LABEL in VARCHAR2,
22 X_CREATION_DATE in DATE,
23 X_CREATED_BY in NUMBER,
24 X_LAST_UPDATE_DATE in DATE,
25 X_LAST_UPDATED_BY in NUMBER,
26 X_LAST_UPDATE_LOGIN in NUMBER
27 ) is
28 cursor C is select ROWID from CS_SR_UWQ_NODES_B
29 where NODE_ID = X_NODE_ID
30 ;
31 begin
32 insert into CS_SR_UWQ_NODES_B (
33 NODE_ID,
34 NODE_VIEW,
35 DATA_SOURCE,
36 MEDIA_TYPE_ID,
37 WHERE_CLAUSE,
38 RES_CAT_ENUM_FLAG,
39 NODE_TYPE,
40 HIDE_IF_EMPTY,
41 NODE_DEPTH,
42 PARENT_ID,
43 NODE_QUERY,
44 CURSOR_SQL,
45 CURSOR_KEY_COL,
46 ENABLED_FLAG,
47 OBJECT_VERSION_NUMBER,
48 CREATION_DATE,
49 CREATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_LOGIN
53 ) values (
54 X_NODE_ID,
55 X_NODE_VIEW,
56 X_DATA_SOURCE,
57 X_MEDIA_TYPE_ID,
58 X_WHERE_CLAUSE,
59 X_RES_CAT_ENUM_FLAG,
60 X_NODE_TYPE,
61 X_HIDE_IF_EMPTY,
62 X_NODE_DEPTH,
63 X_PARENT_ID,
64 X_NODE_QUERY,
65 X_CURSOR_SQL,
66 X_CURSOR_KEY_COL,
67 X_ENABLED_FLAG,
68 X_OBJECT_VERSION_NUMBER,
69 X_CREATION_DATE,
70 X_CREATED_BY,
71 X_LAST_UPDATE_DATE,
72 X_LAST_UPDATED_BY,
73 X_LAST_UPDATE_LOGIN
74 );
75
76 insert into CS_SR_UWQ_NODES_TL (
77 NODE_ID,
78 NODE_LABEL,
79 CREATION_DATE,
80 CREATED_BY,
81 LAST_UPDATE_DATE,
82 LAST_UPDATED_BY,
83 LAST_UPDATE_LOGIN,
84 LANGUAGE,
85 SOURCE_LANG
86 ) select
87 X_NODE_ID,
88 X_NODE_LABEL,
89 X_CREATION_DATE,
90 X_CREATED_BY,
91 X_LAST_UPDATE_DATE,
92 X_LAST_UPDATED_BY,
93 X_LAST_UPDATE_LOGIN,
94 L.LANGUAGE_CODE,
95 userenv('LANG')
96 from FND_LANGUAGES L
97 where L.INSTALLED_FLAG in ('I', 'B')
98 and not exists
99 (select NULL
100 from CS_SR_UWQ_NODES_TL T
101 where T.NODE_ID = X_NODE_ID
102 and T.LANGUAGE = L.LANGUAGE_CODE);
103
104 open c;
105 fetch c into X_ROWID;
106 if (c%notfound) then
107 close c;
108 raise no_data_found;
109 end if;
110 close c;
111
112 end INSERT_ROW;
113
114 procedure LOCK_ROW (
115 X_NODE_ID in NUMBER,
116 X_NODE_VIEW in VARCHAR2,
117 X_DATA_SOURCE in VARCHAR2,
118 X_MEDIA_TYPE_ID in NUMBER,
119 X_WHERE_CLAUSE in VARCHAR2,
120 X_RES_CAT_ENUM_FLAG in VARCHAR2,
121 X_NODE_TYPE in VARCHAR2,
122 X_HIDE_IF_EMPTY in VARCHAR2,
123 X_NODE_DEPTH in NUMBER,
124 X_PARENT_ID in NUMBER,
125 X_NODE_QUERY in VARCHAR2,
126 X_CURSOR_SQL in VARCHAR2,
127 X_CURSOR_KEY_COL in VARCHAR2,
128 X_ENABLED_FLAG in VARCHAR2,
129 X_OBJECT_VERSION_NUMBER in NUMBER,
130 X_SECURITY_GROUP_ID in NUMBER,
131 X_NODE_LABEL in VARCHAR2
132 ) is
133 cursor c is select
134 NODE_VIEW,
135 DATA_SOURCE,
136 MEDIA_TYPE_ID,
137 WHERE_CLAUSE,
138 RES_CAT_ENUM_FLAG,
139 NODE_TYPE,
140 HIDE_IF_EMPTY,
141 NODE_DEPTH,
142 PARENT_ID,
143 NODE_QUERY,
144 CURSOR_SQL,
145 CURSOR_KEY_COL,
146 ENABLED_FLAG,
147 OBJECT_VERSION_NUMBER,
148 SECURITY_GROUP_ID
149 from CS_SR_UWQ_NODES_B
150 where NODE_ID = X_NODE_ID
151 for update of NODE_ID nowait;
152 recinfo c%rowtype;
153
154 cursor c1 is select
155 NODE_LABEL,
156 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
157 from CS_SR_UWQ_NODES_TL
158 where NODE_ID = X_NODE_ID
159 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
160 for update of NODE_ID nowait;
161 begin
162 open c;
163 fetch c into recinfo;
164 if (c%notfound) then
165 close c;
166 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
167 app_exception.raise_exception;
168 end if;
169 close c;
170 if ( (recinfo.NODE_VIEW = X_NODE_VIEW)
171 AND (recinfo.DATA_SOURCE = X_DATA_SOURCE)
172 AND ((recinfo.MEDIA_TYPE_ID = X_MEDIA_TYPE_ID)
173 OR ((recinfo.MEDIA_TYPE_ID is null) AND (X_MEDIA_TYPE_ID is null)))
174 AND ((recinfo.WHERE_CLAUSE = X_WHERE_CLAUSE)
175 OR ((recinfo.WHERE_CLAUSE is null) AND (X_WHERE_CLAUSE is null)))
176 AND ((recinfo.RES_CAT_ENUM_FLAG = X_RES_CAT_ENUM_FLAG)
177 OR ((recinfo.RES_CAT_ENUM_FLAG is null) AND (X_RES_CAT_ENUM_FLAG is null)))
178 AND ((recinfo.NODE_TYPE = X_NODE_TYPE)
179 OR ((recinfo.NODE_TYPE is null) AND (X_NODE_TYPE is null)))
180 AND ((recinfo.HIDE_IF_EMPTY = X_HIDE_IF_EMPTY)
181 OR ((recinfo.HIDE_IF_EMPTY is null) AND (X_HIDE_IF_EMPTY is null)))
182 AND ((recinfo.NODE_DEPTH = X_NODE_DEPTH)
183 OR ((recinfo.NODE_DEPTH is null) AND (X_NODE_DEPTH is null)))
184 AND ((recinfo.PARENT_ID = X_PARENT_ID)
185 OR ((recinfo.PARENT_ID is null) AND (X_PARENT_ID is null)))
186 AND ((recinfo.NODE_QUERY = X_NODE_QUERY)
187 OR ((recinfo.NODE_QUERY is null) AND (X_NODE_QUERY is null)))
188 AND ((recinfo.CURSOR_SQL = X_CURSOR_SQL)
189 OR ((recinfo.CURSOR_SQL is null) AND (X_CURSOR_SQL is null)))
190 AND ((recinfo.CURSOR_KEY_COL = X_CURSOR_KEY_COL)
191 OR ((recinfo.CURSOR_KEY_COL is null) AND (X_CURSOR_KEY_COL is null)))
192 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
193 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
194 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
195 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
196 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
197 ) then
198 null;
199 else
200 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
201 app_exception.raise_exception;
202 end if;
203
204 for tlinfo in c1 loop
205 if (tlinfo.BASELANG = 'Y') then
206 if ( (tlinfo.NODE_LABEL = X_NODE_LABEL)
207 ) then
208 null;
209 else
210 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
211 app_exception.raise_exception;
212 end if;
213 end if;
214 end loop;
215 return;
216 end LOCK_ROW;
217
218 procedure UPDATE_ROW (
219 X_NODE_ID in NUMBER,
220 X_NODE_VIEW in VARCHAR2,
221 X_DATA_SOURCE in VARCHAR2,
222 X_MEDIA_TYPE_ID in NUMBER,
223 X_WHERE_CLAUSE in VARCHAR2,
224 X_RES_CAT_ENUM_FLAG in VARCHAR2,
225 X_NODE_TYPE in VARCHAR2,
226 X_HIDE_IF_EMPTY in VARCHAR2,
227 X_NODE_DEPTH in NUMBER,
228 X_PARENT_ID in NUMBER,
229 X_NODE_QUERY in VARCHAR2,
230 X_CURSOR_SQL in VARCHAR2,
231 X_CURSOR_KEY_COL in VARCHAR2,
232 X_ENABLED_FLAG in VARCHAR2,
233 X_OBJECT_VERSION_NUMBER in NUMBER,
234 X_SECURITY_GROUP_ID in NUMBER,
235 X_NODE_LABEL in VARCHAR2,
236 X_LAST_UPDATE_DATE in DATE,
237 X_LAST_UPDATED_BY in NUMBER,
238 X_LAST_UPDATE_LOGIN in NUMBER
239 ) is
240 begin
241
242 update CS_SR_UWQ_NODES_B set
243 ENABLED_FLAG = X_ENABLED_FLAG
244 where NODE_ID = X_NODE_ID
245 and LAST_UPDATED_BY = X_LAST_UPDATED_BY;
246
247 update CS_SR_UWQ_NODES_B set
248 NODE_VIEW = X_NODE_VIEW,
249 DATA_SOURCE = X_DATA_SOURCE,
250 MEDIA_TYPE_ID = X_MEDIA_TYPE_ID,
251 WHERE_CLAUSE = X_WHERE_CLAUSE,
252 RES_CAT_ENUM_FLAG = X_RES_CAT_ENUM_FLAG,
253 NODE_TYPE = X_NODE_TYPE,
254 HIDE_IF_EMPTY = X_HIDE_IF_EMPTY,
255 NODE_DEPTH = X_NODE_DEPTH,
256 PARENT_ID = X_PARENT_ID,
257 NODE_QUERY = X_NODE_QUERY,
258 CURSOR_SQL = X_CURSOR_SQL,
259 CURSOR_KEY_COL = X_CURSOR_KEY_COL,
260 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
261 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
262 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
263 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
264 where NODE_ID = X_NODE_ID;
265
266 if (sql%notfound) then
267 raise no_data_found;
268 end if;
269
270 update CS_SR_UWQ_NODES_TL set
271 NODE_LABEL = X_NODE_LABEL,
272 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
273 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
274 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
275 SOURCE_LANG = userenv('LANG')
276 where NODE_ID = X_NODE_ID
277 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
278
279
280 if (sql%notfound) then
281 raise no_data_found;
282 end if;
283 end UPDATE_ROW;
284
285 procedure DELETE_ROW (
286 X_NODE_ID in NUMBER
287 ) is
288 begin
289 delete from CS_SR_UWQ_NODES_TL
290 where NODE_ID = X_NODE_ID;
291
292 if (sql%notfound) then
293 raise no_data_found;
294 end if;
295
296 delete from CS_SR_UWQ_NODES_B
297 where NODE_ID = X_NODE_ID;
298
299 if (sql%notfound) then
300 raise no_data_found;
301 end if;
302 end DELETE_ROW;
303
304 procedure ADD_LANGUAGE
305 is
306 begin
307 delete from CS_SR_UWQ_NODES_TL T
308 where not exists
309 (select NULL
310 from CS_SR_UWQ_NODES_B B
311 where B.NODE_ID = T.NODE_ID
312 );
313
314 update CS_SR_UWQ_NODES_TL T set (
315 NODE_LABEL
316 ) = (select
317 B.NODE_LABEL
318 from CS_SR_UWQ_NODES_TL B
319 where B.NODE_ID = T.NODE_ID
320 and B.LANGUAGE = T.SOURCE_LANG)
321 where (
322 T.NODE_ID,
323 T.LANGUAGE
324 ) in (select
325 SUBT.NODE_ID,
326 SUBT.LANGUAGE
327 from CS_SR_UWQ_NODES_TL SUBB, CS_SR_UWQ_NODES_TL SUBT
328 where SUBB.NODE_ID = SUBT.NODE_ID
329 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
330 and (SUBB.NODE_LABEL <> SUBT.NODE_LABEL
331 ));
332
333 insert into CS_SR_UWQ_NODES_TL (
334 NODE_ID,
335 NODE_LABEL,
336 CREATION_DATE,
337 CREATED_BY,
338 LAST_UPDATE_DATE,
339 LAST_UPDATED_BY,
340 LAST_UPDATE_LOGIN,
341 LANGUAGE,
342 SOURCE_LANG
343 ) select
344 B.NODE_ID,
345 B.NODE_LABEL,
346 B.CREATION_DATE,
347 B.CREATED_BY,
348 B.LAST_UPDATE_DATE,
349 B.LAST_UPDATED_BY,
350 B.LAST_UPDATE_LOGIN,
351 L.LANGUAGE_CODE,
352 B.SOURCE_LANG
353 from CS_SR_UWQ_NODES_TL B, FND_LANGUAGES L
354 where L.INSTALLED_FLAG in ('I', 'B')
355 and B.LANGUAGE = userenv('LANG')
356 and not exists
357 (select NULL
358 from CS_SR_UWQ_NODES_TL T
359 where T.NODE_ID = B.NODE_ID
360 and T.LANGUAGE = L.LANGUAGE_CODE);
361 end ADD_LANGUAGE;
362
363 Procedure Translate_Row
364 (x_node_id IN NUMBER,
365 x_node_label IN VARCHAR2,
366 x_owner IN VARCHAR2)
367 is
368 Begin
369 UPDATE CS_SR_UWQ_NODES_TL
370 set
371 NODE_LABEL = X_NODE_LABEL,
372 LAST_UPDATE_DATE = SYSDATE,
373 LAST_UPDATED_BY = decode(X_OWNER,'SEED',1,0),
374 LAST_UPDATE_LOGIN = 0,
375 SOURCE_LANG = userenv('LANG')
376 where node_id = x_node_id
377 and userenv('LANG') in (language, source_lang);
378 End Translate_Row;
379
380 end CS_SR_UWQ_NODES_PKG;
[Home] [Help]