1 package body jtfb_ak_regions_pkg as
2 /* $Header: jtfbrgnb.pls 120.2 2005/10/25 05:30:02 psanyal ship $ */
3 --
4 --
5 procedure INSERT_ROW (
6 X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
7 X_REGION_APPLICATION_ID in NUMBER,
8 X_REGION_CODE in VARCHAR2,
9 X_DATABASE_OBJECT_NAME in VARCHAR2,
10 X_REGION_STYLE in VARCHAR2,
11 X_NUM_COLUMNS in NUMBER,
12 X_ICX_CUSTOM_CALL in VARCHAR2,
13 X_NAME in VARCHAR2,
14 X_DESCRIPTION in VARCHAR2,
15 X_REGION_DEFAULTING_API_PKG in VARCHAR2,
16 X_REGION_DEFAULTING_API_PROC in VARCHAR2,
17 X_REGION_VALIDATION_API_PKG in VARCHAR2,
18 X_REGION_VALIDATION_API_PROC in VARCHAR2,
19 X_APPL_MODULE_OBJECT_TYPE in VARCHAR2,
20 X_NUM_ROWS_DISPLAY in NUMBER,
21 X_REGION_OBJECT_TYPE in VARCHAR2,
22 X_IMAGE_FILE_NAME in VARCHAR2,
23 X_ISFORM_FLAG in VARCHAR2,
24 X_HELP_TARGET in VARCHAR2,
25 X_STYLE_SHEET_FILENAME in VARCHAR2,
26 X_VERSION in VARCHAR2,
27 X_APPLICATIONMODULE_USAGE_NAME in VARCHAR2,
28 X_ADD_INDEXED_CHILDREN in VARCHAR2 := NULL,
29 X_STATEFUL_FLAG in VARCHAR2 := NULL,
30 X_FUNCTION_NAME in VARCHAR2 := NULL,
31 X_CHILDREN_VIEW_USAGE_NAME in VARCHAR2 := NULL,
32 X_CREATION_DATE in DATE,
33 X_CREATED_BY in NUMBER,
34 X_LAST_UPDATE_DATE in DATE,
35 X_LAST_UPDATED_BY in NUMBER,
36 X_LAST_UPDATE_LOGIN in NUMBER,
37 X_ATTRIBUTE_CATEGORY in VARCHAR2,
38 X_ATTRIBUTE1 in VARCHAR2,
39 X_ATTRIBUTE2 in VARCHAR2,
40 X_ATTRIBUTE3 in VARCHAR2,
41 X_ATTRIBUTE4 in VARCHAR2,
42 X_ATTRIBUTE5 in VARCHAR2,
43 X_ATTRIBUTE6 in VARCHAR2,
44 X_ATTRIBUTE7 in VARCHAR2,
45 X_ATTRIBUTE8 in VARCHAR2,
46 X_ATTRIBUTE9 in VARCHAR2,
47 X_ATTRIBUTE10 in VARCHAR2,
48 X_ATTRIBUTE11 in VARCHAR2,
49 X_ATTRIBUTE12 in VARCHAR2,
50 X_ATTRIBUTE13 in VARCHAR2,
51 X_ATTRIBUTE14 in VARCHAR2,
52 X_ATTRIBUTE15 in VARCHAR2
53 ) is
54 cursor C is select ROWID from AK_REGIONS
55 where REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
56 and REGION_CODE = X_REGION_CODE;
57 begin
58 insert into AK_REGIONS (
59 REGION_APPLICATION_ID,
60 REGION_CODE,
61 DATABASE_OBJECT_NAME,
62 REGION_STYLE,
63 NUM_COLUMNS,
64 ICX_CUSTOM_CALL,
65 REGION_DEFAULTING_API_PKG,
66 REGION_DEFAULTING_API_PROC,
67 REGION_VALIDATION_API_PKG,
68 REGION_VALIDATION_API_PROC,
69 APPLICATIONMODULE_OBJECT_TYPE,
70 NUM_ROWS_DISPLAY,
71 REGION_OBJECT_TYPE,
72 ISFORM_FLAG,
73 IMAGE_FILE_NAME,
74 HELP_TARGET,
75 STYLE_SHEET_FILENAME,
76 VERSION,
77 APPLICATIONMODULE_USAGE_NAME,
78 ADD_INDEXED_CHILDREN,
79 STATEFUL_FLAG,
80 FUNCTION_NAME,
81 CHILDREN_VIEW_USAGE_NAME,
82 CREATION_DATE,
83 CREATED_BY,
84 LAST_UPDATE_DATE,
85 LAST_UPDATED_BY,
86 LAST_UPDATE_LOGIN,
87 ATTRIBUTE_CATEGORY,
88 ATTRIBUTE1,
89 ATTRIBUTE2,
90 ATTRIBUTE3,
91 ATTRIBUTE4,
92 ATTRIBUTE5,
93 ATTRIBUTE6,
94 ATTRIBUTE7,
95 ATTRIBUTE8,
96 ATTRIBUTE9,
97 ATTRIBUTE10,
98 ATTRIBUTE11,
99 ATTRIBUTE12,
100 ATTRIBUTE13,
101 ATTRIBUTE14,
102 ATTRIBUTE15,
103 STANDALONE
104 ) values (
105 X_REGION_APPLICATION_ID,
106 X_REGION_CODE,
107 X_DATABASE_OBJECT_NAME,
108 X_REGION_STYLE,
109 X_NUM_COLUMNS,
110 X_ICX_CUSTOM_CALL,
111 X_REGION_DEFAULTING_API_PKG,
112 X_REGION_DEFAULTING_API_PROC,
113 X_REGION_VALIDATION_API_PKG,
114 X_REGION_VALIDATION_API_PROC,
115 X_APPL_MODULE_OBJECT_TYPE,
116 X_NUM_ROWS_DISPLAY,
117 X_REGION_OBJECT_TYPE,
118 X_ISFORM_FLAG,
119 X_IMAGE_FILE_NAME,
120 X_HELP_TARGET,
121 X_STYLE_SHEET_FILENAME,
122 X_VERSION,
123 X_APPLICATIONMODULE_USAGE_NAME,
124 X_ADD_INDEXED_CHILDREN,
125 X_STATEFUL_FLAG,
126 X_FUNCTION_NAME,
127 X_CHILDREN_VIEW_USAGE_NAME,
128
129 sysdate,
130 fnd_global.user_id,
131 sysdate,
132 fnd_global.user_id,
133 fnd_global.login_id,
134
135 X_ATTRIBUTE_CATEGORY,
136 X_ATTRIBUTE1,
137 X_ATTRIBUTE2,
138 X_ATTRIBUTE3,
139 X_ATTRIBUTE4,
140 X_ATTRIBUTE5,
141 X_ATTRIBUTE6,
142 X_ATTRIBUTE7,
143 X_ATTRIBUTE8,
144 X_ATTRIBUTE9,
145 X_ATTRIBUTE10,
146 X_ATTRIBUTE11,
147 X_ATTRIBUTE12,
148 X_ATTRIBUTE13,
149 X_ATTRIBUTE14,
150 X_ATTRIBUTE15,
151 'Y' -- STANDALONE
152 );
153
154 open c;
155 fetch c into X_ROWID;
156 if (c%notfound) then
157 close c;
158 raise no_data_found;
159 end if;
160 close c;
161
162 insert into AK_REGIONS_TL (
163 REGION_APPLICATION_ID,
164 REGION_CODE,
165 LANGUAGE,
166 NAME,
167 DESCRIPTION,
168 SOURCE_LANG,
169 CREATED_BY,
170 CREATION_DATE,
171 LAST_UPDATED_BY,
172 LAST_UPDATE_DATE,
173 LAST_UPDATE_LOGIN
174 ) select
175 X_REGION_APPLICATION_ID,
176 X_REGION_CODE,
177 L.LANGUAGE_CODE,
178 X_NAME,
179 X_DESCRIPTION,
180 userenv('LANG'),
181
182 fnd_global.user_id,
183 sysdate,
184 fnd_global.user_id,
185 sysdate,
186 fnd_global.login_id
187
188 from FND_LANGUAGES L
189 where L.INSTALLED_FLAG in ('I', 'B')
190 and not exists
191 (select NULL
192 from AK_REGIONS_TL T
193 where T.REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
194 and T.REGION_CODE = X_REGION_CODE
195 and T.LANGUAGE = L.LANGUAGE_CODE);
196 end INSERT_ROW;
197 --
198 --
199 procedure LOCK_ROW (
200 X_REGION_APPLICATION_ID in NUMBER,
201 X_REGION_CODE in VARCHAR2,
202 X_ATTRIBUTE_CATEGORY in VARCHAR2,
203 X_ATTRIBUTE1 in VARCHAR2,
204 X_ATTRIBUTE2 in VARCHAR2,
205 X_ATTRIBUTE3 in VARCHAR2,
206 X_ATTRIBUTE4 in VARCHAR2,
207 X_ATTRIBUTE5 in VARCHAR2,
208 X_ATTRIBUTE6 in VARCHAR2,
209 X_ATTRIBUTE7 in VARCHAR2,
210 X_ATTRIBUTE8 in VARCHAR2,
211 X_ATTRIBUTE9 in VARCHAR2,
212 X_ATTRIBUTE10 in VARCHAR2,
213 X_ATTRIBUTE11 in VARCHAR2,
214 X_ATTRIBUTE12 in VARCHAR2,
215 X_ATTRIBUTE13 in VARCHAR2,
216 X_ATTRIBUTE14 in VARCHAR2,
217 X_ATTRIBUTE15 in VARCHAR2,
218 X_DATABASE_OBJECT_NAME in VARCHAR2,
219 X_REGION_STYLE in VARCHAR2,
220 X_NUM_COLUMNS in NUMBER,
221 X_ICX_CUSTOM_CALL in VARCHAR2,
222 X_NAME in VARCHAR2,
223 X_DESCRIPTION in VARCHAR2,
224 X_REGION_DEFAULTING_API_PKG in VARCHAR2,
225 X_REGION_DEFAULTING_API_PROC in VARCHAR2,
226 X_REGION_VALIDATION_API_PKG in VARCHAR2,
227 X_REGION_VALIDATION_API_PROC in VARCHAR2,
228 X_APPL_MODULE_OBJECT_TYPE in VARCHAR2,
229 X_NUM_ROWS_DISPLAY in NUMBER,
230 X_REGION_OBJECT_TYPE in VARCHAR2,
231 X_IMAGE_FILE_NAME in VARCHAR2,
232 X_ISFORM_FLAG in VARCHAR2,
233 X_HELP_TARGET in VARCHAR2,
234 X_STYLE_SHEET_FILENAME in VARCHAR2,
235 X_VERSION in VARCHAR2,
236 X_APPLICATIONMODULE_USAGE_NAME in VARCHAR2,
237 X_ADD_INDEXED_CHILDREN in VARCHAR2,
238 X_STATEFUL_FLAG in VARCHAR2,
239 X_FUNCTION_NAME in VARCHAR2,
240 X_CHILDREN_VIEW_USAGE_NAME in VARCHAR2
241 ) is
242 cursor c is select
243 ATTRIBUTE_CATEGORY,
244 ATTRIBUTE1,
245 ATTRIBUTE2,
246 ATTRIBUTE3,
247 ATTRIBUTE4,
248 ATTRIBUTE5,
249 ATTRIBUTE6,
250 ATTRIBUTE7,
251 ATTRIBUTE8,
252 ATTRIBUTE9,
253 ATTRIBUTE10,
254 ATTRIBUTE11,
255 ATTRIBUTE12,
256 ATTRIBUTE13,
257 ATTRIBUTE14,
258 ATTRIBUTE15,
259 DATABASE_OBJECT_NAME,
260 REGION_STYLE,
261 NUM_COLUMNS,
262 ICX_CUSTOM_CALL,
263 REGION_DEFAULTING_API_PKG,
264 REGION_DEFAULTING_API_PROC,
265 REGION_VALIDATION_API_PKG,
266 REGION_VALIDATION_API_PROC,
267 APPLICATIONMODULE_OBJECT_TYPE,
268 NUM_ROWS_DISPLAY,
269 REGION_OBJECT_TYPE,
270 ISFORM_FLAG,
271 IMAGE_FILE_NAME,
272 HELP_TARGET,
273 STYLE_SHEET_FILENAME,
274 VERSION,
275 APPLICATIONMODULE_USAGE_NAME,
276 ADD_INDEXED_CHILDREN,
277 STATEFUL_FLAG,
278 FUNCTION_NAME,
279 CHILDREN_VIEW_USAGE_NAME
280 from AK_REGIONS
281 where REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
282 and REGION_CODE = X_REGION_CODE
283 for update of REGION_APPLICATION_ID nowait;
284 recinfo c%rowtype;
285
286 cursor c1 is select
287 NAME,
288 DESCRIPTION
289 from AK_REGIONS_TL
290 where REGION_CODE = X_REGION_CODE
291 and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
292 and LANGUAGE = userenv('LANG')
293 for update of REGION_CODE nowait;
294 tlinfo c1%rowtype;
295
296 begin
297 open c;
298 fetch c into recinfo;
299 if (c%notfound) then
300 close c;
301 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
302 app_exception.raise_exception;
303 end if;
304 close c;
305 if ( ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
306 OR ((recinfo.ATTRIBUTE_CATEGORY is null)
307 AND (X_ATTRIBUTE_CATEGORY is null)))
308 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
309 OR ((recinfo.ATTRIBUTE1 is null)
310 AND (X_ATTRIBUTE1 is null)))
311 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
312 OR ((recinfo.ATTRIBUTE2 is null)
313 AND (X_ATTRIBUTE2 is null)))
314 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
315 OR ((recinfo.ATTRIBUTE3 is null)
316 AND (X_ATTRIBUTE3 is null)))
317 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
318 OR ((recinfo.ATTRIBUTE4 is null)
319 AND (X_ATTRIBUTE4 is null)))
320 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
321 OR ((recinfo.ATTRIBUTE5 is null)
322 AND (X_ATTRIBUTE5 is null)))
323 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
324 OR ((recinfo.ATTRIBUTE6 is null)
325 AND (X_ATTRIBUTE6 is null)))
326 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
327 OR ((recinfo.ATTRIBUTE7 is null)
328 AND (X_ATTRIBUTE7 is null)))
329 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
330 OR ((recinfo.ATTRIBUTE8 is null)
331 AND (X_ATTRIBUTE8 is null)))
332 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
333 OR ((recinfo.ATTRIBUTE9 is null)
334 AND (X_ATTRIBUTE9 is null)))
335 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
336 OR ((recinfo.ATTRIBUTE10 is null)
337 AND (X_ATTRIBUTE10 is null)))
338 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
339 OR ((recinfo.ATTRIBUTE11 is null)
340 AND (X_ATTRIBUTE11 is null)))
341 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
342 OR ((recinfo.ATTRIBUTE12 is null)
343 AND (X_ATTRIBUTE12 is null)))
344 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
345 OR ((recinfo.ATTRIBUTE13 is null)
346 AND (X_ATTRIBUTE13 is null)))
347 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
348 OR ((recinfo.ATTRIBUTE14 is null)
349 AND (X_ATTRIBUTE14 is null)))
350 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
351 OR ((recinfo.ATTRIBUTE15 is null)
352 AND (X_ATTRIBUTE15 is null)))
353 AND (recinfo.DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME)
354 AND (recinfo.REGION_STYLE = X_REGION_STYLE)
355 AND ((recinfo.NUM_COLUMNS = X_NUM_COLUMNS)
356 OR ((recinfo.NUM_COLUMNS is null)
357 AND (X_NUM_COLUMNS is null)))
358 AND ((recinfo.ICX_CUSTOM_CALL = X_ICX_CUSTOM_CALL)
359 OR ((recinfo.ICX_CUSTOM_CALL is null)
360 AND (X_ICX_CUSTOM_CALL is null)))
361 AND ((recinfo.REGION_DEFAULTING_API_PKG = X_REGION_DEFAULTING_API_PKG)
362 or ((recinfo.REGION_DEFAULTING_API_PKG is null)
363 and (X_REGION_DEFAULTING_API_PKG is null)))
364 AND ((recinfo.REGION_DEFAULTING_API_PROC = X_REGION_DEFAULTING_API_PROC)
365 or ((recinfo.REGION_DEFAULTING_API_PROC is null)
366 and (X_REGION_DEFAULTING_API_PROC is null)))
367 AND ((recinfo.REGION_VALIDATION_API_PKG = X_REGION_VALIDATION_API_PKG)
368 or ((recinfo.REGION_VALIDATION_API_PKG is null)
369 and (X_REGION_VALIDATION_API_PKG is null)))
370 AND ((recinfo.REGION_VALIDATION_API_PROC = X_REGION_VALIDATION_API_PROC)
371 or ((recinfo.REGION_VALIDATION_API_PROC is null)
372 and (X_REGION_VALIDATION_API_PROC is null)))
373 AND ((recinfo.APPLICATIONMODULE_OBJECT_TYPE = X_APPL_MODULE_OBJECT_TYPE)
374 or ((recinfo.APPLICATIONMODULE_OBJECT_TYPE is null)
375 and (X_APPL_MODULE_OBJECT_TYPE is null)))
376 AND ((recinfo.NUM_ROWS_DISPLAY = X_NUM_ROWS_DISPLAY)
377 or ((recinfo.NUM_ROWS_DISPLAY is null)
378 and (X_NUM_ROWS_DISPLAY is null)))
379 AND ((recinfo.REGION_OBJECT_TYPE = X_REGION_OBJECT_TYPE)
380 or ((recinfo.REGION_OBJECT_TYPE is null)
381 and (X_REGION_OBJECT_TYPE is null)))
382 AND ((recinfo.ISFORM_FLAG = X_ISFORM_FLAG)
383 or ((recinfo.ISFORM_FLAG is null)
384 and (X_ISFORM_FLAG is null)))
385 AND ((recinfo.IMAGE_FILE_NAME = X_IMAGE_FILE_NAME)
386 or ((recinfo.IMAGE_FILE_NAME is null)
387 and (X_IMAGE_FILE_NAME is null)))
388 AND ((recinfo.HELP_TARGET = X_HELP_TARGET)
389 or ((recinfo.HELP_TARGET is null)
390 and (X_HELP_TARGET is null)))
391 AND ((recinfo.STYLE_SHEET_FILENAME = X_STYLE_SHEET_FILENAME)
392 or ((recinfo.STYLE_SHEET_FILENAME is null)
393 and (X_STYLE_SHEET_FILENAME is null)))
394 AND ((recinfo.VERSION = X_VERSION)
395 or ((recinfo.VERSION is null)
396 and (X_VERSION is null)))
397 AND ((recinfo.APPLICATIONMODULE_USAGE_NAME = X_APPLICATIONMODULE_USAGE_NAME)
398 or ((recinfo.APPLICATIONMODULE_USAGE_NAME is null)
399 and (X_APPLICATIONMODULE_USAGE_NAME is null)))
400 AND ((recinfo.ADD_INDEXED_CHILDREN = X_ADD_INDEXED_CHILDREN)
401 or ((recinfo.ADD_INDEXED_CHILDREN is null)
402 and (X_ADD_INDEXED_CHILDREN is null)))
403 AND ((recinfo.STATEFUL_FLAG = X_STATEFUL_FLAG)
404 or ((recinfo.STATEFUL_FLAG is null)
405 and (X_STATEFUL_FLAG is null)))
406 AND ((recinfo.FUNCTION_NAME = X_FUNCTION_NAME)
407 or ((recinfo.FUNCTION_NAME is null)
408 and (X_FUNCTION_NAME is null)))
409 AND ((recinfo.CHILDREN_VIEW_USAGE_NAME = X_CHILDREN_VIEW_USAGE_NAME)
410 or ((recinfo.CHILDREN_VIEW_USAGE_NAME is null)
411 and (X_CHILDREN_VIEW_USAGE_NAME is null)))
412 ) then
413 null;
414 else
415 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
416 app_exception.raise_exception;
417 end if;
418
419 open c1;
420 fetch c1 into tlinfo;
421 if (c1%notfound) then
422 close c1;
423 return;
424 end if;
425 close c1;
426
427 if ( (tlinfo.NAME = X_NAME)
428 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
429 OR ((tlinfo.DESCRIPTION is null)
430 AND (X_DESCRIPTION is null)))
431 ) then
432 null;
433 else
434 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
435 app_exception.raise_exception;
436 end if;
437 return;
438 end LOCK_ROW;
439 --
440 --
441 procedure UPDATE_ROW (
442 X_REGION_APPLICATION_ID in NUMBER,
443 X_REGION_CODE in VARCHAR2,
444 X_DATABASE_OBJECT_NAME in VARCHAR2,
445 X_REGION_STYLE in VARCHAR2,
446 X_NUM_COLUMNS in NUMBER,
447 X_ICX_CUSTOM_CALL in VARCHAR2,
448 X_NAME in VARCHAR2,
449 X_DESCRIPTION in VARCHAR2,
450 X_REGION_DEFAULTING_API_PKG in VARCHAR2,
451 X_REGION_DEFAULTING_API_PROC in VARCHAR2,
452 X_REGION_VALIDATION_API_PKG in VARCHAR2,
453 X_REGION_VALIDATION_API_PROC in VARCHAR2,
454 X_APPL_MODULE_OBJECT_TYPE in VARCHAR2,
455 X_NUM_ROWS_DISPLAY in NUMBER,
456 X_REGION_OBJECT_TYPE in VARCHAR2,
457 X_IMAGE_FILE_NAME in VARCHAR2,
458 X_ISFORM_FLAG in VARCHAR2,
459 X_HELP_TARGET in VARCHAR2,
460 X_STYLE_SHEET_FILENAME in VARCHAR2,
461 X_VERSION in VARCHAR2,
462 X_APPLICATIONMODULE_USAGE_NAME in VARCHAR2,
463 X_ADD_INDEXED_CHILDREN in VARCHAR2 := NULL,
464 X_STATEFUL_FLAG in VARCHAR2 := NULL,
465 X_FUNCTION_NAME in VARCHAR2 := NULL,
466 X_CHILDREN_VIEW_USAGE_NAME in VARCHAR2 := NULL,
467 X_LAST_UPDATE_DATE in DATE,
468 X_LAST_UPDATED_BY in NUMBER,
469 X_LAST_UPDATE_LOGIN in NUMBER,
470 X_ATTRIBUTE_CATEGORY in VARCHAR2,
471 X_ATTRIBUTE1 in VARCHAR2,
472 X_ATTRIBUTE2 in VARCHAR2,
473 X_ATTRIBUTE3 in VARCHAR2,
474 X_ATTRIBUTE4 in VARCHAR2,
475 X_ATTRIBUTE5 in VARCHAR2,
476 X_ATTRIBUTE6 in VARCHAR2,
477 X_ATTRIBUTE7 in VARCHAR2,
478 X_ATTRIBUTE8 in VARCHAR2,
479 X_ATTRIBUTE9 in VARCHAR2,
480 X_ATTRIBUTE10 in VARCHAR2,
481 X_ATTRIBUTE11 in VARCHAR2,
482 X_ATTRIBUTE12 in VARCHAR2,
483 X_ATTRIBUTE13 in VARCHAR2,
484 X_ATTRIBUTE14 in VARCHAR2,
485 X_ATTRIBUTE15 in VARCHAR2
486 ) is
487 begin
488 update AK_REGIONS set
489 REGION_APPLICATION_ID = X_REGION_APPLICATION_ID,
490 REGION_CODE = X_REGION_CODE,
491 DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME,
492 REGION_STYLE = X_REGION_STYLE,
493 NUM_COLUMNS = X_NUM_COLUMNS,
494 ICX_CUSTOM_CALL = X_ICX_CUSTOM_CALL,
495 REGION_DEFAULTING_API_PKG = X_REGION_DEFAULTING_API_PKG,
496 REGION_DEFAULTING_API_PROC = X_REGION_DEFAULTING_API_PROC,
497 REGION_VALIDATION_API_PKG = X_REGION_VALIDATION_API_PKG,
498 REGION_VALIDATION_API_PROC = X_REGION_VALIDATION_API_PROC,
499 APPLICATIONMODULE_OBJECT_TYPE = X_APPL_MODULE_OBJECT_TYPE,
500 NUM_ROWS_DISPLAY = X_NUM_ROWS_DISPLAY,
501 REGION_OBJECT_TYPE = X_REGION_OBJECT_TYPE,
502 ISFORM_FLAG = X_ISFORM_FLAG,
503 IMAGE_FILE_NAME = X_IMAGE_FILE_NAME,
504 HELP_TARGET = X_HELP_TARGET,
505 STYLE_SHEET_FILENAME = X_STYLE_SHEET_FILENAME,
506 VERSION = X_VERSION,
507 APPLICATIONMODULE_USAGE_NAME = X_APPLICATIONMODULE_USAGE_NAME,
508 ADD_INDEXED_CHILDREN = X_ADD_INDEXED_CHILDREN,
509 STATEFUL_FLAG = X_STATEFUL_FLAG,
510 FUNCTION_NAME = X_FUNCTION_NAME,
511 CHILDREN_VIEW_USAGE_NAME = X_CHILDREN_VIEW_USAGE_NAME,
512
513 LAST_UPDATE_DATE = sysdate,
514 LAST_UPDATED_BY = fnd_global.user_id,
515 LAST_UPDATE_LOGIN = fnd_global.login_id,
516
517 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
518 ATTRIBUTE1 = X_ATTRIBUTE1,
519 ATTRIBUTE2 = X_ATTRIBUTE2,
520 ATTRIBUTE3 = X_ATTRIBUTE3,
521 ATTRIBUTE4 = X_ATTRIBUTE4,
522 ATTRIBUTE5 = X_ATTRIBUTE5,
523 ATTRIBUTE6 = X_ATTRIBUTE6,
524 ATTRIBUTE7 = X_ATTRIBUTE7,
525 ATTRIBUTE8 = X_ATTRIBUTE8,
526 ATTRIBUTE9 = X_ATTRIBUTE9,
527 ATTRIBUTE10 = X_ATTRIBUTE10,
528 ATTRIBUTE11 = X_ATTRIBUTE11,
529 ATTRIBUTE12 = X_ATTRIBUTE12,
530 ATTRIBUTE13 = X_ATTRIBUTE13,
531 ATTRIBUTE14 = X_ATTRIBUTE14,
532 ATTRIBUTE15 = X_ATTRIBUTE15,
533 STANDALONE = 'Y'
534 where REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
535 and REGION_CODE = X_REGION_CODE;
536 if (sql%notfound) then
537 raise no_data_found;
538 end if;
539
540 update AK_REGIONS_TL set
541 NAME = X_NAME,
542 DESCRIPTION = X_DESCRIPTION,
543
544 LAST_UPDATE_DATE = sysdate,
545 LAST_UPDATED_BY = fnd_global.user_id,
546 LAST_UPDATE_LOGIN = fnd_global.login_id,
547
548 SOURCE_LANG = userenv('LANG')
549 where REGION_CODE = X_REGION_CODE
550 and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
551 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
552 if (sql%notfound) then
553 raise no_data_found;
554 end if;
555 end UPDATE_ROW;
556 --
557 --
558 procedure DELETE_ROW (
559 X_REGION_APPLICATION_ID in NUMBER,
560 X_REGION_CODE in VARCHAR2
561 ) is
562 begin
563 delete from AK_REGIONS
564 where REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
565 and REGION_CODE = X_REGION_CODE;
566
567 if (sql%notfound) then
568 raise no_data_found;
569 end if;
570
571 delete from AK_REGIONS_TL
572 where REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
573 and REGION_CODE = X_REGION_CODE;
574
575 if (sql%notfound) then
576 raise no_data_found;
577 end if;
578 end DELETE_ROW;
579
580 procedure ADD_LANGUAGE
581 is
582 begin
583 delete from AK_REGIONS_TL T
584 where not exists
585 (select NULL
586 from AK_REGIONS B
587 where B.REGION_CODE = T.REGION_CODE
588 and B.REGION_APPLICATION_ID = T.REGION_APPLICATION_ID
589 );
590
591 update AK_REGIONS_TL T set (
592 NAME,
593 DESCRIPTION
594 ) = (select
595 B.NAME,
596 B.DESCRIPTION
597 from AK_REGIONS_TL B
598 where B.REGION_CODE = T.REGION_CODE
599 and B.REGION_APPLICATION_ID = T.REGION_APPLICATION_ID
600 and B.LANGUAGE = T.SOURCE_LANG)
601 where (
602 T.REGION_CODE,
603 T.REGION_APPLICATION_ID,
604 T.LANGUAGE
605 ) in (select
606 SUBT.REGION_CODE,
607 SUBT.REGION_APPLICATION_ID,
608 SUBT.LANGUAGE
609 from AK_REGIONS_TL SUBB, AK_REGIONS_TL SUBT
610 where SUBB.REGION_CODE = SUBT.REGION_CODE
611 and SUBB.REGION_APPLICATION_ID = SUBT.REGION_APPLICATION_ID
612 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
613 and (SUBB.NAME <> SUBT.NAME
614 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
615 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
616 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
617 ));
618
619 insert into AK_REGIONS_TL (
620 REGION_APPLICATION_ID,
621 REGION_CODE,
622 NAME,
623 DESCRIPTION,
624 CREATED_BY,
625 CREATION_DATE,
626 LAST_UPDATED_BY,
627 LAST_UPDATE_DATE,
628 LAST_UPDATE_LOGIN,
629 LANGUAGE,
630 SOURCE_LANG
631 ) select
632 B.REGION_APPLICATION_ID,
633 B.REGION_CODE,
634 B.NAME,
635 B.DESCRIPTION,
636 B.CREATED_BY,
637 B.CREATION_DATE,
638 B.LAST_UPDATED_BY,
639 B.LAST_UPDATE_DATE,
640 B.LAST_UPDATE_LOGIN,
641 L.LANGUAGE_CODE,
642 B.SOURCE_LANG
643 from AK_REGIONS_TL B, FND_LANGUAGES L
644 where L.INSTALLED_FLAG in ('I', 'B')
645 and B.LANGUAGE = userenv('LANG')
646 and not exists
647 (select NULL
648 from AK_REGIONS_TL T
649 where T.REGION_CODE = B.REGION_CODE
650 and T.REGION_APPLICATION_ID = B.REGION_APPLICATION_ID
651 and T.LANGUAGE = L.LANGUAGE_CODE);
652 end ADD_LANGUAGE;
653 --
654 --
655 end jtfb_ak_regions_pkg;
[Home] [Help]