[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_MET_GRPS_PKG
Source
1 PACKAGE BODY fnd_oam_met_grps_pkg AS
2 /* $Header: AFOAMMGB.pls 120.2 2005/10/19 11:29:39 ilawler noship $ */
3 procedure LOAD_ROW (
4 X_METRIC_GROUP_ID in VARCHAR2,
5 X_SEQUENCE in VARCHAR2,
6 X_OWNER in VARCHAR2,
7 X_METRIC_GROUP_DISPLAY_NAME in VARCHAR2,
8 X_DESCRIPTION in VARCHAR2) is
9 begin
10
11 fnd_oam_met_grps_pkg.LOAD_ROW (
12 X_METRIC_GROUP_ID => X_METRIC_GROUP_ID,
13 X_SEQUENCE => X_SEQUENCE,
14 X_OWNER => X_OWNER,
15 X_METRIC_GROUP_DISPLAY_NAME => X_METRIC_GROUP_DISPLAY_NAME,
16 X_DESCRIPTION => X_DESCRIPTION,
17 x_custom_mode => '',
18 x_last_update_date => '');
19
20 end LOAD_ROW;
21
22 procedure LOAD_ROW (
23 X_METRIC_GROUP_ID in VARCHAR2,
24 X_SEQUENCE in VARCHAR2,
25 X_OWNER in VARCHAR2,
26 X_METRIC_GROUP_DISPLAY_NAME in VARCHAR2,
27 X_DESCRIPTION in VARCHAR2,
28 x_custom_mode in varchar2,
29 x_last_update_date in varchar2) is
30
31 mgroup_id number;
32 row_id varchar2(64);
33 f_luby number; -- entity owner in file
34 f_ludate date; -- entity update date in file
35 db_luby number; -- entity owner in db
36 db_ludate date; -- entity update date in db
37
38 cursor c1 is
39 select last_updated_by, last_update_date
40 from fnd_oam_met_grps_tl
41 where metric_group_id = to_number(X_METRIC_GROUP_ID)
42 order by last_update_date asc;
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 -- added for bug fix 2507658
52 -- check if this metric group id already exists.
53 select metric_group_id
54 into mgroup_id
55 from fnd_oam_met_grps_tl
56 where metric_group_id = to_number(X_METRIC_GROUP_ID)
57 and rownum = 1;
58
59 -- obtain the last update stamp; pick the first row
60 for rec in c1 loop
61 db_luby := rec.last_updated_by;
62 db_ludate := rec.last_update_date;
63 exit when 1=1;
64 end loop;
65
66 --select metric_group_id,LAST_UPDATED_BY, LAST_UPDATE_DATE
67 --into mgroup_id, db_luby, db_ludate
68 --from fnd_oam_met_grps_tl
69 --where metric_group_id = to_number(X_METRIC_GROUP_ID)
70 --and LANGUAGE = userenv('LANG');
71
72 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
73 db_ludate, X_CUSTOM_MODE)) then
74 fnd_oam_met_grps_pkg.UPDATE_ROW (
75 X_METRIC_GROUP_ID => mgroup_id,
76 X_SEQUENCE => to_number(X_SEQUENCE),
77 X_METRIC_GROUP_DISPLAY_NAME => X_METRIC_GROUP_DISPLAY_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_met_grps_pkg.INSERT_ROW (
87 X_ROWID => row_id,
88 X_METRIC_GROUP_ID => to_number(X_METRIC_GROUP_ID),
89 X_SEQUENCE => to_number(X_SEQUENCE),
90 X_METRIC_GROUP_DISPLAY_NAME => X_METRIC_GROUP_DISPLAY_NAME,
91 X_DESCRIPTION => X_DESCRIPTION,
92 X_CREATION_DATE => f_ludate,
93 X_CREATED_BY => f_luby,
94 X_LAST_UPDATE_DATE => f_ludate,
95 X_LAST_UPDATED_BY => f_luby,
96 X_LAST_UPDATE_LOGIN => 0 );
97 end;
98 end LOAD_ROW;
99
100 procedure TRANSLATE_ROW (
101 X_METRIC_GROUP_ID in VARCHAR2,
102 X_OWNER in VARCHAR2,
103 X_METRIC_GROUP_DISPLAY_NAME in VARCHAR2,
104 X_DESCRIPTION in VARCHAR2) is
105 begin
106
107 FND_OAM_MET_GRPS_PKG.translate_row(
108 x_metric_group_id => x_metric_group_id,
109 x_owner => x_owner,
110 x_metric_group_display_name => x_metric_group_display_name,
111 x_description => x_description,
112 x_custom_mode => '',
113 x_last_update_date => '');
114
115 end TRANSLATE_ROW;
116
117
118 procedure TRANSLATE_ROW (
119 X_METRIC_GROUP_ID in VARCHAR2,
120 X_OWNER in VARCHAR2,
121 X_METRIC_GROUP_DISPLAY_NAME in VARCHAR2,
122 X_DESCRIPTION in VARCHAR2,
123 X_CUSTOM_MODE in VARCHAR2,
124 X_LAST_UPDATE_DATE in VARCHAR2) is
125
126 f_luby number; -- entity owner in file
127 f_ludate date; -- entity update date in file
128 db_luby number; -- entity owner in db
129 db_ludate date; -- entity update date in db
130
131 begin
132
133 -- Translate owner to file_last_updated_by
134 f_luby := fnd_load_util.owner_id(x_owner);
135
136 -- Translate char last_update_date to date
137 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
138
139 begin
140 select LAST_UPDATED_BY, LAST_UPDATE_DATE
141 into db_luby, db_ludate
142 from fnd_oam_met_grps_tl
143 where metric_group_id = to_number(X_METRIC_GROUP_ID)
144 and LANGUAGE = userenv('LANG');
145
146 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
147 db_ludate, X_CUSTOM_MODE)) then
148 update fnd_oam_met_grps_tl set
149 metric_group_display_name = nvl(X_METRIC_GROUP_DISPLAY_NAME, metric_group_display_name),
150 description = nvl(X_DESCRIPTION, description),
151 source_lang = userenv('LANG'),
152 last_update_date = f_ludate,
153 last_updated_by = f_luby,
154 last_update_login = 0
155 where metric_group_id = to_number(X_METRIC_GROUP_ID)
156 and userenv('LANG') in (language, source_lang);
157 end if;
158 exception
159 when no_data_found then
160 null;
161 end;
162
163 end TRANSLATE_ROW;
164
165 procedure INSERT_ROW (
166 X_ROWID IN OUT NOCOPY VARCHAR2,
167 X_METRIC_GROUP_ID in NUMBER,
168 X_SEQUENCE in VARCHAR2,
169 X_METRIC_GROUP_DISPLAY_NAME in VARCHAR2,
170 X_DESCRIPTION in VARCHAR2,
171 X_CREATED_BY in NUMBER,
172 X_CREATION_DATE in DATE,
173 X_LAST_UPDATED_BY in NUMBER,
174 X_LAST_UPDATE_DATE in DATE,
175 X_LAST_UPDATE_LOGIN in NUMBER)
176 is
177 cursor C is select ROWID from FND_OAM_MET_GRPS_TL
178 where METRIC_GROUP_ID = X_METRIC_GROUP_ID;
179 begin
180 insert into FND_OAM_MET_GRPS_TL (
181 METRIC_GROUP_ID,
182 SEQUENCE,
183 METRIC_GROUP_DISPLAY_NAME,
184 DESCRIPTION,
185 CREATED_BY,
186 CREATION_DATE,
187 LAST_UPDATED_BY,
188 LAST_UPDATE_DATE,
189 LAST_UPDATE_LOGIN,
190 LANGUAGE,
191 SOURCE_LANG
192 ) select
193 X_METRIC_GROUP_ID,
194 X_SEQUENCE,
195 X_METRIC_GROUP_DISPLAY_NAME,
196 X_DESCRIPTION,
197 X_CREATED_BY,
198 X_CREATION_DATE,
199 X_LAST_UPDATED_BY,
200 X_LAST_UPDATE_DATE,
201 X_LAST_UPDATE_LOGIN,
202 L.LANGUAGE_CODE,
203 userenv('LANG')
204 from FND_LANGUAGES L
205 where L.INSTALLED_FLAG in ('I', 'B')
206 and not exists
207 (select NULL
208 from FND_OAM_MET_GRPS_TL T
209 where T.METRIC_GROUP_ID = X_METRIC_GROUP_ID
210 and T.LANGUAGE = L.LANGUAGE_CODE);
211
212 open c;
213 fetch c into X_ROWID;
214 if (c%notfound) then
215 close c;
216 raise no_data_found;
217 end if;
218 close c;
219 END INSERT_ROW;
220
221 procedure UPDATE_ROW (
222 X_METRIC_GROUP_ID in NUMBER,
223 X_SEQUENCE in NUMBER,
224 X_METRIC_GROUP_DISPLAY_NAME in VARCHAR2,
225 X_DESCRIPTION in VARCHAR2,
226 X_LAST_UPDATE_DATE in DATE,
227 X_LAST_UPDATED_BY in NUMBER,
228 X_LAST_UPDATE_LOGIN in NUMBER
229 ) is
230 begin
231 update FND_OAM_MET_GRPS_TL set
232 SEQUENCE = X_SEQUENCE,
233 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
234 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
235 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
236 where METRIC_GROUP_ID = X_METRIC_GROUP_ID;
237
238 update FND_OAM_MET_GRPS_TL set
239 METRIC_GROUP_DISPLAY_NAME = X_METRIC_GROUP_DISPLAY_NAME,
240 DESCRIPTION = X_DESCRIPTION,
241 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
242 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
243 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
244 SOURCE_LANG = userenv('LANG')
245 where METRIC_GROUP_ID = X_METRIC_GROUP_ID
246 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
247
248 if (sql%notfound) then
249 raise no_data_found;
250 end if;
251 end UPDATE_ROW;
252
253 procedure DELETE_ROW (
254 X_METRIC_GROUP_ID in NUMBER
255 ) is
256 begin
257 delete from FND_OAM_MET_GRPS_TL
258 where METRIC_GROUP_ID = X_METRIC_GROUP_ID;
259
260 if (sql%notfound) then
261 raise no_data_found;
262 end if;
263 end DELETE_ROW;
264
265 procedure ADD_LANGUAGE
266 is
267 begin
268
269 /* Mar/19/03 requested by Ric Ginsberg */
270 /* The following delete and update statements are commented out */
271 /* as a quick workaround to fix the time-consuming table handler issue */
272 /* Eventually we'll need to turn them into a separate fix_language procedure */
273 /*
274
275 update FND_OAM_MET_GRPS_TL T set (
276 SEQUENCE,
277 METRIC_GROUP_DISPLAY_NAME,
278 DESCRIPTION
279 ) = (select
280 B.SEQUENCE,
281 B.METRIC_GROUP_DISPLAY_NAME,
282 B.DESCRIPTION
283 from FND_OAM_MET_GRPS_TL B
284 where B.METRIC_GROUP_ID = T.METRIC_GROUP_ID
285 and B.LANGUAGE = T.SOURCE_LANG)
286 where (
287 T.METRIC_GROUP_ID,
288 T.LANGUAGE
289 ) in (select
290 SUBT.METRIC_GROUP_ID,
291 SUBT.LANGUAGE
292 from FND_OAM_MET_GRPS_TL SUBB, FND_OAM_MET_GRPS_TL SUBT
293 where SUBB.METRIC_GROUP_ID = SUBT.METRIC_GROUP_ID
294 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
295 and (SUBB.METRIC_GROUP_DISPLAY_NAME <> SUBT.METRIC_GROUP_DISPLAY_NAME
296 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
297 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
298 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
299 ));
300 */
301
302 insert into FND_OAM_MET_GRPS_TL (
303 METRIC_GROUP_ID,
304 SEQUENCE,
305 METRIC_GROUP_DISPLAY_NAME,
306 DESCRIPTION,
307 CREATED_BY,
308 CREATION_DATE,
309 LAST_UPDATED_BY,
310 LAST_UPDATE_DATE,
311 LAST_UPDATE_LOGIN,
312 LANGUAGE,
313 SOURCE_LANG
314 ) select
315 B.METRIC_GROUP_ID,
316 B.SEQUENCE,
317 B.METRIC_GROUP_DISPLAY_NAME,
318 B.DESCRIPTION,
319 B.CREATED_BY,
320 B.CREATION_DATE,
321 B.LAST_UPDATED_BY,
322 B.LAST_UPDATE_DATE,
323 B.LAST_UPDATE_LOGIN,
324 L.LANGUAGE_CODE,
325 B.SOURCE_LANG
326 from FND_OAM_MET_GRPS_TL B, FND_LANGUAGES L
327 where L.INSTALLED_FLAG in ('I', 'B')
328 and B.LANGUAGE = userenv('LANG')
329 and not exists
330 (select NULL
331 from FND_OAM_MET_GRPS_TL T
332 where T.METRIC_GROUP_ID = B.METRIC_GROUP_ID
333 and T.LANGUAGE = L.LANGUAGE_CODE);
334 end ADD_LANGUAGE;
335
336 END fnd_oam_met_grps_pkg;