[Home] [Help]
PACKAGE BODY: APPS.BIS_AK_REGION_PUB
Source
1 PACKAGE BODY BIS_AK_REGION_PUB as
2 /* $Header: BISPAKRB.pls 120.7.12000000.3 2007/01/31 18:42:32 akoduri ship $ */
3 ----------------------------------------------------------------------------
4 -- PACKAGE: BIS_AK_REGION_PUB --
5 -- --
6 -- DESCRIPTION: Private package that calls the AK packages to --
7 -- insert/update/delete records in the AK tables. --
8 --
9 -- --
10 -- MODIFICATIONS --
11 -- Date User Modification
12 -- XX-XXX-XX XXXXXXXX Modifications made, which procedures changed & --
13 -- list bug number, if fixing a bug. --
14 -- --
15 -- 11/21/01 mdamle Initial creation --
16 -- 01/10/03 nbarik Enhancement : 2638594 Portlet Builder --
17 -- Added DELETE_REGION_ITEM_ROW --
18 -- 10/13/03 nbarik Bug 2806513 - Added AK_OBJECTS_PKG.INSERT_ROW --
19 -- 12/25/03 mdamle Page Definer Integration - overloaded for --
20 -- functionality and error messaging --
21 -- 02/10/03 nbarik BSC/PMV Integration - Overloaded Procedures --
22 -- 05/12/04 adrao Modifed Insert/Update APIs to ensure Nested --
23 -- Region code is inserted and updated --
24 -- 05/22/04 adrao Added Exception handling to propogated to UI --
25 -- 06/07/04 mdamle Added DELETE_REGION_AND_REGION_ITEMS --
26 -- 06/29/04 mdamle Added INSERT_AK_OBJECT to the overloaded --
27 -- insert routines --
28 -- Added calls to ext. APIs for delete --
29 -- 07/22/04 mdamle Enh#3786101 - Consider Materialized view as --
30 -- a valid database object --
31 -- 30-JUL-2004 rpenneru Modified for enhancemen#3748519 --
32 -- 08/04/04 mdamle Bug#3823878 - Add lock_row --
33 -- 08/16/04 sawu Bug#3822777 - Added IS_MEASURE_TYPE and VALIDATE_MEASURE --
34 -- 08/16/04 sawu Bug#3859267 - Added IS_COMPARE_TYPE and VALIDATE_COMPARE,
35 -- overloaded UPDATE_REGION_ITEM_ATTR
36 -- 09/24/04 mdamle Bug#3893663 - Return SQLERRM for all unexp errs --
37 -- Added rollback within the lock procedure --
38 -- 09/29/04 sawu Bug#3921384 - Nullify attribute1 when attribute2
39 -- is set to null for 'Compare to Measure No Target'
40 -- 11/04/04 ankgoel Bug#3990675 - Call AK_REGION_ITEMS_PKG.UPDATE_ROW only
41 -- AK Item exists
42 -- 11/05/04 ankgoel Bug#3937907 - Added AK_DATA_SET to verify if AK data
43 -- will be modified for the source and compare-to columns
44 -- 11/24/04 sawu Bug#4028958: added GET_COMPARE_AGG_FUNCTION,
45 -- IS_VIEW_BY_REPORT, IS_AGGREGATE_DEFINED and
46 -- updated UPDATE_REGION_ITEM_ATTR, COMPARE_TYPE_AND_SHORTNAME
47 -- 11/29/04 skchoudh Bug#4028958 Replaces the Aggregate Function of
48 -- COMPARE_TO with Measure
49 -- 01/08/05 mdamle Add Url to AK_REGION_ITEMS routines --
50 -- 02/01/05 mdamle Add order_sequence, direction to AK_REGION_ITEMS --
51 -- 03/21/05 ankagarw bug#4235732 - changing count(*) to count(1) --
52 -- 04/26/05 ankagarw bug#4194925 - saving measure display name as --
53 -- attribute long label in ak_region_items --
54 -- 04/28/05 ankgoel Bug#4289493 - Truncated ak_object name to 23 chars
55 -- 19-MAY-2005 visuri GSCC Issues bug 4363854 --
56 -- 06/14/05 ankgoel Bug#4371653 - Region name not getting saved
57 -- 06/30/05 akoduri Bug#4370200 - Default Number of Rows not getting saved
58 -- 07/07/05 rpenneru Bug#4468843 - Synonym should be treated as a valid DB Object.
59 -- 07/14/05 adrao Bug#4448994 added API Get_Region_Code_TL_Data
60 -- 06/19/06 ankgoel Bug#5256605 - Support MLS for AK Region Items --
61 -- 09-Aug-06 ankgoel Bug#5412517 Del all customizations for a ak region
62 -- 10/20/06 akoduri Bug#5584162 - Enable Sort For Percent Of Total
63 ----------------------------------------------------------------------------
64
65 --return true if the attribute types and measure levels are the same
66 FUNCTION COMPARE_TYPE_AND_SHORTNAME(
67 p_src_type IN Ak_Region_Items.ATTRIBUTE1%TYPE
68 ,p_src_short_name IN Ak_Region_Items.ATTRIBUTE2%TYPE
69 ,p_target_type IN Ak_Region_Items.ATTRIBUTE1%TYPE
70 ,p_target_short_name IN Ak_Region_Items.ATTRIBUTE2%TYPE
71 ) RETURN BOOLEAN
72 IS
73 BEGIN
74 RETURN ((p_src_type = p_target_type) AND (p_src_short_name = p_target_short_name));
75 END COMPARE_TYPE_AND_SHORTNAME;
76
77 --return the aggregate_function (ak_region_items.attribute9) for referenced measure
78 --column for this particular compare_to ak_region_item
79 FUNCTION GET_COMPARE_AGG_FUNCTION(
80 p_region_code IN Ak_Region_Items.REGION_CODE%TYPE
81 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type
82 ,p_compare_code IN Ak_Region_Items.ATTRIBUTE_CODE%Type
83 ) RETURN Ak_Region_Items.ATTRIBUTE9%TYPE
84 IS
85 l_ret_val Ak_Region_Items.ATTRIBUTE9%TYPE := NULL;
86
87 --need to take care when multiple attribute codes defined in the same report
88 CURSOR agg_cur IS
89 SELECT attribute9
90 FROM ak_region_items
91 WHERE region_code = p_region_code
92 AND region_application_id = p_region_app_id
93 AND attribute_code = p_compare_code
94 AND attribute1 IN (BIS_AK_REGION_PUB.C_MEASURE, BIS_AK_REGION_PUB.C_MEASURE_NO_TARGET);
95
96 BEGIN
97 --retrive the first such aggregate function
98 FOR rec IN agg_cur LOOP
99 l_ret_val := rec.attribute9;
100 EXIT;
101 END LOOP;
102
103 RETURN l_ret_val;
104 EXCEPTION
105 WHEN OTHERS THEN
106 RETURN NULL;
107 END GET_COMPARE_AGG_FUNCTION;
108
109
110 --return true if and only if p_attribute_type is one of C_MEASURE or C_MEASURE_NO_TARGET
111 FUNCTION IS_MEASURE_TYPE(
112 p_attribute_type IN VARCHAR2
113 ) RETURN BOOLEAN
114 IS
115 BEGIN
116 RETURN (p_attribute_type = BIS_AK_REGION_PUB.C_MEASURE) OR (p_attribute_type = BIS_AK_REGION_PUB.C_MEASURE_NO_TARGET);
117 END IS_MEASURE_TYPE;
118
119 --return true if and only if p_attribute_type is C_COMPARE_TO_MEASURE_NO_TARGET
120 FUNCTION IS_COMPARE_TYPE(
121 p_attribute_type IN VARCHAR2
122 ) RETURN BOOLEAN
123 IS
124 BEGIN
125 RETURN (p_attribute_type = BIS_AK_REGION_PUB.C_COMPARE_TO_MEASURE_NO_TARGET);
126 END IS_COMPARE_TYPE;
127
128 --return true if and only if given measure short name exists
129 FUNCTION VALIDATE_MEASURE(
130 p_short_name IN Bisbv_Performance_Measures.MEASURE_SHORT_NAME%Type
131 ,x_measure_short_name OUT NOCOPY Bisbv_Performance_Measures.MEASURE_SHORT_NAME%TYPE
132 ,x_measure_name OUT NOCOPY Bisbv_Performance_Measures.MEASURE_NAME%TYPE
133 ) RETURN BOOLEAN
134 IS
135 l_result BOOLEAN := false;
136 l_measure_name Bisbv_Performance_Measures.MEASURE_NAME%TYPE;
137
138 CURSOR mea_cur IS
139 SELECT measure_name
140 FROM Bisbv_Performance_Measures
141 WHERE measure_short_name = p_short_name;
142
143 BEGIN
144 IF (p_short_name IS NOT NULL) THEN
145 OPEN mea_cur;
146 FETCH mea_cur INTO l_measure_name;
147 IF (mea_cur%FOUND) THEN
148 --pick the first match
149 l_result := true;
150 x_measure_short_name := p_short_name;
151 x_measure_name := l_measure_name;
152 END IF;
153 CLOSE mea_cur;
154 END IF;
155
156 RETURN l_result;
157 EXCEPTION
158 WHEN OTHERS THEN RETURN false;
159 END VALIDATE_MEASURE;
160
161 --return true if any only if p_compare_code refers to a valid entry in ak_region_items which
162 --subsequently refers to a valid measure
163 FUNCTION VALIDATE_COMPARE(
164 p_region_code IN Ak_Region_Items.REGION_CODE%TYPE
165 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type
166 ,p_compare_code IN Ak_Region_Items.ATTRIBUTE_CODE%Type
167 ,x_measure_short_name OUT NOCOPY Bisbv_Performance_Measures.MEASURE_SHORT_NAME%TYPE
168 ,x_measure_name OUT NOCOPY Bisbv_Performance_Measures.MEASURE_NAME%TYPE
169 ) RETURN BOOLEAN
170 IS
171 l_result BOOLEAN := false;
172
173 --need to handle the case when there are multiple ak_region_items in the same
174 --report with the same attribute_code
175 CURSOR comp_cur IS
176 SELECT attribute2
177 FROM Ak_Region_Items
178 WHERE REGION_CODE = p_region_code
179 AND REGION_APPLICATION_ID = p_region_app_id
180 AND ATTRIBUTE_CODE = p_compare_code
181 AND ATTRIBUTE1 IN (BIS_AK_REGION_PUB.C_MEASURE, BIS_AK_REGION_PUB.C_MEASURE_NO_TARGET);
182 BEGIN
183 IF (p_compare_code IS NOT NULL) THEN
184 FOR rec IN comp_cur LOOP
185 l_result := VALIDATE_MEASURE(p_short_name => rec.attribute2, x_measure_short_name => x_measure_short_name, x_measure_name => x_measure_name);
186 EXIT WHEN (l_result = true);
187 END LOOP;
188 END IF;
189
190 RETURN l_result;
191 EXCEPTION
192 WHEN OTHERS THEN RETURN false;
193 END VALIDATE_COMPARE;
194
195 procedure INSERT_REGION_ROW (
196 X_ROWID in out NOCOPY VARCHAR2,
197 X_USER_ID in NUMBER,
198 X_REGION_APPLICATION_ID in NUMBER,
199 X_REGION_CODE in VARCHAR2,
200 X_DATABASE_OBJECT_NAME in VARCHAR2,
201 X_NAME in VARCHAR2,
202 X_DESCRIPTION in VARCHAR2,
203 X_NUM_ROWS_DISPLAY in NUMBER,
204 X_REGION_STYLE in VARCHAR2,
205 X_REGION_OBJECT_TYPE in VARCHAR2,
206 X_ISFORM_FLAG in VARCHAR2,
207 X_ATTRIBUTE_CATEGORY in VARCHAR2,
208 X_ATTRIBUTE1 in VARCHAR2,
209 X_ATTRIBUTE2 in VARCHAR2,
210 X_ATTRIBUTE3 in VARCHAR2,
211 X_ATTRIBUTE4 in VARCHAR2,
212 X_ATTRIBUTE5 in VARCHAR2,
213 X_ATTRIBUTE6 in VARCHAR2,
214 X_ATTRIBUTE7 in VARCHAR2,
215 X_ATTRIBUTE8 in VARCHAR2,
216 X_ATTRIBUTE9 in VARCHAR2,
217 X_ATTRIBUTE10 in VARCHAR2,
218 X_ATTRIBUTE11 in VARCHAR2,
219 X_ATTRIBUTE12 in VARCHAR2,
220 X_ATTRIBUTE13 in VARCHAR2,
221 X_ATTRIBUTE14 in VARCHAR2,
222 X_ATTRIBUTE15 in VARCHAR2) is
223
224 begin
225
226 IF valid_database_object(X_DATABASE_OBJECT_NAME) and not AK_OBJECT_EXISTS(X_DATABASE_OBJECT_NAME) THEN
227 INSERT_AK_OBJECT(
228 P_DATABASE_OBJECT_NAME => X_DATABASE_OBJECT_NAME,
229 P_APPLICATION_ID => X_REGION_APPLICATION_ID);
230 END IF;
231
232 AK_REGIONS_PKG.INSERT_ROW(
233 X_ROWID => X_ROWID,
234 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
235 X_REGION_CODE => upper(X_REGION_CODE),
236 X_DATABASE_OBJECT_NAME => X_DATABASE_OBJECT_NAME,
237 X_REGION_STYLE => X_REGION_STYLE,
238 X_NUM_COLUMNS => null,
239 X_ICX_CUSTOM_CALL => null,
240 X_NAME => X_NAME,
241 X_DESCRIPTION => X_DESCRIPTION,
242 X_REGION_DEFAULTING_API_PKG => null,
243 X_REGION_DEFAULTING_API_PROC => null,
244 X_REGION_VALIDATION_API_PKG => null,
245 X_REGION_VALIDATION_API_PROC => null,
246 X_APPL_MODULE_OBJECT_TYPE => null,
247 X_NUM_ROWS_DISPLAY => X_NUM_ROWS_DISPLAY,
248 X_REGION_OBJECT_TYPE => X_REGION_OBJECT_TYPE,
249 X_IMAGE_FILE_NAME => null,
250 X_ISFORM_FLAG => X_ISFORM_FLAG,
251 X_HELP_TARGET => null,
252 X_STYLE_SHEET_FILENAME => null,
253 X_VERSION => null,
254 X_APPLICATIONMODULE_USAGE_NAME=>null,
255 X_CREATION_DATE => sysdate,
256 X_CREATED_BY => X_USER_ID,
257 X_LAST_UPDATE_DATE => sysdate,
258 X_LAST_UPDATED_BY => X_USER_ID,
259 X_LAST_UPDATE_LOGIN => X_USER_ID,
260 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
261 X_ATTRIBUTE1 => X_ATTRIBUTE1,
262 X_ATTRIBUTE2 => X_ATTRIBUTE2,
263 X_ATTRIBUTE3 => X_ATTRIBUTE3,
264 X_ATTRIBUTE4 => X_ATTRIBUTE4,
265 X_ATTRIBUTE5 => X_ATTRIBUTE5,
266 X_ATTRIBUTE6 => X_ATTRIBUTE6,
267 X_ATTRIBUTE7 => X_ATTRIBUTE7,
268 X_ATTRIBUTE8 => X_ATTRIBUTE8,
269 X_ATTRIBUTE9 => X_ATTRIBUTE9,
270 X_ATTRIBUTE10 => X_ATTRIBUTE10,
271 X_ATTRIBUTE11 => X_ATTRIBUTE11,
272 X_ATTRIBUTE12 => X_ATTRIBUTE12,
273 X_ATTRIBUTE13 => X_ATTRIBUTE13,
274 X_ATTRIBUTE14 => X_ATTRIBUTE14,
275 X_ATTRIBUTE15 => X_ATTRIBUTE15);
276
277 end INSERT_REGION_ROW;
278
279 procedure UPDATE_REGION_ROW (
280 X_USER_ID in NUMBER,
281 X_REGION_APPLICATION_ID in NUMBER,
282 X_REGION_CODE in VARCHAR2,
283 X_DATABASE_OBJECT_NAME in VARCHAR2,
284 X_NAME in VARCHAR2,
285 X_DESCRIPTION in VARCHAR2,
286 X_NUM_ROWS_DISPLAY in NUMBER,
287 X_REGION_STYLE in VARCHAR2,
288 X_REGION_OBJECT_TYPE in VARCHAR2,
289 X_ATTRIBUTE_CATEGORY in VARCHAR2,
290 X_ATTRIBUTE1 in VARCHAR2,
291 X_ATTRIBUTE2 in VARCHAR2,
292 X_ATTRIBUTE3 in VARCHAR2,
293 X_ATTRIBUTE4 in VARCHAR2,
294 X_ATTRIBUTE5 in VARCHAR2,
295 X_ATTRIBUTE6 in VARCHAR2,
296 X_ATTRIBUTE7 in VARCHAR2,
297 X_ATTRIBUTE8 in VARCHAR2,
298 X_ATTRIBUTE9 in VARCHAR2,
299 X_ATTRIBUTE10 in VARCHAR2,
300 X_ATTRIBUTE11 in VARCHAR2,
301 X_ATTRIBUTE12 in VARCHAR2,
302 X_ATTRIBUTE13 in VARCHAR2,
303 X_ATTRIBUTE14 in VARCHAR2,
304 X_ATTRIBUTE15 in VARCHAR2) is
305
306 l_region_rec AK_REGION_PUB.Region_Rec_Type;
307
308 cursor cRegion is
309 select icx_custom_call,
310 num_columns,
311 region_defaulting_api_pkg,
312 region_defaulting_api_proc,
313 region_validation_api_pkg,
314 region_validation_api_proc,
315 applicationmodule_object_type,
316 image_file_name,
317 isform_flag,
318 help_target,
319 style_sheet_filename,
320 version,
321 applicationmodule_usage_name,
322 add_indexed_children,
323 stateful_flag,
324 function_name,
325 children_view_usage_name,
326 search_panel,
327 advanced_search_panel,
328 customize_panel,
329 default_search_panel,
330 results_based_search,
331 display_graph_table,
332 disable_header,
333 standalone,
334 auto_customization_criteria
335 from ak_regions
336 where region_code = X_REGION_CODE
337 and region_application_id = X_REGION_APPLICATION_ID;
338
339 begin
340
341 if cRegion%ISOPEN then
342 CLOSE cRegion;
343 end if;
344 OPEN cRegion;
345 FETCH cRegion INTO
346 l_region_rec.icx_custom_call,
347 l_region_rec.num_columns,
348 l_region_rec.region_defaulting_api_pkg,
349 l_region_rec.region_defaulting_api_proc,
350 l_region_rec.region_validation_api_pkg,
351 l_region_rec.region_validation_api_proc,
352 l_region_rec.applicationmodule_object_type,
353 l_region_rec.image_file_name,
354 l_region_rec.isform_flag,
355 l_region_rec.help_target,
356 l_region_rec.style_sheet_filename,
357 l_region_rec.version,
358 l_region_rec.applicationmodule_usage_name,
359 l_region_rec.add_indexed_children,
360 l_region_rec.stateful_flag,
361 l_region_rec.function_name,
362 l_region_rec.children_view_usage_name,
363 l_region_rec.search_panel,
364 l_region_rec.advanced_search_panel,
365 l_region_rec.customize_panel,
366 l_region_rec.default_search_panel,
367 l_region_rec.results_based_search,
368 l_region_rec.display_graph_table,
369 l_region_rec.disable_header,
370 l_region_rec.standalone,
371 l_region_rec.auto_customization_criteria;
372 CLOSE cRegion;
373
374 AK_REGIONS_PKG.UPDATE_ROW(
375 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
376 X_REGION_CODE => X_REGION_CODE,
377 X_DATABASE_OBJECT_NAME => X_DATABASE_OBJECT_NAME,
378 X_REGION_STYLE => X_REGION_STYLE,
379 X_NUM_COLUMNS =>l_region_rec.num_columns,
380 X_ICX_CUSTOM_CALL => l_region_rec.icx_custom_call,
381 X_NAME => X_NAME,
382 X_DESCRIPTION => X_DESCRIPTION,
383 X_REGION_DEFAULTING_API_PKG => l_region_rec.region_defaulting_api_pkg,
384 X_REGION_DEFAULTING_API_PROC => l_region_rec.region_defaulting_api_proc,
385 X_REGION_VALIDATION_API_PKG => l_region_rec.region_validation_api_pkg,
386 X_REGION_VALIDATION_API_PROC => l_region_rec.region_validation_api_proc,
387 X_APPL_MODULE_OBJECT_TYPE => l_region_rec.applicationmodule_object_type,
388 X_NUM_ROWS_DISPLAY => X_NUM_ROWS_DISPLAY,
389 X_REGION_OBJECT_TYPE => X_REGION_OBJECT_TYPE,
390 X_IMAGE_FILE_NAME => l_region_rec.image_file_name,
391 X_ISFORM_FLAG => l_region_rec.isform_flag,
392 X_HELP_TARGET => l_region_rec.help_target,
393 X_STYLE_SHEET_FILENAME => l_region_rec.style_sheet_filename,
394 X_VERSION => l_region_rec.version,
395 X_APPLICATIONMODULE_USAGE_NAME => l_region_rec.applicationmodule_usage_name,
396 X_ADD_INDEXED_CHILDREN => l_region_rec.add_indexed_children,
397 X_STATEFUL_FLAG => l_region_rec.stateful_flag,
398 X_FUNCTION_NAME => l_region_rec.function_name,
399 X_CHILDREN_VIEW_USAGE_NAME => l_region_rec.children_view_usage_name,
400 X_SEARCH_PANEL => l_region_rec.search_panel,
401 X_ADVANCED_SEARCH_PANEL =>l_region_rec.advanced_search_panel,
402 X_CUSTOMIZE_PANEL => l_region_rec.customize_panel,
403 X_DEFAULT_SEARCH_PANEL => l_region_rec.default_search_panel,
404 X_RESULTS_BASED_SEARCH => l_region_rec.results_based_search,
405 X_DISPLAY_GRAPH_TABLE => l_region_rec.display_graph_table,
406 X_DISABLE_HEADER => l_region_rec.disable_header,
407 X_STANDALONE => l_region_rec.standalone,
408 X_AUTO_CUSTOMIZATION_CRITERIA =>l_region_rec.auto_customization_criteria,
409 X_LAST_UPDATE_DATE => sysdate,
410 X_LAST_UPDATED_BY => X_USER_ID,
411 X_LAST_UPDATE_LOGIN => X_USER_ID,
412 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
413 X_ATTRIBUTE1 => X_ATTRIBUTE1,
414 X_ATTRIBUTE2 => X_ATTRIBUTE2,
415 X_ATTRIBUTE3 => X_ATTRIBUTE3,
416 X_ATTRIBUTE4 => X_ATTRIBUTE4,
417 X_ATTRIBUTE5 => X_ATTRIBUTE5,
418 X_ATTRIBUTE6 => X_ATTRIBUTE6,
419 X_ATTRIBUTE7 => X_ATTRIBUTE7,
420 X_ATTRIBUTE8 => X_ATTRIBUTE8,
421 X_ATTRIBUTE9 => X_ATTRIBUTE9,
422 X_ATTRIBUTE10 => X_ATTRIBUTE10,
423 X_ATTRIBUTE11 => X_ATTRIBUTE11,
424 X_ATTRIBUTE12 => X_ATTRIBUTE12,
425 X_ATTRIBUTE13 => X_ATTRIBUTE13,
426 X_ATTRIBUTE14 => X_ATTRIBUTE14,
427 X_ATTRIBUTE15 => X_ATTRIBUTE15);
428 end UPDATE_REGION_ROW;
429
430
431 procedure INSERT_REGION_ITEM_ROW (
432 X_ROWID in out NOCOPY VARCHAR2,
433 X_USER_ID in NUMBER,
434 X_REGION_APPLICATION_ID in NUMBER,
435 X_REGION_CODE in VARCHAR2,
436 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
437 X_ATTRIBUTE_CODE in VARCHAR2,
438 X_DISPLAY_SEQUENCE in number,
439 X_NODE_DISPLAY_FLAG in VARCHAR2,
440 X_NODE_QUERY_FLAG in VARCHAR2,
441 X_ATTRIBUTE_LABEL_LONG in VARCHAR2,
442 X_ATTRIBUTE_LABEL_LENGTH in NUMBER,
443 X_DISPLAY_VALUE_LENGTH in number,
444 X_ITEM_STYLE in VARCHAR2,
445 X_REQUIRED_FLAG in VARCHAR2,
446 X_NESTED_REGION_CODE IN VARCHAR2,
447 X_NESTED_REGION_APPL_ID IN NUMBER,
448 X_ATTRIBUTE_CATEGORY in VARCHAR2,
449 X_ATTRIBUTE1 in VARCHAR2,
450 X_ATTRIBUTE2 in VARCHAR2,
451 X_ATTRIBUTE3 in VARCHAR2,
452 X_ATTRIBUTE4 in VARCHAR2,
453 X_ATTRIBUTE5 in VARCHAR2,
454 X_ATTRIBUTE6 in VARCHAR2,
455 X_ATTRIBUTE7 in VARCHAR2,
456 X_ATTRIBUTE8 in VARCHAR2,
457 X_ATTRIBUTE9 in VARCHAR2,
458 X_ATTRIBUTE10 in VARCHAR2,
459 X_ATTRIBUTE11 in VARCHAR2,
460 X_ATTRIBUTE12 in VARCHAR2,
461 X_ATTRIBUTE13 in VARCHAR2,
462 X_ATTRIBUTE14 in VARCHAR2,
463 X_ATTRIBUTE15 in VARCHAR2,
464 X_URL in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR,
465 X_ORDER_SEQUENCE in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR,
466 X_ORDER_DIRECTION in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR) is
467
468 l_display_value_length number;
469 l_attributeCount number;
470 l_attribute_rowid varchar2(50);
471 l_url AK_REGION_ITEMS.URL%TYPE;
472 l_nested_region_appl_id number;
473
474 cursor cAttributeExists is
475 select count(1)
476 from ak_attributes
477 where attribute_code = X_ATTRIBUTE_CODE
478 and attribute_application_id = X_ATTRIBUTE_APPLICATION_ID;
479
480 begin
481
482 if cAttributeExists%ISOPEN then
483 CLOSE cAttributeExists;
484 end if;
485 OPEN cAttributeExists;
486 FETCH cAttributeExists INTO l_attributeCount;
487 CLOSE cAttributeExists;
488
489 if l_attributeCount = 0 then
490 -- Insert into Attributes
491
492 AK_ATTRIBUTES_PKG.INSERT_ROW (
493 X_ROWID => l_attribute_rowid,
494 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
495 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
496 X_ATTRIBUTE_LABEL_LENGTH => c_ATTR_LABEL_LENGTH,
497 X_ATTRIBUTE_VALUE_LENGTH => c_ATTR_VALUE_LENGTH,
498 X_BOLD => c_BOLD ,
499 X_ITALIC => c_ITALIC,
500 X_UPPER_CASE_FLAG => c_UPPER_CASE_FLAG,
501 X_VERTICAL_ALIGNMENT => c_VERTICAL_ALIGNMENT,
502 X_HORIZONTAL_ALIGNMENT => c_HORIZONTAL_ALIGNMENT,
503 X_DEFAULT_VALUE_VARCHAR2 => null,
504 X_DEFAULT_VALUE_NUMBER => null,
505 X_DEFAULT_VALUE_DATE => null,
506 X_LOV_REGION_CODE => null,
507 X_LOV_REGION_APPLICATION_ID => null,
508 X_DATA_TYPE => c_ATTR_DATATYPE,
509 X_DISPLAY_HEIGHT => null,
510 X_ITEM_STYLE => X_ITEM_STYLE,
511 X_CSS_CLASS_NAME => null,
512 X_CSS_LABEL_CLASS_NAME => null,
513 X_PRECISION => null,
514 X_EXPANSION => null,
515 X_ALS_MAX_LENGTH => null,
516 X_POPLIST_VIEWOBJECT => null,
517 X_POPLIST_DISPLAY_ATTRIBUTE => null,
518 X_POPLIST_VALUE_ATTRIBUTE => null,
519 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
520 X_ATTRIBUTE1 => null,
521 X_ATTRIBUTE2 => null,
522 X_ATTRIBUTE3 => null,
523 X_ATTRIBUTE4 => null,
524 X_ATTRIBUTE5 => null,
525 X_ATTRIBUTE6 => null,
526 X_ATTRIBUTE7 => null,
527 X_ATTRIBUTE8 => null,
528 X_ATTRIBUTE9 => null,
529 X_ATTRIBUTE10 => null,
530 X_ATTRIBUTE11 => null,
531 X_ATTRIBUTE12 => null,
532 X_ATTRIBUTE13 => null,
533 X_ATTRIBUTE14 => null,
534 X_ATTRIBUTE15 => null,
535 X_NAME => X_ATTRIBUTE_CODE,
536 X_ATTRIBUTE_LABEL_LONG => null,
537 X_ATTRIBUTE_LABEL_SHORT => null,
538 X_DESCRIPTION => null,
539 X_CREATION_DATE => sysdate,
540 X_CREATED_BY => X_USER_ID,
541 X_LAST_UPDATE_DATE => sysdate,
542 X_LAST_UPDATED_BY => X_USER_ID,
543 X_LAST_UPDATE_LOGIN => X_USER_ID);
544 end if;
545
546 if X_URL <> BIS_COMMON_UTILS.G_DEF_CHAR then
547 l_url := x_url;
548 end if;
549
550 l_nested_region_appl_id := x_nested_region_appl_id;
551 if (x_nested_region_code is null) then
552 l_nested_region_appl_id := null;
553 end if;
554
555
556 AK_REGION_ITEMS_PKG.INSERT_ROW (
557 X_ROWID => X_ROWID,
558 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
559 X_REGION_CODE => upper(X_REGION_CODE),
560 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
561 X_ATTRIBUTE_CODE => upper(X_ATTRIBUTE_CODE),
562 X_DISPLAY_SEQUENCE => X_DISPLAY_SEQUENCE,
563 X_NODE_DISPLAY_FLAG => X_NODE_DISPLAY_FLAG,
564 X_NODE_QUERY_FLAG => X_NODE_QUERY_FLAG,
565 X_ATTRIBUTE_LABEL_LENGTH => X_ATTRIBUTE_LABEL_LENGTH,
566 X_BOLD => c_BOLD,
567 X_ITALIC => c_ITALIC,
568 X_VERTICAL_ALIGNMENT => c_VERTICAL_ALIGNMENT,
569 X_HORIZONTAL_ALIGNMENT => c_HORIZONTAL_ALIGNMENT,
570 X_ITEM_STYLE => X_ITEM_STYLE,
571 X_OBJECT_ATTRIBUTE_FLAG => c_OBJECT_ATTRIBUTE_FLAG,
572 X_ATTRIBUTE_LABEL_LONG => X_ATTRIBUTE_LABEL_LONG,
573 X_DESCRIPTION => null,
574 X_SECURITY_CODE => null,
575 X_UPDATE_FLAG => c_UPDATE_FLAG,
576 X_REQUIRED_FLAG => X_REQUIRED_FLAG,
577 X_DISPLAY_VALUE_LENGTH => X_DISPLAY_VALUE_LENGTH,
578 X_LOV_REGION_APPLICATION_ID => null,
579 X_LOV_REGION_CODE => null,
580 X_LOV_FOREIGN_KEY_NAME => null,
581 X_LOV_ATTRIBUTE_APPLICATION_ID => null,
582 X_LOV_ATTRIBUTE_CODE => null,
583 X_LOV_DEFAULT_FLAG => null,
584 X_REGION_DEFAULTING_API_PKG => null,
585 X_REGION_DEFAULTING_API_PROC => null,
586 X_REGION_VALIDATION_API_PKG => null,
587 X_REGION_VALIDATION_API_PROC => null,
588 X_ORDER_SEQUENCE => X_ORDER_SEQUENCE,
589 X_ORDER_DIRECTION => x_ORDER_DIRECTION,
590 X_DEFAULT_VALUE_VARCHAR2 => null,
591 X_DEFAULT_VALUE_NUMBER => null,
592 X_DEFAULT_VALUE_DATE => null,
593 X_ITEM_NAME => replace(initcap(X_ATTRIBUTE_CODE), '_', ''),
594 X_DISPLAY_HEIGHT => c_DISPLAY_HEIGHT,
595 X_SUBMIT => c_SUBMIT,
596 X_ENCRYPT => c_ENCRYPT,
597 X_VIEW_USAGE_NAME => null,
598 X_VIEW_ATTRIBUTE_NAME => null,
599 X_CSS_CLASS_NAME => null,
600 X_CSS_LABEL_CLASS_NAME => null,
601 X_URL => l_URL,
602 X_POPLIST_VIEWOBJECT => null,
603 X_POPLIST_DISPLAY_ATTRIBUTE => null,
604 X_POPLIST_VALUE_ATTRIBUTE => null,
605 X_IMAGE_FILE_NAME => null,
606 X_NESTED_REGION_CODE => upper(X_NESTED_REGION_CODE),
607 X_NESTED_REGION_APPL_ID => l_NESTED_REGION_APPL_ID,
608 X_MENU_NAME => null,
609 X_FLEXFIELD_NAME => null,
610 X_FLEXFIELD_APPLICATION_ID => null,
611 X_TABULAR_FUNCTION_CODE => null,
612 X_TIP_TYPE => null,
613 X_TIP_MESSAGE_NAME => null,
614 X_TIP_MESSAGE_APPLICATION_ID => null,
615 X_FLEX_SEGMENT_LIST => null,
616 X_ENTITY_ID => null,
617 X_ANCHOR => null,
618 X_POPLIST_VIEW_USAGE_NAME => null,
619 X_USER_CUSTOMIZABLE => null,
620 X_ADMIN_CUSTOMIZABLE => c_ADMIN_CUSTOMIZABLE,
621 X_INVOKE_FUNCTION_NAME => null,
622 X_ATTRIBUTE_LABEL_SHORT => null,
623 X_EXPANSION => null,
624 X_ALS_MAX_LENGTH => null,
625 X_SORTBY_VIEW_ATTRIBUTE_NAME => null,
626 X_ICX_CUSTOM_CALL => null,
627 X_INITIAL_SORT_SEQUENCE => null,
628 X_CUSTOMIZATION_APPLICATION_ID => null,
629 X_CUSTOMIZATION_CODE => null,
630 X_CREATION_DATE => sysdate,
631 X_CREATED_BY => X_USER_ID,
632 X_LAST_UPDATE_DATE => sysdate,
633 X_LAST_UPDATED_BY => X_USER_ID,
634 X_LAST_UPDATE_LOGIN => X_USER_ID,
635 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
636 X_ATTRIBUTE1 => X_ATTRIBUTE1,
637 X_ATTRIBUTE2 => X_ATTRIBUTE2,
638 X_ATTRIBUTE3 => X_ATTRIBUTE3,
639 X_ATTRIBUTE4 => X_ATTRIBUTE4,
640 X_ATTRIBUTE5 => X_ATTRIBUTE5,
641 X_ATTRIBUTE6 => X_ATTRIBUTE6,
642 X_ATTRIBUTE7 => X_ATTRIBUTE7,
643 X_ATTRIBUTE8 => X_ATTRIBUTE8,
644 X_ATTRIBUTE9 => X_ATTRIBUTE9,
645 X_ATTRIBUTE10 => X_ATTRIBUTE10,
646 X_ATTRIBUTE11 => X_ATTRIBUTE11,
647 X_ATTRIBUTE12 => X_ATTRIBUTE12,
648 X_ATTRIBUTE13 => X_ATTRIBUTE13,
649 X_ATTRIBUTE14 => X_ATTRIBUTE14,
650 X_ATTRIBUTE15 => X_ATTRIBUTE15);
651
652 end INSERT_REGION_ITEM_ROW;
653
654
655 procedure UPDATE_REGION_ITEM_ROW (
656 X_USER_ID in NUMBER,
657 X_REGION_APPLICATION_ID in NUMBER,
658 X_REGION_CODE in VARCHAR2,
659 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
660 X_ATTRIBUTE_CODE in VARCHAR2,
661 X_DISPLAY_SEQUENCE in VARCHAR2,
662 X_NODE_DISPLAY_FLAG in VARCHAR2,
663 X_NODE_QUERY_FLAG in VARCHAR2,
664 X_ATTRIBUTE_LABEL_LONG in VARCHAR2,
665 X_ATTRIBUTE_LABEL_LENGTH in NUMBER,
666 X_DISPLAY_VALUE_LENGTH in number,
667 X_ITEM_STYLE in VARCHAR2,
668 X_REQUIRED_FLAG in VARCHAR2,
669 X_NESTED_REGION_CODE IN VARCHAR2,
670 X_NESTED_REGION_APPL_ID IN NUMBER,
671 X_ATTRIBUTE_CATEGORY in VARCHAR2,
672 X_ATTRIBUTE1 in VARCHAR2,
673 X_ATTRIBUTE2 in VARCHAR2,
674 X_ATTRIBUTE3 in VARCHAR2,
675 X_ATTRIBUTE4 in VARCHAR2,
676 X_ATTRIBUTE5 in VARCHAR2,
677 X_ATTRIBUTE6 in VARCHAR2,
678 X_ATTRIBUTE7 in VARCHAR2,
679 X_ATTRIBUTE8 in VARCHAR2,
680 X_ATTRIBUTE9 in VARCHAR2,
681 X_ATTRIBUTE10 in VARCHAR2,
682 X_ATTRIBUTE11 in VARCHAR2,
683 X_ATTRIBUTE12 in VARCHAR2,
684 X_ATTRIBUTE13 in VARCHAR2,
685 X_ATTRIBUTE14 in VARCHAR2,
686 X_ATTRIBUTE15 in VARCHAR2,
687 X_URL in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR,
688 X_ORDER_SEQUENCE in VARCHAR2 := NULL,
689 X_ORDER_DIRECTION in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR)
690 is
691
692 l_regionItem_rec AK_REGION_PUB.Item_Rec_Type;
693
694 cursor cRegionItem is
695 select display_sequence,
696 bold,
697 italic,
698 vertical_alignment,
699 horizontal_alignment,
700 item_style,
701 object_attribute_flag,
702 icx_custom_call,
703 update_flag,
704 required_flag,
705 security_code,
706 default_value_varchar2,
707 default_value_number,
708 default_value_date,
709 lov_region_application_id,
710 lov_region_code,
711 lov_foreign_key_name,
712 lov_attribute_application_id,
713 lov_attribute_code,
714 lov_default_flag,
715 region_defaulting_api_pkg,
716 region_defaulting_api_proc,
717 region_validation_api_pkg,
718 region_validation_api_proc,
719 order_sequence,
720 order_direction,
721 display_height,
722 submit,
723 encrypt,
724 css_class_name,
725 view_usage_name,
726 view_attribute_name,
727 url,
728 poplist_viewobject,
729 poplist_display_attribute,
730 poplist_value_attribute,
731 image_file_name,
732 item_name,
733 css_label_class_name,
734 menu_name,
735 flexfield_name,
736 flexfield_application_id,
737 tabular_function_code,
738 tip_type,
739 tip_message_name,
740 tip_message_application_id,
741 flex_segment_list,
742 entity_id,
743 anchor,
744 poplist_view_usage_name,
745 user_customizable,
746 sortby_view_attribute_name,
747 admin_customizable,
748 invoke_function_name,
749 expansion,
750 als_max_length,
751 initial_sort_sequence,
752 customization_application_id,
753 customization_code,
754 attribute_category,
755 attribute1,
756 attribute2,
757 attribute3,
758 attribute4,
759 attribute5,
760 attribute6,
761 attribute7,
762 attribute8,
763 attribute9,
764 attribute10,
765 attribute12,
766 attribute13,
767 attribute14,
768 attribute15,
769 attribute_label_short,
770 description
771 from ak_region_items_vl
772 where region_code = X_REGION_CODE
773 and region_application_id = X_REGION_APPLICATION_ID
774 and attribute_code = X_ATTRIBUTE_CODE
775 and attribute_application_id = X_ATTRIBUTE_APPLICATION_ID;
776
777
778 begin
779
780 -- Save the current data
781 if cRegionItem%ISOPEN then
782 CLOSE cRegionItem;
783 end if;
784 OPEN cRegionItem;
785 FETCH cRegionItem INTO
786 l_regionItem_rec.display_sequence,
787 l_regionItem_rec.bold,
788 l_regionItem_rec.italic,
789 l_regionItem_rec.vertical_alignment,
790 l_regionItem_rec.horizontal_alignment,
791 l_regionItem_rec.item_style,
792 l_regionItem_rec.object_attribute_flag,
793 l_regionItem_rec.icx_custom_call,
794 l_regionItem_rec.update_flag,
795 l_regionItem_rec.required_flag,
796 l_regionItem_rec.security_code,
797 l_regionItem_rec.default_value_varchar2,
798 l_regionItem_rec.default_value_number,
799 l_regionItem_rec.default_value_date,
800 l_regionItem_rec.lov_region_application_id,
801 l_regionItem_rec.lov_region_code,
802 l_regionItem_rec.lov_foreign_key_name,
803 l_regionItem_rec.lov_attribute_application_id,
804 l_regionItem_rec.lov_attribute_code,
805 l_regionItem_rec.lov_default_flag,
806 l_regionItem_rec.region_defaulting_api_pkg,
807 l_regionItem_rec.region_defaulting_api_proc,
808 l_regionItem_rec.region_validation_api_pkg,
809 l_regionItem_rec.region_validation_api_proc,
810 l_regionItem_rec.order_sequence,
811 l_regionItem_rec.order_direction,
812 l_regionItem_rec.display_height,
813 l_regionItem_rec.submit,
814 l_regionItem_rec.encrypt,
815 l_regionItem_rec.css_class_name,
816 l_regionItem_rec.view_usage_name,
817 l_regionItem_rec.view_attribute_name,
818 l_regionItem_rec.url,
819 l_regionItem_rec.poplist_viewobject,
820 l_regionItem_rec.poplist_display_attr,
821 l_regionItem_rec.poplist_value_attr,
822 l_regionItem_rec.image_file_name,
823 l_regionItem_rec.item_name,
824 l_regionItem_rec.css_label_class_name,
825 l_regionItem_rec.menu_name,
826 l_regionItem_rec.flexfield_name,
827 l_regionItem_rec.flexfield_application_id,
828 l_regionItem_rec.tabular_function_code,
829 l_regionItem_rec.tip_type,
830 l_regionItem_rec.tip_message_name,
831 l_regionItem_rec.tip_message_application_id,
832 l_regionItem_rec.flex_segment_list,
833 l_regionItem_rec.entity_id,
834 l_regionItem_rec.anchor,
835 l_regionItem_rec.poplist_view_usage_name,
836 l_regionItem_rec.user_customizable,
837 l_regionItem_rec.sortby_view_attribute_name,
838 l_regionItem_rec.admin_customizable,
839 l_regionItem_rec.invoke_function_name,
840 l_regionItem_rec.expansion,
841 l_regionItem_rec.als_max_length,
842 l_regionItem_rec.initial_sort_sequence,
843 l_regionItem_rec.customization_application_id,
844 l_regionItem_rec.customization_code,
845 l_regionItem_rec.attribute_category,
846 l_regionItem_rec.attribute1,
847 l_regionItem_rec.attribute2,
848 l_regionItem_rec.attribute3,
849 l_regionItem_rec.attribute4,
850 l_regionItem_rec.attribute5,
851 l_regionItem_rec.attribute6,
852 l_regionItem_rec.attribute7,
853 l_regionItem_rec.attribute8,
854 l_regionItem_rec.attribute9,
855 l_regionItem_rec.attribute10,
856 l_regionItem_rec.attribute12,
857 l_regionItem_rec.attribute13,
858 l_regionItem_rec.attribute14,
859 l_regionItem_rec.attribute15,
860 l_regionItem_rec.attribute_label_short,
861 l_regionItem_rec.description;
862 CLOSE cRegionItem;
863
864 l_regionItem_rec.url := x_url;
865
866 if X_ORDER_DIRECTION <> BIS_COMMON_UTILS.G_DEF_CHAR then
867 l_regionItem_rec.order_direction := x_ORDER_DIRECTION;
868 end if;
869
870 AK_REGION_ITEMS_PKG.UPDATE_ROW (
871 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
872 X_REGION_CODE => X_REGION_CODE,
873 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
874 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
875 X_DISPLAY_SEQUENCE => X_DISPLAY_SEQUENCE,
876 X_NODE_DISPLAY_FLAG => X_NODE_DISPLAY_FLAG,
877 X_NODE_QUERY_FLAG => X_NODE_QUERY_FLAG,
878 X_ATTRIBUTE_LABEL_LENGTH => X_ATTRIBUTE_LABEL_LENGTH,
879 X_BOLD => l_regionItem_rec.bold,
880 X_ITALIC => l_regionItem_rec.italic,
881 X_VERTICAL_ALIGNMENT => l_regionItem_rec.VERTICAL_ALIGNMENT,
882 X_HORIZONTAL_ALIGNMENT => l_regionItem_rec.HORIZONTAL_ALIGNMENT,
883 X_ITEM_STYLE => l_regionItem_rec.ITEM_STYLE,
884 X_OBJECT_ATTRIBUTE_FLAG => l_regionItem_rec.OBJECT_ATTRIBUTE_FLAG,
885 X_ATTRIBUTE_LABEL_LONG => X_ATTRIBUTE_LABEL_LONG,
886 X_DESCRIPTION => l_regionItem_rec.description,
887 X_SECURITY_CODE => l_regionItem_rec.security_code,
888 X_UPDATE_FLAG => l_regionItem_rec.UPDATE_FLAG,
889 X_REQUIRED_FLAG => l_regionItem_rec.REQUIRED_FLAG,
890 X_DISPLAY_VALUE_LENGTH => X_DISPLAY_VALUE_LENGTH,
891 X_LOV_REGION_APPLICATION_ID => l_regionItem_rec.lov_region_application_id,
892 X_LOV_REGION_CODE => l_regionItem_rec.lov_region_code,
893 X_LOV_FOREIGN_KEY_NAME => l_regionItem_rec.lov_foreign_key_name,
894 X_LOV_ATTRIBUTE_APPLICATION_ID => l_regionItem_rec.lov_attribute_application_id,
895 X_LOV_ATTRIBUTE_CODE => l_regionItem_rec.lov_attribute_code,
896 X_LOV_DEFAULT_FLAG => l_regionItem_rec.lov_default_flag,
897 X_REGION_DEFAULTING_API_PKG => l_regionItem_rec.region_defaulting_api_pkg,
898 X_REGION_DEFAULTING_API_PROC => l_regionItem_rec.region_defaulting_api_proc,
899 X_REGION_VALIDATION_API_PKG => l_regionItem_rec.region_validation_api_pkg,
900 X_REGION_VALIDATION_API_PROC => l_regionItem_rec.region_validation_api_proc,
901 X_ORDER_SEQUENCE => X_ORDER_SEQUENCE,
902 X_ORDER_DIRECTION => l_regionItem_rec.order_direction,
903 X_DEFAULT_VALUE_VARCHAR2 => l_regionItem_rec.default_value_varchar2,
904 X_DEFAULT_VALUE_NUMBER => l_regionItem_rec.default_value_number,
905 X_DEFAULT_VALUE_DATE => l_regionItem_rec.default_value_date,
906 X_ITEM_NAME => l_regionItem_rec.item_name,
907 X_DISPLAY_HEIGHT => l_regionItem_rec.display_height,
908 X_SUBMIT => l_regionItem_rec.submit,
909 X_ENCRYPT => l_regionItem_rec.encrypt,
910 X_VIEW_USAGE_NAME => l_regionItem_rec.view_usage_name,
911 X_VIEW_ATTRIBUTE_NAME => l_regionItem_rec.view_attribute_name,
912 X_CSS_CLASS_NAME => l_regionItem_rec.css_class_name,
913 X_CSS_LABEL_CLASS_NAME => l_regionItem_rec.css_label_class_name,
914 X_URL => l_regionItem_rec.url,
915 X_POPLIST_VIEWOBJECT => l_regionItem_rec.poplist_viewobject,
916 X_POPLIST_DISPLAY_ATTRIBUTE => l_regionItem_rec.poplist_display_attr,
917 X_POPLIST_VALUE_ATTRIBUTE => l_regionItem_rec.poplist_value_attr,
918 X_IMAGE_FILE_NAME => l_regionItem_rec.image_file_name,
919 X_NESTED_REGION_CODE => X_NESTED_REGION_CODE,
920 X_NESTED_REGION_APPL_ID => X_NESTED_REGION_APPL_ID,
921 X_MENU_NAME =>l_regionItem_rec.menu_name,
922 X_FLEXFIELD_NAME => l_regionItem_rec.flexfield_name,
923 X_FLEXFIELD_APPLICATION_ID => l_regionItem_rec.flexfield_application_id,
924 X_TABULAR_FUNCTION_CODE => l_regionItem_rec.tabular_function_code,
925 X_TIP_TYPE => l_regionItem_rec.tip_type,
926 X_TIP_MESSAGE_NAME => l_regionItem_rec.tip_message_name,
927 X_TIP_MESSAGE_APPLICATION_ID => l_regionItem_rec.tip_message_application_id,
928 X_FLEX_SEGMENT_LIST => l_regionItem_rec.flex_segment_list,
929 X_ENTITY_ID => l_regionItem_rec.entity_id,
930 X_ANCHOR => l_regionItem_rec.anchor,
931 X_POPLIST_VIEW_USAGE_NAME => l_regionItem_rec.poplist_view_usage_name,
932 X_USER_CUSTOMIZABLE => l_regionItem_rec.user_customizable,
933 X_ADMIN_CUSTOMIZABLE => l_regionItem_rec.admin_customizable,
934 X_INVOKE_FUNCTION_NAME => l_regionItem_rec.invoke_function_name,
935 X_EXPANSION =>l_regionItem_rec.expansion,
936 X_ALS_MAX_LENGTH => l_regionItem_rec.als_max_length,
937 X_SORTBY_VIEW_ATTRIBUTE_NAME =>l_regionItem_rec.sortby_view_attribute_name,
938 X_ICX_CUSTOM_CALL => l_regionItem_rec.icx_custom_call,
939 X_INITIAL_SORT_SEQUENCE => l_regionItem_rec.initial_sort_sequence,
940 X_CUSTOMIZATION_APPLICATION_ID => l_regionItem_rec.customization_application_id,
941 X_CUSTOMIZATION_CODE => l_regionItem_rec.customization_code,
942 X_LAST_UPDATE_DATE => sysdate,
943 X_LAST_UPDATED_BY => X_USER_ID,
944 X_LAST_UPDATE_LOGIN => X_USER_ID,
945 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
946 X_ATTRIBUTE1 => X_ATTRIBUTE1,
947 X_ATTRIBUTE2 => X_ATTRIBUTE2,
948 X_ATTRIBUTE3 => X_ATTRIBUTE3,
949 X_ATTRIBUTE4 => X_ATTRIBUTE4,
950 X_ATTRIBUTE5 => X_ATTRIBUTE5,
951 X_ATTRIBUTE6 => X_ATTRIBUTE6,
952 X_ATTRIBUTE7 => X_ATTRIBUTE7,
953 X_ATTRIBUTE8 => X_ATTRIBUTE8,
954 X_ATTRIBUTE9 => X_ATTRIBUTE9,
955 X_ATTRIBUTE10 => X_ATTRIBUTE10,
956 X_ATTRIBUTE11 => X_ATTRIBUTE11,
957 X_ATTRIBUTE12 => X_ATTRIBUTE12,
958 X_ATTRIBUTE13 => X_ATTRIBUTE13,
959 X_ATTRIBUTE14 => X_ATTRIBUTE14,
960 X_ATTRIBUTE15 => X_ATTRIBUTE15);
961
962 end UPDATE_REGION_ITEM_ROW;
963
964 --nbarik 10/01/03 - Delete Region Item row
965 PROCEDURE DELETE_REGION_ITEM_ROW (
966 X_REGION_APPLICATION_ID IN NUMBER,
967 X_REGION_CODE IN VARCHAR2,
968 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
969 X_ATTRIBUTE_CODE IN VARCHAR2
970 ) IS
971 BEGIN
972
973 AK_REGION_ITEMS_PKG.DELETE_ROW(
974 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
975 X_REGION_CODE => X_REGION_CODE,
976 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
977 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE
978 );
979
980 END DELETE_REGION_ITEM_ROW;
981
982 -- mdamle 12/25/2003 - overloaded for additional functionality & error messaging
983 procedure INSERT_REGION_ROW (
984 p_REGION_CODE in VARCHAR2
985 ,p_REGION_APPLICATION_ID in NUMBER
986 ,p_DATABASE_OBJECT_NAME in VARCHAR2
987 ,p_NAME in VARCHAR2
988 ,p_REGION_STYLE in VARCHAR2 := c_TABLE_LAYOUT_STYLE
989 ,p_DESCRIPTION in VARCHAR2 := NULL
990 ,p_APPL_MODULE_OBJECT_TYPE in VARCHAR2 := NULL
991 ,p_ATTRIBUTE_CATEGORY in VARCHAR2 := NULL
992 ,p_ATTRIBUTE1 in VARCHAR2 := NULL
993 ,p_ATTRIBUTE2 in VARCHAR2 := NULL
994 ,p_ATTRIBUTE3 in VARCHAR2 := NULL
995 ,p_ATTRIBUTE4 in VARCHAR2 := NULL
996 ,p_ATTRIBUTE5 in VARCHAR2 := NULL
997 ,p_ATTRIBUTE6 in VARCHAR2 := NULL
998 ,p_ATTRIBUTE7 in VARCHAR2 := NULL
999 ,p_ATTRIBUTE8 in VARCHAR2 := NULL
1000 ,p_ATTRIBUTE9 in VARCHAR2 := NULL
1001 ,p_ATTRIBUTE10 in VARCHAR2 := NULL
1002 ,p_ATTRIBUTE11 in VARCHAR2 := NULL
1003 ,p_ATTRIBUTE12 in VARCHAR2 := NULL
1004 ,p_ATTRIBUTE13 in VARCHAR2 := NULL
1005 ,p_ATTRIBUTE14 in VARCHAR2 := NULL
1006 ,p_ATTRIBUTE15 in VARCHAR2 := NULL
1007 ,x_return_status OUT NOCOPY VARCHAR2
1008 ,x_msg_count OUT NOCOPY NUMBER
1009 ,x_msg_data OUT NOCOPY VARCHAR2
1010 ) is
1011
1012 l_rowid Varchar2(30);
1013 l_image_file_name Varchar2(80) := null;
1014 begin
1015
1016 IF valid_database_object(p_DATABASE_OBJECT_NAME) and not AK_OBJECT_EXISTS(p_DATABASE_OBJECT_NAME ) THEN
1017 INSERT_AK_OBJECT(
1018 P_DATABASE_OBJECT_NAME => p_DATABASE_OBJECT_NAME ,
1019 P_APPLICATION_ID => p_REGION_APPLICATION_ID);
1020 END IF;
1021
1022
1023 if (p_REGION_STYLE = c_PAGE_LAYOUT_STYLE) then
1024 l_image_file_name := c_IMAGE_FILE_NAME;
1025 end if;
1026
1027 AK_REGIONS_PKG.INSERT_ROW(
1028 X_ROWID => l_ROWID,
1029 X_REGION_APPLICATION_ID => p_REGION_APPLICATION_ID,
1030 X_REGION_CODE => upper(p_REGION_CODE),
1031 X_DATABASE_OBJECT_NAME => p_DATABASE_OBJECT_NAME,
1032 X_REGION_STYLE => p_REGION_STYLE,
1033 X_NUM_COLUMNS => null,
1034 X_ICX_CUSTOM_CALL => null,
1035 X_NAME => p_NAME,
1036 X_DESCRIPTION => p_DESCRIPTION,
1037 X_REGION_DEFAULTING_API_PKG => null,
1038 X_REGION_DEFAULTING_API_PROC => null,
1039 X_REGION_VALIDATION_API_PKG => null,
1040 X_REGION_VALIDATION_API_PROC => null,
1041 X_APPL_MODULE_OBJECT_TYPE => p_appl_module_object_type,
1042 X_NUM_ROWS_DISPLAY => null,
1043 X_REGION_OBJECT_TYPE => null,
1044 X_IMAGE_FILE_NAME => l_image_file_name,
1045 X_ISFORM_FLAG => c_ISFORM_FLAG,
1046 X_HELP_TARGET => null,
1047 X_STYLE_SHEET_FILENAME => null,
1048 X_VERSION => null,
1049 X_APPLICATIONMODULE_USAGE_NAME=>null,
1050 X_ADD_INDEXED_CHILDREN => c_ADD_INDEXED_CHILDREN,
1051 X_CREATION_DATE => sysdate,
1052 X_CREATED_BY => fnd_global.user_id,
1053 X_LAST_UPDATE_DATE => sysdate,
1054 X_LAST_UPDATED_BY => fnd_global.user_id,
1055 X_LAST_UPDATE_LOGIN => fnd_global.user_id,
1056 X_ATTRIBUTE_CATEGORY => p_ATTRIBUTE_CATEGORY,
1057 X_ATTRIBUTE1 => p_ATTRIBUTE1,
1058 X_ATTRIBUTE2 => p_ATTRIBUTE2,
1059 X_ATTRIBUTE3 => p_ATTRIBUTE3,
1060 X_ATTRIBUTE4 => p_ATTRIBUTE4,
1061 X_ATTRIBUTE5 => p_ATTRIBUTE5,
1062 X_ATTRIBUTE6 => p_ATTRIBUTE6,
1063 X_ATTRIBUTE7 => p_ATTRIBUTE7,
1064 X_ATTRIBUTE8 => p_ATTRIBUTE8,
1065 X_ATTRIBUTE9 => p_ATTRIBUTE9,
1066 X_ATTRIBUTE10 => p_ATTRIBUTE10,
1067 X_ATTRIBUTE11 => p_ATTRIBUTE11,
1068 X_ATTRIBUTE12 => p_ATTRIBUTE12,
1069 X_ATTRIBUTE13 => p_ATTRIBUTE13,
1070 X_ATTRIBUTE14 => p_ATTRIBUTE14,
1071 X_ATTRIBUTE15 => p_ATTRIBUTE15);
1072
1073 EXCEPTION
1074 WHEN FND_API.G_EXC_ERROR THEN
1075 x_return_status := FND_API.G_RET_STS_ERROR;
1076 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1077 ,p_data => x_msg_data);
1078 WHEN OTHERS THEN
1079 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1080 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1081 ,p_data => x_msg_data);
1082 if (x_msg_data is null) then
1083 x_msg_data := 'BIS_AK_REGION_PUB.INSERT_ROW: ' || SQLERRM;
1084 end if;
1085
1086 end INSERT_REGION_ROW;
1087
1088 -- nbarik 02/10/04 - overloaded for region record type
1089 PROCEDURE INSERT_REGION_ROW
1090 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
1091 , p_Report_Region_Rec IN BIS_AK_REGION_PUB.Bis_Region_Rec_Type
1092 , x_return_status OUT NOCOPY VARCHAR2
1093 , x_msg_count OUT NOCOPY NUMBER
1094 , x_msg_data OUT NOCOPY VARCHAR2
1095 ) IS
1096
1097 l_rowid VARCHAR2(30);
1098 BEGIN
1099
1100 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1101
1102 IF valid_database_object(p_Report_Region_Rec.Database_Object_Name) and not AK_OBJECT_EXISTS(p_Report_Region_Rec.Database_Object_Name) THEN
1103 INSERT_AK_OBJECT(
1104 P_DATABASE_OBJECT_NAME => p_Report_Region_Rec.Database_Object_Name,
1105 P_APPLICATION_ID => p_Report_Region_Rec.Region_Application_Id);
1106 END IF;
1107
1108 AK_REGIONS_PKG.INSERT_ROW
1109 ( X_ROWID => l_rowid
1110 , X_REGION_APPLICATION_ID => p_Report_Region_Rec.Region_Application_Id
1111 , X_REGION_CODE => UPPER(p_Report_Region_Rec.Region_Code)
1112 , X_DATABASE_OBJECT_NAME => p_Report_Region_Rec.Database_Object_Name
1113 , X_REGION_STYLE => p_Report_Region_Rec.Region_Style
1114 , X_NUM_COLUMNS => NULL
1115 , X_ICX_CUSTOM_CALL => NULL
1116 , X_NAME => p_Report_Region_Rec.Region_Name
1117 , X_DESCRIPTION => p_Report_Region_Rec.Region_Description
1118 , X_REGION_DEFAULTING_API_PKG => NULL
1119 , X_REGION_DEFAULTING_API_PROC => NULL
1120 , X_REGION_VALIDATION_API_PKG => NULL
1121 , X_REGION_VALIDATION_API_PROC => NULL
1122 , X_APPL_MODULE_OBJECT_TYPE => NULL
1123 , X_NUM_ROWS_DISPLAY => p_Report_Region_Rec.Display_Rows
1124 , X_REGION_OBJECT_TYPE => p_Report_Region_Rec.Region_Object_Type
1125 , X_IMAGE_FILE_NAME => NULL
1126 , X_ISFORM_FLAG => c_ISFORM_FLAG
1127 , X_HELP_TARGET => p_Report_Region_Rec.Help_Target
1128 , X_STYLE_SHEET_FILENAME => NULL
1129 , X_VERSION => NULL
1130 , X_APPLICATIONMODULE_USAGE_NAME => NULL
1131 , X_ADD_INDEXED_CHILDREN => c_ADD_INDEXED_CHILDREN
1132 , X_CREATION_DATE => SYSDATE
1133 , X_CREATED_BY => fnd_global.user_id
1134 , X_LAST_UPDATE_DATE => SYSDATE
1135 , X_LAST_UPDATED_BY => fnd_global.user_id
1136 , X_LAST_UPDATE_LOGIN => fnd_global.user_id
1137 , X_ATTRIBUTE_CATEGORY => C_ATTRIBUTE_CATEGORY
1138 , X_ATTRIBUTE1 => p_Report_Region_Rec.Disable_View_By
1139 , X_ATTRIBUTE2 => p_Report_Region_Rec.No_Of_Portlet_Rows
1140 , X_ATTRIBUTE3 => p_Report_Region_Rec.Schedule
1141 , X_ATTRIBUTE4 => p_Report_Region_Rec.Header_File_Procedure
1142 , X_ATTRIBUTE5 => p_Report_Region_Rec.Footer_File_Procedure
1143 , X_ATTRIBUTE6 => p_Report_Region_Rec.Group_By
1144 , X_ATTRIBUTE7 => p_Report_Region_Rec.Order_By
1145 , X_ATTRIBUTE8 => p_Report_Region_Rec.Plsql_For_Report_Query
1146 , X_ATTRIBUTE9 => p_Report_Region_Rec.Display_Subtotals
1147 , X_ATTRIBUTE10 => p_Report_Region_Rec.Data_Source
1148 , X_ATTRIBUTE11 => p_Report_Region_Rec.Where_Clause
1149 , X_ATTRIBUTE12 => p_Report_Region_Rec.Dimension_Group
1150 , X_ATTRIBUTE13 => p_Report_Region_Rec.Parameter_Layout
1151 , X_ATTRIBUTE14 => NULL
1152 , X_ATTRIBUTE15 => NULL
1153 );
1154
1155 IF (p_commit = FND_API.G_TRUE) THEN
1156 COMMIT;
1157 END IF;
1158
1159 EXCEPTION
1160 WHEN FND_API.G_EXC_ERROR THEN
1161 x_return_status := FND_API.G_RET_STS_ERROR;
1162 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1163 ,p_data => x_msg_data);
1164 WHEN OTHERS THEN
1165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1166 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1167 ,p_data => x_msg_data);
1168 IF (x_msg_data IS NULL) THEN
1169 x_msg_data := 'BIS_AK_REGION_PUB.INSERT_REGION_ROW: ' || SQLERRM;
1170 END IF;
1171
1172 END INSERT_REGION_ROW;
1173
1174 procedure UPDATE_REGION_ROW (
1175 p_REGION_CODE in VARCHAR2
1176 ,p_REGION_APPLICATION_ID in NUMBER
1177 ,p_DATABASE_OBJECT_NAME in VARCHAR2
1178 ,p_NAME in VARCHAR2
1179 ,p_REGION_STYLE in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1180 ,p_DESCRIPTION in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1181 ,p_APPL_MODULE_OBJECT_TYPE in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1182 ,p_ATTRIBUTE_CATEGORY in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1183 ,p_ATTRIBUTE1 in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1184 ,p_ATTRIBUTE2 in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1185 ,p_ATTRIBUTE3 in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1186 ,p_ATTRIBUTE4 in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1187 ,p_ATTRIBUTE5 in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1188 ,p_ATTRIBUTE6 in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1189 ,p_ATTRIBUTE7 in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1190 ,p_ATTRIBUTE8 in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1191 ,p_ATTRIBUTE9 in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1192 ,p_ATTRIBUTE10 in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1193 ,p_ATTRIBUTE11 in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1194 ,p_ATTRIBUTE12 in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1195 ,p_ATTRIBUTE13 in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1196 ,p_ATTRIBUTE14 in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1197 ,p_ATTRIBUTE15 in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
1198 ,x_return_status OUT NOCOPY VARCHAR2
1199 ,x_msg_count OUT NOCOPY NUMBER
1200 ,x_msg_data OUT NOCOPY VARCHAR2
1201 ) is
1202
1203 l_region_rec AK_REGION_PUB.Region_Rec_Type;
1204
1205 cursor cRegion is
1206 select icx_custom_call,
1207 num_columns,
1208 region_defaulting_api_pkg,
1209 region_defaulting_api_proc,
1210 region_validation_api_pkg,
1211 region_validation_api_proc,
1212 applicationmodule_object_type,
1213 image_file_name,
1214 isform_flag,
1215 help_target,
1216 style_sheet_filename,
1217 version,
1218 applicationmodule_usage_name,
1219 add_indexed_children,
1220 stateful_flag,
1221 function_name,
1222 children_view_usage_name,
1223 search_panel,
1224 advanced_search_panel,
1225 customize_panel,
1226 default_search_panel,
1227 results_based_search,
1228 display_graph_table,
1229 disable_header,
1230 standalone,
1231 auto_customization_criteria,
1232 region_style,
1233 name,
1234 description,
1235 attribute_category,
1236 attribute1,
1237 attribute2,
1238 attribute3,
1239 attribute4,
1240 attribute5,
1241 attribute6,
1242 attribute7,
1243 attribute8,
1244 attribute9,
1245 attribute10,
1246 attribute11,
1247 attribute12,
1248 attribute13,
1249 attribute14,
1250 attribute15
1251 from ak_regions_vl
1252 where region_code = p_REGION_CODE
1253 and region_application_id = p_REGION_APPLICATION_ID;
1254
1255 begin
1256
1257 if cRegion%ISOPEN then
1258 CLOSE cRegion;
1259 end if;
1260 OPEN cRegion;
1261 FETCH cRegion INTO
1262 l_region_rec.icx_custom_call,
1263 l_region_rec.num_columns,
1264 l_region_rec.region_defaulting_api_pkg,
1265 l_region_rec.region_defaulting_api_proc,
1266 l_region_rec.region_validation_api_pkg,
1267 l_region_rec.region_validation_api_proc,
1268 l_region_rec.applicationmodule_object_type,
1269 l_region_rec.image_file_name,
1270 l_region_rec.isform_flag,
1271 l_region_rec.help_target,
1272 l_region_rec.style_sheet_filename,
1273 l_region_rec.version,
1274 l_region_rec.applicationmodule_usage_name,
1275 l_region_rec.add_indexed_children,
1276 l_region_rec.stateful_flag,
1277 l_region_rec.function_name,
1278 l_region_rec.children_view_usage_name,
1279 l_region_rec.search_panel,
1280 l_region_rec.advanced_search_panel,
1281 l_region_rec.customize_panel,
1282 l_region_rec.default_search_panel,
1283 l_region_rec.results_based_search,
1284 l_region_rec.display_graph_table,
1285 l_region_rec.disable_header,
1286 l_region_rec.standalone,
1287 l_region_rec.auto_customization_criteria,
1288 l_region_rec.region_style,
1289 l_region_rec.name,
1290 l_region_rec.description,
1291 l_region_rec.attribute_category,
1292 l_region_rec.attribute1,
1293 l_region_rec.attribute2,
1294 l_region_rec.attribute3,
1295 l_region_rec.attribute4,
1296 l_region_rec.attribute5,
1297 l_region_rec.attribute6,
1298 l_region_rec.attribute7,
1299 l_region_rec.attribute8,
1300 l_region_rec.attribute9,
1301 l_region_rec.attribute10,
1302 l_region_rec.attribute11,
1303 l_region_rec.attribute12,
1304 l_region_rec.attribute13,
1305 l_region_rec.attribute14,
1306 l_region_rec.attribute15;
1307 CLOSE cRegion;
1308
1309 if (p_region_style <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1310 l_region_rec.region_style := p_region_style;
1311 end if;
1312 IF (p_name <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1313 l_region_rec.name := p_name;
1314 END IF;
1315 if (p_description <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1316 l_region_rec.description := p_description;
1317 end if;
1318 if (p_appl_module_object_type <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1319 l_region_rec.applicationmodule_object_type := p_appl_module_object_type;
1320 end if;
1321 if (p_attribute_category <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1322 l_region_rec.attribute_category := p_attribute_category;
1323 end if;
1324 if (p_attribute1 <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1325 l_region_rec.attribute1 := p_attribute1;
1326 end if;
1327 if (p_attribute2 <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1328 l_region_rec.attribute2 := p_attribute2;
1329 end if;
1330 if (p_attribute3 <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1331 l_region_rec.attribute3 := p_attribute3;
1332 end if;
1333 if (p_attribute4 <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1334 l_region_rec.attribute4 := p_attribute4;
1335 end if;
1336 if (p_attribute5 <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1337 l_region_rec.attribute5 := p_attribute5;
1338 end if;
1339 if (p_attribute6 <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1340 l_region_rec.attribute6 := p_attribute6;
1341 end if;
1342 if (p_attribute7 <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1343 l_region_rec.attribute7 := p_attribute7;
1344 end if;
1345 if (p_attribute8 <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1346 l_region_rec.attribute8 := p_attribute8;
1347 end if;
1348 if (p_attribute9 <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1349 l_region_rec.attribute9 := p_attribute9;
1350 end if;
1351 if (p_attribute10 <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1352 l_region_rec.attribute10 := p_attribute10;
1353 end if;
1354 if (p_attribute11 <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1355 l_region_rec.attribute11 := p_attribute11;
1356 end if;
1357 if (p_attribute12 <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1358 l_region_rec.attribute12 := p_attribute12;
1359 end if;
1360 if (p_attribute13 <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1361 l_region_rec.attribute13 := p_attribute13;
1362 end if;
1363 if (p_attribute14 <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1364 l_region_rec.attribute14 := p_attribute14;
1365 end if;
1366 if (p_attribute15 <> BIS_COMMON_UTILS.G_DEF_CHAR) then
1367 l_region_rec.attribute15 := p_attribute15;
1368 end if;
1369
1370
1371 AK_REGIONS_PKG.UPDATE_ROW(
1372 X_REGION_APPLICATION_ID => p_REGION_APPLICATION_ID,
1373 X_REGION_CODE => p_REGION_CODE,
1374 X_DATABASE_OBJECT_NAME => p_DATABASE_OBJECT_NAME,
1375 X_REGION_STYLE => l_region_rec.region_style,
1376 X_NUM_COLUMNS =>l_region_rec.num_columns,
1377 X_ICX_CUSTOM_CALL => l_region_rec.icx_custom_call,
1378 X_NAME => l_region_rec.name,
1379 X_DESCRIPTION => l_region_rec.description,
1380 X_REGION_DEFAULTING_API_PKG => l_region_rec.region_defaulting_api_pkg,
1381 X_REGION_DEFAULTING_API_PROC => l_region_rec.region_defaulting_api_proc,
1382 X_REGION_VALIDATION_API_PKG => l_region_rec.region_validation_api_pkg,
1383 X_REGION_VALIDATION_API_PROC => l_region_rec.region_validation_api_proc,
1384 X_APPL_MODULE_OBJECT_TYPE => l_region_rec.applicationmodule_object_type,
1385 X_NUM_ROWS_DISPLAY => null,
1386 X_REGION_OBJECT_TYPE => Null,
1387 X_IMAGE_FILE_NAME => l_region_rec.image_file_name,
1388 X_ISFORM_FLAG => l_region_rec.isform_flag,
1389 X_HELP_TARGET => l_region_rec.help_target,
1390 X_STYLE_SHEET_FILENAME => l_region_rec.style_sheet_filename,
1391 X_VERSION => l_region_rec.version,
1392 X_APPLICATIONMODULE_USAGE_NAME => l_region_rec.applicationmodule_usage_name,
1393 X_ADD_INDEXED_CHILDREN => l_region_rec.add_indexed_children,
1394 X_STATEFUL_FLAG => l_region_rec.stateful_flag,
1395 X_FUNCTION_NAME => l_region_rec.function_name,
1396 X_CHILDREN_VIEW_USAGE_NAME => l_region_rec.children_view_usage_name,
1397 X_SEARCH_PANEL => l_region_rec.search_panel,
1398 X_ADVANCED_SEARCH_PANEL =>l_region_rec.advanced_search_panel,
1399 X_CUSTOMIZE_PANEL => l_region_rec.customize_panel,
1400 X_DEFAULT_SEARCH_PANEL => l_region_rec.default_search_panel,
1401 X_RESULTS_BASED_SEARCH => l_region_rec.results_based_search,
1402 X_DISPLAY_GRAPH_TABLE => l_region_rec.display_graph_table,
1403 X_DISABLE_HEADER => l_region_rec.disable_header,
1404 X_STANDALONE => l_region_rec.standalone,
1405 X_AUTO_CUSTOMIZATION_CRITERIA =>l_region_rec.auto_customization_criteria,
1406 X_LAST_UPDATE_DATE => sysdate,
1407 X_LAST_UPDATED_BY => fnd_global.user_id,
1408 X_LAST_UPDATE_LOGIN => fnd_global.user_id,
1409 X_ATTRIBUTE_CATEGORY => l_region_rec.ATTRIBUTE_CATEGORY,
1410 X_ATTRIBUTE1 => l_region_rec.attribute1,
1411 X_ATTRIBUTE2 => l_region_rec.attribute2,
1412 X_ATTRIBUTE3 => l_region_rec.attribute3,
1413 X_ATTRIBUTE4 => l_region_rec.attribute4,
1414 X_ATTRIBUTE5 => l_region_rec.attribute5,
1415 X_ATTRIBUTE6 => l_region_rec.attribute6,
1416 X_ATTRIBUTE7 => l_region_rec.attribute7,
1417 X_ATTRIBUTE8 => l_region_rec.attribute8,
1418 X_ATTRIBUTE9 => l_region_rec.attribute9,
1419 X_ATTRIBUTE10 => l_region_rec.attribute10,
1420 X_ATTRIBUTE11 => l_region_rec.attribute11,
1421 X_ATTRIBUTE12 => l_region_rec.attribute12,
1422 X_ATTRIBUTE13 => l_region_rec.attribute13,
1423 X_ATTRIBUTE14 => l_region_rec.attribute14,
1424 X_ATTRIBUTE15 => l_region_rec.attribute15);
1425
1426 EXCEPTION
1427 WHEN FND_API.G_EXC_ERROR THEN
1428 x_return_status := FND_API.G_RET_STS_ERROR;
1429 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1430 ,p_data => x_msg_data);
1431 WHEN OTHERS THEN
1432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1433 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1434 ,p_data => x_msg_data);
1435 if (x_msg_data is null) then
1436 x_msg_data := 'BIS_AK_REGION_PUB.UPDATE_REGION_ROW: ' || SQLERRM;
1437 end if;
1438
1439 end UPDATE_REGION_ROW;
1440
1441 -- nbarik 02/10/04 - overloaded for region record type
1442 PROCEDURE UPDATE_REGION_ROW
1443 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
1444 , p_Report_Region_Rec IN BIS_AK_REGION_PUB.Bis_Region_Rec_Type
1445 , x_return_status OUT NOCOPY VARCHAR2
1446 , x_msg_count OUT NOCOPY NUMBER
1447 , x_msg_data OUT NOCOPY VARCHAR2
1448 ) IS
1449
1450 l_region_rec AK_REGION_PUB.Region_Rec_Type;
1451
1452 CURSOR cRegion IS
1453 SELECT icx_custom_call,
1454 num_columns,
1455 region_defaulting_api_pkg,
1456 region_defaulting_api_proc,
1457 region_validation_api_pkg,
1458 region_validation_api_proc,
1459 applicationmodule_object_type,
1460 image_file_name,
1461 isform_flag,
1462 help_target,
1463 style_sheet_filename,
1464 version,
1465 applicationmodule_usage_name,
1466 add_indexed_children,
1467 stateful_flag,
1468 function_name,
1469 children_view_usage_name,
1470 search_panel,
1471 advanced_search_panel,
1472 customize_panel,
1473 default_search_panel,
1474 results_based_search,
1475 display_graph_table,
1476 disable_header,
1477 standalone,
1478 auto_customization_criteria,
1479 region_style,
1480 name,
1481 description,
1482 num_rows_display,
1483 attribute_category,
1484 attribute1,
1485 attribute2,
1486 attribute3,
1487 attribute4,
1488 attribute5,
1489 attribute6,
1490 attribute7,
1491 attribute8,
1492 attribute9,
1493 attribute10,
1494 attribute11,
1495 attribute12,
1496 attribute13,
1497 attribute14,
1498 attribute15
1499 FROM ak_regions_vl
1500 WHERE region_code = p_Report_Region_Rec.Region_Code
1501 AND region_application_id = p_Report_Region_Rec.Region_Application_Id;
1502
1503 BEGIN
1504
1505 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1506 IF cRegion%ISOPEN THEN
1507 CLOSE cRegion;
1508 END IF;
1509 OPEN cRegion;
1510 FETCH cRegion INTO
1511 l_region_rec.icx_custom_call,
1512 l_region_rec.num_columns,
1513 l_region_rec.region_defaulting_api_pkg,
1514 l_region_rec.region_defaulting_api_proc,
1515 l_region_rec.region_validation_api_pkg,
1516 l_region_rec.region_validation_api_proc,
1517 l_region_rec.applicationmodule_object_type,
1518 l_region_rec.image_file_name,
1519 l_region_rec.isform_flag,
1520 l_region_rec.help_target,
1521 l_region_rec.style_sheet_filename,
1522 l_region_rec.version,
1523 l_region_rec.applicationmodule_usage_name,
1524 l_region_rec.add_indexed_children,
1525 l_region_rec.stateful_flag,
1526 l_region_rec.function_name,
1527 l_region_rec.children_view_usage_name,
1528 l_region_rec.search_panel,
1529 l_region_rec.advanced_search_panel,
1530 l_region_rec.customize_panel,
1531 l_region_rec.default_search_panel,
1532 l_region_rec.results_based_search,
1533 l_region_rec.display_graph_table,
1534 l_region_rec.disable_header,
1535 l_region_rec.standalone,
1536 l_region_rec.auto_customization_criteria,
1537 l_region_rec.region_style,
1538 l_region_rec.name,
1539 l_region_rec.description,
1540 l_region_rec.num_rows_display,
1541 l_region_rec.attribute_category,
1542 l_region_rec.attribute1,
1543 l_region_rec.attribute2,
1544 l_region_rec.attribute3,
1545 l_region_rec.attribute4,
1546 l_region_rec.attribute5,
1547 l_region_rec.attribute6,
1548 l_region_rec.attribute7,
1549 l_region_rec.attribute8,
1550 l_region_rec.attribute9,
1551 l_region_rec.attribute10,
1552 l_region_rec.attribute11,
1553 l_region_rec.attribute12,
1554 l_region_rec.attribute13,
1555 l_region_rec.attribute14,
1556 l_region_rec.attribute15;
1557 CLOSE cRegion;
1558
1559 IF (p_Report_Region_Rec.Region_Style <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1560 l_region_rec.region_style := p_Report_Region_Rec.Region_Style;
1561 END IF;
1562 IF (p_Report_Region_Rec.Region_Name <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1563 l_region_rec.name := p_Report_Region_Rec.Region_Name;
1564 END IF;
1565 IF (p_Report_Region_Rec.Display_Rows <> BIS_COMMON_UTILS.G_DEF_NUM) THEN
1566 l_region_rec.num_rows_display := p_Report_Region_Rec.Display_Rows;
1567 END IF;
1568 IF (p_Report_Region_Rec.Region_Description <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1569 l_region_rec.description := p_Report_Region_Rec.Region_Description;
1570 END IF;
1571 IF (p_Report_Region_Rec.Disable_View_By <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1572 l_region_rec.attribute1 := p_Report_Region_Rec.Disable_View_By;
1573 END IF;
1574 IF (p_Report_Region_Rec.No_Of_Portlet_Rows <> BIS_COMMON_UTILS.G_DEF_NUM) THEN
1575 l_region_rec.attribute2 := p_Report_Region_Rec.No_Of_Portlet_Rows;
1576 END IF;
1577 IF (p_Report_Region_Rec.Schedule <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1578 l_region_rec.attribute3 := p_Report_Region_Rec.Schedule;
1579 END IF;
1580 IF (p_Report_Region_Rec.Header_File_Procedure <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1581 l_region_rec.attribute4 := p_Report_Region_Rec.Header_File_Procedure;
1582 END IF;
1583 IF (p_Report_Region_Rec.Footer_File_Procedure <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1584 l_region_rec.attribute5 := p_Report_Region_Rec.Footer_File_Procedure;
1585 END IF;
1586 IF (p_Report_Region_Rec.Group_By <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1587 l_region_rec.attribute6 := p_Report_Region_Rec.Group_By;
1588 END IF;
1589 IF (p_Report_Region_Rec.Order_By <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1590 l_region_rec.attribute7 := p_Report_Region_Rec.Order_By;
1591 END IF;
1592 IF (p_Report_Region_Rec.Plsql_For_Report_Query <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1593 l_region_rec.attribute8 := p_Report_Region_Rec.Plsql_For_Report_Query;
1594 END IF;
1595 IF (p_Report_Region_Rec.Display_Subtotals <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1596 l_region_rec.attribute9 := p_Report_Region_Rec.Display_Subtotals;
1597 END IF;
1598 IF (p_Report_Region_Rec.Data_Source <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1599 l_region_rec.attribute10 := p_Report_Region_Rec.Data_Source;
1600 END IF;
1601 IF (p_Report_Region_Rec.Where_Clause <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1602 l_region_rec.attribute11 := p_Report_Region_Rec.Where_Clause;
1603 END IF;
1604 IF (p_Report_Region_Rec.Dimension_Group <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1605 l_region_rec.attribute12 := p_Report_Region_Rec.Dimension_Group;
1606 END IF;
1607 IF (p_Report_Region_Rec.Parameter_Layout <> BIS_COMMON_UTILS.G_DEF_CHAR) THEN
1608 l_region_rec.attribute13 := p_Report_Region_Rec.Parameter_Layout;
1609 END IF;
1610
1611 AK_REGIONS_PKG.UPDATE_ROW(
1612 X_REGION_APPLICATION_ID => p_Report_Region_Rec.Region_Application_Id,
1613 X_REGION_CODE => p_Report_Region_Rec.Region_Code,
1614 X_DATABASE_OBJECT_NAME => p_Report_Region_Rec.Database_Object_Name,
1615 X_REGION_STYLE => l_region_rec.region_style,
1616 X_NUM_COLUMNS => l_region_rec.num_columns,
1617 X_ICX_CUSTOM_CALL => l_region_rec.icx_custom_call,
1618 X_NAME => l_region_rec.name,
1619 X_DESCRIPTION => l_region_rec.description,
1620 X_REGION_DEFAULTING_API_PKG => l_region_rec.region_defaulting_api_pkg,
1621 X_REGION_DEFAULTING_API_PROC => l_region_rec.region_defaulting_api_proc,
1622 X_REGION_VALIDATION_API_PKG => l_region_rec.region_validation_api_pkg,
1623 X_REGION_VALIDATION_API_PROC => l_region_rec.region_validation_api_proc,
1624 X_APPL_MODULE_OBJECT_TYPE => l_region_rec.applicationmodule_object_type,
1625 X_NUM_ROWS_DISPLAY => l_region_rec.num_rows_display,
1626 X_REGION_OBJECT_TYPE => p_Report_Region_Rec.Region_Object_Type,
1627 X_IMAGE_FILE_NAME => l_region_rec.image_file_name,
1628 X_ISFORM_FLAG => l_region_rec.isform_flag,
1629 X_HELP_TARGET => p_Report_Region_Rec.Help_Target,
1630 X_STYLE_SHEET_FILENAME => l_region_rec.style_sheet_filename,
1631 X_VERSION => l_region_rec.version,
1632 X_APPLICATIONMODULE_USAGE_NAME => l_region_rec.applicationmodule_usage_name,
1633 X_ADD_INDEXED_CHILDREN => l_region_rec.add_indexed_children,
1634 X_STATEFUL_FLAG => l_region_rec.stateful_flag,
1635 X_FUNCTION_NAME => l_region_rec.function_name,
1636 X_CHILDREN_VIEW_USAGE_NAME => l_region_rec.children_view_usage_name,
1637 X_SEARCH_PANEL => l_region_rec.search_panel,
1638 X_ADVANCED_SEARCH_PANEL =>l_region_rec.advanced_search_panel,
1639 X_CUSTOMIZE_PANEL => l_region_rec.customize_panel,
1640 X_DEFAULT_SEARCH_PANEL => l_region_rec.default_search_panel,
1641 X_RESULTS_BASED_SEARCH => l_region_rec.results_based_search,
1642 X_DISPLAY_GRAPH_TABLE => l_region_rec.display_graph_table,
1643 X_DISABLE_HEADER => l_region_rec.disable_header,
1644 X_STANDALONE => l_region_rec.standalone,
1645 X_AUTO_CUSTOMIZATION_CRITERIA =>l_region_rec.auto_customization_criteria,
1646 X_LAST_UPDATE_DATE => SYSDATE,
1647 X_LAST_UPDATED_BY => fnd_global.user_id,
1648 X_LAST_UPDATE_LOGIN => fnd_global.user_id,
1649 X_ATTRIBUTE_CATEGORY => l_region_rec.ATTRIBUTE_CATEGORY,
1650 X_ATTRIBUTE1 => l_region_rec.attribute1,
1651 X_ATTRIBUTE2 => l_region_rec.attribute2,
1652 X_ATTRIBUTE3 => l_region_rec.attribute3,
1653 X_ATTRIBUTE4 => l_region_rec.attribute4,
1654 X_ATTRIBUTE5 => l_region_rec.attribute5,
1655 X_ATTRIBUTE6 => l_region_rec.attribute6,
1656 X_ATTRIBUTE7 => l_region_rec.attribute7,
1657 X_ATTRIBUTE8 => l_region_rec.attribute8,
1658 X_ATTRIBUTE9 => l_region_rec.attribute9,
1659 X_ATTRIBUTE10 => l_region_rec.attribute10,
1660 X_ATTRIBUTE11 => l_region_rec.attribute11,
1661 X_ATTRIBUTE12 => l_region_rec.attribute12,
1662 X_ATTRIBUTE13 => l_region_rec.attribute13,
1663 X_ATTRIBUTE14 => l_region_rec.attribute14,
1664 X_ATTRIBUTE15 => l_region_rec.attribute15);
1665
1666 IF (p_commit = FND_API.G_TRUE) THEN
1667 COMMIT;
1668 END IF;
1669
1670 EXCEPTION
1671 WHEN FND_API.G_EXC_ERROR THEN
1672 x_return_status := FND_API.G_RET_STS_ERROR;
1673 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1674 ,p_data => x_msg_data);
1675 WHEN OTHERS THEN
1676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1677 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1678 ,p_data => x_msg_data);
1679 IF (x_msg_data IS NULL) THEN
1680 x_msg_data := 'BIS_AK_REGION_PUB.UPDATE_REGION_ROW: ' || SQLERRM;
1681 END IF;
1682
1683 END UPDATE_REGION_ROW;
1684
1685 -- nbarik - 04/05/04 - Enh 3546750 - BSC/PMV Integration - Added p_commit
1686 PROCEDURE DELETE_REGION_ROW
1687 (p_REGION_CODE in VARCHAR2
1688 ,p_REGION_APPLICATION_ID in NUMBER
1689 ,x_return_status OUT NOCOPY VARCHAR2
1690 ,x_msg_count OUT NOCOPY NUMBER
1691 ,x_msg_data OUT NOCOPY VARCHAR2
1692 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1693 ) IS
1694 BEGIN
1695
1696 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1697
1698 AK_REGIONS_PKG.DELETE_ROW(
1699 X_REGION_APPLICATION_ID => p_REGION_APPLICATION_ID,
1700 X_REGION_CODE => p_REGION_CODE
1701 );
1702
1703 IF (p_commit = FND_API.G_TRUE) THEN
1704 COMMIT;
1705 END IF;
1706
1707 EXCEPTION
1708 WHEN FND_API.G_EXC_ERROR THEN
1709 x_return_status := FND_API.G_RET_STS_ERROR;
1710 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1711 ,p_data => x_msg_data);
1712 WHEN OTHERS THEN
1713 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1714 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1715 ,p_data => x_msg_data);
1716 if (x_msg_data is null) then
1717 x_msg_data := 'BIS_AK_REGION_PUB.DELETE_REGION_ROW: ' || SQLERRM;
1718 end if;
1719
1720 END DELETE_REGION_ROW;
1721
1722 procedure INSERT_REGION_ITEM_ROW (
1723 p_REGION_CODE in VARCHAR2
1724 ,p_REGION_APPLICATION_ID in NUMBER
1725 ,p_ATTRIBUTE_CODE in VARCHAR2
1726 ,p_ATTRIBUTE_APPLICATION_ID in NUMBER
1727 ,p_DISPLAY_SEQUENCE in number
1728 ,p_NODE_DISPLAY_FLAG in VARCHAR2 := 'Y'
1729 ,p_ATTRIBUTE_LABEL_LONG in VARCHAR2 := NULL
1730 ,p_NESTED_REGION_CODE in VARCHAR2 := NULL
1731 ,p_NESTED_REGION_APPL_ID in NUMBER := NULL
1732 ,p_ATTRIBUTE_CATEGORY in VARCHAR2 := NULL
1733 ,p_ATTRIBUTE1 in VARCHAR2 := NULL
1734 ,p_ATTRIBUTE2 in VARCHAR2 := NULL
1735 ,p_ATTRIBUTE3 in VARCHAR2 := NULL
1736 ,p_ATTRIBUTE4 in VARCHAR2 := NULL
1737 ,p_ATTRIBUTE5 in VARCHAR2 := NULL
1738 ,p_ATTRIBUTE6 in VARCHAR2 := NULL
1739 ,p_ATTRIBUTE7 in VARCHAR2 := NULL
1740 ,p_ATTRIBUTE8 in VARCHAR2 := NULL
1741 ,p_ATTRIBUTE9 in VARCHAR2 := NULL
1742 ,p_ATTRIBUTE10 in VARCHAR2 := NULL
1743 ,p_ATTRIBUTE11 in VARCHAR2 := NULL
1744 ,p_ATTRIBUTE12 in VARCHAR2 := NULL
1745 ,p_ATTRIBUTE13 in VARCHAR2 := NULL
1746 ,p_ATTRIBUTE14 in VARCHAR2 := NULL
1747 ,p_ATTRIBUTE15 in VARCHAR2 := NULL
1748 ,p_URL in VARCHAR2 := NULL
1749 ,p_ORDER_SEQUENCE in VARCHAR2 := NULL
1750 ,p_ORDER_DIRECTION in VARCHAR2 := NULL
1751 ,x_return_status OUT NOCOPY VARCHAR2
1752 ,x_msg_count OUT NOCOPY NUMBER
1753 ,x_msg_data OUT NOCOPY VARCHAR2
1754 ) is
1755 l_label_length number;
1756 l_attributeCount number;
1757 l_attribute_rowid varchar2(30);
1758 l_rowid varchar2(30);
1759 l_item_style varchar2(30);
1760 l_nested_region_appl_id number;
1761 cursor cAttributeExists is
1762 select count(1)
1763 from ak_attributes
1764 where attribute_code = p_ATTRIBUTE_CODE
1765 and attribute_application_id = p_ATTRIBUTE_APPLICATION_ID;
1766
1767 begin
1768
1769 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1770 if cAttributeExists%ISOPEN then
1771 CLOSE cAttributeExists;
1772 end if;
1773 OPEN cAttributeExists;
1774 FETCH cAttributeExists INTO l_attributeCount;
1775 CLOSE cAttributeExists;
1776
1777 if l_attributeCount = 0 then
1778 -- Insert into Attributes
1779
1780 AK_ATTRIBUTES_PKG.INSERT_ROW (
1781 X_ROWID => l_attribute_rowid,
1782 X_ATTRIBUTE_APPLICATION_ID => p_ATTRIBUTE_APPLICATION_ID,
1783 X_ATTRIBUTE_CODE => p_ATTRIBUTE_CODE,
1784 X_ATTRIBUTE_LABEL_LENGTH => c_ATTR_LABEL_LENGTH,
1785 X_ATTRIBUTE_VALUE_LENGTH => c_ATTR_VALUE_LENGTH,
1786 X_BOLD => c_BOLD,
1787 X_ITALIC => c_ITALIC,
1788 X_UPPER_CASE_FLAG => c_UPPER_CASE_FLAG,
1789 X_VERTICAL_ALIGNMENT => c_VERTICAL_ALIGNMENT,
1790 X_HORIZONTAL_ALIGNMENT => c_HORIZONTAL_ALIGNMENT,
1791 X_DEFAULT_VALUE_VARCHAR2 => null,
1792 X_DEFAULT_VALUE_NUMBER => null,
1793 X_DEFAULT_VALUE_DATE => null,
1794 X_LOV_REGION_CODE => null,
1795 X_LOV_REGION_APPLICATION_ID => null,
1796 X_DATA_TYPE => c_ATTR_DATATYPE,
1797 X_DISPLAY_HEIGHT => null,
1798 X_ITEM_STYLE => c_TEXT_STYLE,
1799 X_CSS_CLASS_NAME => null,
1800 X_CSS_LABEL_CLASS_NAME => null,
1801 X_PRECISION => null,
1802 X_EXPANSION => null,
1803 X_ALS_MAX_LENGTH => null,
1804 X_POPLIST_VIEWOBJECT => null,
1805 X_POPLIST_DISPLAY_ATTRIBUTE => null,
1806 X_POPLIST_VALUE_ATTRIBUTE => null,
1807 X_ATTRIBUTE_CATEGORY => null,
1808 X_ATTRIBUTE1 => null,
1809 X_ATTRIBUTE2 => null,
1810 X_ATTRIBUTE3 => null,
1811 X_ATTRIBUTE4 => null,
1812 X_ATTRIBUTE5 => null,
1813 X_ATTRIBUTE6 => null,
1814 X_ATTRIBUTE7 => null,
1815 X_ATTRIBUTE8 => null,
1816 X_ATTRIBUTE9 => null,
1817 X_ATTRIBUTE10 => null,
1818 X_ATTRIBUTE11 => null,
1819 X_ATTRIBUTE12 => null,
1820 X_ATTRIBUTE13 => null,
1821 X_ATTRIBUTE14 => null,
1822 X_ATTRIBUTE15 => null,
1823 X_NAME => p_ATTRIBUTE_CODE,
1824 X_ATTRIBUTE_LABEL_LONG => null,
1825 X_ATTRIBUTE_LABEL_SHORT => null,
1826 X_DESCRIPTION => null,
1827 X_CREATION_DATE => sysdate,
1828 X_CREATED_BY => fnd_global.user_id,
1829 X_LAST_UPDATE_DATE => sysdate,
1830 X_LAST_UPDATED_BY => fnd_global.user_id,
1831 X_LAST_UPDATE_LOGIN => fnd_global.user_id);
1832 end if;
1833
1834 if p_Attribute_label_long is null then
1835 l_label_length := 0;
1836 else
1837 l_label_length := length(p_Attribute_label_long);
1838 end if;
1839
1840 if (p_nested_region_code is not null) then
1841 l_item_Style := c_NESTED_REGION_STYLE;
1842 else
1843 l_Item_style := c_TEXT_STYLE;
1844 end if;
1845
1846 l_nested_region_appl_id := p_nested_region_appl_id;
1847 if (p_nested_region_code is null) then
1848 l_nested_region_appl_id := null;
1849 end if;
1850
1851 AK_REGION_ITEMS_PKG.INSERT_ROW (
1852 X_ROWID => l_ROWID,
1853 X_REGION_APPLICATION_ID => p_REGION_APPLICATION_ID,
1854 X_REGION_CODE => upper(p_REGION_CODE),
1855 X_ATTRIBUTE_APPLICATION_ID => p_ATTRIBUTE_APPLICATION_ID,
1856 X_ATTRIBUTE_CODE => upper(p_ATTRIBUTE_CODE),
1857 X_DISPLAY_SEQUENCE => p_DISPLAY_SEQUENCE,
1858 X_NODE_DISPLAY_FLAG => c_NODE_DISPLAY_FLAG,
1859 X_NODE_QUERY_FLAG => c_NODE_QUERY_FLAG,
1860 X_ATTRIBUTE_LABEL_LENGTH => l_label_length,
1861 X_BOLD => c_BOLD,
1862 X_ITALIC => c_ITALIC,
1863 X_VERTICAL_ALIGNMENT => c_VERTICAL_ALIGNMENT,
1864 X_HORIZONTAL_ALIGNMENT => c_HORIZONTAL_ALIGNMENT,
1865 X_ITEM_STYLE => l_item_style,
1866 X_OBJECT_ATTRIBUTE_FLAG => c_OBJECT_ATTRIBUTE_FLAG,
1867 X_ATTRIBUTE_LABEL_LONG => p_ATTRIBUTE_LABEL_LONG,
1868 X_DESCRIPTION => null,
1869 X_SECURITY_CODE => null,
1870 X_UPDATE_FLAG => c_UPDATE_FLAG,
1871 X_REQUIRED_FLAG => c_REQUIRED_FLAG,
1872 X_DISPLAY_VALUE_LENGTH => 0,
1873 X_LOV_REGION_APPLICATION_ID => null,
1874 X_LOV_REGION_CODE => null,
1875 X_LOV_FOREIGN_KEY_NAME => null,
1876 X_LOV_ATTRIBUTE_APPLICATION_ID => null,
1877 X_LOV_ATTRIBUTE_CODE => null,
1878 X_LOV_DEFAULT_FLAG => null,
1879 X_REGION_DEFAULTING_API_PKG => null,
1880 X_REGION_DEFAULTING_API_PROC => null,
1881 X_REGION_VALIDATION_API_PKG => null,
1882 X_REGION_VALIDATION_API_PROC => null,
1883 X_ORDER_SEQUENCE => p_ORDER_SEQUENCE,
1884 X_ORDER_DIRECTION => p_ORDER_DIRECTION,
1885 X_DEFAULT_VALUE_VARCHAR2 => null,
1886 X_DEFAULT_VALUE_NUMBER => null,
1887 X_DEFAULT_VALUE_DATE => null,
1888 X_ITEM_NAME => replace(initcap(p_ATTRIBUTE_CODE), '_', ''),
1889 X_DISPLAY_HEIGHT => c_DISPLAY_HEIGHT,
1890 X_SUBMIT => c_SUBMIT,
1891 X_ENCRYPT => c_ENCRYPT,
1892 X_VIEW_USAGE_NAME => null,
1893 X_VIEW_ATTRIBUTE_NAME => null,
1894 X_CSS_CLASS_NAME => null,
1895 X_CSS_LABEL_CLASS_NAME => null,
1896 X_URL => p_URL,
1897 X_POPLIST_VIEWOBJECT => null,
1898 X_POPLIST_DISPLAY_ATTRIBUTE => null,
1899 X_POPLIST_VALUE_ATTRIBUTE => null,
1900 X_IMAGE_FILE_NAME => null,
1901 X_NESTED_REGION_CODE => upper(p_NESTED_REGION_CODE),
1902 X_NESTED_REGION_APPL_ID => l_NESTED_REGION_APPL_ID,
1903 X_MENU_NAME => null,
1904 X_FLEXFIELD_NAME => null,
1905 X_FLEXFIELD_APPLICATION_ID => null,
1906 X_TABULAR_FUNCTION_CODE => null,
1907 X_TIP_TYPE => null,
1908 X_TIP_MESSAGE_NAME => null,
1909 X_TIP_MESSAGE_APPLICATION_ID => null,
1910 X_FLEX_SEGMENT_LIST => null,
1911 X_ENTITY_ID => null,
1912 X_ANCHOR => null,
1913 X_POPLIST_VIEW_USAGE_NAME => null,
1914 X_USER_CUSTOMIZABLE => null,
1915 X_ADMIN_CUSTOMIZABLE => c_ADMIN_CUSTOMIZABLE,
1916 X_INVOKE_FUNCTION_NAME => null,
1917 X_ATTRIBUTE_LABEL_SHORT => null,
1918 X_EXPANSION => null,
1919 X_ALS_MAX_LENGTH => null,
1920 X_SORTBY_VIEW_ATTRIBUTE_NAME => null,
1921 X_ICX_CUSTOM_CALL => null,
1922 X_INITIAL_SORT_SEQUENCE => null,
1923 X_CUSTOMIZATION_APPLICATION_ID => null,
1924 X_CUSTOMIZATION_CODE => null,
1925 X_CREATION_DATE => sysdate,
1926 X_CREATED_BY => fnd_global.user_id,
1927 X_LAST_UPDATE_DATE => sysdate,
1928 X_LAST_UPDATED_BY => fnd_global.user_id,
1929 X_LAST_UPDATE_LOGIN => fnd_global.user_id,
1930 X_ATTRIBUTE_CATEGORY => p_ATTRIBUTE_CATEGORY,
1931 X_ATTRIBUTE1 => p_ATTRIBUTE1,
1932 X_ATTRIBUTE2 => p_ATTRIBUTE2,
1933 X_ATTRIBUTE3 => p_ATTRIBUTE3,
1934 X_ATTRIBUTE4 => p_ATTRIBUTE4,
1935 X_ATTRIBUTE5 => p_ATTRIBUTE5,
1936 X_ATTRIBUTE6 => p_ATTRIBUTE6,
1937 X_ATTRIBUTE7 => p_ATTRIBUTE7,
1938 X_ATTRIBUTE8 => p_ATTRIBUTE8,
1939 X_ATTRIBUTE9 => p_ATTRIBUTE9,
1940 X_ATTRIBUTE10 => p_ATTRIBUTE10,
1941 X_ATTRIBUTE11 => p_ATTRIBUTE11,
1942 X_ATTRIBUTE12 => p_ATTRIBUTE12,
1943 X_ATTRIBUTE13 => p_ATTRIBUTE13,
1944 X_ATTRIBUTE14 => p_ATTRIBUTE14,
1945 X_ATTRIBUTE15 => p_ATTRIBUTE15);
1946
1947 EXCEPTION
1948 WHEN FND_API.G_EXC_ERROR THEN
1949 x_return_status := FND_API.G_RET_STS_ERROR;
1950 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1951 ,p_data => x_msg_data);
1952 WHEN OTHERS THEN
1953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1954 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
1955 ,p_data => x_msg_data);
1956 if (x_msg_data is null) then
1957 x_msg_data := 'BIS_AK_REGION_PUB.INSERT_REGION_ITEM_ROW: ' || SQLERRM;
1958 end if;
1959
1960 end INSERT_REGION_ITEM_ROW;
1961
1962 -- nbarik 02/10/04 - overloaded for region record type
1963 PROCEDURE INSERT_REGION_ITEM_ROW
1964 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
1965 , p_region_code IN VARCHAR2
1966 , p_region_application_id IN NUMBER
1967 , p_Region_Item_Rec IN BIS_AK_REGION_PUB.Bis_Region_Item_Rec_Type
1968 , x_return_status OUT NOCOPY VARCHAR2
1969 , x_msg_count OUT NOCOPY NUMBER
1970 , x_msg_data OUT NOCOPY VARCHAR2
1971 ) IS
1972 l_attribute_rowid VARCHAR2(30);
1973 l_attributeCount NUMBER;
1974 l_label_length NUMBER;
1975 l_rowid VARCHAR2(30);
1976 l_Item_Style AK_REGION_ITEMS.ITEM_STYLE%TYPE;
1977
1978 CURSOR cAttributeExists IS
1979 SELECT COUNT(1)
1980 FROM ak_attributes
1981 WHERE attribute_code = p_Region_Item_Rec.Attribute_Code
1982 AND attribute_application_id = p_Region_Item_Rec.Attribute_Application_Id;
1983
1984 BEGIN
1985
1986 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1987
1988 IF cAttributeExists%ISOPEN THEN
1989 CLOSE cAttributeExists;
1990 END IF;
1991 OPEN cAttributeExists;
1992 FETCH cAttributeExists INTO l_attributeCount;
1993 CLOSE cAttributeExists;
1994
1995
1996 IF l_attributeCount = 0 THEN
1997 -- Insert into Attributes
1998 AK_ATTRIBUTES_PKG.INSERT_ROW (
1999 X_ROWID => l_attribute_rowid,
2000 X_ATTRIBUTE_APPLICATION_ID => p_Region_Item_Rec.Attribute_Application_Id,
2001 X_ATTRIBUTE_CODE => UPPER(p_Region_Item_Rec.Attribute_Code),
2002 X_ATTRIBUTE_LABEL_LENGTH => c_ATTR_LABEL_LENGTH,
2003 X_ATTRIBUTE_VALUE_LENGTH => c_ATTR_VALUE_LENGTH,
2004 X_BOLD => c_BOLD,
2005 X_ITALIC => c_ITALIC,
2006 X_UPPER_CASE_FLAG => c_UPPER_CASE_FLAG,
2007 X_VERTICAL_ALIGNMENT => c_VERTICAL_ALIGNMENT,
2008 X_HORIZONTAL_ALIGNMENT => c_HORIZONTAL_ALIGNMENT,
2009 X_DEFAULT_VALUE_VARCHAR2 => NULL,
2010 X_DEFAULT_VALUE_NUMBER => NULL,
2011 X_DEFAULT_VALUE_DATE => NULL,
2012 X_LOV_REGION_CODE => NULL,
2013 X_LOV_REGION_APPLICATION_ID => NULL,
2014 X_DATA_TYPE => c_ATTR_DATATYPE,
2015 X_DISPLAY_HEIGHT => NULL,
2016 X_ITEM_STYLE => c_TEXT_STYLE,
2017 X_CSS_CLASS_NAME => NULL,
2018 X_CSS_LABEL_CLASS_NAME => NULL,
2019 X_PRECISION => NULL,
2020 X_EXPANSION => NULL,
2021 X_ALS_MAX_LENGTH => NULL,
2022 X_POPLIST_VIEWOBJECT => NULL,
2023 X_POPLIST_DISPLAY_ATTRIBUTE => NULL,
2024 X_POPLIST_VALUE_ATTRIBUTE => NULL,
2025 X_ATTRIBUTE_CATEGORY => NULL,
2026 X_ATTRIBUTE1 => NULL,
2027 X_ATTRIBUTE2 => NULL,
2028 X_ATTRIBUTE3 => NULL,
2029 X_ATTRIBUTE4 => NULL,
2030 X_ATTRIBUTE5 => NULL,
2031 X_ATTRIBUTE6 => NULL,
2032 X_ATTRIBUTE7 => NULL,
2033 X_ATTRIBUTE8 => NULL,
2034 X_ATTRIBUTE9 => NULL,
2035 X_ATTRIBUTE10 => NULL,
2036 X_ATTRIBUTE11 => NULL,
2037 X_ATTRIBUTE12 => NULL,
2038 X_ATTRIBUTE13 => NULL,
2039 X_ATTRIBUTE14 => NULL,
2040 X_ATTRIBUTE15 => NULL,
2041 X_NAME => p_Region_Item_Rec.Attribute_Code,
2042 X_ATTRIBUTE_LABEL_LONG => NULL,
2043 X_ATTRIBUTE_LABEL_SHORT => NULL,
2044 X_DESCRIPTION => NULL,
2045 X_CREATION_DATE => SYSDATE,
2046 X_CREATED_BY => fnd_global.user_id,
2047 X_LAST_UPDATE_DATE => SYSDATE,
2048 X_LAST_UPDATED_BY => fnd_global.user_id,
2049 X_LAST_UPDATE_LOGIN => fnd_global.user_id);
2050 END IF;
2051
2052 IF p_Region_Item_Rec.Long_Label IS NULL THEN
2053 l_label_length := 0;
2054 ELSE
2055 l_label_length := LENGTH(p_Region_Item_Rec.Long_Label);
2056 END IF;
2057
2058
2059 IF p_Region_Item_Rec.Item_Style IS NULL THEN
2060 l_Item_Style := c_TEXT_STYLE;
2061 ELSE
2062 l_Item_Style := p_Region_Item_Rec.Item_Style;
2063 END IF;
2064
2065
2066 AK_REGION_ITEMS_PKG.INSERT_ROW (
2067 X_ROWID => l_rowid,
2068 X_REGION_APPLICATION_ID => p_region_application_id,
2069 X_REGION_CODE => UPPER(p_region_code),
2070 X_ATTRIBUTE_APPLICATION_ID => p_Region_Item_Rec.Attribute_Application_Id,
2071 X_ATTRIBUTE_CODE => UPPER(p_Region_Item_Rec.Attribute_Code),
2072 X_DISPLAY_SEQUENCE => p_Region_Item_Rec.Display_Sequence,
2073 X_NODE_DISPLAY_FLAG => p_Region_Item_Rec.Node_Display_Flag,
2074 X_NODE_QUERY_FLAG => p_Region_Item_Rec.Queryable_Flag,
2075 X_ATTRIBUTE_LABEL_LENGTH => l_label_length,
2076 X_BOLD => c_BOLD,
2077 X_ITALIC => c_ITALIC,
2078 X_VERTICAL_ALIGNMENT => c_VERTICAL_ALIGNMENT,
2079 X_HORIZONTAL_ALIGNMENT => c_HORIZONTAL_ALIGNMENT,
2080 X_ITEM_STYLE => l_Item_Style,
2081 X_OBJECT_ATTRIBUTE_FLAG => c_OBJECT_ATTRIBUTE_FLAG,
2082 X_ATTRIBUTE_LABEL_LONG => p_Region_Item_Rec.Long_Label,
2083 X_DESCRIPTION => NULL,
2084 X_SECURITY_CODE => NULL,
2085 X_UPDATE_FLAG => c_UPDATE_FLAG,
2086 X_REQUIRED_FLAG => p_Region_Item_Rec.Required_Flag,
2087 X_DISPLAY_VALUE_LENGTH => p_Region_Item_Rec.Display_Length,
2088 X_LOV_REGION_APPLICATION_ID => NULL,
2089 X_LOV_REGION_CODE => NULL,
2090 X_LOV_FOREIGN_KEY_NAME => NULL,
2091 X_LOV_ATTRIBUTE_APPLICATION_ID => NULL,
2092 X_LOV_ATTRIBUTE_CODE => NULL,
2093 X_LOV_DEFAULT_FLAG => NULL,
2094 X_REGION_DEFAULTING_API_PKG => NULL,
2095 X_REGION_DEFAULTING_API_PROC => NULL,
2096 X_REGION_VALIDATION_API_PKG => NULL,
2097 X_REGION_VALIDATION_API_PROC => NULL,
2098 X_ORDER_SEQUENCE => p_Region_Item_Rec.Sort_Sequence,
2099 X_ORDER_DIRECTION => p_Region_Item_Rec.Sort_Direction,
2100 X_DEFAULT_VALUE_VARCHAR2 => NULL,
2101 X_DEFAULT_VALUE_NUMBER => NULL,
2102 X_DEFAULT_VALUE_DATE => NULL,
2103 X_ITEM_NAME => REPLACE(INITCAP(p_Region_Item_Rec.Attribute_Code), '_', ''),
2104 X_DISPLAY_HEIGHT => c_DISPLAY_HEIGHT,
2105 X_SUBMIT => c_SUBMIT,
2106 X_ENCRYPT => c_ENCRYPT,
2107 X_VIEW_USAGE_NAME => NULL,
2108 X_VIEW_ATTRIBUTE_NAME => NULL,
2109 X_CSS_CLASS_NAME => NULL,
2110 X_CSS_LABEL_CLASS_NAME => NULL,
2111 X_URL => p_Region_Item_Rec.Url,
2112 X_POPLIST_VIEWOBJECT => NULL,
2113 X_POPLIST_DISPLAY_ATTRIBUTE => NULL,
2114 X_POPLIST_VALUE_ATTRIBUTE => NULL,
2115 X_IMAGE_FILE_NAME => NULL,
2116 X_NESTED_REGION_CODE => p_Region_Item_Rec.Nested_Region_Code,
2117 X_NESTED_REGION_APPL_ID => p_Region_Item_Rec.Nested_Region_Application_Id,
2118 X_MENU_NAME => NULL,
2119 X_FLEXFIELD_NAME => NULL,
2120 X_FLEXFIELD_APPLICATION_ID => NULL,
2121 X_TABULAR_FUNCTION_CODE => NULL,
2122 X_TIP_TYPE => NULL,
2123 X_TIP_MESSAGE_NAME => NULL,
2124 X_TIP_MESSAGE_APPLICATION_ID => NULL,
2125 X_FLEX_SEGMENT_LIST => NULL,
2126 X_ENTITY_ID => NULL,
2127 X_ANCHOR => NULL,
2128 X_POPLIST_VIEW_USAGE_NAME => NULL,
2129 X_USER_CUSTOMIZABLE => NULL,
2130 X_ADMIN_CUSTOMIZABLE => c_ADMIN_CUSTOMIZABLE,
2131 X_INVOKE_FUNCTION_NAME => NULL,
2132 X_ATTRIBUTE_LABEL_SHORT => NULL,
2133 X_EXPANSION => NULL,
2134 X_ALS_MAX_LENGTH => NULL,
2135 X_SORTBY_VIEW_ATTRIBUTE_NAME => NULL,
2136 X_ICX_CUSTOM_CALL => NULL,
2137 X_INITIAL_SORT_SEQUENCE => p_Region_Item_Rec.Initial_Sort_Sequence,
2138 X_CUSTOMIZATION_APPLICATION_ID => NULL,
2139 X_CUSTOMIZATION_CODE => NULL,
2140 X_CREATION_DATE => SYSDATE,
2141 X_CREATED_BY => fnd_global.user_id,
2142 X_LAST_UPDATE_DATE => SYSDATE,
2143 X_LAST_UPDATED_BY => fnd_global.user_id,
2144 X_LAST_UPDATE_LOGIN => fnd_global.user_id,
2145 X_ATTRIBUTE_CATEGORY => C_ATTRIBUTE_CATEGORY,
2146 X_ATTRIBUTE1 => p_Region_Item_Rec.Attribute_Type,
2147 X_ATTRIBUTE2 => p_Region_Item_Rec.Measure_Level,
2148 X_ATTRIBUTE3 => p_Region_Item_Rec.Base_Column,
2149 X_ATTRIBUTE4 => p_Region_Item_Rec.Lov_Where_Clause,
2150 X_ATTRIBUTE5 => p_Region_Item_Rec.Graph_Position,
2151 X_ATTRIBUTE6 => p_Region_Item_Rec.Graph_Style,
2152 X_ATTRIBUTE7 => p_Region_Item_Rec.Display_Format,
2153 X_ATTRIBUTE8 => p_Region_Item_Rec.Schedule,
2154 X_ATTRIBUTE9 => p_Region_Item_Rec.Aggregate_Function,
2155 X_ATTRIBUTE10 => p_Region_Item_Rec.Display_Total,
2156 X_ATTRIBUTE11 => p_Region_Item_Rec.Override_Hierarchy,
2157 X_ATTRIBUTE12 => NULL,
2158 X_ATTRIBUTE13 => p_Region_Item_Rec.Variance,
2159 X_ATTRIBUTE14 => p_Region_Item_Rec.Display_Type,
2160 X_ATTRIBUTE15 => p_Region_Item_Rec.Lov_Table);
2161
2162 IF (p_commit = FND_API.G_TRUE) THEN
2163 COMMIT;
2164 END IF;
2165
2166 EXCEPTION
2167 WHEN FND_API.G_EXC_ERROR THEN
2168 x_return_status := FND_API.G_RET_STS_ERROR;
2169 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2170 ,p_data => x_msg_data);
2171 WHEN OTHERS THEN
2172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2173 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2174 ,p_data => x_msg_data);
2175 IF (x_msg_data IS NULL) THEN
2176 x_msg_data := 'BIS_AK_REGION_PUB.INSERT_REGION_ITEM_ROW: ' || SQLERRM;
2177 END IF;
2178
2179 END INSERT_REGION_ITEM_ROW;
2180
2181 -- nbarik 02/10/04 - overloaded for region record type
2182 -- adrao 05/12/04 -- added Nested Region & Nested Region Application Id
2183 PROCEDURE UPDATE_REGION_ITEM_ROW
2184 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
2185 , p_region_code IN VARCHAR2
2186 , p_region_application_id IN NUMBER
2187 , p_Region_Item_Rec IN BIS_AK_REGION_PUB.Bis_Region_Item_Rec_Type
2188 , x_return_status OUT NOCOPY VARCHAR2
2189 , x_msg_count OUT NOCOPY NUMBER
2190 , x_msg_data OUT NOCOPY VARCHAR2
2191 ) IS
2192
2193 l_regionItem_rec AK_REGION_PUB.Item_Rec_Type;
2194 l_label_length NUMBER;
2195
2196 CURSOR cRegionItem IS
2197 SELECT display_sequence,
2198 bold,
2199 italic,
2200 vertical_alignment,
2201 horizontal_alignment,
2202 item_style,
2203 object_attribute_flag,
2204 icx_custom_call,
2205 update_flag,
2206 required_flag,
2207 security_code,
2208 default_value_varchar2,
2209 default_value_number,
2210 default_value_date,
2211 lov_region_application_id,
2212 lov_region_code,
2213 lov_foreign_key_name,
2214 lov_attribute_application_id,
2215 lov_attribute_code,
2216 lov_default_flag,
2217 region_defaulting_api_pkg,
2218 region_defaulting_api_proc,
2219 region_validation_api_pkg,
2220 region_validation_api_proc,
2221 order_sequence,
2222 order_direction,
2223 display_height,
2224 submit,
2225 encrypt,
2226 css_class_name,
2227 view_usage_name,
2228 view_attribute_name,
2229 nested_region_application_id,
2230 nested_region_code,
2231 url,
2232 poplist_viewobject,
2233 poplist_display_attribute,
2234 poplist_value_attribute,
2235 image_file_name,
2236 item_name,
2237 css_label_class_name,
2238 menu_name,
2239 flexfield_name,
2240 flexfield_application_id,
2241 tabular_function_code,
2242 tip_type,
2243 tip_message_name,
2244 tip_message_application_id,
2245 flex_segment_list,
2246 entity_id,
2247 anchor,
2248 poplist_view_usage_name,
2249 user_customizable,
2250 sortby_view_attribute_name,
2251 admin_customizable,
2252 invoke_function_name,
2253 expansion,
2254 als_max_length,
2255 initial_sort_sequence,
2256 customization_application_id,
2257 customization_code,
2258 attribute_category,
2259 attribute1,
2260 attribute2,
2261 attribute3,
2262 attribute4,
2263 attribute5,
2264 attribute6,
2265 attribute7,
2266 attribute8,
2267 attribute9,
2268 attribute10,
2269 attribute12,
2270 attribute13,
2271 attribute14,
2272 attribute15,
2273 attribute_label_short,
2274 description
2275 FROM ak_region_items_vl
2276 WHERE region_code = p_region_code
2277 AND region_application_id = p_region_application_id
2278 AND attribute_code = p_Region_Item_Rec.Attribute_Code
2279 AND attribute_application_id = p_Region_Item_Rec.Attribute_Application_Id;
2280
2281 c_region_item_rec cRegionItem%ROWTYPE;
2282
2283 BEGIN
2284 -- Save the current data
2285 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
2286
2287 IF cRegionItem%ISOPEN THEN
2288 CLOSE cRegionItem;
2289 END IF;
2290 OPEN cRegionItem;
2291 FETCH cRegionItem INTO c_region_item_rec;
2292 IF cRegionItem%FOUND THEN
2293 l_regionItem_rec.display_sequence := c_region_item_rec.display_sequence;
2294 l_regionItem_rec.bold := c_region_item_rec.bold;
2295 l_regionItem_rec.italic := c_region_item_rec.italic;
2296 l_regionItem_rec.vertical_alignment := c_region_item_rec.vertical_alignment;
2297 l_regionItem_rec.horizontal_alignment := c_region_item_rec.horizontal_alignment;
2298 l_regionItem_rec.item_style := c_region_item_rec.item_style;
2299 l_regionItem_rec.object_attribute_flag := c_region_item_rec.object_attribute_flag;
2300 l_regionItem_rec.icx_custom_call := c_region_item_rec.icx_custom_call;
2301 l_regionItem_rec.update_flag := c_region_item_rec.update_flag;
2302 l_regionItem_rec.required_flag := c_region_item_rec.required_flag;
2303 l_regionItem_rec.security_code := c_region_item_rec.security_code;
2304 l_regionItem_rec.default_value_varchar2 := c_region_item_rec.default_value_varchar2;
2305 l_regionItem_rec.default_value_number := c_region_item_rec.default_value_number;
2306 l_regionItem_rec.default_value_date := c_region_item_rec.default_value_date;
2307 l_regionItem_rec.lov_region_application_id := c_region_item_rec.lov_region_application_id;
2308 l_regionItem_rec.lov_region_code := c_region_item_rec.lov_region_code;
2309 l_regionItem_rec.lov_foreign_key_name := c_region_item_rec.lov_foreign_key_name;
2310 l_regionItem_rec.lov_attribute_application_id := c_region_item_rec.lov_attribute_application_id;
2311 l_regionItem_rec.lov_attribute_code := c_region_item_rec.lov_attribute_code;
2312 l_regionItem_rec.lov_default_flag := c_region_item_rec.lov_default_flag;
2313 l_regionItem_rec.region_defaulting_api_pkg := c_region_item_rec.region_defaulting_api_pkg;
2314 l_regionItem_rec.region_defaulting_api_proc := c_region_item_rec.region_defaulting_api_proc;
2315 l_regionItem_rec.region_validation_api_pkg := c_region_item_rec.region_validation_api_pkg;
2316 l_regionItem_rec.region_validation_api_proc := c_region_item_rec.region_validation_api_proc;
2317 l_regionItem_rec.order_sequence := c_region_item_rec.order_sequence;
2318 l_regionItem_rec.order_direction := c_region_item_rec.order_direction;
2319 l_regionItem_rec.display_height := c_region_item_rec.display_height;
2320 l_regionItem_rec.submit := c_region_item_rec.submit;
2321 l_regionItem_rec.encrypt := c_region_item_rec.encrypt;
2322 l_regionItem_rec.css_class_name := c_region_item_rec.css_class_name;
2323 l_regionItem_rec.view_usage_name := c_region_item_rec.view_usage_name;
2324 l_regionItem_rec.view_attribute_name := c_region_item_rec.view_attribute_name;
2325 l_regionItem_rec.nested_region_application_id := c_region_item_rec.nested_region_application_id;
2326 l_regionItem_rec.nested_region_code := c_region_item_rec.nested_region_code;
2327 l_regionItem_rec.url := c_region_item_rec.url;
2328 l_regionItem_rec.poplist_viewobject := c_region_item_rec.poplist_viewobject;
2329 l_regionItem_rec.poplist_display_attr := c_region_item_rec.poplist_display_attribute;
2330 l_regionItem_rec.poplist_value_attr := c_region_item_rec.poplist_value_attribute;
2331 l_regionItem_rec.image_file_name := c_region_item_rec.image_file_name;
2332 l_regionItem_rec.item_name := c_region_item_rec.item_name;
2333 l_regionItem_rec.css_label_class_name := c_region_item_rec.css_label_class_name;
2334 l_regionItem_rec.menu_name := c_region_item_rec.menu_name;
2335 l_regionItem_rec.flexfield_name := c_region_item_rec.flexfield_name;
2336 l_regionItem_rec.flexfield_application_id := c_region_item_rec.flexfield_application_id;
2337 l_regionItem_rec.tabular_function_code := c_region_item_rec.tabular_function_code;
2338 l_regionItem_rec.tip_type := c_region_item_rec.tip_type;
2339 l_regionItem_rec.tip_message_name := c_region_item_rec.tip_message_name;
2340 l_regionItem_rec.tip_message_application_id := c_region_item_rec.tip_message_application_id;
2341 l_regionItem_rec.flex_segment_list := c_region_item_rec.flex_segment_list;
2342 l_regionItem_rec.entity_id := c_region_item_rec.entity_id;
2343 l_regionItem_rec.anchor := c_region_item_rec.anchor;
2344 l_regionItem_rec.poplist_view_usage_name := c_region_item_rec.poplist_view_usage_name;
2345 l_regionItem_rec.user_customizable := c_region_item_rec.user_customizable;
2346 l_regionItem_rec.sortby_view_attribute_name := c_region_item_rec.sortby_view_attribute_name;
2347 l_regionItem_rec.admin_customizable := c_region_item_rec.admin_customizable;
2348 l_regionItem_rec.invoke_function_name := c_region_item_rec.invoke_function_name;
2349 l_regionItem_rec.expansion := c_region_item_rec.expansion;
2350 l_regionItem_rec.als_max_length := c_region_item_rec.als_max_length;
2351 l_regionItem_rec.initial_sort_sequence := c_region_item_rec.initial_sort_sequence;
2352 l_regionItem_rec.customization_application_id := c_region_item_rec.customization_application_id;
2353 l_regionItem_rec.customization_code := c_region_item_rec.customization_code;
2354 l_regionItem_rec.attribute_category := c_region_item_rec.attribute_category;
2355 l_regionItem_rec.attribute1 := c_region_item_rec.attribute1;
2356 l_regionItem_rec.attribute2 := c_region_item_rec.attribute2;
2357 l_regionItem_rec.attribute3 := c_region_item_rec.attribute3;
2358 l_regionItem_rec.attribute4 := c_region_item_rec.attribute4;
2359 l_regionItem_rec.attribute5 := c_region_item_rec.attribute5;
2360 l_regionItem_rec.attribute6 := c_region_item_rec.attribute6;
2361 l_regionItem_rec.attribute7 := c_region_item_rec.attribute7;
2362 l_regionItem_rec.attribute8 := c_region_item_rec.attribute8;
2363 l_regionItem_rec.attribute9 := c_region_item_rec.attribute9;
2364 l_regionItem_rec.attribute10 := c_region_item_rec.attribute10;
2365 l_regionItem_rec.attribute12 := c_region_item_rec.attribute12;
2366 l_regionItem_rec.attribute13 := c_region_item_rec.attribute13;
2367 l_regionItem_rec.attribute14 := c_region_item_rec.attribute14;
2368 l_regionItem_rec.attribute15 := c_region_item_rec.attribute15;
2369 l_regionItem_rec.attribute_label_short := c_region_item_rec.attribute_label_short;
2370 l_regionItem_rec.description := c_region_item_rec.description;
2371
2372 IF p_Region_Item_Rec.Long_Label IS NULL THEN
2373 l_label_length := 0;
2374 ELSE
2375 l_label_length := LENGTH(p_Region_Item_Rec.Long_Label);
2376 END IF;
2377
2378 AK_REGION_ITEMS_PKG.UPDATE_ROW (
2379 X_REGION_APPLICATION_ID => p_region_application_id,
2380 X_REGION_CODE => p_region_code,
2381 X_ATTRIBUTE_APPLICATION_ID => p_Region_Item_Rec.Attribute_Application_Id,
2382 X_ATTRIBUTE_CODE => p_Region_Item_Rec.Attribute_Code,
2383 X_DISPLAY_SEQUENCE => p_Region_Item_Rec.Display_Sequence,
2384 X_NODE_DISPLAY_FLAG => p_Region_Item_Rec.Node_Display_Flag,
2385 X_NODE_QUERY_FLAG => p_Region_Item_Rec.Queryable_Flag,
2386 X_ATTRIBUTE_LABEL_LENGTH => l_label_length,
2387 X_BOLD => l_regionItem_rec.bold,
2388 X_ITALIC => l_regionItem_rec.italic,
2389 X_VERTICAL_ALIGNMENT => l_regionItem_rec.VERTICAL_ALIGNMENT,
2390 X_HORIZONTAL_ALIGNMENT => l_regionItem_rec.HORIZONTAL_ALIGNMENT,
2391 X_ITEM_STYLE => l_regionItem_rec.ITEM_STYLE,
2392 X_OBJECT_ATTRIBUTE_FLAG => l_regionItem_rec.OBJECT_ATTRIBUTE_FLAG,
2393 X_ATTRIBUTE_LABEL_LONG => p_Region_Item_Rec.Long_Label,
2394 X_DESCRIPTION => l_regionItem_rec.description,
2395 X_SECURITY_CODE => l_regionItem_rec.security_code,
2396 X_UPDATE_FLAG => l_regionItem_rec.UPDATE_FLAG,
2397 X_REQUIRED_FLAG => p_Region_Item_Rec.Required_Flag,
2398 X_DISPLAY_VALUE_LENGTH => p_Region_Item_Rec.Display_Length,
2399 X_LOV_REGION_APPLICATION_ID => l_regionItem_rec.lov_region_application_id,
2400 X_LOV_REGION_CODE => l_regionItem_rec.lov_region_code,
2401 X_LOV_FOREIGN_KEY_NAME => l_regionItem_rec.lov_foreign_key_name,
2402 X_LOV_ATTRIBUTE_APPLICATION_ID => l_regionItem_rec.lov_attribute_application_id,
2403 X_LOV_ATTRIBUTE_CODE => l_regionItem_rec.lov_attribute_code,
2404 X_LOV_DEFAULT_FLAG => l_regionItem_rec.lov_default_flag,
2405 X_REGION_DEFAULTING_API_PKG => l_regionItem_rec.region_defaulting_api_pkg,
2406 X_REGION_DEFAULTING_API_PROC => l_regionItem_rec.region_defaulting_api_proc,
2407 X_REGION_VALIDATION_API_PKG => l_regionItem_rec.region_validation_api_pkg,
2408 X_REGION_VALIDATION_API_PROC => l_regionItem_rec.region_validation_api_proc,
2409 X_ORDER_SEQUENCE => p_Region_Item_Rec.Sort_Sequence,
2410 X_ORDER_DIRECTION => p_Region_Item_Rec.Sort_Direction,
2411 X_DEFAULT_VALUE_VARCHAR2 => l_regionItem_rec.default_value_varchar2,
2412 X_DEFAULT_VALUE_NUMBER => l_regionItem_rec.default_value_number,
2413 X_DEFAULT_VALUE_DATE => l_regionItem_rec.default_value_date,
2414 X_ITEM_NAME => l_regionItem_rec.item_name,
2415 X_DISPLAY_HEIGHT => l_regionItem_rec.display_height,
2416 X_SUBMIT => l_regionItem_rec.submit,
2417 X_ENCRYPT => l_regionItem_rec.encrypt,
2418 X_VIEW_USAGE_NAME => l_regionItem_rec.view_usage_name,
2419 X_VIEW_ATTRIBUTE_NAME => l_regionItem_rec.view_attribute_name,
2420 X_CSS_CLASS_NAME => l_regionItem_rec.css_class_name,
2421 X_CSS_LABEL_CLASS_NAME => l_regionItem_rec.css_label_class_name,
2422 X_URL => p_Region_Item_Rec.Url,
2423 X_POPLIST_VIEWOBJECT => l_regionItem_rec.poplist_viewobject,
2424 X_POPLIST_DISPLAY_ATTRIBUTE => l_regionItem_rec.poplist_display_attr,
2425 X_POPLIST_VALUE_ATTRIBUTE => l_regionItem_rec.poplist_value_attr,
2426 X_IMAGE_FILE_NAME => l_regionItem_rec.image_file_name,
2427 X_NESTED_REGION_CODE => l_regionItem_rec.nested_region_code,
2428 X_NESTED_REGION_APPL_ID => l_regionItem_rec.nested_region_application_id,
2429 X_MENU_NAME =>l_regionItem_rec.menu_name,
2430 X_FLEXFIELD_NAME => l_regionItem_rec.flexfield_name,
2431 X_FLEXFIELD_APPLICATION_ID => l_regionItem_rec.flexfield_application_id,
2432 X_TABULAR_FUNCTION_CODE => l_regionItem_rec.tabular_function_code,
2433 X_TIP_TYPE => l_regionItem_rec.tip_type,
2434 X_TIP_MESSAGE_NAME => l_regionItem_rec.tip_message_name,
2435 X_TIP_MESSAGE_APPLICATION_ID => l_regionItem_rec.tip_message_application_id,
2436 X_FLEX_SEGMENT_LIST => l_regionItem_rec.flex_segment_list,
2437 X_ENTITY_ID => l_regionItem_rec.entity_id,
2438 X_ANCHOR => l_regionItem_rec.anchor,
2439 X_POPLIST_VIEW_USAGE_NAME => l_regionItem_rec.poplist_view_usage_name,
2440 X_USER_CUSTOMIZABLE => l_regionItem_rec.user_customizable,
2441 X_ADMIN_CUSTOMIZABLE => l_regionItem_rec.admin_customizable,
2442 X_INVOKE_FUNCTION_NAME => l_regionItem_rec.invoke_function_name,
2443 X_EXPANSION =>l_regionItem_rec.expansion,
2444 X_ALS_MAX_LENGTH => l_regionItem_rec.als_max_length,
2445 X_SORTBY_VIEW_ATTRIBUTE_NAME =>l_regionItem_rec.sortby_view_attribute_name,
2446 X_ICX_CUSTOM_CALL => l_regionItem_rec.icx_custom_call,
2447 X_INITIAL_SORT_SEQUENCE => p_Region_Item_Rec.Initial_Sort_Sequence,
2448 X_CUSTOMIZATION_APPLICATION_ID => l_regionItem_rec.customization_application_id,
2449 X_CUSTOMIZATION_CODE => l_regionItem_rec.customization_code,
2450 X_LAST_UPDATE_DATE => SYSDATE,
2451 X_LAST_UPDATED_BY => fnd_global.user_id,
2452 X_LAST_UPDATE_LOGIN => fnd_global.user_id,
2453 X_ATTRIBUTE_CATEGORY => C_ATTRIBUTE_CATEGORY,
2454 X_ATTRIBUTE1 => p_Region_Item_Rec.Attribute_Type,
2455 X_ATTRIBUTE2 => p_Region_Item_Rec.Measure_Level,
2456 X_ATTRIBUTE3 => p_Region_Item_Rec.Base_Column,
2457 X_ATTRIBUTE4 => p_Region_Item_Rec.Lov_Where_Clause,
2458 X_ATTRIBUTE5 => p_Region_Item_Rec.Graph_Position,
2459 X_ATTRIBUTE6 => p_Region_Item_Rec.Graph_Style,
2460 X_ATTRIBUTE7 => p_Region_Item_Rec.Display_Format,
2461 X_ATTRIBUTE8 => p_Region_Item_Rec.Schedule,
2462 X_ATTRIBUTE9 => p_Region_Item_Rec.Aggregate_Function,
2463 X_ATTRIBUTE10 => p_Region_Item_Rec.Display_Total,
2464 X_ATTRIBUTE11 => p_Region_Item_Rec.Override_Hierarchy,
2465 X_ATTRIBUTE12 => NULL,
2466 X_ATTRIBUTE13 => p_Region_Item_Rec.Variance,
2467 X_ATTRIBUTE14 => p_Region_Item_Rec.Display_Type,
2468 X_ATTRIBUTE15 => p_Region_Item_Rec.Lov_Table);
2469
2470 IF (p_commit = FND_API.G_TRUE) THEN
2471 COMMIT;
2472 END IF;
2473
2474 END IF;
2475 CLOSE cRegionItem;
2476
2477 EXCEPTION
2478 WHEN FND_API.G_EXC_ERROR THEN
2479 x_return_status := FND_API.G_RET_STS_ERROR;
2480 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2481 ,p_data => x_msg_data);
2482 WHEN OTHERS THEN
2483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2484 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2485 ,p_data => x_msg_data);
2486 IF (x_msg_data IS NULL) THEN
2487 x_msg_data := 'BIS_AK_REGION_PUB.UPDATE_REGION_ITEM_ROW: ' || SQLERRM;
2488 END IF;
2489
2490 END UPDATE_REGION_ITEM_ROW;
2491
2492 -- nbarik - 04/05/04 - Enh 3546750 - BSC/PMV Integration - Added p_commit
2493 PROCEDURE DELETE_REGION_ITEM_ROW
2494 (p_REGION_CODE in VARCHAR2
2495 ,p_REGION_APPLICATION_ID in NUMBER
2496 ,p_ATTRIBUTE_CODE in VARCHAR2
2497 ,p_ATTRIBUTE_APPLICATION_ID in NUMBER
2498 ,x_return_status OUT NOCOPY VARCHAR2
2499 ,x_msg_count OUT NOCOPY NUMBER
2500 ,x_msg_data OUT NOCOPY VARCHAR2
2501 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2502 ) IS
2503 BEGIN
2504
2505 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
2506
2507 AK_REGION_ITEMS_PKG.DELETE_ROW(
2508 X_REGION_APPLICATION_ID => p_REGION_APPLICATION_ID,
2509 X_REGION_CODE => p_REGION_CODE,
2510 X_ATTRIBUTE_APPLICATION_ID => p_ATTRIBUTE_APPLICATION_ID,
2511 X_ATTRIBUTE_CODE => p_ATTRIBUTE_CODE
2512 );
2513
2514 IF (p_commit = FND_API.G_TRUE) THEN
2515 COMMIT;
2516 END IF;
2517
2518 EXCEPTION
2519 WHEN FND_API.G_EXC_ERROR THEN
2520 x_return_status := FND_API.G_RET_STS_ERROR;
2521 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2522 ,p_data => x_msg_data);
2523 WHEN OTHERS THEN
2524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2525 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2526 ,p_data => x_msg_data);
2527 if (x_msg_data is null) then
2528 x_msg_data := 'BIS_AK_REGION_PUB.DELETE_REGION_ITEM_ROW: ' || SQLERRM;
2529 end if;
2530
2531 END DELETE_REGION_ITEM_ROW;
2532
2533
2534 PROCEDURE DELETE_REGION_AND_REGION_ITEMS(
2535 p_REGION_CODE IN VARCHAR2
2536 ,p_REGION_APPLICATION_ID IN NUMBER
2537 ,x_return_status OUT NOCOPY VARCHAR2
2538 ,x_msg_count OUT NOCOPY NUMBER
2539 ,x_msg_data OUT NOCOPY VARCHAR2
2540 ) is
2541
2542 begin
2543
2544 delete_region_items(
2545 p_REGION_CODE => p_REGION_CODE,
2546 p_REGION_APPLICATION_ID => p_REGION_APPLICATION_ID,
2547 x_return_status => x_return_status,
2548 x_msg_count => x_msg_count,
2549 x_msg_data =>x_msg_data);
2550
2551
2552 BIS_AK_REGION_PUB.DELETE_REGION_ROW(
2553 p_REGION_CODE => p_REGION_CODE ,
2554 p_REGION_APPLICATION_ID => p_REGION_APPLICATION_ID,
2555 x_return_status => x_return_status,
2556 x_msg_count => x_msg_count,
2557 x_msg_data =>x_msg_data);
2558
2559 delete_ext_region_items(
2560 p_REGION_CODE => p_REGION_CODE,
2561 p_REGION_APPLICATION_ID => p_REGION_APPLICATION_ID,
2562 x_return_status => x_return_status,
2563 x_msg_count => x_msg_count,
2564 x_msg_data =>x_msg_data);
2565
2566 BIS_REGION_EXTENSION_PVT.DELETE_REGION_EXTN_RECORD(
2567 p_commit => FND_API.G_FALSE,
2568 pRegionCode => p_REGION_CODE,
2569 pRegionAppId => p_REGION_APPLICATION_ID);
2570
2571 BIS_CUSTOMIZATIONS_PVT.delete_region_customizations
2572 ( p_region_code => p_region_code
2573 , p_region_application_id => p_region_application_id
2574 , x_return_status => x_return_status
2575 , x_msg_count => x_msg_count
2576 , x_msg_data => x_msg_data
2577 );
2578
2579 EXCEPTION
2580 WHEN FND_API.G_EXC_ERROR THEN
2581 x_return_status := FND_API.G_RET_STS_ERROR;
2582 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2583 ,p_data => x_msg_data);
2584 WHEN OTHERS THEN
2585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2586 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2587 ,p_data => x_msg_data);
2588 if (x_msg_data is null) then
2589 x_msg_data := 'BIS_AK_REGION_PUB.DELETE_REGION_AND_REGION_ITEMS: ' || SQLERRM;
2590 end if;
2591
2592
2593 END DELETE_REGION_AND_REGION_ITEMS;
2594
2595
2596 PROCEDURE DELETE_REGION_ITEMS (
2597 p_REGION_CODE IN VARCHAR2
2598 ,p_REGION_APPLICATION_ID IN NUMBER
2599 ,x_return_status OUT NOCOPY VARCHAR2
2600 ,x_msg_count OUT NOCOPY NUMBER
2601 ,x_msg_data OUT NOCOPY VARCHAR2
2602 ) is
2603
2604 cursor items_cursor IS
2605 select attribute_code, attribute_application_id
2606 from ak_region_items
2607 where region_code = p_REGION_CODE
2608 and region_application_id = p_REGION_APPLICATION_ID;
2609
2610 begin
2611 if items_cursor%ISOPEN then
2612 close items_cursor;
2613 end if;
2614
2615 for cr in items_cursor loop
2616 BIS_AK_REGION_PUB.DELETE_REGION_ITEM_ROW(
2617 p_REGION_CODE => p_REGION_CODE,
2618 p_REGION_APPLICATION_ID => p_REGION_APPLICATION_ID,
2619 p_ATTRIBUTE_CODE => cr.attribute_code,
2620 p_ATTRIBUTE_APPLICATION_ID => cr.attribute_application_id,
2621 x_return_status => x_return_status,
2622 x_msg_count => x_msg_count,
2623 x_msg_data =>x_msg_data);
2624 end loop;
2625
2626 if items_cursor%ISOPEN then
2627 close items_cursor;
2628 end if;
2629
2630 EXCEPTION
2631 WHEN FND_API.G_EXC_ERROR THEN
2632 x_return_status := FND_API.G_RET_STS_ERROR;
2633 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2634 ,p_data => x_msg_data);
2635 WHEN OTHERS THEN
2636 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2637 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2638 ,p_data => x_msg_data);
2639 if (x_msg_data is null) then
2640 x_msg_data := 'BIS_AK_REGION_PUB.DELETE_REGION_ITEMS: ' || SQLERRM;
2641 end if;
2642
2643
2644 END DELETE_REGION_ITEMS;
2645
2646 PROCEDURE DELETE_EXT_REGION_ITEMS (
2647 p_REGION_CODE IN VARCHAR2
2648 ,p_REGION_APPLICATION_ID IN NUMBER
2649 ,x_return_status OUT NOCOPY VARCHAR2
2650 ,x_msg_count OUT NOCOPY NUMBER
2651 ,x_msg_data OUT NOCOPY VARCHAR2
2652 ) is
2653
2654 cursor items_cursor IS
2655 select attribute_code, attribute_application_id
2656 from bis_ak_region_item_extension
2657 where region_code = p_REGION_CODE
2658 and region_application_id = p_REGION_APPLICATION_ID;
2659
2660 begin
2661 if items_cursor%ISOPEN then
2662 close items_cursor;
2663 end if;
2664
2665 for cr in items_cursor loop
2666 BIS_REGION_ITEM_EXTENSION_PVT.DELETE_REGION_ITEM_RECORD(
2667 p_commit => FND_API.G_FALSE,
2668 pRegionCode => p_REGION_CODE,
2669 pRegionAppId => p_REGION_APPLICATION_ID,
2670 pAttributeCode => cr.attribute_code,
2671 pAttributeAppId => cr.attribute_application_id);
2672 end loop;
2673
2674 if items_cursor%ISOPEN then
2675 close items_cursor;
2676 end if;
2677
2678 EXCEPTION
2679 WHEN FND_API.G_EXC_ERROR THEN
2680 x_return_status := FND_API.G_RET_STS_ERROR;
2681 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2682 ,p_data => x_msg_data);
2683 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2685 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2686 ,p_data => x_msg_data);
2687 WHEN NO_DATA_FOUND THEN
2688 x_return_status := FND_API.G_RET_STS_ERROR;
2689 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2690 ,p_data => x_msg_data);
2691 WHEN OTHERS THEN
2692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2693 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2694 ,p_data => x_msg_data);
2695 if (x_msg_data is null) then
2696 x_msg_data := 'BIS_AK_REGION_PUB.DELETE_EXT_ITEMS: ' || SQLERRM;
2697 end if;
2698
2699
2700 END DELETE_EXT_REGION_ITEMS;
2701
2702
2703 function VALID_DATABASE_OBJECT (
2704 P_DATABASE_OBJECT_NAME IN VARCHAR2) return boolean IS
2705
2706 l_count number;
2707 l_valid boolean;
2708
2709 begin
2710
2711 -- mdamle 07/22/04 - Enh#3786101 - Consider Materialized view as a valid database object
2712 select count(1) into l_count from user_objects
2713 where object_type in ('VIEW', 'MATERIALIZED VIEW','SYNONYM')
2714 and object_name = p_database_object_name;
2715
2716 if (l_count > 0) then
2717 l_valid := true;
2718 else
2719 l_valid := false;
2720 end if;
2721
2722 return l_valid;
2723
2724 end VALID_DATABASE_OBJECT;
2725
2726 function AK_OBJECT_EXISTS(
2727 P_DATABASE_OBJECT_NAME IN VARCHAR2) return boolean IS
2728
2729 l_count number;
2730 l_exists boolean;
2731
2732 begin
2733
2734 SELECT count(1) into l_count
2735 FROM ak_objects
2736 WHERE database_object_name = P_DATABASE_OBJECT_NAME;
2737
2738 IF l_count > 0 THEN
2739 l_exists := true;
2740 else
2741 l_exists := false;
2742 end if;
2743
2744 return l_exists;
2745 end AK_OBJECT_EXISTS;
2746
2747
2748 procedure INSERT_AK_OBJECT (
2749 P_DATABASE_OBJECT_NAME IN VARCHAR2
2750 ,P_APPLICATION_ID IN NUMBER) IS
2751
2752 l_object_rowid varchar2(50);
2753 begin
2754 -- Insert into Objects
2755 AK_OBJECTS_PKG.INSERT_ROW(
2756 X_ROWID => l_object_rowid,
2757 X_DATABASE_OBJECT_NAME => upper(P_DATABASE_OBJECT_NAME),
2758 X_APPLICATION_ID => P_APPLICATION_ID,
2759 X_NAME => SUBSTR(P_DATABASE_OBJECT_NAME, 1, 23), -- bug#4289493
2760 X_DESCRIPTION => null,
2761 X_PRIMARY_KEY_NAME => null,
2762 X_DEFAULTING_API_PKG => null,
2763 X_DEFAULTING_API_PROC => null,
2764 X_VALIDATION_API_PKG => null,
2765 X_VALIDATION_API_PROC => null,
2766 X_CREATION_DATE => sysdate,
2767 X_CREATED_BY => fnd_global.user_id,
2768 X_LAST_UPDATE_DATE => sysdate,
2769 X_LAST_UPDATED_BY => fnd_global.user_id,
2770 X_LAST_UPDATE_LOGIN => fnd_global.user_id,
2771 X_ATTRIBUTE_CATEGORY => null,
2772 X_ATTRIBUTE1 => null,
2773 X_ATTRIBUTE2 => null,
2774 X_ATTRIBUTE3 => null,
2775 X_ATTRIBUTE4 => null,
2776 X_ATTRIBUTE5 => null,
2777 X_ATTRIBUTE6 => null,
2778 X_ATTRIBUTE7 => null,
2779 X_ATTRIBUTE8 => null,
2780 X_ATTRIBUTE9 => null,
2781 X_ATTRIBUTE10 => null,
2782 X_ATTRIBUTE11 => null,
2783 X_ATTRIBUTE12 => null,
2784 X_ATTRIBUTE13 => null,
2785 X_ATTRIBUTE14 => null,
2786 X_ATTRIBUTE15 => null);
2787
2788 end INSERT_AK_OBJECT;
2789
2790
2791 PROCEDURE GET_REGION_ITEM_REC
2792 ( p_region_code IN VARCHAR2
2793 , p_region_application_id IN NUMBER
2794 , p_Attribute_Code IN AK_REGION_ITEMS.ATTRIBUTE_CODE%TYPE
2795 , p_Attribute_Application_Id IN AK_REGION_ITEMS.ATTRIBUTE_APPLICATION_ID%TYPE
2796 , x_Region_Item_Rec OUT NOCOPY BIS_AK_REGION_PUB.Bis_Region_Item_Rec_Type
2797 , x_return_status OUT NOCOPY VARCHAR2
2798 , x_msg_count OUT NOCOPY NUMBER
2799 , x_msg_data OUT NOCOPY VARCHAR2
2800 ) IS
2801 CURSOR cRegionItem IS
2802 SELECT display_sequence,
2803 node_display_flag,
2804 required_flag,
2805 node_query_flag,
2806 display_value_length,
2807 attribute_label_long,
2808 order_sequence,
2809 initial_sort_sequence,
2810 order_direction,
2811 url,
2812 attribute1,
2813 attribute7,
2814 attribute14,
2815 attribute2,
2816 attribute3,
2817 attribute4,
2818 attribute5,
2819 attribute6,
2820 attribute15,
2821 attribute9,
2822 attribute10,
2823 attribute13,
2824 attribute8,
2825 attribute11
2826 FROM ak_region_items_vl
2827 WHERE region_code = p_region_code
2828 AND region_application_id = p_region_application_id
2829 AND attribute_code = p_Attribute_Code
2830 AND attribute_application_id = p_Attribute_Application_Id;
2831
2832 BEGIN
2833 -- Save the current data
2834 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
2835
2836 x_Region_Item_Rec.Attribute_Code := p_Attribute_Code;
2837 x_Region_Item_Rec.Attribute_Application_Id := p_Attribute_Application_Id;
2838 IF cRegionItem%ISOPEN THEN
2839 CLOSE cRegionItem;
2840 END IF;
2841 OPEN cRegionItem;
2842 FETCH cRegionItem INTO
2843 x_Region_Item_Rec.Display_Sequence,
2844 x_Region_Item_Rec.Node_Display_Flag,
2845 x_Region_Item_Rec.Required_Flag,
2846 x_Region_Item_Rec.Queryable_Flag,
2847 x_Region_Item_Rec.Display_Length,
2848 x_Region_Item_Rec.Long_Label,
2849 x_Region_Item_Rec.Sort_Sequence,
2850 x_Region_Item_Rec.Initial_Sort_Sequence,
2851 x_Region_Item_Rec.Sort_Direction,
2852 x_Region_Item_Rec.Url,
2853 x_Region_Item_Rec.Attribute_Type,
2854 x_Region_Item_Rec.Display_Format,
2855 x_Region_Item_Rec.Display_Type,
2856 x_Region_Item_Rec.Measure_Level,
2857 x_Region_Item_Rec.Base_Column,
2858 x_Region_Item_Rec.Lov_Where_Clause,
2859 x_Region_Item_Rec.Graph_Position,
2860 x_Region_Item_Rec.Graph_Style,
2861 x_Region_Item_Rec.Lov_Table,
2862 x_Region_Item_Rec.Aggregate_Function,
2863 x_Region_Item_Rec.Display_Total,
2864 x_Region_Item_Rec.Variance,
2865 x_Region_Item_Rec.Schedule,
2866 x_Region_Item_Rec.Override_Hierarchy;
2867 CLOSE cRegionItem;
2868
2869 EXCEPTION
2870 WHEN NO_DATA_FOUND THEN
2871 x_return_status := FND_API.G_RET_STS_ERROR;
2872 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2873 ,p_data => x_msg_data);
2874 WHEN OTHERS THEN
2875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2876 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2877 ,p_data => x_msg_data);
2878 IF (x_msg_data IS NULL) THEN
2879 x_msg_data := 'BIS_AK_REGION_PUB.GET_REGION_ITEM_REC: ' || SQLERRM;
2880 END IF;
2881 END GET_REGION_ITEM_REC;
2882
2883
2884 --deprecated, call the next one with p_type parameter
2885 PROCEDURE UPDATE_REGION_ITEM_ATTR
2886 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
2887 , p_region_code IN VARCHAR2
2888 , p_region_application_id IN NUMBER
2889 , p_Attribute_Code IN AK_REGION_ITEMS.ATTRIBUTE_CODE%TYPE
2890 , p_Attribute_Application_Id IN AK_REGION_ITEMS.ATTRIBUTE_APPLICATION_ID%TYPE
2891 , p_Short_Name IN VARCHAR2
2892 , x_return_status OUT NOCOPY VARCHAR2
2893 , x_msg_count OUT NOCOPY NUMBER
2894 , x_msg_data OUT NOCOPY VARCHAR2
2895 ) IS
2896
2897 l_Region_Item_Rec BIS_AK_REGION_PUB.Bis_Region_Item_Rec_Type;
2898 l_measure_short_name Bisbv_Performance_Measures.MEASURE_SHORT_NAME%TYPE;
2899 l_measure_name Bisbv_Performance_Measures.MEASURE_NAME%TYPE;
2900 l_msg_data VARCHAR2(300);
2901 l_msg_count NUMBER;
2902 l_ret_status VARCHAR2(10);
2903
2904 BEGIN
2905 -- Save the current data
2906 x_return_status := FND_API.G_RET_STS_SUCCESS;
2907
2908 BIS_AK_REGION_PUB.UPDATE_REGION_ITEM_ATTR
2909 ( p_commit => p_commit
2910 , p_region_code => p_region_code
2911 , p_region_application_id => p_region_application_id
2912 , p_Attribute_Code => p_Attribute_Code
2913 , p_Attribute_Application_Id => p_Attribute_Application_Id
2914 , p_Short_Name => p_Short_Name
2915 , p_type => NULL
2916 , p_Meas_Name => NULL
2917 , x_return_status => x_return_status
2918 , x_msg_count => x_msg_count
2919 , x_msg_data => x_msg_data
2920 );
2921 EXCEPTION
2922 WHEN FND_API.G_EXC_ERROR THEN
2923 x_return_status := FND_API.G_RET_STS_ERROR;
2924 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2925 ,p_data => x_msg_data);
2926 WHEN OTHERS THEN
2927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2928 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
2929 ,p_data => x_msg_data);
2930 IF (x_msg_data IS NULL) THEN
2931 x_msg_data := 'BIS_AK_REGION_PUB.UPDATE_REGION_ITEM_ATTR: ' || SQLERRM;
2932 END IF;
2933 END UPDATE_REGION_ITEM_ATTR;
2934
2935 --bug#3859267: overloaded to take in p_type, which is one of
2936 --C_MEASURE, C_MEASURE_NO_TARGET, or C_COMPARE_TO_MEASURE_NO_TARGET
2937 PROCEDURE UPDATE_REGION_ITEM_ATTR
2938 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
2939 , p_region_code IN VARCHAR2
2940 , p_region_application_id IN NUMBER
2941 , p_Attribute_Code IN AK_REGION_ITEMS.ATTRIBUTE_CODE%TYPE
2942 , p_Attribute_Application_Id IN AK_REGION_ITEMS.ATTRIBUTE_APPLICATION_ID%TYPE
2943 , p_Short_Name IN VARCHAR2
2944 , p_type IN VARCHAR2
2945 , p_Meas_Name IN VARCHAR2
2946 , x_return_status OUT NOCOPY VARCHAR2
2947 , x_msg_count OUT NOCOPY NUMBER
2948 , x_msg_data OUT NOCOPY VARCHAR2
2949 ) IS
2950
2951 l_Region_Item_Rec BIS_AK_REGION_PUB.Bis_Region_Item_Rec_Type;
2952 l_measure_short_name Bisbv_Performance_Measures.MEASURE_SHORT_NAME%TYPE;
2953 l_measure_name Bisbv_Performance_Measures.MEASURE_NAME%TYPE;
2954 l_msg_data VARCHAR2(300);
2955 l_msg_count NUMBER;
2956 l_ret_status VARCHAR2(10);
2957 l_type VARCHAR2(30);
2958
2959 BEGIN
2960 -- Save the current data
2961 x_return_status := FND_API.G_RET_STS_SUCCESS;
2962 --make sure p_type is recognized
2963 IF ((p_type IS NOT NULL) AND
2964 (p_type <> BIS_AK_REGION_PUB.C_MEASURE) AND
2965 (p_type <> BIS_AK_REGION_PUB.C_MEASURE_NO_TARGET) AND
2966 (p_type <> BIS_AK_REGION_PUB.C_COMPARE_TO_MEASURE_NO_TARGET)) THEN
2967 RAISE FND_API.G_EXC_ERROR;
2968 END IF;
2969
2970 BIS_AK_REGION_PUB.GET_REGION_ITEM_REC
2971 ( p_region_code => p_region_code
2972 , p_region_application_id => p_region_application_id
2973 , p_Attribute_Code => p_Attribute_Code
2974 , p_Attribute_Application_Id => p_Attribute_Application_Id
2975 , x_Region_Item_Rec => l_Region_Item_Rec
2976 , x_return_status => l_ret_status
2977 , x_msg_count => l_msg_count
2978 , x_msg_data => l_msg_data
2979 );
2980
2981 IF ((l_ret_status IS NOT NULL) AND (l_ret_status <> FND_API.G_RET_STS_SUCCESS)) THEN
2982 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2983 END IF;
2984
2985 --set type
2986 IF (p_type IS NULL) THEN
2987 l_type := BIS_AK_REGION_PUB.C_MEASURE_NO_TARGET;
2988 ELSE
2989 l_type := p_type;
2990 END IF;
2991
2992 --check attribute1
2993 IF (BIS_UTILITIES_PUB.Value_Missing(l_Region_Item_Rec.Attribute_Type) = FND_API.G_TRUE) THEN
2994 l_Region_Item_Rec.Attribute_Type := BIS_AK_REGION_PUB.C_MEASURE_NO_TARGET;
2995 END IF;
2996
2997 --set attribute2
2998 IF (BIS_UTILITIES_PUB.Value_Missing(p_Short_Name) = FND_API.G_TRUE) THEN
2999 l_Region_Item_Rec.Measure_Level := NULL;
3000 IF (IS_COMPARE_TYPE(l_Region_Item_Rec.Attribute_Type) = TRUE) THEN
3001 l_Region_Item_Rec.Attribute_Type := NULL;
3002 END IF;
3003 ELSE
3004 IF ((BIS_UTILITIES_PUB.Value_Missing(l_Region_Item_Rec.Measure_Level) = FND_API.G_TRUE) OR
3005 (COMPARE_TYPE_AND_SHORTNAME(l_Region_Item_Rec.Attribute_Type, l_Region_Item_Rec.Measure_Level, l_type, p_Short_Name) = TRUE) OR
3006 ((IS_MEASURE_TYPE(l_Region_Item_Rec.Attribute_Type) = TRUE) AND
3007 (VALIDATE_MEASURE(p_short_name=>l_Region_Item_Rec.Measure_Level, x_measure_short_name=>l_measure_short_name, x_measure_name=>l_measure_name) = FALSE)) OR
3008 ((IS_COMPARE_TYPE(l_Region_Item_Rec.Attribute_Type) = TRUE) AND
3009 (VALIDATE_COMPARE(p_region_code=>p_region_code, p_region_app_id=>p_region_application_id, p_compare_code=>l_Region_Item_Rec.Measure_Level, x_measure_short_name=>l_measure_short_name, x_measure_name=>l_measure_name) = FALSE))) THEN
3010
3011 l_Region_Item_Rec.Attribute_Type := l_type;
3012 l_Region_Item_Rec.Measure_Level := p_Short_Name;
3013
3014 IF (BIS_UTILITIES_PUB.Value_Missing(l_Region_Item_Rec.Long_Label) = FND_API.G_TRUE) THEN
3015 l_Region_Item_Rec.Long_Label := substrb(p_Meas_Name, 1, 80);
3016 END IF;
3017 --special handlings for view-based report:
3018 IF (IS_VIEW_BASED_REPORT(p_region_code, p_region_application_id) = FND_API.G_TRUE) THEN
3019
3020 --bug#4018318: set node_display_flag to 'N' for compare type for view based report
3021 IF (IS_COMPARE_TYPE(l_Region_Item_Rec.Attribute_Type) = TRUE) THEN
3022 l_Region_Item_Rec.Node_Display_Flag := 'N';
3023 END IF;
3024
3025 --bug#4028958: need to handle aggregation function for view based report
3026 --if aggregate function is null, need to populate it according to description in bug#4028958
3027 IF (l_Region_Item_Rec.Aggregate_Function IS NULL OR (IS_COMPARE_TYPE(l_Region_Item_Rec.Attribute_Type) = TRUE)) THEN
3028
3029 --handle measure type here:
3030 IF (IS_MEASURE_TYPE(l_Region_Item_Rec.Attribute_Type) = TRUE) THEN
3031 --if veiw based and view by report, for measure type, if attribute9 is null, set it to 'SUM'
3032 IF (IS_VIEW_BY_REPORT(p_region_code, p_region_application_id) = FND_API.G_TRUE) THEN
3033 l_Region_Item_Rec.Aggregate_Function := BIS_AK_REGION_PUB.C_SUM;
3034 ELSIF (IS_AGGREGATE_DEFINED(p_region_code, p_region_application_id) = FND_API.G_TRUE) THEN
3035 --if view based and non-view by report, for measure type, if attribute9 is null,
3036 --check if any other column in the same report has non-null attribute9, is so, set this measure column to 'SUM'
3037 l_Region_Item_Rec.Aggregate_Function := BIS_AK_REGION_PUB.C_SUM;
3038 END IF;
3039
3040 --handle compare type here:
3041 ELSIF (IS_COMPARE_TYPE(l_Region_Item_Rec.Attribute_Type) = TRUE) THEN
3042 --if view based report, and attribute9 is null, always sync up with measure column's attribute9
3043 l_Region_Item_Rec.Aggregate_Function := GET_COMPARE_AGG_FUNCTION(p_region_code, p_region_application_id, l_Region_Item_Rec.Measure_Level);
3044 END IF;
3045 END IF;
3046 END IF;
3047
3048 END IF;
3049 END IF;
3050
3051 BIS_AK_REGION_PUB.UPDATE_REGION_ITEM_ROW
3052 ( p_commit => p_commit
3053 , p_region_code => p_region_code
3054 , p_region_application_id => p_region_application_id
3055 , p_Region_Item_Rec => l_Region_Item_Rec
3056 , x_return_status => x_return_status
3057 , x_msg_count => x_msg_count
3058 , x_msg_data => x_msg_data
3059 );
3060
3061 EXCEPTION
3062 WHEN FND_API.G_EXC_ERROR THEN
3063 x_return_status := FND_API.G_RET_STS_ERROR;
3064 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
3065 ,p_data => x_msg_data);
3066 WHEN OTHERS THEN
3067 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3068 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
3069 ,p_data => x_msg_data);
3070 IF (x_msg_data IS NULL) THEN
3071 x_msg_data := 'BIS_AK_REGION_PUB.UPDATE_REGION_ITEM_ATTR: ' || SQLERRM;
3072 END IF;
3073 END UPDATE_REGION_ITEM_ATTR;
3074
3075 PROCEDURE LOCK_REGION_ROW
3076 ( p_region_code IN VARCHAR2
3077 , p_region_application_id IN NUMBER
3078 , p_last_update_date IN VARCHAR2
3079 , x_record_status OUT NOCOPY VARCHAR2
3080 ) IS
3081
3082 l_last_update_date date;
3083
3084 cursor cRegion is select last_update_date
3085 from ak_regions
3086 where region_code = p_region_code
3087 and region_application_id = p_region_application_id
3088 for update of region_application_id nowait;
3089
3090 BEGIN
3091
3092 SAVEPOINT SP_LOCK_REGION_ROW;
3093
3094 IF cRegion%ISOPEN THEN
3095 CLOSE cRegion;
3096 END IF;
3097 OPEN cRegion;
3098 FETCH cRegion INTO l_last_update_date;
3099
3100 if (cRegion%notfound) then
3101 x_record_status := BIS_AK_REGION_PUB.c_RECORD_DELETED;
3102 end if;
3103
3104 if p_last_update_date is not null then
3105 if p_last_update_date <> TO_CHAR(l_last_update_date, BIS_AK_REGION_PUB.C_LAST_UPDATE_DATE_FORMAT) then
3106 x_record_status := BIS_AK_REGION_PUB.c_RECORD_CHANGED;
3107 end if;
3108 end if;
3109
3110 rollback to SP_LOCK_REGION_ROW;
3111
3112 CLOSE cRegion;
3113
3114 EXCEPTION
3115 WHEN OTHERS THEN
3116 close cRegion;
3117 x_record_status := BIS_AK_REGION_PUB.c_RECORD_CHANGED;
3118 rollback to SP_LOCK_REGION_ROW;
3119 END LOCK_REGION_ROW;
3120
3121 -- ankgoel: bug#3937907 - Verify if AK data will be modified or not for the current source and compare-to column only
3122 -- SC - Source Column modified
3123 -- CC - Compare-to Column modified
3124 -- SSCC - Both Source and Compare-to columns modified
3125 FUNCTION AK_DATA_SET(
3126 p_region_code IN Ak_Region_Items.REGION_CODE%TYPE
3127 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type
3128 ,p_source_code IN Ak_Region_Items.ATTRIBUTE_CODE%Type
3129 ,p_compare_code IN Ak_Region_Items.ATTRIBUTE_CODE%Type
3130 ,p_measure_short_name IN Bisbv_Performance_Measures.MEASURE_SHORT_NAME%TYPE
3131 ) RETURN VARCHAR2
3132 IS
3133 ak_sc_modify BOOLEAN := TRUE;
3134 ak_cc_modify BOOLEAN := TRUE;
3135 ak_modify VARCHAR2(10) := 'SCCC';
3136 l_measure_short_name Bisbv_Performance_Measures.MEASURE_SHORT_NAME%TYPE;
3137 l_measure_name Bisbv_Performance_Measures.MEASURE_NAME%TYPE;
3138
3139 CURSOR attribute_cur(p_attr_code VARCHAR2) IS
3140 SELECT a.attribute2
3141 FROM ak_region_items a
3142 WHERE a.region_code = p_region_code
3143 AND a.region_application_id = p_region_app_id
3144 AND a.attribute1 IN ('MEASURE', 'MEASURE_NOTARGET')
3145 AND a.attribute_code = p_attr_code
3146 AND a.attribute2 = p_measure_short_name;
3147
3148 CURSOR compare_col_cur IS
3149 SELECT sc.attribute2
3150 FROM ak_region_items sc, ak_region_items cc
3151 WHERE sc.region_code = p_region_code
3152 AND sc.region_code = cc.region_code
3153 AND sc.region_application_id = p_region_app_id
3154 AND (sc.attribute1 IN ('MEASURE','MEASURE_NOTARGET') AND sc.attribute_code = p_source_code)
3155 AND (cc.attribute1 = 'COMPARE_TO_MEASURE_NO_TARGET' AND cc.attribute_code = p_compare_code)
3156 AND sc.attribute_code = cc.attribute2
3157 AND sc.attribute2 = p_measure_short_name;
3158
3159 BEGIN
3160
3161 FOR rec IN attribute_cur(p_source_code) LOOP
3162 ak_sc_modify := FALSE;
3163 END LOOP;
3164
3165 IF (p_compare_code IS NOT NULL) THEN
3166 FOR rec IN compare_col_cur LOOP
3167 ak_cc_modify := FALSE;
3168 END LOOP;
3169 FOR rec IN attribute_cur(p_compare_code) LOOP
3170 ak_cc_modify := FALSE;
3171 END LOOP;
3172 ELSE
3173 ak_cc_modify := FALSE;
3174 END IF;
3175
3176 IF((ak_sc_modify) AND (ak_cc_modify)) THEN
3177 ak_modify := 'SCCC';
3178 ELSIF (ak_sc_modify) THEN
3179 ak_modify := 'SC';
3180 ELSIF (ak_cc_modify) THEN
3181 ak_modify := 'CC';
3182 ELSE
3183 ak_modify := NULL;
3184 END IF;
3185
3186 RETURN ak_modify;
3187 EXCEPTION
3188 WHEN OTHERS THEN
3189 RETURN 'SCCC';
3190 END AK_DATA_SET;
3191
3192 --return 'T' if given report is view based, 'F' otherwise
3193 FUNCTION IS_VIEW_BASED_REPORT(
3194 p_region_code IN Ak_Regions.REGION_CODE%TYPE
3195 ,p_region_app_id IN Ak_Regions.REGION_APPLICATION_ID%Type
3196 ) RETURN VARCHAR2
3197 IS
3198 l_attribute10 Ak_Regions.ATTRIBUTE10%TYPE;
3199 l_ret_val VARCHAR2(1) := FND_API.G_FALSE;
3200 BEGIN
3201 SELECT attribute10 INTO l_attribute10
3202 FROM ak_regions
3203 WHERE region_code = p_region_code
3204 AND region_application_id = p_region_app_id;
3205
3206 IF (l_attribute10 IS NULL) THEN
3207 l_ret_val := FND_API.G_TRUE;
3208 ELSE
3209 l_ret_val := FND_API.G_FALSE;
3210 END IF;
3211
3212 RETURN l_ret_val;
3213 EXCEPTION
3214 WHEN OTHERS THEN
3215 RETURN FND_API.G_FALSE;
3216 END IS_VIEW_BASED_REPORT;
3217
3218
3219 --return 'T' if given report is view-by, 'F' otherwise
3220 FUNCTION IS_VIEW_BY_REPORT(
3221 p_region_code IN Ak_Regions.REGION_CODE%TYPE
3222 ,p_region_app_id IN Ak_Regions.REGION_APPLICATION_ID%Type
3223 ) RETURN VARCHAR2
3224 IS
3225 l_attribute1 Ak_Regions.ATTRIBUTE1%TYPE;
3226 l_ret_val VARCHAR2(1) := FND_API.G_TRUE;
3227 BEGIN
3228 SELECT attribute1 INTO l_attribute1
3229 FROM ak_regions
3230 WHERE region_code = p_region_code
3231 AND region_application_id = p_region_app_id;
3232
3233 IF ((l_attribute1 = 'N') OR (l_attribute1 IS NULL)) THEN
3234 l_ret_val := FND_API.G_TRUE;
3235 ELSIF (l_attribute1 = 'Y') THEN
3236 l_ret_val := FND_API.G_FALSE;
3237 END IF;
3238
3239 RETURN l_ret_val;
3240 EXCEPTION
3241 WHEN OTHERS THEN
3242 RETURN FND_API.G_TRUE;
3243 END IS_VIEW_BY_REPORT;
3244
3245 --return 'T' if at least one aggregate function is defined in any column
3246 --for the given report, 'F' otherwise
3247 FUNCTION IS_AGGREGATE_DEFINED(
3248 p_region_code IN Ak_Region_Items.REGION_CODE%TYPE
3249 ,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type
3250 ) RETURN VARCHAR2
3251 IS
3252 l_count NUMBER := 0;
3253 l_ret_val VARCHAR2(1) := FND_API.G_FALSE;
3254 BEGIN
3255 SELECT COUNT(0) INTO l_count
3256 FROM ak_region_items
3257 WHERE region_code = p_region_code
3258 AND region_application_id = p_region_app_id
3259 AND attribute9 IS NOT NULL;
3260
3261 IF (l_count > 0) THEN
3262 l_ret_val := FND_API.G_TRUE;
3263 END IF;
3264
3265 RETURN l_ret_val;
3266 EXCEPTION
3267 WHEN OTHERS THEN
3268 RETURN FND_API.G_FALSE;
3269 END IS_AGGREGATE_DEFINED;
3270
3271 -- added for Bug#4448994 and as a general utility file.
3272 PROCEDURE Get_Region_Code_TL_Data (
3273 p_Region_Code IN Ak_Regions.REGION_CODE%TYPE
3274 , p_Region_Application_Id IN Ak_Regions.REGION_APPLICATION_ID%TYPE
3275 , x_Region_Name OUT NOCOPY Ak_Regions_Tl.NAME%TYPE
3276 , x_Region_Description OUT NOCOPY Ak_Regions_Tl.DESCRIPTION%TYPE
3277 , x_Region_Created_By OUT NOCOPY Ak_Regions_Tl.CREATED_BY%TYPE
3278 , x_Region_Creation_Date OUT NOCOPY Ak_Regions_Tl.CREATION_DATE%TYPE
3279 , x_Region_Last_Updated_By OUT NOCOPY Ak_Regions_Tl.LAST_UPDATED_BY%TYPE
3280 , x_Region_Last_Update_Date OUT NOCOPY Ak_Regions_Tl.LAST_UPDATE_DATE%TYPE
3281 , x_Region_Last_Update_Login OUT NOCOPY Ak_Regions_Tl.LAST_UPDATE_LOGIN%TYPE
3282 , x_return_status OUT NOCOPY VARCHAR2
3283 , x_msg_count OUT NOCOPY NUMBER
3284 , x_msg_data OUT NOCOPY VARCHAR2
3285 ) IS
3286 CURSOR c_AkRegionsTl IS
3287 SELECT
3288 A.REGION_CODE
3289 , A.REGION_APPLICATION_ID
3290 , A.NAME
3291 , A.DESCRIPTION
3292 , A.CREATED_BY
3293 , A.CREATION_DATE
3294 , A.LAST_UPDATED_BY
3295 , A.LAST_UPDATE_DATE
3296 , A.LAST_UPDATE_LOGIN
3297 FROM AK_REGIONS_VL A
3298 WHERE A.REGION_CODE = p_Region_Code
3299 AND A.REGION_APPLICATION_ID = p_Region_Application_Id;
3300 BEGIN
3301 FND_MSG_PUB.Initialize;
3302 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
3303
3304 FOR c_AkRTl IN c_AkRegionsTl LOOP
3305 x_Region_Name := c_AkRTl.NAME;
3306 x_Region_Description := c_AkRTl.DESCRIPTION;
3307 x_Region_Created_By := c_AkRTl.CREATED_BY;
3308 x_Region_Creation_Date := c_AkRTl.CREATION_DATE;
3309 x_Region_Last_Updated_By := c_AkRTl.LAST_UPDATED_BY;
3310 x_Region_Last_Update_Date := c_AkRTl.LAST_UPDATE_DATE;
3311 x_Region_Last_Update_Login := c_AkRTl.LAST_UPDATE_LOGIN;
3312 END LOOP;
3313
3314 EXCEPTION
3315 WHEN OTHERS THEN
3316 IF (x_msg_data IS NULL) THEN
3317 x_msg_data := SQLERRM || ' at BIS_AK_REGION_PUB.Get_Region_Code_TL_Data ';
3318 END IF;
3319 x_Return_Status := FND_API.G_RET_STS_ERROR;
3320 END Get_Region_Code_TL_Data;
3321
3322 -- Bug#5256605 : Reset the display sequence of AK Region Items
3323 -- starting from -1 down below. This is done before updating all AK Items.
3324 PROCEDURE reset_ak_items_display_seq (
3325 p_region_code IN VARCHAR2
3326 , p_region_application_id IN NUMBER
3327 , x_return_status OUT NOCOPY VARCHAR2
3328 , x_msg_count OUT NOCOPY NUMBER
3329 , x_msg_data OUT NOCOPY VARCHAR2
3330 )
3331 IS
3332 l_counter NUMBER;
3333 CURSOR cr_items IS
3334 SELECT attribute_code, attribute_application_id
3335 FROM ak_region_items
3336 WHERE region_code = p_region_code
3337 AND region_application_id = p_region_application_id;
3338
3339 BEGIN
3340 IF cr_items%ISOPEN THEN
3341 CLOSE cr_items;
3342 END IF;
3343
3344 l_counter := -1;
3345 FOR cr IN cr_items LOOP
3346 UPDATE ak_region_items
3347 SET display_sequence = l_counter
3348 WHERE attribute_code = cr.attribute_code
3349 AND attribute_application_id = cr.attribute_application_id
3350 AND region_code = p_region_code
3351 AND region_application_id = p_region_application_id;
3352 l_counter := l_counter - 1;
3353 END LOOP;
3354
3355 IF cr_items%ISOPEN THEN
3356 CLOSE cr_items;
3357 END IF;
3358
3359 EXCEPTION
3360 WHEN NO_DATA_FOUND THEN
3361 IF cr_items%ISOPEN THEN
3362 CLOSE cr_items;
3363 END IF;
3364 x_return_status := FND_API.G_RET_STS_ERROR;
3365 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
3366 , p_count => x_msg_count
3367 , p_data => x_msg_data);
3368 WHEN OTHERS THEN
3369 IF cr_items%ISOPEN THEN
3370 CLOSE cr_items;
3371 END IF;
3372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3373 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
3374 , p_count => x_msg_count
3375 , p_data => x_msg_data);
3376 IF (x_msg_data IS NULL) THEN
3377 x_msg_data := 'BIS_AK_REGION_PUB.reset_ak_items_display_seq: ' || SQLERRM;
3378 END IF;
3379 END reset_ak_items_display_seq;
3380
3381 END BIS_AK_REGION_PUB;