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