[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_CHARTS_PKG
Source
1 PACKAGE BODY FND_OAM_CHARTS_PKG AS
2 /* $Header: AFOAMCTB.pls 115.1 2004/04/14 04:33:25 bhosingh noship $ */
3 procedure LOAD_ROW (
4 X_CHART_ID in NUMBER,
5 X_CHART_GROUP_ID in NUMBER,
6 X_DISPLAY_ORDER in NUMBER,
7 X_ALLOW_CONFIG in VARCHAR2,
8 X_REFRESH_INTERVAL in NUMBER,
9 X_REFRESH_TYPE in VARCHAR2,
10 X_CHART_TYPE in VARCHAR2,
11 X_OWNER in VARCHAR2,
12 X_CHART_NAME in VARCHAR2,
13 X_DESCRIPTION in VARCHAR2) is
14 begin
15
16 FND_OAM_CHARTS_PKG.LOAD_ROW (
17 X_CHART_ID => X_CHART_ID,
18 X_CHART_GROUP_ID => X_CHART_GROUP_ID,
19 X_DISPLAY_ORDER => X_DISPLAY_ORDER,
20 X_ALLOW_CONFIG => X_ALLOW_CONFIG,
21 X_REFRESH_INTERVAL => X_REFRESH_INTERVAL,
22 X_REFRESH_TYPE => X_REFRESH_TYPE,
23 X_CHART_TYPE => X_CHART_TYPE,
24 X_OWNER => X_OWNER,
25 X_CHART_NAME => X_CHART_NAME,
26 X_DESCRIPTION => X_DESCRIPTION,
27 x_custom_mode => '',
28 x_last_update_date => '');
29
30 end LOAD_ROW;
31
32 procedure LOAD_ROW (
33 X_CHART_ID in NUMBER,
34 X_CHART_GROUP_ID in NUMBER,
35 X_DISPLAY_ORDER in NUMBER,
36 X_ALLOW_CONFIG in VARCHAR2,
37 X_REFRESH_INTERVAL in NUMBER,
38 X_REFRESH_TYPE in VARCHAR2,
39 X_CHART_TYPE in VARCHAR2,
40 X_OWNER in VARCHAR2,
41 X_CHART_NAME in VARCHAR2,
42 X_DESCRIPTION in VARCHAR2,
43 x_custom_mode in varchar2,
44 x_last_update_date in varchar2) is
45
46 mgroup_id number;
47 row_id varchar2(64);
48 f_luby number; -- entity owner in file
49 f_ludate date; -- entity update date in file
50 db_luby number; -- entity owner in db
51 db_ludate date; -- entity update date in db
52
53 begin
54 -- Translate owner to file_last_updated_by
55 f_luby := fnd_load_util.owner_id(x_owner);
56
57 -- Translate char last_update_date to date
58 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
59
60 begin
61 -- check if this chart id already exists.
62 select chart_id, LAST_UPDATED_BY, LAST_UPDATE_DATE
63 into mgroup_id, db_luby, db_ludate
64 from fnd_oam_charts
65 where chart_id = to_number(X_CHART_ID);
66
67 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
68 db_ludate, X_CUSTOM_MODE)) then
69 FND_OAM_CHARTS_PKG.UPDATE_ROW (
70 X_CHART_ID => mgroup_id,
71 X_CHART_GROUP_ID => to_number(X_CHART_GROUP_ID),
72 X_DISPLAY_ORDER => to_number(X_DISPLAY_ORDER),
73 X_ALLOW_CONFIG => X_ALLOW_CONFIG,
74 X_REFRESH_INTERVAL => to_number(X_REFRESH_INTERVAL),
75 X_REFRESH_TYPE => X_REFRESH_TYPE,
76 X_CHART_TYPE => X_CHART_TYPE,
77 X_CHART_NAME => X_CHART_NAME,
78 X_DESCRIPTION => X_DESCRIPTION,
79 X_LAST_UPDATE_DATE => f_ludate,
80 X_LAST_UPDATED_BY => f_luby,
81 X_LAST_UPDATE_LOGIN => 0 );
82 end if;
83 exception
84 when NO_DATA_FOUND then
85
86 FND_OAM_CHARTS_PKG.INSERT_ROW (
87 X_ROWID => row_id,
88 X_CHART_ID => to_number(X_CHART_ID),
89 X_CHART_GROUP_ID => to_number(X_CHART_GROUP_ID),
90 X_DISPLAY_ORDER => to_number(X_DISPLAY_ORDER),
91 X_ALLOW_CONFIG => X_ALLOW_CONFIG,
92 X_REFRESH_INTERVAL => to_number(X_REFRESH_INTERVAL),
93 X_REFRESH_TYPE => X_REFRESH_TYPE,
94 X_CHART_TYPE => X_CHART_TYPE,
95 X_CHART_NAME => X_CHART_NAME,
96 X_DESCRIPTION => X_DESCRIPTION,
97 X_CREATION_DATE => f_ludate,
98 X_CREATED_BY => f_luby,
99 X_LAST_UPDATE_DATE => f_ludate,
100 X_LAST_UPDATED_BY => f_luby,
101 X_LAST_UPDATE_LOGIN => 0 );
102 end;
103 end LOAD_ROW;
104
105 procedure TRANSLATE_ROW (
106 X_CHART_ID in NUMBER,
107 X_CHART_NAME in VARCHAR2,
108 X_DESCRIPTION in VARCHAR2,
109 X_OWNER in VARCHAR2) is
110 begin
111
112 FND_OAM_CHARTS_PKG.translate_row(
113 X_CHART_ID => X_CHART_ID,
114 X_CHART_NAME => X_CHART_NAME,
115 x_description => x_description,
116 x_owner => x_owner,
117 x_custom_mode => '',
118 x_last_update_date => '');
119
120 end TRANSLATE_ROW;
121
122
123 procedure TRANSLATE_ROW (
124 X_CHART_ID in NUMBER,
125 X_CHART_NAME in VARCHAR2,
126 X_DESCRIPTION in VARCHAR2,
127 X_OWNER in VARCHAR2,
128 X_CUSTOM_MODE in VARCHAR2,
129 X_LAST_UPDATE_DATE in VARCHAR2) is
130
131 f_luby number; -- entity owner in file
132 f_ludate date; -- entity update date in file
133 db_luby number; -- entity owner in db
134 db_ludate date; -- entity update date in db
135
136 begin
137
138 -- Translate owner to file_last_updated_by
139 f_luby := fnd_load_util.owner_id(x_owner);
140
141 -- Translate char last_update_date to date
142 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
143
144 begin
145 select LAST_UPDATED_BY, LAST_UPDATE_DATE
146 into db_luby, db_ludate
147 from fnd_oam_charts_tl
148 where chart_id = to_number(X_CHART_ID)
149 and LANGUAGE = userenv('LANG');
150
151 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
152 db_ludate, X_CUSTOM_MODE)) then
153 update fnd_oam_charts_tl set
154 chart_name = nvl(X_CHART_NAME, chart_name),
155 description = nvl(X_DESCRIPTION, description),
156 source_lang = userenv('LANG'),
157 last_update_date = f_ludate,
158 last_updated_by = f_luby,
159 last_update_login = 0
160 where chart_id = to_number(X_CHART_ID)
161 and userenv('LANG') in (language, source_lang);
162 end if;
163 exception
164 when no_data_found then
165 null;
166 end;
167
168 end TRANSLATE_ROW;
169
170 procedure INSERT_ROW (
171 X_ROWID in out nocopy VARCHAR2,
172 X_CHART_ID in NUMBER,
173 X_CHART_GROUP_ID in NUMBER,
174 X_DISPLAY_ORDER in NUMBER,
175 X_ALLOW_CONFIG in VARCHAR2,
176 X_REFRESH_INTERVAL in NUMBER,
177 X_REFRESH_TYPE in VARCHAR2,
178 X_CHART_TYPE in VARCHAR2,
179 X_CHART_NAME in VARCHAR2,
180 X_DESCRIPTION in VARCHAR2,
181 X_CREATION_DATE in DATE,
182 X_CREATED_BY in NUMBER,
183 X_LAST_UPDATE_DATE in DATE,
184 X_LAST_UPDATED_BY in NUMBER,
185 X_LAST_UPDATE_LOGIN in NUMBER
186 ) is
187 cursor C is select ROWID from FND_OAM_CHARTS
188 where CHART_ID = X_CHART_ID
189 ;
190 begin
191 insert into FND_OAM_CHARTS (
192 CHART_ID,
193 CHART_GROUP_ID,
194 DISPLAY_ORDER,
195 ALLOW_CONFIG,
196 REFRESH_INTERVAL,
197 REFRESH_TYPE,
198 CHART_TYPE,
199 CREATION_DATE,
200 CREATED_BY,
201 LAST_UPDATE_DATE,
202 LAST_UPDATED_BY,
203 LAST_UPDATE_LOGIN
204 ) values (
205 X_CHART_ID,
206 X_CHART_GROUP_ID,
207 X_DISPLAY_ORDER,
208 X_ALLOW_CONFIG,
209 X_REFRESH_INTERVAL,
210 X_REFRESH_TYPE,
211 X_CHART_TYPE,
212 X_CREATION_DATE,
213 X_CREATED_BY,
214 X_LAST_UPDATE_DATE,
215 X_LAST_UPDATED_BY,
216 X_LAST_UPDATE_LOGIN
217 );
218
219 insert into FND_OAM_CHARTS_TL (
220 CHART_ID,
221 CHART_NAME,
222 DESCRIPTION,
223 CREATED_BY,
224 CREATION_DATE,
225 LAST_UPDATED_BY,
226 LAST_UPDATE_DATE,
227 LAST_UPDATE_LOGIN,
228 LANGUAGE,
229 SOURCE_LANG
230 ) select
231 X_CHART_ID,
232 X_CHART_NAME,
233 X_DESCRIPTION,
234 X_CREATED_BY,
235 X_CREATION_DATE,
236 X_LAST_UPDATED_BY,
237 X_LAST_UPDATE_DATE,
238 X_LAST_UPDATE_LOGIN,
239 L.LANGUAGE_CODE,
240 userenv('LANG')
241 from FND_LANGUAGES L
242 where L.INSTALLED_FLAG in ('I', 'B')
243 and not exists
244 (select NULL
245 from FND_OAM_CHARTS_TL T
246 where T.CHART_ID = X_CHART_ID
247 and T.LANGUAGE = L.LANGUAGE_CODE);
248
249 open c;
250 fetch c into X_ROWID;
251 if (c%notfound) then
252 close c;
253 raise no_data_found;
254 end if;
255 close c;
256
257 end INSERT_ROW;
258
259 procedure LOCK_ROW (
260 X_CHART_ID in NUMBER,
261 X_CHART_GROUP_ID in NUMBER,
262 X_DISPLAY_ORDER in NUMBER,
263 X_ALLOW_CONFIG in VARCHAR2,
264 X_REFRESH_INTERVAL in NUMBER,
265 X_REFRESH_TYPE in VARCHAR2,
266 X_CHART_TYPE in VARCHAR2,
267 X_CHART_NAME in VARCHAR2,
268 X_DESCRIPTION in VARCHAR2
269 ) is
270 cursor c is select
271 CHART_GROUP_ID,
272 DISPLAY_ORDER,
273 ALLOW_CONFIG,
274 REFRESH_INTERVAL,
275 REFRESH_TYPE,
276 CHART_TYPE
277 from FND_OAM_CHARTS
278 where CHART_ID = X_CHART_ID
279 for update of CHART_ID nowait;
280 recinfo c%rowtype;
281
282 cursor c1 is select
283 CHART_NAME,
284 DESCRIPTION,
285 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
286 from FND_OAM_CHARTS_TL
287 where CHART_ID = X_CHART_ID
288 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
289 for update of CHART_ID nowait;
290 begin
291 open c;
292 fetch c into recinfo;
293 if (c%notfound) then
294 close c;
295 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
296 app_exception.raise_exception;
297 end if;
298 close c;
299 if ( (recinfo.CHART_GROUP_ID = X_CHART_GROUP_ID)
300 AND (recinfo.DISPLAY_ORDER = X_DISPLAY_ORDER)
301 AND (recinfo.ALLOW_CONFIG = X_ALLOW_CONFIG)
302 AND (recinfo.REFRESH_INTERVAL = X_REFRESH_INTERVAL)
303 AND (recinfo.REFRESH_TYPE = X_REFRESH_TYPE)
304 AND (recinfo.CHART_TYPE = X_CHART_TYPE)
305 ) then
306 null;
307 else
308 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
309 app_exception.raise_exception;
310 end if;
311
312 for tlinfo in c1 loop
313 if (tlinfo.BASELANG = 'Y') then
314 if ( (tlinfo.CHART_NAME = X_CHART_NAME)
315 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
316 ) then
317 null;
318 else
319 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
320 app_exception.raise_exception;
321 end if;
322 end if;
323 end loop;
324 return;
325 end LOCK_ROW;
326
327 procedure UPDATE_ROW (
328 X_CHART_ID in NUMBER,
329 X_CHART_GROUP_ID in NUMBER,
330 X_DISPLAY_ORDER in NUMBER,
331 X_ALLOW_CONFIG in VARCHAR2,
332 X_REFRESH_INTERVAL in NUMBER,
333 X_REFRESH_TYPE in VARCHAR2,
334 X_CHART_TYPE in VARCHAR2,
335 X_CHART_NAME in VARCHAR2,
336 X_DESCRIPTION in VARCHAR2,
337 X_LAST_UPDATE_DATE in DATE,
338 X_LAST_UPDATED_BY in NUMBER,
339 X_LAST_UPDATE_LOGIN in NUMBER
340 ) is
341 begin
342 update FND_OAM_CHARTS set
343 CHART_GROUP_ID = X_CHART_GROUP_ID,
344 DISPLAY_ORDER = X_DISPLAY_ORDER,
345 ALLOW_CONFIG = X_ALLOW_CONFIG,
346 REFRESH_INTERVAL = X_REFRESH_INTERVAL,
347 REFRESH_TYPE = X_REFRESH_TYPE,
348 CHART_TYPE = X_CHART_TYPE,
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 CHART_ID = X_CHART_ID;
353
354 if (sql%notfound) then
355 raise no_data_found;
356 end if;
357
358 update FND_OAM_CHARTS_TL set
359 CHART_NAME = X_CHART_NAME,
360 DESCRIPTION = X_DESCRIPTION,
361 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
362 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
363 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
364 SOURCE_LANG = userenv('LANG')
365 where CHART_ID = X_CHART_ID
366 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
367
368 if (sql%notfound) then
369 raise no_data_found;
370 end if;
371 end UPDATE_ROW;
372
373 procedure DELETE_ROW (
374 X_CHART_ID in NUMBER
375 ) is
376 begin
377 delete from FND_OAM_CHARTS_TL
378 where CHART_ID = X_CHART_ID;
379
380 if (sql%notfound) then
381 raise no_data_found;
382 end if;
383
384 delete from FND_OAM_CHARTS
385 where CHART_ID = X_CHART_ID;
386
387 if (sql%notfound) then
388 raise no_data_found;
389 end if;
390 end DELETE_ROW;
391
392 procedure ADD_LANGUAGE
393 is
394 begin
395 /* Mar/19/03 requested by Ric Ginsberg */
396 /* The following update statements are commented out */
397 /* as a quick workaround to fix the time-consuming table handler issue */
398 /* Eventually we'll need to turn them into a separate fix_language procedure */
399 /*
400 delete from FND_OAM_CHARTS_TL T
401 where not exists
402 (select NULL
403 from FND_OAM_CHARTS B
404 where B.CHART_ID = T.CHART_ID
405 );
406
407 update FND_OAM_CHARTS_TL T set (
408 CHART_NAME,
409 DESCRIPTION
410 ) = (select
411 B.CHART_NAME,
412 B.DESCRIPTION
413 from FND_OAM_CHARTS_TL B
414 where B.CHART_ID = T.CHART_ID
415 and B.LANGUAGE = T.SOURCE_LANG)
416 where (
417 T.CHART_ID,
418 T.LANGUAGE
419 ) in (select
420 SUBT.CHART_ID,
421 SUBT.LANGUAGE
422 from FND_OAM_CHARTS_TL SUBB, FND_OAM_CHARTS_TL SUBT
423 where SUBB.CHART_ID = SUBT.CHART_ID
424 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
425 and (SUBB.CHART_NAME <> SUBT.CHART_NAME
426 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
427 ));
428
429 */
430
431 insert into FND_OAM_CHARTS_TL (
432 CHART_ID,
433 CHART_NAME,
434 DESCRIPTION,
435 CREATED_BY,
436 CREATION_DATE,
437 LAST_UPDATED_BY,
438 LAST_UPDATE_DATE,
439 LAST_UPDATE_LOGIN,
440 LANGUAGE,
441 SOURCE_LANG
442 ) select /*+ ORDERED */
443 B.CHART_ID,
444 B.CHART_NAME,
445 B.DESCRIPTION,
446 B.CREATED_BY,
447 B.CREATION_DATE,
448 B.LAST_UPDATED_BY,
449 B.LAST_UPDATE_DATE,
450 B.LAST_UPDATE_LOGIN,
451 L.LANGUAGE_CODE,
452 B.SOURCE_LANG
453 from FND_OAM_CHARTS_TL B, FND_LANGUAGES L
454 where L.INSTALLED_FLAG in ('I', 'B')
455 and B.LANGUAGE = userenv('LANG')
456 and not exists
457 (select NULL
458 from FND_OAM_CHARTS_TL T
459 where T.CHART_ID = B.CHART_ID
460 and T.LANGUAGE = L.LANGUAGE_CODE);
461 end ADD_LANGUAGE;
462
463 end FND_OAM_CHARTS_PKG;