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