1 package body AK_FLOW_PAGES_PKG as
2 /* $Header: AKDFPAGB.pls 120.3 2006/01/25 15:57:01 tshort ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_FLOW_APPLICATION_ID in NUMBER,
6 X_FLOW_CODE in VARCHAR2,
7 X_PAGE_APPLICATION_ID in NUMBER,
8 X_PAGE_CODE in VARCHAR2,
9 X_PRIMARY_REGION_APPL_ID in NUMBER,
10 X_PRIMARY_REGION_CODE in VARCHAR2,
11 X_NAME in VARCHAR2,
12 X_DESCRIPTION in VARCHAR2,
13 X_CREATION_DATE in DATE,
14 X_CREATED_BY in NUMBER,
15 X_LAST_UPDATE_DATE in DATE,
16 X_LAST_UPDATED_BY in NUMBER,
17 X_LAST_UPDATE_LOGIN in NUMBER,
18 X_ATTRIBUTE_CATEGORY in VARCHAR2,
19 X_ATTRIBUTE1 in VARCHAR2,
20 X_ATTRIBUTE2 in VARCHAR2,
21 X_ATTRIBUTE3 in VARCHAR2,
22 X_ATTRIBUTE4 in VARCHAR2,
23 X_ATTRIBUTE5 in VARCHAR2,
24 X_ATTRIBUTE6 in VARCHAR2,
25 X_ATTRIBUTE7 in VARCHAR2,
26 X_ATTRIBUTE8 in VARCHAR2,
27 X_ATTRIBUTE9 in VARCHAR2,
28 X_ATTRIBUTE10 in VARCHAR2,
29 X_ATTRIBUTE11 in VARCHAR2,
30 X_ATTRIBUTE12 in VARCHAR2,
31 X_ATTRIBUTE13 in VARCHAR2,
32 X_ATTRIBUTE14 in VARCHAR2,
33 X_ATTRIBUTE15 in VARCHAR2
34 ) is
35 cursor C is select ROWID from AK_FLOW_PAGES
36 where FLOW_APPLICATION_ID = X_FLOW_APPLICATION_ID
37 and FLOW_CODE = X_FLOW_CODE
38 and PAGE_APPLICATION_ID = X_PAGE_APPLICATION_ID
39 and PAGE_CODE = X_PAGE_CODE;
40 begin
41 insert into AK_FLOW_PAGES (
42 FLOW_APPLICATION_ID,
43 FLOW_CODE,
44 PAGE_APPLICATION_ID,
45 PAGE_CODE,
46 PRIMARY_REGION_APPL_ID,
47 PRIMARY_REGION_CODE,
48 CREATION_DATE,
49 CREATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_LOGIN,
53 ATTRIBUTE_CATEGORY,
54 ATTRIBUTE1,
55 ATTRIBUTE2,
56 ATTRIBUTE3,
57 ATTRIBUTE4,
58 ATTRIBUTE5,
59 ATTRIBUTE6,
60 ATTRIBUTE7,
61 ATTRIBUTE8,
62 ATTRIBUTE9,
63 ATTRIBUTE10,
64 ATTRIBUTE11,
65 ATTRIBUTE12,
66 ATTRIBUTE13,
67 ATTRIBUTE14,
68 ATTRIBUTE15
69 ) values (
70 X_FLOW_APPLICATION_ID,
71 X_FLOW_CODE,
72 X_PAGE_APPLICATION_ID,
73 X_PAGE_CODE,
74 X_PRIMARY_REGION_APPL_ID,
75 X_PRIMARY_REGION_CODE,
76 X_CREATION_DATE,
77 X_CREATED_BY,
78 X_LAST_UPDATE_DATE,
79 X_LAST_UPDATED_BY,
80 X_LAST_UPDATE_LOGIN,
81 X_ATTRIBUTE_CATEGORY,
82 X_ATTRIBUTE1,
83 X_ATTRIBUTE2,
84 X_ATTRIBUTE3,
85 X_ATTRIBUTE4,
86 X_ATTRIBUTE5,
87 X_ATTRIBUTE6,
88 X_ATTRIBUTE7,
89 X_ATTRIBUTE8,
90 X_ATTRIBUTE9,
91 X_ATTRIBUTE10,
92 X_ATTRIBUTE11,
93 X_ATTRIBUTE12,
94 X_ATTRIBUTE13,
95 X_ATTRIBUTE14,
96 X_ATTRIBUTE15
97 );
98
99 open c;
100 fetch c into X_ROWID;
101 if (c%notfound) then
102 close c;
103 raise no_data_found;
104 end if;
105 close c;
106
107 insert into AK_FLOW_PAGES_TL (
108 FLOW_APPLICATION_ID,
109 FLOW_CODE,
110 PAGE_APPLICATION_ID,
111 PAGE_CODE,
112 LANGUAGE,
113 NAME,
114 DESCRIPTION,
115 SOURCE_LANG,
116 CREATED_BY,
117 CREATION_DATE,
118 LAST_UPDATED_BY,
119 LAST_UPDATE_DATE,
120 LAST_UPDATE_LOGIN
121 ) select
122 X_FLOW_APPLICATION_ID,
123 X_FLOW_CODE,
124 X_PAGE_APPLICATION_ID,
125 X_PAGE_CODE,
126 L.LANGUAGE_CODE,
127 X_NAME,
128 X_DESCRIPTION,
129 userenv('LANG'),
130 X_CREATED_BY,
131 X_CREATION_DATE,
132 X_LAST_UPDATED_BY,
133 X_LAST_UPDATE_DATE,
134 X_LAST_UPDATE_LOGIN
135 from FND_LANGUAGES L
136 where L.INSTALLED_FLAG in ('I', 'B')
137 and not exists
138 (select NULL
139 from AK_FLOW_PAGES_TL T
140 where T.FLOW_APPLICATION_ID = X_FLOW_APPLICATION_ID
141 and T.FLOW_CODE = X_FLOW_CODE
142 and T.PAGE_APPLICATION_ID = X_PAGE_APPLICATION_ID
143 and T.PAGE_CODE = X_PAGE_CODE
144 and T.LANGUAGE = L.LANGUAGE_CODE);
145 end INSERT_ROW;
146
147 procedure LOCK_ROW (
148 X_FLOW_APPLICATION_ID in NUMBER,
149 X_FLOW_CODE in VARCHAR2,
150 X_PAGE_APPLICATION_ID in NUMBER,
151 X_PAGE_CODE in VARCHAR2,
152 X_ATTRIBUTE4 in VARCHAR2,
153 X_ATTRIBUTE5 in VARCHAR2,
154 X_ATTRIBUTE6 in VARCHAR2,
155 X_ATTRIBUTE7 in VARCHAR2,
156 X_ATTRIBUTE8 in VARCHAR2,
157 X_ATTRIBUTE9 in VARCHAR2,
158 X_ATTRIBUTE10 in VARCHAR2,
159 X_ATTRIBUTE11 in VARCHAR2,
160 X_ATTRIBUTE12 in VARCHAR2,
161 X_ATTRIBUTE13 in VARCHAR2,
162 X_ATTRIBUTE14 in VARCHAR2,
163 X_ATTRIBUTE15 in VARCHAR2,
164 X_PRIMARY_REGION_APPL_ID in NUMBER,
165 X_PRIMARY_REGION_CODE in VARCHAR2,
166 X_ATTRIBUTE_CATEGORY in VARCHAR2,
167 X_ATTRIBUTE1 in VARCHAR2,
168 X_ATTRIBUTE2 in VARCHAR2,
169 X_ATTRIBUTE3 in VARCHAR2,
170 X_NAME in VARCHAR2,
171 X_DESCRIPTION in VARCHAR2
172 ) is
173 cursor c is select
174 ATTRIBUTE4,
175 ATTRIBUTE5,
176 ATTRIBUTE6,
177 ATTRIBUTE7,
178 ATTRIBUTE8,
179 ATTRIBUTE9,
180 ATTRIBUTE10,
181 ATTRIBUTE11,
182 ATTRIBUTE12,
183 ATTRIBUTE13,
184 ATTRIBUTE14,
185 ATTRIBUTE15,
186 PRIMARY_REGION_APPL_ID,
187 PRIMARY_REGION_CODE,
188 ATTRIBUTE_CATEGORY,
189 ATTRIBUTE1,
190 ATTRIBUTE2,
191 ATTRIBUTE3
192 from AK_FLOW_PAGES
193 where FLOW_APPLICATION_ID = X_FLOW_APPLICATION_ID
194 and FLOW_CODE = X_FLOW_CODE
195 and PAGE_APPLICATION_ID = X_PAGE_APPLICATION_ID
196 and PAGE_CODE = X_PAGE_CODE
197 for update of FLOW_CODE nowait;
198 recinfo c%rowtype;
199
200 cursor c1 is select
201 NAME,
202 DESCRIPTION
203 from AK_FLOW_PAGES_TL
204 where FLOW_APPLICATION_ID = X_FLOW_APPLICATION_ID
205 and FLOW_CODE = X_FLOW_CODE
206 and PAGE_APPLICATION_ID = X_PAGE_APPLICATION_ID
207 and PAGE_CODE = X_PAGE_CODE
208 and LANGUAGE = userenv('LANG')
209 for update of FLOW_CODE nowait;
210 tlinfo c1%rowtype;
211
212 begin
213 open c;
214 fetch c into recinfo;
215 if (c%notfound) then
216 close c;
217 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
218 app_exception.raise_exception;
219 end if;
220 close c;
221 if ( ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
222 OR ((recinfo.ATTRIBUTE4 is null)
223 AND (X_ATTRIBUTE4 is null)))
224 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
225 OR ((recinfo.ATTRIBUTE5 is null)
226 AND (X_ATTRIBUTE5 is null)))
227 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
228 OR ((recinfo.ATTRIBUTE6 is null)
229 AND (X_ATTRIBUTE6 is null)))
230 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
231 OR ((recinfo.ATTRIBUTE7 is null)
232 AND (X_ATTRIBUTE7 is null)))
233 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
234 OR ((recinfo.ATTRIBUTE8 is null)
235 AND (X_ATTRIBUTE8 is null)))
236 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
237 OR ((recinfo.ATTRIBUTE9 is null)
238 AND (X_ATTRIBUTE9 is null)))
239 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
240 OR ((recinfo.ATTRIBUTE10 is null)
241 AND (X_ATTRIBUTE10 is null)))
242 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
243 OR ((recinfo.ATTRIBUTE11 is null)
244 AND (X_ATTRIBUTE11 is null)))
245 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
246 OR ((recinfo.ATTRIBUTE12 is null)
247 AND (X_ATTRIBUTE12 is null)))
248 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
249 OR ((recinfo.ATTRIBUTE13 is null)
250 AND (X_ATTRIBUTE13 is null)))
251 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
252 OR ((recinfo.ATTRIBUTE14 is null)
253 AND (X_ATTRIBUTE14 is null)))
254 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
255 OR ((recinfo.ATTRIBUTE15 is null)
256 AND (X_ATTRIBUTE15 is null)))
257 AND (recinfo.PRIMARY_REGION_APPL_ID = X_PRIMARY_REGION_APPL_ID)
258 AND (recinfo.PRIMARY_REGION_CODE = X_PRIMARY_REGION_CODE)
259 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
260 OR ((recinfo.ATTRIBUTE_CATEGORY is null)
261 AND (X_ATTRIBUTE_CATEGORY is null)))
262 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
263 OR ((recinfo.ATTRIBUTE1 is null)
264 AND (X_ATTRIBUTE1 is null)))
265 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
266 OR ((recinfo.ATTRIBUTE2 is null)
267 AND (X_ATTRIBUTE2 is null)))
268 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
269 OR ((recinfo.ATTRIBUTE3 is null)
270 AND (X_ATTRIBUTE3 is null)))
271 ) then
272 null;
273 else
274 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
275 app_exception.raise_exception;
276 end if;
277
278 open c1;
279 fetch c1 into tlinfo;
280 if (c1%notfound) then
281 close c1;
282 return;
283 end if;
284 close c1;
285
286 if ( (tlinfo.NAME = X_NAME)
287 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
288 OR ((tlinfo.DESCRIPTION is null)
289 AND (X_DESCRIPTION is null)))
290 ) then
291 null;
292 else
293 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
294 app_exception.raise_exception;
295 end if;
296 return;
297 end LOCK_ROW;
298
299
300 procedure UPDATE_ROW (
301 X_FLOW_APPLICATION_ID in NUMBER,
302 X_FLOW_CODE in VARCHAR2,
303 X_PAGE_APPLICATION_ID in NUMBER,
304 X_PAGE_CODE in VARCHAR2,
305 X_PRIMARY_REGION_APPL_ID in NUMBER,
306 X_PRIMARY_REGION_CODE in VARCHAR2,
307 X_NAME in VARCHAR2,
308 X_DESCRIPTION in VARCHAR2,
309 X_LAST_UPDATE_DATE in DATE,
310 X_LAST_UPDATED_BY in NUMBER,
311 X_LAST_UPDATE_LOGIN in NUMBER,
312 X_ATTRIBUTE_CATEGORY in VARCHAR2,
313 X_ATTRIBUTE1 in VARCHAR2,
314 X_ATTRIBUTE2 in VARCHAR2,
315 X_ATTRIBUTE3 in VARCHAR2,
316 X_ATTRIBUTE4 in VARCHAR2,
317 X_ATTRIBUTE5 in VARCHAR2,
318 X_ATTRIBUTE6 in VARCHAR2,
319 X_ATTRIBUTE7 in VARCHAR2,
320 X_ATTRIBUTE8 in VARCHAR2,
321 X_ATTRIBUTE9 in VARCHAR2,
322 X_ATTRIBUTE10 in VARCHAR2,
323 X_ATTRIBUTE11 in VARCHAR2,
324 X_ATTRIBUTE12 in VARCHAR2,
325 X_ATTRIBUTE13 in VARCHAR2,
326 X_ATTRIBUTE14 in VARCHAR2,
327 X_ATTRIBUTE15 in VARCHAR2
328 ) is
329 begin
330 update AK_FLOW_PAGES set
331 FLOW_APPLICATION_ID = X_FLOW_APPLICATION_ID,
332 FLOW_CODE = X_FLOW_CODE,
333 PAGE_APPLICATION_ID = X_PAGE_APPLICATION_ID,
334 PAGE_CODE = X_PAGE_CODE,
335 PRIMARY_REGION_APPL_ID = X_PRIMARY_REGION_APPL_ID,
336 PRIMARY_REGION_CODE = X_PRIMARY_REGION_CODE,
337 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
338 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
339 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
340 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
341 ATTRIBUTE1 = X_ATTRIBUTE1,
342 ATTRIBUTE2 = X_ATTRIBUTE2,
343 ATTRIBUTE3 = X_ATTRIBUTE3,
344 ATTRIBUTE4 = X_ATTRIBUTE4,
345 ATTRIBUTE5 = X_ATTRIBUTE5,
346 ATTRIBUTE6 = X_ATTRIBUTE6,
347 ATTRIBUTE7 = X_ATTRIBUTE7,
348 ATTRIBUTE8 = X_ATTRIBUTE8,
349 ATTRIBUTE9 = X_ATTRIBUTE9,
350 ATTRIBUTE10 = X_ATTRIBUTE10,
351 ATTRIBUTE11 = X_ATTRIBUTE11,
352 ATTRIBUTE12 = X_ATTRIBUTE12,
353 ATTRIBUTE13 = X_ATTRIBUTE13,
354 ATTRIBUTE14 = X_ATTRIBUTE14,
355 ATTRIBUTE15 = X_ATTRIBUTE15
356 where FLOW_APPLICATION_ID = X_FLOW_APPLICATION_ID
357 and FLOW_CODE = X_FLOW_CODE
358 and PAGE_APPLICATION_ID = X_PAGE_APPLICATION_ID
359 and PAGE_CODE = X_PAGE_CODE;
360 if (sql%notfound) then
361 raise no_data_found;
362 end if;
363
364 update AK_FLOW_PAGES_TL set
365 NAME = X_NAME,
366 DESCRIPTION = X_DESCRIPTION,
367 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
368 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
369 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
370 SOURCE_LANG = userenv('LANG')
371 where FLOW_CODE = X_FLOW_CODE
372 and FLOW_APPLICATION_ID = X_FLOW_APPLICATION_ID
373 and PAGE_CODE = X_PAGE_CODE
374 and PAGE_APPLICATION_ID = X_PAGE_APPLICATION_ID
375 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
376 if (sql%notfound) then
377 raise no_data_found;
378 end if;
379 end UPDATE_ROW;
380
381 procedure DELETE_ROW (
382 X_FLOW_APPLICATION_ID in NUMBER,
383 X_FLOW_CODE in VARCHAR2,
384 X_PAGE_APPLICATION_ID in NUMBER,
385 X_PAGE_CODE in VARCHAR2
386 ) is
387 begin
388 delete from AK_FLOW_PAGES
389 where FLOW_APPLICATION_ID = X_FLOW_APPLICATION_ID
390 and FLOW_CODE = X_FLOW_CODE
391 and PAGE_APPLICATION_ID = X_PAGE_APPLICATION_ID
392 and PAGE_CODE = X_PAGE_CODE;
393
394 if (sql%notfound) then
395 raise no_data_found;
396 end if;
397
398 delete from AK_FLOW_PAGES_TL
399 where FLOW_APPLICATION_ID = X_FLOW_APPLICATION_ID
400 and FLOW_CODE = X_FLOW_CODE
401 and PAGE_APPLICATION_ID = X_PAGE_APPLICATION_ID
402 and PAGE_CODE = X_PAGE_CODE;
403 if (sql%notfound) then
404 raise no_data_found;
405 end if;
406 end DELETE_ROW;
407
408 procedure ADD_LANGUAGE
409 is
410 begin
411 /* Mar/19/03 requested by Ric Ginsberg */
412 /* The following delete and update statements are commented out */
413 /* as a quick workaround to fix the time-consuming table handler issue */
414 /* Eventually we'll need to turn them into a separate fix_language procedure */
415 /*
416
417 delete from AK_FLOW_PAGES_TL T
418 where not exists
419 (select NULL
420 from AK_FLOW_PAGES B
421 where B.FLOW_CODE = T.FLOW_CODE
422 and B.FLOW_APPLICATION_ID = T.FLOW_APPLICATION_ID
423 and B.PAGE_CODE = T.PAGE_CODE
424 and B.PAGE_APPLICATION_ID = T.PAGE_APPLICATION_ID
425 );
426
427 update AK_FLOW_PAGES_TL T set (
428 NAME,
429 DESCRIPTION
430 ) = (select
431 B.NAME,
432 B.DESCRIPTION
433 from AK_FLOW_PAGES_TL B
434 where B.FLOW_CODE = T.FLOW_CODE
435 and B.FLOW_APPLICATION_ID = T.FLOW_APPLICATION_ID
436 and B.PAGE_CODE = T.PAGE_CODE
437 and B.PAGE_APPLICATION_ID = T.PAGE_APPLICATION_ID
438 and B.LANGUAGE = T.SOURCE_LANG)
439 where (
440 T.FLOW_CODE,
441 T.FLOW_APPLICATION_ID,
442 T.PAGE_CODE,
443 T.PAGE_APPLICATION_ID,
444 T.LANGUAGE
445 ) in (select
446 SUBT.FLOW_CODE,
447 SUBT.FLOW_APPLICATION_ID,
448 SUBT.PAGE_CODE,
449 SUBT.PAGE_APPLICATION_ID,
450 SUBT.LANGUAGE
451 from AK_FLOW_PAGES_TL SUBB, AK_FLOW_PAGES_TL SUBT
452 where SUBB.FLOW_CODE = SUBT.FLOW_CODE
453 and SUBB.FLOW_APPLICATION_ID = SUBT.FLOW_APPLICATION_ID
454 and SUBB.PAGE_CODE = SUBT.PAGE_CODE
455 and SUBB.PAGE_APPLICATION_ID = SUBT.PAGE_APPLICATION_ID
456 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
457 and (SUBB.NAME <> SUBT.NAME
458 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
459 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
460 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
461 ));
462
463 */
464
465 insert /*+ append parallel(tt) */ into AK_FLOW_PAGES_TL tt (
466 FLOW_APPLICATION_ID,
467 FLOW_CODE,
468 PAGE_APPLICATION_ID,
469 PAGE_CODE,
470 NAME,
471 DESCRIPTION,
472 CREATED_BY,
473 CREATION_DATE,
474 LAST_UPDATED_BY,
475 LAST_UPDATE_DATE,
476 LAST_UPDATE_LOGIN,
477 LANGUAGE,
478 SOURCE_LANG
479 ) select /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
480 (select /*+ no_merge ordered parallel(b) */
481 B.FLOW_APPLICATION_ID,
482 B.FLOW_CODE,
483 B.PAGE_APPLICATION_ID,
484 B.PAGE_CODE,
485 B.NAME,
486 B.DESCRIPTION,
487 B.CREATED_BY,
488 B.CREATION_DATE,
489 B.LAST_UPDATED_BY,
490 B.LAST_UPDATE_DATE,
491 B.LAST_UPDATE_LOGIN,
492 L.LANGUAGE_CODE,
493 B.SOURCE_LANG
494 from AK_FLOW_PAGES_TL B, FND_LANGUAGES L
495 where L.INSTALLED_FLAG in ('I', 'B')
496 and B.LANGUAGE = userenv('LANG')
497 ) v, AK_FLOW_PAGES_TL T
498 where T.FLOW_CODE(+) = v.FLOW_CODE
499 and T.FLOW_APPLICATION_ID(+) = v.FLOW_APPLICATION_ID
500 and T.PAGE_CODE(+) = v.PAGE_CODE
501 and T.PAGE_APPLICATION_ID(+) = v.PAGE_APPLICATION_ID
502 and T.LANGUAGE(+) = v.LANGUAGE_CODE
503 and T.FLOW_CODE is NULL
504 and T.FLOW_APPLICATION_ID is NULL
505 and T.PAGE_CODE is NULL
506 and T.PAGE_APPLICATION_ID is NULL;
507
508 end ADD_LANGUAGE;
509
510 end AK_FLOW_PAGES_PKG;
[Home] [Help]