[Home] [Help]
PACKAGE BODY: APPS.BNE_DUPLICATE_PROFILES_PKG
Source
1 package body BNE_DUPLICATE_PROFILES_PKG as
2 /* $Header: bnedupprofb.pls 120.2 2005/06/29 03:39:53 dvayro noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_APPLICATION_ID in NUMBER,
7 X_DUP_PROFILE_CODE in VARCHAR2,
8 X_OBJECT_VERSION_NUMBER in NUMBER,
9 X_INTEGRATOR_APP_ID in NUMBER,
10 X_INTEGRATOR_CODE in VARCHAR2,
11 X_USER_NAME in VARCHAR2,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from BNE_DUPLICATE_PROFILES_B
19 where APPLICATION_ID = X_APPLICATION_ID
20 and DUP_PROFILE_CODE = X_DUP_PROFILE_CODE
21 ;
22 begin
23 insert into BNE_DUPLICATE_PROFILES_B (
24 APPLICATION_ID,
25 DUP_PROFILE_CODE,
26 OBJECT_VERSION_NUMBER,
27 INTEGRATOR_APP_ID,
28 INTEGRATOR_CODE,
29 CREATION_DATE,
30 CREATED_BY,
31 LAST_UPDATE_DATE,
32 LAST_UPDATED_BY,
33 LAST_UPDATE_LOGIN
34 ) values (
35 X_APPLICATION_ID,
36 X_DUP_PROFILE_CODE,
37 X_OBJECT_VERSION_NUMBER,
38 X_INTEGRATOR_APP_ID,
39 X_INTEGRATOR_CODE,
40 X_CREATION_DATE,
41 X_CREATED_BY,
42 X_LAST_UPDATE_DATE,
43 X_LAST_UPDATED_BY,
44 X_LAST_UPDATE_LOGIN
45 );
46
47 insert into BNE_DUPLICATE_PROFILES_TL (
48 CREATED_BY,
49 CREATION_DATE,
50 LAST_UPDATED_BY,
51 LAST_UPDATE_LOGIN,
52 LAST_UPDATE_DATE,
53 APPLICATION_ID,
54 DUP_PROFILE_CODE,
55 USER_NAME,
56 LANGUAGE,
57 SOURCE_LANG
58 ) select
59 X_CREATED_BY,
60 X_CREATION_DATE,
61 X_LAST_UPDATED_BY,
62 X_LAST_UPDATE_LOGIN,
63 X_LAST_UPDATE_DATE,
64 X_APPLICATION_ID,
65 X_DUP_PROFILE_CODE,
66 X_USER_NAME,
67 L.LANGUAGE_CODE,
68 userenv('LANG')
69 from FND_LANGUAGES L
70 where L.INSTALLED_FLAG in ('I', 'B')
71 and not exists
72 (select NULL
73 from BNE_DUPLICATE_PROFILES_TL T
74 where T.APPLICATION_ID = X_APPLICATION_ID
75 and T.DUP_PROFILE_CODE = X_DUP_PROFILE_CODE
76 and T.LANGUAGE = L.LANGUAGE_CODE);
77
78 open c;
79 fetch c into X_ROWID;
80 if (c%notfound) then
81 close c;
82 raise no_data_found;
83 end if;
84 close c;
85
86 end INSERT_ROW;
87
88 procedure LOCK_ROW (
89 X_APPLICATION_ID in NUMBER,
90 X_DUP_PROFILE_CODE in VARCHAR2,
91 X_OBJECT_VERSION_NUMBER in NUMBER,
92 X_INTEGRATOR_APP_ID in NUMBER,
93 X_INTEGRATOR_CODE in VARCHAR2,
94 X_USER_NAME in VARCHAR2
95 ) is
96 cursor c is select
97 OBJECT_VERSION_NUMBER,
98 INTEGRATOR_APP_ID,
99 INTEGRATOR_CODE
100 from BNE_DUPLICATE_PROFILES_B
101 where APPLICATION_ID = X_APPLICATION_ID
102 and DUP_PROFILE_CODE = X_DUP_PROFILE_CODE
103 for update of APPLICATION_ID nowait;
104 recinfo c%rowtype;
105
106 cursor c1 is select
107 USER_NAME,
108 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
109 from BNE_DUPLICATE_PROFILES_TL
110 where APPLICATION_ID = X_APPLICATION_ID
111 and DUP_PROFILE_CODE = X_DUP_PROFILE_CODE
112 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
113 for update of APPLICATION_ID nowait;
114 begin
115 open c;
116 fetch c into recinfo;
117 if (c%notfound) then
118 close c;
119 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
120 app_exception.raise_exception;
121 end if;
122 close c;
123 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
124 AND (recinfo.INTEGRATOR_APP_ID = X_INTEGRATOR_APP_ID)
125 AND (recinfo.INTEGRATOR_CODE = X_INTEGRATOR_CODE)
126 ) then
127 null;
128 else
129 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
130 app_exception.raise_exception;
131 end if;
132
133 for tlinfo in c1 loop
134 if (tlinfo.BASELANG = 'Y') then
135 if ( (tlinfo.USER_NAME = X_USER_NAME)
136 ) then
137 null;
138 else
139 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
140 app_exception.raise_exception;
141 end if;
142 end if;
143 end loop;
144 return;
145 end LOCK_ROW;
146
147 procedure UPDATE_ROW (
148 X_APPLICATION_ID in NUMBER,
149 X_DUP_PROFILE_CODE in VARCHAR2,
150 X_OBJECT_VERSION_NUMBER in NUMBER,
151 X_INTEGRATOR_APP_ID in NUMBER,
152 X_INTEGRATOR_CODE in VARCHAR2,
153 X_USER_NAME in VARCHAR2,
154 X_LAST_UPDATE_DATE in DATE,
155 X_LAST_UPDATED_BY in NUMBER,
156 X_LAST_UPDATE_LOGIN in NUMBER
157 ) is
158 begin
159 update BNE_DUPLICATE_PROFILES_B set
160 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
161 INTEGRATOR_APP_ID = X_INTEGRATOR_APP_ID,
162 INTEGRATOR_CODE = X_INTEGRATOR_CODE,
163 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
164 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
165 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
166 where APPLICATION_ID = X_APPLICATION_ID
167 and DUP_PROFILE_CODE = X_DUP_PROFILE_CODE;
168
169 if (sql%notfound) then
170 raise no_data_found;
171 end if;
172
173 update BNE_DUPLICATE_PROFILES_TL set
174 USER_NAME = X_USER_NAME,
175 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
176 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
177 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
178 SOURCE_LANG = userenv('LANG')
179 where APPLICATION_ID = X_APPLICATION_ID
180 and DUP_PROFILE_CODE = X_DUP_PROFILE_CODE
181 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
182
183 if (sql%notfound) then
184 raise no_data_found;
185 end if;
186 end UPDATE_ROW;
187
188 procedure DELETE_ROW (
189 X_APPLICATION_ID in NUMBER,
190 X_DUP_PROFILE_CODE in VARCHAR2
191 ) is
192 begin
193 delete from BNE_DUPLICATE_PROFILES_TL
194 where APPLICATION_ID = X_APPLICATION_ID
195 and DUP_PROFILE_CODE = X_DUP_PROFILE_CODE;
196
197 if (sql%notfound) then
198 raise no_data_found;
199 end if;
200
201 delete from BNE_DUPLICATE_PROFILES_B
202 where APPLICATION_ID = X_APPLICATION_ID
203 and DUP_PROFILE_CODE = X_DUP_PROFILE_CODE;
204
205 if (sql%notfound) then
206 raise no_data_found;
207 end if;
208 end DELETE_ROW;
209
210 procedure ADD_LANGUAGE
211 is
212 begin
213 delete from BNE_DUPLICATE_PROFILES_TL T
214 where not exists
215 (select NULL
216 from BNE_DUPLICATE_PROFILES_B B
217 where B.APPLICATION_ID = T.APPLICATION_ID
218 and B.DUP_PROFILE_CODE = T.DUP_PROFILE_CODE
219 );
220
221 update BNE_DUPLICATE_PROFILES_TL T set (
222 USER_NAME
223 ) = (select
224 B.USER_NAME
225 from BNE_DUPLICATE_PROFILES_TL B
226 where B.APPLICATION_ID = T.APPLICATION_ID
227 and B.DUP_PROFILE_CODE = T.DUP_PROFILE_CODE
228 and B.LANGUAGE = T.SOURCE_LANG)
229 where (
230 T.APPLICATION_ID,
231 T.DUP_PROFILE_CODE,
232 T.LANGUAGE
233 ) in (select
234 SUBT.APPLICATION_ID,
235 SUBT.DUP_PROFILE_CODE,
236 SUBT.LANGUAGE
237 from BNE_DUPLICATE_PROFILES_TL SUBB, BNE_DUPLICATE_PROFILES_TL SUBT
238 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
239 and SUBB.DUP_PROFILE_CODE = SUBT.DUP_PROFILE_CODE
240 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
241 and (SUBB.USER_NAME <> SUBT.USER_NAME
242 ));
243
244 insert into BNE_DUPLICATE_PROFILES_TL (
245 CREATED_BY,
246 CREATION_DATE,
247 LAST_UPDATED_BY,
248 LAST_UPDATE_LOGIN,
249 LAST_UPDATE_DATE,
250 APPLICATION_ID,
251 DUP_PROFILE_CODE,
252 USER_NAME,
253 LANGUAGE,
254 SOURCE_LANG
255 ) select /*+ ORDERED */
256 B.CREATED_BY,
257 B.CREATION_DATE,
258 B.LAST_UPDATED_BY,
259 B.LAST_UPDATE_LOGIN,
260 B.LAST_UPDATE_DATE,
261 B.APPLICATION_ID,
262 B.DUP_PROFILE_CODE,
263 B.USER_NAME,
264 L.LANGUAGE_CODE,
265 B.SOURCE_LANG
266 from BNE_DUPLICATE_PROFILES_TL B, FND_LANGUAGES L
267 where L.INSTALLED_FLAG in ('I', 'B')
268 and B.LANGUAGE = userenv('LANG')
269 and not exists
270 (select NULL
271 from BNE_DUPLICATE_PROFILES_TL T
272 where T.APPLICATION_ID = B.APPLICATION_ID
273 and T.DUP_PROFILE_CODE = B.DUP_PROFILE_CODE
274 and T.LANGUAGE = L.LANGUAGE_CODE);
275 end ADD_LANGUAGE;
276
277
278 --------------------------------------------------------------------------------
279 -- PROCEDURE: TRANSLATE_ROW --
280 -- --
281 -- DESCRIPTION: Load a translation into the BNE_DUPLICATE_PROFILES entity. --
282 -- This proc is called from the apps loader. --
283 -- --
284 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
285 -- --
286 -- --
287 -- MODIFICATION HISTORY --
288 -- Date Username Description --
289 -- 27-May-2004 DGROVES CREATED --
290 --------------------------------------------------------------------------------
291 procedure TRANSLATE_ROW(
292 x_dup_profile_asn in VARCHAR2,
293 x_dup_profile_code in VARCHAR2,
294 x_user_name in VARCHAR2,
295 x_owner in VARCHAR2,
296 x_last_update_date in VARCHAR2,
297 x_custom_mode in VARCHAR2
298 )is
299 l_app_id number;
300 f_luby number; -- entity owner in file
301 f_ludate date; -- entity update date in file
302 db_luby number; -- entity owner in db
303 db_ludate date; -- entity update date in db
304 begin
305 -- translate values to IDs
306 l_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_dup_profile_asn);
307
308 -- Translate owner to file_last_updated_by
309 f_luby := fnd_load_util.owner_id(x_owner);
310
311 -- Translate char last_update_date to date
312 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
313 begin
314 select LAST_UPDATED_BY, LAST_UPDATE_DATE
315 into db_luby, db_ludate
316 from BNE_DUPLICATE_PROFILES_TL
317 where APPLICATION_ID = l_app_id
318 and DUP_PROFILE_CODE = x_dup_profile_code
319 and LANGUAGE = userenv('LANG');
320
321 -- Test for customization and version
322 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
323 db_ludate, x_custom_mode)) then
324
325 update BNE_DUPLICATE_PROFILES_TL
326 set USER_NAME = x_user_name,
327 LAST_UPDATE_DATE = f_ludate,
328 LAST_UPDATED_BY = f_luby,
329 LAST_UPDATE_LOGIN = 0,
330 SOURCE_LANG = userenv('LANG')
331 where APPLICATION_ID = l_app_id
332 AND DUP_PROFILE_CODE = x_dup_profile_code
333 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG)
334 ;
335 end if;
336 exception
337 when no_data_found then
338 -- Do not insert missing translations, skip this row
339 null;
340 end;
341 end TRANSLATE_ROW;
342
343 --------------------------------------------------------------------------------
344 -- PROCEDURE: LOAD_ROW --
345 -- --
346 -- DESCRIPTION: Load a row into the BNE_DUPLICATE_PROFILES entity. --
347 -- This proc is called from the apps loader. --
348 -- --
349 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
350 -- --
351 -- --
352 -- MODIFICATION HISTORY --
353 -- Date Username Description --
354 -- 27-May-2004 DGROVES CREATED --
355 --------------------------------------------------------------------------------
356 procedure LOAD_ROW(
357 x_dup_profile_asn in VARCHAR2,
358 x_dup_profile_code in VARCHAR2,
359 x_object_version_number in VARCHAR2,
360 x_integrator_asn in VARCHAR2,
361 x_integrator_code in VARCHAR2,
362 x_user_name in VARCHAR2,
363 x_owner in VARCHAR2,
364 x_last_update_date in VARCHAR2,
365 x_custom_mode in VARCHAR2
366 ) is
367 l_app_id number;
368 l_integrator_app_id number;
369 l_row_id varchar2(64);
370 f_luby number; -- entity owner in file
371 f_ludate date; -- entity update date in file
372 db_luby number; -- entity owner in db
373 db_ludate date; -- entity update date in db
374 begin
375 -- translate values to IDs
376 l_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_dup_profile_asn);
377 l_integrator_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_integrator_asn);
378
379 -- Translate owner to file_last_updated_by
380 f_luby := fnd_load_util.owner_id(x_owner);
381
382 -- Translate char last_update_date to date
383 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
384 begin
385 select LAST_UPDATED_BY, LAST_UPDATE_DATE
386 into db_luby, db_ludate
387 from BNE_DUPLICATE_PROFILES_B
388 where APPLICATION_ID = l_app_id
389 and DUP_PROFILE_CODE = x_dup_profile_code;
390
391 -- Test for customization and version
392 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
393 db_ludate, x_custom_mode)) then
394 -- Update existing row
395 BNE_DUPLICATE_PROFILES_PKG.Update_Row(
396 X_APPLICATION_ID => l_app_id,
397 X_DUP_PROFILE_CODE => x_dup_profile_code,
398 X_OBJECT_VERSION_NUMBER => x_object_version_number,
399 X_INTEGRATOR_APP_ID => l_integrator_app_id,
400 X_INTEGRATOR_CODE => x_integrator_code,
401 X_USER_NAME => x_user_name,
402 X_LAST_UPDATE_DATE => f_ludate,
403 X_LAST_UPDATED_BY => f_luby,
404 X_LAST_UPDATE_LOGIN => 0
405 );
406 end if;
407 exception
408 when no_data_found then
409 -- Record doesn't exist - insert in all cases
410 BNE_DUPLICATE_PROFILES_PKG.Insert_Row(
411 X_ROWID => l_row_id,
412 X_APPLICATION_ID => l_app_id,
413 X_DUP_PROFILE_CODE => x_dup_profile_code,
414 X_OBJECT_VERSION_NUMBER => x_object_version_number,
415 X_INTEGRATOR_APP_ID => l_integrator_app_id,
416 X_INTEGRATOR_CODE => x_integrator_code,
417 X_USER_NAME => x_user_name,
418 X_CREATION_DATE => f_ludate,
419 X_CREATED_BY => f_luby,
420 X_LAST_UPDATE_DATE => f_ludate,
421 X_LAST_UPDATED_BY => f_luby,
422 X_LAST_UPDATE_LOGIN => 0
423 );
424 end;
425 end LOAD_ROW;
426
427 end BNE_DUPLICATE_PROFILES_PKG;