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