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