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