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