[Home] [Help]
PACKAGE BODY: APPS.AR_BPA_DATASRC_APPS_PKG
Source
1 package body AR_BPA_DATASRC_APPS_PKG as
2 /* $Header: ARBPDSAB.pls 120.3 2005/12/01 20:16:24 lishao noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_APPLICATION_ID in NUMBER,
6 X_INTERFACE_CONTEXT in VARCHAR2,
7 X_ENABLED_FLAG in VARCHAR2,
8 X_SEEDED_FLAG in VARCHAR2,
9 X_DATASRC_APP_NAME in VARCHAR2,
10 X_DATASRC_APP_DESC in VARCHAR2,
11 X_PRIMARY_FLAG 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 AR_BPA_DATASRC_APPS_B
19 where APPLICATION_ID = X_APPLICATION_ID
20 ;
21 begin
22 insert into AR_BPA_DATASRC_APPS_B (
23 APPLICATION_ID,
24 INTERFACE_CONTEXT,
25 ENABLED_FLAG,
26 SEEDED_FLAG,
27 PRIMARY_FLAG,
28 CREATION_DATE,
29 CREATED_BY,
30 LAST_UPDATE_DATE,
31 LAST_UPDATED_BY,
32 LAST_UPDATE_LOGIN
33 ) values (
34 X_APPLICATION_ID,
35 X_INTERFACE_CONTEXT,
36 X_ENABLED_FLAG,
37 X_SEEDED_FLAG,
38 X_PRIMARY_FLAG,
39 X_CREATION_DATE,
40 X_CREATED_BY,
41 X_LAST_UPDATE_DATE,
42 X_LAST_UPDATED_BY,
43 X_LAST_UPDATE_LOGIN
44 );
45
46 insert into AR_BPA_DATASRC_APPS_TL (
47 APPLICATION_ID,
48 DATASRC_APP_NAME,
49 DATASRC_APP_DESC,
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN,
55 LANGUAGE,
56 SOURCE_LANG
57 ) select
58 X_APPLICATION_ID,
59 X_DATASRC_APP_NAME,
60 X_DATASRC_APP_DESC,
61 X_CREATED_BY,
62 X_CREATION_DATE,
63 X_LAST_UPDATED_BY,
64 X_LAST_UPDATE_DATE,
65 X_LAST_UPDATE_LOGIN,
66 L.LANGUAGE_CODE,
67 userenv('LANG')
68 from FND_LANGUAGES L
69 where L.INSTALLED_FLAG in ('I', 'B')
70 and not exists
71 (select NULL
72 from AR_BPA_DATASRC_APPS_TL T
73 where T.APPLICATION_ID = X_APPLICATION_ID
74 and T.LANGUAGE = L.LANGUAGE_CODE);
75
76 open c;
77 fetch c into X_ROWID;
78 if (c%notfound) then
79 close c;
80 raise no_data_found;
81 end if;
82 close c;
83
84 end INSERT_ROW;
85
86 procedure LOCK_ROW (
87 X_APPLICATION_ID in NUMBER,
88 X_INTERFACE_CONTEXT in VARCHAR2,
89 X_ENABLED_FLAG in VARCHAR2,
90 X_SEEDED_FLAG in VARCHAR2,
91 X_DATASRC_APP_NAME in VARCHAR2,
92 X_DATASRC_APP_DESC in VARCHAR2,
93 X_PRIMARY_FLAG in VARCHAR2
94 ) is
95 cursor c is select
96 INTERFACE_CONTEXT,
97 ENABLED_FLAG,
98 SEEDED_FLAG,
99 PRIMARY_FLAG
100 from AR_BPA_DATASRC_APPS_B
101 where APPLICATION_ID = X_APPLICATION_ID
102 for update of APPLICATION_ID nowait;
103 recinfo c%rowtype;
104
105 cursor c1 is select
106 DATASRC_APP_NAME,
107 DATASRC_APP_DESC,
108 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
109 from AR_BPA_DATASRC_APPS_TL
110 where APPLICATION_ID = X_APPLICATION_ID
111 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
112 for update of APPLICATION_ID nowait;
113 begin
114 open c;
115 fetch c into recinfo;
116 if (c%notfound) then
117 close c;
118 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
119 app_exception.raise_exception;
120 end if;
121 close c;
122 if ( ((recinfo.INTERFACE_CONTEXT = X_INTERFACE_CONTEXT)
123 OR ((recinfo.INTERFACE_CONTEXT is null) AND (X_INTERFACE_CONTEXT is null)))
124 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
125 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
126 AND ((recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
127 OR ((recinfo.SEEDED_FLAG is null) AND (X_SEEDED_FLAG is null)))
128 AND ((recinfo.PRIMARY_FLAG = X_PRIMARY_FLAG)
129 OR ((recinfo.PRIMARY_FLAG is null) AND (X_PRIMARY_FLAG is null)))
130 ) then
131 null;
132 else
133 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
134 app_exception.raise_exception;
135 end if;
136
137 for tlinfo in c1 loop
138 if (tlinfo.BASELANG = 'Y') then
139 if ( (tlinfo.DATASRC_APP_NAME = X_DATASRC_APP_NAME)
140 AND ((tlinfo.DATASRC_APP_DESC = X_DATASRC_APP_DESC)
141 OR ((tlinfo.DATASRC_APP_DESC is null) AND (X_DATASRC_APP_DESC is null)))
142 ) then
143 null;
144 else
145 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
146 app_exception.raise_exception;
147 end if;
148 end if;
149 end loop;
150 return;
151 end LOCK_ROW;
152
153 procedure UPDATE_ROW (
154 X_APPLICATION_ID in NUMBER,
155 X_INTERFACE_CONTEXT in VARCHAR2,
156 X_ENABLED_FLAG in VARCHAR2,
157 X_SEEDED_FLAG in VARCHAR2,
158 X_DATASRC_APP_NAME in VARCHAR2,
159 X_DATASRC_APP_DESC in VARCHAR2,
160 X_PRIMARY_FLAG in VARCHAR2,
161 X_LAST_UPDATE_DATE in DATE,
162 X_LAST_UPDATED_BY in NUMBER,
163 X_LAST_UPDATE_LOGIN in NUMBER
164 ) is
165 begin
166 update AR_BPA_DATASRC_APPS_B set
167 INTERFACE_CONTEXT = X_INTERFACE_CONTEXT,
168 ENABLED_FLAG = X_ENABLED_FLAG,
169 SEEDED_FLAG = X_SEEDED_FLAG,
170 PRIMARY_FLAG = X_PRIMARY_FLAG,
171 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
172 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
173 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
174 where APPLICATION_ID = X_APPLICATION_ID;
175
176 if (sql%notfound) then
177 raise no_data_found;
178 end if;
179
180 update AR_BPA_DATASRC_APPS_TL set
181 DATASRC_APP_NAME = X_DATASRC_APP_NAME,
182 DATASRC_APP_DESC = X_DATASRC_APP_DESC,
183 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
184 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
185 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
186 SOURCE_LANG = userenv('LANG')
187 where APPLICATION_ID = X_APPLICATION_ID
188 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
189
190 if (sql%notfound) then
191 raise no_data_found;
192 end if;
193 end UPDATE_ROW;
194
195 procedure DELETE_ROW (
196 X_APPLICATION_ID in NUMBER
197 ) is
198 begin
199 /* First delete interface attribute from the Item table. */
200 delete from AR_BPA_ITEMS_TL
201 where item_id in (select item_id from ar_bpa_items_b
202 where SEEDED_APPLICATION_ID = X_APPLICATION_ID);
203
204 delete from AR_BPA_ITEMS_B
205 where SEEDED_APPLICATION_ID = X_APPLICATION_ID;
206
207 delete from AR_BPA_DATASRC_APPS_TL
208 where APPLICATION_ID = X_APPLICATION_ID;
209
210 if (sql%notfound) then
211 raise no_data_found;
212 end if;
213
214 delete from AR_BPA_DATASRC_APPS_B
215 where APPLICATION_ID = X_APPLICATION_ID;
216
217 if (sql%notfound) then
218 raise no_data_found;
219 end if;
220 end DELETE_ROW;
221
222 procedure ADD_LANGUAGE
223 is
224 begin
225 delete from AR_BPA_DATASRC_APPS_TL T
226 where not exists
227 (select NULL
228 from AR_BPA_DATASRC_APPS_B B
229 where B.APPLICATION_ID = T.APPLICATION_ID
230 );
231
232 update AR_BPA_DATASRC_APPS_TL T set (
233 DATASRC_APP_NAME,
234 DATASRC_APP_DESC
235 ) = (select
236 B.DATASRC_APP_NAME,
237 B.DATASRC_APP_DESC
238 from AR_BPA_DATASRC_APPS_TL B
239 where B.APPLICATION_ID = T.APPLICATION_ID
240 and B.LANGUAGE = T.SOURCE_LANG)
241 where (
242 T.APPLICATION_ID,
243 T.LANGUAGE
244 ) in (select
245 SUBT.APPLICATION_ID,
246 SUBT.LANGUAGE
247 from AR_BPA_DATASRC_APPS_TL SUBB, AR_BPA_DATASRC_APPS_TL SUBT
248 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
249 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
250 and (SUBB.DATASRC_APP_NAME <> SUBT.DATASRC_APP_NAME
251 or SUBB.DATASRC_APP_DESC <> SUBT.DATASRC_APP_DESC
252 or (SUBB.DATASRC_APP_DESC is null and SUBT.DATASRC_APP_DESC is not null)
253 or (SUBB.DATASRC_APP_DESC is not null and SUBT.DATASRC_APP_DESC is null)
254 ));
255
256 insert into AR_BPA_DATASRC_APPS_TL (
257 APPLICATION_ID,
258 DATASRC_APP_NAME,
262 LAST_UPDATED_BY,
259 DATASRC_APP_DESC,
260 CREATED_BY,
261 CREATION_DATE,
263 LAST_UPDATE_DATE,
264 LAST_UPDATE_LOGIN,
265 LANGUAGE,
266 SOURCE_LANG
267 ) select
268 B.APPLICATION_ID,
269 B.DATASRC_APP_NAME,
270 B.DATASRC_APP_DESC,
271 B.CREATED_BY,
272 B.CREATION_DATE,
273 B.LAST_UPDATED_BY,
274 B.LAST_UPDATE_DATE,
275 B.LAST_UPDATE_LOGIN,
276 L.LANGUAGE_CODE,
277 B.SOURCE_LANG
278 from AR_BPA_DATASRC_APPS_TL B, FND_LANGUAGES L
279 where L.INSTALLED_FLAG in ('I', 'B')
280 and B.LANGUAGE = userenv('LANG')
281 and not exists
282 (select NULL
283 from AR_BPA_DATASRC_APPS_TL T
284 where T.APPLICATION_ID = B.APPLICATION_ID
285 and T.LANGUAGE = L.LANGUAGE_CODE);
286 end ADD_LANGUAGE;
287
288 procedure TRANSLATE_ROW (
289 X_APPLICATION_ID in NUMBER,
290 X_DATASRC_APP_NAME in VARCHAR2,
291 X_DATASRC_APP_DESC in VARCHAR2,
292 X_OWNER in VARCHAR2) IS
293 begin
294
295 update AR_BPA_DATASRC_APPS_TL
296 set DATASRC_APP_NAME = X_DATASRC_APP_NAME,
297 DATASRC_APP_DESC = X_DATASRC_APP_DESC,
298 source_lang = userenv('LANG'),
299 last_update_date = sysdate,
300 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
301 last_update_login = 0
302 where APPLICATION_ID = X_APPLICATION_ID
303 and userenv('LANG') in (language, source_lang);
304
305 end TRANSLATE_ROW;
306
307 procedure LOAD_ROW (
308 X_APPLICATION_ID in NUMBER,
309 X_INTERFACE_CONTEXT in VARCHAR2,
310 X_ENABLED_FLAG in VARCHAR2,
311 X_SEEDED_FLAG in VARCHAR2,
312 X_DATASRC_APP_NAME in VARCHAR2,
313 X_DATASRC_APP_DESC in VARCHAR2,
314 X_PRIMARY_FLAG in VARCHAR2,
315 X_OWNER IN VARCHAR2
316 ) IS
317 begin
318 declare
319 user_id number := 0;
320 row_id varchar2(64);
321 begin
322 if (X_OWNER = 'SEED') then
323 user_id := 1;
324 end if;
325
326 AR_BPA_DATASRC_APPS_PKG.UPDATE_ROW (
327 X_APPLICATION_ID => X_APPLICATION_ID,
328 X_INTERFACE_CONTEXT => X_INTERFACE_CONTEXT,
329 X_ENABLED_FLAG => X_ENABLED_FLAG,
330 X_SEEDED_FLAG => X_SEEDED_FLAG,
331 X_DATASRC_APP_NAME => X_DATASRC_APP_NAME,
332 X_DATASRC_APP_DESC => X_DATASRC_APP_DESC ,
333 X_PRIMARY_FLAG => X_PRIMARY_FLAG,
334 X_LAST_UPDATE_DATE => sysdate,
335 X_LAST_UPDATED_BY => user_id,
336 X_LAST_UPDATE_LOGIN => 0);
337 exception
338 when NO_DATA_FOUND then
339 AR_BPA_DATASRC_APPS_PKG.INSERT_ROW (
340 X_ROWID => row_id,
341 X_APPLICATION_ID => X_APPLICATION_ID,
342 X_INTERFACE_CONTEXT => X_INTERFACE_CONTEXT,
343 X_ENABLED_FLAG => X_ENABLED_FLAG,
344 X_SEEDED_FLAG => X_SEEDED_FLAG,
345 X_DATASRC_APP_NAME => X_DATASRC_APP_NAME,
346 X_DATASRC_APP_DESC => X_DATASRC_APP_DESC ,
347 X_PRIMARY_FLAG => X_PRIMARY_FLAG,
348 X_CREATION_DATE => sysdate,
349 X_CREATED_BY => user_id,
350 X_LAST_UPDATE_DATE => sysdate,
351 X_LAST_UPDATED_BY => user_id,
352 X_LAST_UPDATE_LOGIN => 0);
353 end;
354 end LOAD_ROW;
355
356 end AR_BPA_DATASRC_APPS_PKG;