[Home] [Help]
PACKAGE BODY: APPS.JTF_DPF_PHYSICAL_PAGES_PKG
Source
1 package body JTF_DPF_PHYSICAL_PAGES_PKG as
2 /* $Header: jtfdpfpb.pls 120.2 2005/10/25 05:17:48 psanyal ship $ */
3 -- all pages which match the name and appid, and which are seed data
4 cursor find_match_with_owner (p_page_name varchar2, p_application_id number,
5 x_last_updated_by number) is
6 select physical_page_id
7 from jtf_dpf_physical_pages_b
8 where physical_page_name = p_page_name and
9 application_id = p_application_id and
10 last_updated_by = x_last_updated_by
11 order by last_update_date;
12
13 -- same query, without the last_updated_by test
14 cursor find_match(p_page_name varchar2, p_application_id number) is
15 select physical_page_id
16 from jtf_dpf_physical_pages_b
17 where physical_page_name = p_page_name and
18 application_id = p_application_id
19 order by last_update_date;
20
21 procedure INSERT_ROW (
22 X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
23 X_PHYSICAL_PAGE_ID in NUMBER,
24 X_PHYSICAL_PAGE_NAME in VARCHAR2,
25 X_APPLICATION_ID in NUMBER,
26 X_OBJECT_VERSION_NUMBER in NUMBER,
27 X_PHYSICAL_PAGE_DESCRIPTION in VARCHAR2,
28 X_CREATION_DATE in DATE,
29 X_CREATED_BY in NUMBER,
30 X_LAST_UPDATE_DATE in DATE,
31 X_LAST_UPDATED_BY in NUMBER,
32 X_LAST_UPDATE_LOGIN in NUMBER
33 ) is
34 cursor C is select ROWID from JTF_DPF_PHYSICAL_PAGES_B
35 where PHYSICAL_PAGE_ID = X_PHYSICAL_PAGE_ID
36 ;
37 begin
38 insert into JTF_DPF_PHYSICAL_PAGES_B (
39 PHYSICAL_PAGE_ID,
40 PHYSICAL_PAGE_NAME,
41 APPLICATION_ID,
42 OBJECT_VERSION_NUMBER,
43 CREATION_DATE,
44 CREATED_BY,
45 LAST_UPDATE_DATE,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_LOGIN
48 ) values (
49 X_PHYSICAL_PAGE_ID,
50 X_PHYSICAL_PAGE_NAME,
51 X_APPLICATION_ID,
52 X_OBJECT_VERSION_NUMBER,
53 X_CREATION_DATE,
54 X_CREATED_BY,
55 X_LAST_UPDATE_DATE,
56 X_LAST_UPDATED_BY,
57 X_LAST_UPDATE_LOGIN
58 );
59
60 insert into JTF_DPF_PHYSICAL_PAGES_TL (
61 PHYSICAL_PAGE_ID,
62 PHYSICAL_PAGE_DESCRIPTION,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_LOGIN,
67 LANGUAGE,
68 SOURCE_LANG
69 ) select
70 X_PHYSICAL_PAGE_ID,
71 X_PHYSICAL_PAGE_DESCRIPTION,
72 X_CREATED_BY,
73 X_LAST_UPDATE_DATE,
74 X_LAST_UPDATED_BY,
75 X_LAST_UPDATE_LOGIN,
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 JTF_DPF_PHYSICAL_PAGES_TL T
83 where T.PHYSICAL_PAGE_ID = X_PHYSICAL_PAGE_ID
84 and T.LANGUAGE = L.LANGUAGE_CODE);
85
86 open c;
87 fetch c into X_ROWID;
88 if (c%notfound) then
89 close c;
90 raise no_data_found;
91 end if;
92 close c;
93
94 end INSERT_ROW;
95
96 procedure LOCK_ROW (
97 X_PHYSICAL_PAGE_ID in NUMBER,
98 X_PHYSICAL_PAGE_NAME in VARCHAR2,
99 X_APPLICATION_ID in NUMBER,
100 X_OBJECT_VERSION_NUMBER in NUMBER,
101 X_PHYSICAL_PAGE_DESCRIPTION in VARCHAR2
102 ) is
103 cursor c is select
104 PHYSICAL_PAGE_NAME,
105 APPLICATION_ID,
106 OBJECT_VERSION_NUMBER
107 from JTF_DPF_PHYSICAL_PAGES_B
108 where PHYSICAL_PAGE_ID = X_PHYSICAL_PAGE_ID
109 for update of PHYSICAL_PAGE_ID nowait;
110 recinfo c%rowtype;
111
112 cursor c1 is select
113 PHYSICAL_PAGE_DESCRIPTION,
114 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
115 from JTF_DPF_PHYSICAL_PAGES_TL
116 where PHYSICAL_PAGE_ID = X_PHYSICAL_PAGE_ID
117 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
118 for update of PHYSICAL_PAGE_ID nowait;
119 begin
120 open c;
121 fetch c into recinfo;
122 if (c%notfound) then
123 close c;
124 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
125 app_exception.raise_exception;
126 end if;
127 close c;
128 if ( (recinfo.PHYSICAL_PAGE_NAME = X_PHYSICAL_PAGE_NAME)
129 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
130 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
131 ) then
132 null;
133 else
134 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
135 app_exception.raise_exception;
136 end if;
137
138 for tlinfo in c1 loop
139 if (tlinfo.BASELANG = 'Y') then
140 if ( ((tlinfo.PHYSICAL_PAGE_DESCRIPTION = X_PHYSICAL_PAGE_DESCRIPTION)
141 OR ((tlinfo.PHYSICAL_PAGE_DESCRIPTION is null) AND (X_PHYSICAL_PAGE_DESCRIPTION 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_PHYSICAL_PAGE_ID in NUMBER,
155 X_PHYSICAL_PAGE_NAME in VARCHAR2,
156 X_APPLICATION_ID in NUMBER,
157 X_OBJECT_VERSION_NUMBER in NUMBER,
158 X_PHYSICAL_PAGE_DESCRIPTION in VARCHAR2,
159 X_LAST_UPDATE_DATE in DATE,
160 X_LAST_UPDATED_BY in NUMBER,
161 X_LAST_UPDATE_LOGIN in NUMBER
162 ) is
163 begin
164 update JTF_DPF_PHYSICAL_PAGES_B set
165 PHYSICAL_PAGE_NAME = X_PHYSICAL_PAGE_NAME,
166 APPLICATION_ID = X_APPLICATION_ID,
167 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
168 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
169 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
170 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
171 where PHYSICAL_PAGE_ID = X_PHYSICAL_PAGE_ID;
172
173 if (sql%notfound) then
174 raise no_data_found;
175 end if;
176
177 update JTF_DPF_PHYSICAL_PAGES_TL set
178 PHYSICAL_PAGE_DESCRIPTION = X_PHYSICAL_PAGE_DESCRIPTION,
179 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
180 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
181 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
182 SOURCE_LANG = userenv('LANG')
183 where PHYSICAL_PAGE_ID = X_PHYSICAL_PAGE_ID
184 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
185
186 if (sql%notfound) then
187 raise no_data_found;
188 end if;
189 end UPDATE_ROW;
190
191 procedure DELETE_ROW (
192 X_PHYSICAL_PAGE_ID in NUMBER
193 ) is
194 begin
195 delete from JTF_DPF_PHYSICAL_PAGES_TL
196 where PHYSICAL_PAGE_ID = X_PHYSICAL_PAGE_ID;
197
198 if (sql%notfound) then
199 raise no_data_found;
200 end if;
201
202 delete from JTF_DPF_PHYSICAL_PAGES_B
203 where PHYSICAL_PAGE_ID = X_PHYSICAL_PAGE_ID;
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 JTF_DPF_PHYSICAL_PAGES_TL T
214 where not exists
215 (select NULL
216 from JTF_DPF_PHYSICAL_PAGES_B B
217 where B.PHYSICAL_PAGE_ID = T.PHYSICAL_PAGE_ID
218 );
219
220 update JTF_DPF_PHYSICAL_PAGES_TL T set (
221 PHYSICAL_PAGE_DESCRIPTION
222 ) = (select
223 B.PHYSICAL_PAGE_DESCRIPTION
224 from JTF_DPF_PHYSICAL_PAGES_TL B
225 where B.PHYSICAL_PAGE_ID = T.PHYSICAL_PAGE_ID
226 and B.LANGUAGE = T.SOURCE_LANG)
227 where (
228 T.PHYSICAL_PAGE_ID,
229 T.LANGUAGE
230 ) in (select
231 SUBT.PHYSICAL_PAGE_ID,
232 SUBT.LANGUAGE
233 from JTF_DPF_PHYSICAL_PAGES_TL SUBB, JTF_DPF_PHYSICAL_PAGES_TL SUBT
234 where SUBB.PHYSICAL_PAGE_ID = SUBT.PHYSICAL_PAGE_ID
235 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
236 and (SUBB.PHYSICAL_PAGE_DESCRIPTION <> SUBT.PHYSICAL_PAGE_DESCRIPTION
237 or (SUBB.PHYSICAL_PAGE_DESCRIPTION is null and SUBT.PHYSICAL_PAGE_DESCRIPTION is not null)
238 or (SUBB.PHYSICAL_PAGE_DESCRIPTION is not null and SUBT.PHYSICAL_PAGE_DESCRIPTION is null)
239 ));
240
241 insert into JTF_DPF_PHYSICAL_PAGES_TL (
242 PHYSICAL_PAGE_ID,
243 PHYSICAL_PAGE_DESCRIPTION,
244 CREATED_BY,
245 LAST_UPDATE_DATE,
246 LAST_UPDATED_BY,
247 LAST_UPDATE_LOGIN,
248 LANGUAGE,
249 SOURCE_LANG
250 ) select
251 B.PHYSICAL_PAGE_ID,
252 B.PHYSICAL_PAGE_DESCRIPTION,
253 B.CREATED_BY,
254 B.LAST_UPDATE_DATE,
255 B.LAST_UPDATED_BY,
256 B.LAST_UPDATE_LOGIN,
257 L.LANGUAGE_CODE,
258 B.SOURCE_LANG
259 from JTF_DPF_PHYSICAL_PAGES_TL B, FND_LANGUAGES L
260 where L.INSTALLED_FLAG in ('I', 'B')
261 and B.LANGUAGE = userenv('LANG')
262 and not exists
263 (select NULL
264 from JTF_DPF_PHYSICAL_PAGES_TL T
265 where T.PHYSICAL_PAGE_ID = B.PHYSICAL_PAGE_ID
266 and T.LANGUAGE = L.LANGUAGE_CODE);
267 end ADD_LANGUAGE;
268
269 function find_oldest_prefer_owned_by(
270 x_page_name in varchar2,
271 x_application_id in varchar2,
272 x_last_updated_by number) return number is
273 l_candidate number;
274 begin
275 l_candidate := null;
276 open find_match_with_owner(x_page_name, x_application_id,
277 x_last_updated_by);
278 fetch find_match_with_owner into l_candidate;
279 close find_match_with_owner;
280
281 if l_candidate is not null then return l_candidate; end if;
282
283 open find_match(x_page_name, x_application_id);
284 fetch find_match into l_candidate;
285 close find_match;
286 return l_candidate;
287 end;
288
289 procedure TRANSLATE_ROW (
290 X_PAGE_NAME IN VARCHAR2,
291 X_APPLICATION_ID IN VARCHAR2,
292 X_PAGE_DESCRIPTION IN VARCHAR2,
293 X_OWNER IN VARCHAR2
294 ) is
295 l_page_id number;
296 l_user_id number;
297 begin
298 l_user_id := 0;
299 if x_owner = 'SEED' then l_user_id := 1; end if;
300 l_page_id := find_oldest_prefer_owned_by(x_page_name, x_application_id,
301 l_user_id);
302
303 update jtf_dpf_physical_pages_tl set
304 physical_page_description = x_page_description,
305 last_updated_by = l_user_id,
306 last_update_date = sysdate,
307 last_update_login = 0,
308 source_lang = userenv('LANG')
309 where userenv('LANG') in (LANGUAGE, SOURCE_LANG) and
310 physical_page_id = l_page_id;
311 end;
312
313 procedure LOAD_ROW (
314 X_PAGE_NAME IN VARCHAR2,
315 X_APPLICATION_ID IN VARCHAR2,
316 X_PAGE_DESCRIPTION IN VARCHAR2,
317 X_OWNER IN VARCHAR2
318 ) is
319 l_page_id number;
320 l_new_phys_id number;
321 t_rowid rowid;
322 t_user number;
323 begin
324 t_user := 0;
325 if x_owner = 'SEED' then t_user := 1; end if;
326
327 -- if there's not already a physical with this name and appid which is
328 -- seed data...
329 l_page_id := null;
330 open find_match_with_owner(x_page_name, x_application_id, t_user);
331 fetch find_match_with_owner into l_page_id;
332 close find_match_with_owner;
333
334 if l_page_id is null then
335 -- cons up a new page_id, smaller than 10000
336 l_new_phys_id := null;
337 -- arsingh: prevent use of same id by different threads.
338 select JTF_DPF_PHYSICAL_PAGES_S.nextval into l_new_phys_id from dual;
339 -- select max(physical_page_id) into l_new_phys_id from
340 -- jtf_dpf_physical_pages_b where physical_page_id<10000;
341 -- if l_new_phys_id is null then
342 -- l_new_phys_id := 1;
343 -- else
344 -- l_new_phys_id := l_new_phys_id+1;
345 -- end if;
346
347 -- do an insert
348 insert_row(
349 X_ROWID => t_rowid,
350 X_PHYSICAL_PAGE_ID => l_new_phys_id,
351 X_PHYSICAL_PAGE_NAME => x_page_name,
352 X_APPLICATION_ID => x_application_id,
353 X_OBJECT_VERSION_NUMBER => 1,
354 X_PHYSICAL_PAGE_DESCRIPTION => x_page_description,
355 X_CREATION_DATE => sysdate,
356 X_CREATED_BY => t_user,
357 X_LAST_UPDATE_DATE => sysdate,
358 X_LAST_UPDATED_BY => t_user,
359 X_LAST_UPDATE_LOGIN => 0);
360 else
361 -- else do an update
362 update_row(
363 X_PHYSICAL_PAGE_ID => l_page_id,
364 X_PHYSICAL_PAGE_NAME => x_page_name,
365 X_APPLICATION_ID => x_application_id,
366 X_OBJECT_VERSION_NUMBER => 1,
367 X_PHYSICAL_PAGE_DESCRIPTION => x_page_description,
368 X_LAST_UPDATE_DATE => sysdate,
369 X_LAST_UPDATED_BY => t_user,
370 X_LAST_UPDATE_LOGIN => 0);
371 end if;
372 end;
373
374 procedure insert_phy_attributes(
375 X_PHYS_ID IN NUMBER,
376 x_PAGE_ATTRIBUTE_NAME IN VARCHAR2,
377 x_PAGE_ATTRIBUTE_VALUE IN VARCHAR2,
378 X_OWNER IN VARCHAR2
379 ) is
380 t_user number;
381 begin
382 t_user := 0;
383 if x_owner = 'SEED' then t_user := 1; end if;
384 insert into jtf_dpf_phy_attribs(
385 PHYSICAL_PAGE_ID,
386 PAGE_ATTRIBUTE_NAME,
387 PAGE_ATTRIBUTE_VALUE,
388 OBJECT_VERSION_NUMBER,
389 CREATED_BY,
390 CREATION_DATE,
391 LAST_UPDATE_DATE,
392 LAST_UPDATED_BY,
393 LAST_UPDATE_LOGIN)
394 values (
395 x_phys_id,
396 x_page_attribute_name,
397 x_page_attribute_value,
398 1,
399 t_user,
400 sysdate,
401 sysdate,
402 t_user,
403 0);
404 end;
405
406 procedure update_phy_attributes(
407 X_PHYS_ID IN NUMBER,
408 x_PAGE_ATTRIBUTE_NAME IN VARCHAR2,
409 x_PAGE_ATTRIBUTE_VALUE IN VARCHAR2,
410 X_OWNER IN VARCHAR2
411 ) is
412 t_user number;
413 begin
414 t_user := 0;
415 if x_owner = 'SEED' then t_user := 1; end if;
416 update jtf_dpf_phy_attribs set
417 PAGE_ATTRIBUTE_VALUE = x_page_attribute_value,
418 OBJECT_VERSION_NUMBER = object_version_number+1,
419 CREATED_BY = t_user,
420 CREATION_DATE = sysdate,
421 LAST_UPDATE_DATE = sysdate,
422 LAST_UPDATED_BY = t_user,
423 LAST_UPDATE_LOGIN = 0
424 where physical_page_id = x_phys_id and
425 page_attribute_name = x_page_attribute_name;
426 end;
427
428 end JTF_DPF_PHYSICAL_PAGES_PKG;