1 PACKAGE BODY RRS_ATTR_PANE AS
2 /* $Header: RRSGATPB.pls 120.18 2010/12/06 22:35:12 jijiao noship $ */
3
4 /*Get Basic Information for the object*/
5 PROCEDURE Get_Primary_Attributes
6 (
7 p_object_type IN VARCHAR2,
8 p_object_id IN VARCHAR2,
9 x_primary_attributes OUT NOCOPY rrs_primary_attribute_rec,
10 x_error_messages OUT NOCOPY rrs_error_msg_tab
11 ) IS
12
13 l_object_table_name VARCHAR2(30);
14 l_pk_name VARCHAR2(30);
15 l_classification_name VARCHAR2(30);
16 l_date_format_mask VARCHAR2(100);
17
18 BEGIN
19 --Bug Fix for Bug 9027024, need display the start/end date in the format as set in user preference
20 SELECT FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')
21 INTO l_date_format_mask
22 FROM DUAL;
23
24 /* decide which object type we are processing*/
25 --Add RBAC on Site Object View
26 IF UPPER(p_object_type) = 'SITE' AND RRS_SECURITY_PUB.Check_Object_View_Privilege(1.0, 'RRS_SITE', p_object_id) = 'T' THEN
27 BEGIN
28 SELECT RRS_PRIMARY_ATTRIBUTE_REC(
29 SITE_ID,
30 SITE_IDENTIFICATION_NUMBER,
31 NAME,
32 (SELECT MEANING
33 FROM RRS_SITE_USES RSU, AR_LOOKUPS LK
34 WHERE RSU.SITE_ID = p_object_id
35 AND RSU.IS_PRIMARY_FLAG = 'Y'
36 AND LK.LOOKUP_TYPE (+) = 'PARTY_SITE_USE_CODE'
37 AND RSU.SITE_USE_TYPE_CODE = LK.LOOKUP_CODE (+)
38 ),
39 DESCRIPTION,
40 (SELECT MEANING FROM RRS_LOOKUPS_V WHERE LOOKUP_TYPE = 'RRS_SITE_TYPE' AND LOOKUP_CODE = RSV.SITE_TYPE_CODE),
41 (SELECT MEANING FROM RRS_LOOKUPS_V WHERE LOOKUP_TYPE = 'RRS_SITE_STATUS' AND LOOKUP_CODE = RSV.SITE_STATUS_CODE),
42 TO_CHAR(START_DATE, l_date_format_mask), --Bug Fix for Bug 9027024, need display the start/end date in the format as set in user preference
43 TO_CHAR(END_DATE, l_date_format_mask), --Bug Fix for Bug 9027024, need display the start/end date in the format as set in user preference
44 (HZ_FORMAT_PUB.format_address(RSV.location_id, null, null, ', ' , null) || ', ' || (SELECT COUNTRY FROM RRS_LOCATIONS_V WHERE LOCATION_ID = RSV.LOCATION_ID)
45 ),
46 NULL,
47 NULL,
48 NULL)
49 INTO x_primary_attributes
50 FROM RRS_SITES_VL RSV
51 WHERE SITE_ID = p_object_id;
52
53 EXCEPTION
54 WHEN NO_DATA_FOUND THEN
55 Record_Error('No primary attributes were found for ' || p_object_type || ' ' || p_object_id || '.', x_error_messages);
56 WHEN OTHERS THEN
57 Record_Error('Get_Primary_Attributes: ' || dbms_utility.format_error_backtrace, x_error_messages);
58
59 END;
60
61 ELSIF UPPER(p_object_type) = 'LOCATION' THEN
62 BEGIN
63 SELECT RRS_PRIMARY_ATTRIBUTE_REC(
64 LOCATION_ID,
65 NULL,
66 NULL,
67 COUNTRY,
68 DESCRIPTION,
69 NULL,
70 NULL,
71 NULL,
72 NULL,
73 (HZ_FORMAT_PUB.format_address(RLV.location_id, null, null, ',' , null) || ', ' || COUNTRY
74 ),
75 COUNTRY,
76 NULL,
77 NULL)
78 INTO x_primary_attributes
79 FROM RRS_LOCATIONS_V RLV
83 Record_Error('No primary attributes were found for ' || p_object_type || ' ' || p_object_id || '.', x_error_messages);
80 WHERE LOCATION_ID = p_object_id;
81 EXCEPTION
82 WHEN NO_DATA_FOUND THEN
84 WHEN OTHERS THEN
85 Record_Error('Get_Primary_Attributes: ' || dbms_utility.format_error_backtrace, x_error_messages);
86
87 END;
88
89 ELSIF UPPER(p_object_type) = 'NODE' THEN
90 BEGIN
91 SELECT RRS_PRIMARY_ATTRIBUTE_REC(
92 SITE_GROUP_NODE_ID,
93 NODE_IDENTIFICATION_NUMBER,
94 NAME,
95 (SELECT MEANING
96 FROM RRS_LOOKUPS_V LK
97 WHERE LK.LOOKUP_TYPE (+) = 'RRS_NODE_PURPOSE'
98 AND RSGNV.NODE_PURPOSE_CODE = LK.LOOKUP_CODE (+)
99 ),
100 DESCRIPTION,
101 NULL,
102 NULL,
103 NULL,
104 NULL,
105 NULL,
106 NULL,
107 NULL,
108 NULL)
109 INTO x_primary_attributes
110 FROM RRS_SITE_GROUP_NODES_VL RSGNV
111 WHERE SITE_GROUP_NODE_ID = p_object_id;
112
113 EXCEPTION
114 WHEN NO_DATA_FOUND THEN
115 Record_Error('No primary attributes were found for ' || p_object_type || ' ' || p_object_id || '.', x_error_messages);
116 WHEN OTHERS THEN
117 Record_Error('Get_Primary_Attributes: ' || dbms_utility.format_error_backtrace, x_error_messages);
118 END;
119
120 /*ELSIF UPPER(p_object_type) = 'TRADE AREA' THEN
121 SELECT RRS_PRIMARY_ATTRIBUTE_REC(
122 GROUP_ID,
123 SITE_IDENTIFICATION_NUMBER,
124 NAME,
125 (SELECT MEANING
126 FROM RRS_SITE_USES RSU, AR_LOOKUPS LK
127 WHERE RSU.SITE_ID = p_object_id
128 AND RSU.IS_PRIMARY_FLAG = 'Y'
129 AND LK.LOOKUP_TYPE = 'PARTY_SITE_USE_CODE'
130 AND RSU.SITE_USE_TYPE_CODE = LK.LOOKUP_CODE
131 ),
132 DESCRIPTION,
133 (SELECT MEANING FROM RRS_LOOKUPS_V WHERE LOOKUP_TYPE = 'RRS_SITE_TYPE' AND LOOKUP_CODE = RSV.SITE_TYPE_CODE),
134 (SELECT MEANING FROM RRS_LOOKUPS_V WHERE LOOKUP_TYPE = 'RRS_SITE_STATUS' AND LOOKUP_CODE = RSV.SITE_STATUS_CODE),
135 START_DATE,
136 END_DATE,
137 (HZ_FORMAT_PUB.format_address(RSV.location_id, null, null, ',' , null) || ', ' || (SELECT COUNTRY FROM RRS_LOCATIONS_V WHERE LOCATION_ID = RSV.LOCATION_ID)
138 ),
139 (SELECT MEANING FROM RRS_LOOKUPS_V WHERE LOOKUP_TYPE = 'RRS_TRADE_AREA_GROUP_TYPE' AND LOOKUP_CODE = RTAGV.GROUP_TYPE_CODE),
140 NULL,
141 NULL)
142 INTO x_primary_attributes
143 FROM RRS_TRADE_AREA_GROUPS_VL RTAGV
144 WHERE GROUP_ID = p_object_id;
145 */
146 --Add RBAC on Hierarchy object VIEW
147 ELSIF UPPER(p_object_type) = 'HIERARCHY' AND RRS_SECURITY_PUB.Check_Object_View_Privilege(1.0, 'RRS_HIERARCHY', p_object_id) = 'T' THEN
148 BEGIN
149 SELECT RRS_PRIMARY_ATTRIBUTE_REC(
150 SITE_GROUP_ID,
151 NULL,
152 NAME,
153 (SELECT MEANING
154 FROM RRS_LOOKUPS_V LK
155 WHERE LK.LOOKUP_TYPE(+) = 'RRS_HIERARCHY_PURPOSE'
156 AND RSGV.GROUP_PURPOSE_CODE = LK.LOOKUP_CODE (+)
157 ),
158 DESCRIPTION,
159 NULL,
160 NULL,
161 TO_CHAR(START_DATE, l_date_format_mask), --Bug Fix for Bug 9027024, need display the start/end date in the format as set in user preference
162 TO_CHAR(END_DATE, l_date_format_mask), --Bug Fix for Bug 9027024, need display the start/end date in the format as set in user preference
163 NULL,
164 NULL,
165 SITE_GROUP_TYPE_CODE,
166 NULL)
167 INTO x_primary_attributes
168 FROM RRS_SITE_GROUPS_VL RSGV
169 WHERE SITE_GROUP_ID = (SELECT SITE_GROUP_ID FROM RRS_SITE_GROUP_VERSIONS WHERE SITE_GROUP_VERSION_ID = p_object_id);
170
171 EXCEPTION
172 WHEN NO_DATA_FOUND THEN
173 Record_Error('No primary attributes were found for ' || p_object_type || ' ' || p_object_id || '.', x_error_messages);
174 WHEN OTHERS THEN
175 Record_Error('Get_Primary_Attributes: ' || dbms_utility.format_error_backtrace, x_error_messages);
176
177 END;
178 END IF;
179
180 EXCEPTION
181 WHEN NO_DATA_FOUND THEN
182 Record_Error('No primary attributes were found for ' || p_object_type || ' ' || p_object_id || '.', x_error_messages);
183 WHEN OTHERS THEN
184 Record_Error('Get_Primary_Attributes: ' || dbms_utility.format_error_backtrace, x_error_messages);
185 END;
186
187
188 PROCEDURE Get_Attribute_Page
189 (
190 p_where_used IN VARCHAR2,
191 p_object_type IN VARCHAR2,
192 p_object_id IN NUMBER,
193 p_object1 IN VARCHAR2,
194 p_classification_code1 IN VARCHAR2,
195 p_object2 IN VARCHAR2,
196 p_classification_code2 IN VARCHAR2,
197 x_primary_attributes OUT NOCOPY rrs_primary_attribute_rec,
198 x_ag_page_tab OUT NOCOPY rrs_attr_group_page_tab,
199 x_attr_group_tab OUT NOCOPY rrs_attribute_group_tab,
200 x_attribute_tab OUT NOCOPY rrs_attribute_tab,
201 x_error_messages OUT NOCOPY rrs_error_msg_tab
202 ) IS
203
204 l_classification_code VARCHAR2(30);
205 l_page_id_array t_array_of_number;
206 l_attr_group_page_rec rrs_attr_group_page_rec;
207 l_attr_group_page_tab rrs_attr_group_page_tab;
208 l_attribute_group_rec rrs_attribute_group_rec;
209 l_page_entries t_page_entry_tab;
210 l_ext_id_array t_array_of_number;
211
215 l_attribute_tab t_attribute_tab;
212 l_ext_table_name VARCHAR2(30);
213 l_pk_name VARCHAR2(30);
214 l_classification_name VARCHAR2(30);
216
217 l_x_display_value VARCHAR2(1000);
218 l_x_display_type VARCHAR2(200);
219 l_x_dynamic_url VARCHAR2(1000);
220
221 l_query VARCHAR2(1000);
222 l_display_type_code VARCHAR2(10);
223 l_has_view_priv VARCHAR2(10);
224 l_obj_name VARCHAR2(60);
225
226
227 BEGIN
228
229 Get_Primary_Attributes(p_object_type,
230 p_object_id,
231 x_primary_attributes,
232 x_error_messages);
233 --x_primary_attributes IS NULL means user cannot view this object.
234 IF x_primary_attributes IS NULL THEN
235 RETURN;
236 END IF;
237
238 /* decide which object type we are processing*/
239 IF UPPER(p_object_type) = 'NODE' THEN
240 RAISE e_no_uda;
241 ELSIF UPPER(p_object_type) = 'SITE' THEN
242 l_obj_name := 'RRS_SITE';
243 l_ext_table_name := 'RRS_SITES_EXT_VL';
244 l_pk_name := 'SITE_ID';
245 l_classification_name := 'SITE_USE_TYPE_CODE';
246 ELSIF UPPER(p_object_type) = 'LOCATION' THEN
247 l_obj_name := 'RRS_LOCATION';
248 l_ext_table_name := 'RRS_LOCATIONS_EXT_VL';
249 l_pk_name := 'LOCATION_ID';
250 l_classification_name := 'COUNTRY';
251 ELSIF UPPER(p_object_type) = 'TRADE AREA' THEN
252 l_obj_name := 'RRS_TRADE_AREA';
253 l_ext_table_name := 'RRS_TRADE_AREAS_EXT_VL';
254 l_pk_name := 'TRADE_AREA_ID';
255 l_classification_name := 'GROUP_ID';
256 ELSIF UPPER(p_object_type) = 'HIERARCHY' THEN
257 l_obj_name := 'RRS_HIERARCHY';
258 l_ext_table_name := 'RRS_HIERARCHIES_EXT_VL';
259 l_pk_name := 'SITE_GROUP_VERSION_ID';
260 l_classification_name := 'HIERARCHY_PURPOSE_CODE';
261 END IF;
262
263 /*Get Page IDs*/
264 BEGIN
265 IF p_object2 IS NOT NULL AND p_classification_code2 IS NOT NULL THEN
266 SELECT ATTRIBUTE_PAGE_ID
267 BULK COLLECT
268 INTO l_page_id_array
269 FROM RRS_ATTR_PAGE_SETTINGS
270 WHERE WHERE_USED = p_where_used
271 AND OBJECT_TYPE = p_object_type
272 AND CATEGORIZATION_OBJECT1 = p_object1
273 AND CLASSIFICATION_CODE1 = p_classification_code1
274 AND CATEGORIZATION_OBJECT2 = p_object2
275 AND CLASSIFICATION_CODE2 = p_classification_code2;
276
277 ELSIF p_object2 IS NULL AND p_classification_code2 IS NULL THEN
278 SELECT ATTRIBUTE_PAGE_ID
279 BULK COLLECT
280 INTO l_page_id_array
281 FROM RRS_ATTR_PAGE_SETTINGS
282 WHERE WHERE_USED = p_where_used
283 AND OBJECT_TYPE = p_object_type
284 AND CATEGORIZATION_OBJECT1 = p_object1
285 AND CLASSIFICATION_CODE1 = p_classification_code1
286 AND CATEGORIZATION_OBJECT2 IS NULL
287 AND CLASSIFICATION_CODE2 IS NULL;
288
289 ELSIF p_classification_code2 IS NULL THEN
290 SELECT ATTRIBUTE_PAGE_ID
291 BULK COLLECT
292 INTO l_page_id_array
293 FROM RRS_ATTR_PAGE_SETTINGS
294 WHERE WHERE_USED = p_where_used
295 AND OBJECT_TYPE = p_object_type
296 AND CATEGORIZATION_OBJECT1 = p_object1
297 AND CLASSIFICATION_CODE1 = p_classification_code1
298 AND CATEGORIZATION_OBJECT2 = p_object2
299 AND CLASSIFICATION_CODE2 IS NULL;
300 END IF;
301
302 EXCEPTION
303 WHEN NO_DATA_FOUND THEN
304
305 IF x_error_messages IS NULL THEN
306 x_error_messages := new rrs_error_msg_tab();
307 END IF;
308 x_error_messages.EXTEND();
309 x_error_messages(x_error_messages.LAST) := 'Get_Attribute_Group_Page: No attribute page was found for ' || p_object_type || ' ' || p_object_id || '.';
310 --Record_Error('Get_Attribute_Group_Page: No attribute page was found for ' || p_object_type || ' ' || p_object_id || '.', x_error_messages);
311 RAISE e_no_page_found;
312 END;
313
314 IF l_page_id_array IS NULL OR l_page_id_array.COUNT = 0 THEN
315
316 IF x_error_messages IS NULL THEN
317 x_error_messages := new rrs_error_msg_tab();
318 END IF;
319 x_error_messages.EXTEND();
320 x_error_messages(x_error_messages.LAST) := 'Get_Attribute_Group_Page: No attribute page was found for ' || p_object_type || ' ' || p_object_id || '.';
321 --Record_Error('Get_Attribute_Group_Page: No attribute page was found for ' || p_object_type || ' ' || p_object_id || '.', x_error_messages);
322 RAISE e_no_page_found;
323 END IF;
324
325 /*Decide the classification for the page*/
326 IF p_classification_code2 IS NULL THEN
327 l_classification_code := p_classification_code1;
328 ELSE
329 l_classification_code := p_classification_code2;
330 END IF;
331
332 /*For each page, get page information*/
333 FOR i in 1 .. l_page_id_array.COUNT
334 LOOP
335 /*Get Page Information*/
336 BEGIN
337 SELECT RRS_ATTR_GROUP_PAGE_REC(
338 PAGE_ID,
339 OBJECT_NAME,
340 CLASSIFICATION_CODE,
341 DATA_LEVEL_INT_NAME, INTERNAL_NAME, DISPLAY_NAME,
342 DESCRIPTION, SEQUENCE)
343 INTO l_attr_group_page_rec
344 FROM EGO_PAGES_V
345 WHERE PAGE_ID = l_page_id_array(i);
346
347 IF l_attr_group_page_tab IS NULL THEN
348 l_attr_group_page_tab := new rrs_attr_group_page_tab();
349 END IF;
350
351 l_attr_group_page_tab.EXTEND();
352 l_attr_group_page_tab(l_attr_group_page_tab.LAST) := new rrs_attr_group_page_rec(
353 l_attr_group_page_rec.PAGE_ID,
354 l_attr_group_page_rec.OBJECT_NAME,
355 l_attr_group_page_rec.CLASSIFICATION_CODE,
356 l_attr_group_page_rec.DATA_LEVEL_INT_NAME,
360 l_attr_group_page_rec.SEQUENCE
357 l_attr_group_page_rec.INTERNAL_NAME,
358 l_attr_group_page_rec.DISPLAY_NAME,
359 l_attr_group_page_rec.DESCRIPTION,
361 );
362
363 EXCEPTION
364 WHEN NO_DATA_FOUND THEN
365 Record_Error('Get_Attribute_Group_Page: No attribute page was found for Page Id ' || l_page_id_array(i) || '.', x_error_messages);
366 RAISE e_no_page_found;
367 END;
368 END LOOP;
369
370 SELECT RRS_ATTR_GROUP_PAGE_REC(
371 PAGE_ID, OBJECT_NAME, CLASSIFICATION_CODE,
372 DATA_LEVEL_INT_NAME, INTERNAL_NAME, DISPLAY_NAME,
373 DESCRIPTION, SEQUENCE)
374 BULK COLLECT
375 INTO x_ag_page_tab
376 FROM TABLE(l_attr_group_page_tab)
377 ORDER BY SEQUENCE;
378
379
380
381 /*For each page, get the Page Entries Information*/
382 FOR i in 1 .. x_ag_page_tab.COUNT
383 LOOP
384 /*Get Page Entries In the Order of SEQUENCE*/
385 BEGIN
386 SELECT ATTR_GROUP_TYPE, ATTR_GROUP_NAME, SEQUENCE
387 BULK COLLECT
388 INTO l_page_entries
389 FROM EGO_PAGE_ENTRIES_V
390 WHERE PAGE_ID = l_page_id_array(i)
391 AND CLASSIFICATION_CODE = l_classification_code
392 ORDER BY SEQUENCE;
393
394 EXCEPTION
395 WHEN NO_DATA_FOUND THEN
396 Record_Error('Get_Attribute_Group_Page: No attribute page entry was found for page ' || l_page_id_array(i) || '.', x_error_messages);
397 RAISE e_no_page_entry_found;
398 END;
399
400 IF l_page_entries IS NULL OR l_page_entries.COUNT = 0 THEN
401 Record_Error('Get_Attribute_Group_Page: No attribute page entry was found for page ' || l_page_id_array(i) || '.', x_error_messages);
402 RAISE e_no_page_entry_found;
403 END IF;
404
405 /*Get Attribute Group Information*/
406 FOR j in 1 .. l_page_entries.COUNT
407 LOOP
408 BEGIN
409 SELECT RRS_ATTRIBUTE_GROUP_REC(
410 l_page_id_array(i),
411 ATTR_GROUP_ID,
412 ATTR_GROUP_TYPE,
413 ATTR_GROUP_NAME,
414 ATTR_GROUP_DISP_NAME,
415 DESCRIPTION,
416 MULTI_ROW_CODE,
417 SECURITY_CODE,
418 NUM_OF_COLS,
419 NUM_OF_ROWS,
420 l_page_entries(j).sequence)
421 INTO l_attribute_group_rec
422 FROM EGO_ATTR_GROUPS_V
423 WHERE APPLICATION_ID = 718
424 AND ATTR_GROUP_TYPE = l_page_entries(j).attr_group_type
425 AND ATTR_GROUP_NAME = l_page_entries(j).attr_group_name;
426
427 /*Added for RBAC Project, check View privilege*/
428 l_has_view_priv := RRS_SECURITY_PUB.Check_UDA_View_Privilege(p_api_version => 1.0,
429 p_attr_group_id => l_attribute_group_rec.ATTR_GROUP_ID,
430 p_object_name => l_obj_name,
431 p_object_key => p_object_id);
432 --if user has view privelege on this attribute group, we process this attribute group; otherwise we will not include this attribute group in the bracket for the further process.
433 IF l_has_view_priv = FND_API.G_TRUE THEN
434 IF x_attr_group_tab IS NULL THEN
435 x_attr_group_tab := new rrs_attribute_group_tab();
436 END IF;
437
438 x_attr_group_tab.EXTEND();
439 x_attr_group_tab(x_attr_group_tab.LAST) := new rrs_attribute_group_rec(
440 l_attribute_group_rec.PAGE_ID,
441 l_attribute_group_rec.ATTR_GROUP_ID,
442 l_attribute_group_rec.ATTR_GROUP_TYPE,
443 l_attribute_group_rec.ATTR_GROUP_NAME,
444 l_attribute_group_rec.ATTR_GROUP_DISP_NAME,
445 l_attribute_group_rec.DESCRIPTION,
446 l_attribute_group_rec.MULTI_ROW_CODE,
447 l_attribute_group_rec.SECURITY_CODE,
448 l_attribute_group_rec.NUM_OF_COLS,
449 l_attribute_group_rec.NUM_OF_ROWS,
450 l_attribute_group_rec.SEQUENCE);
451
452 END IF;
453 EXCEPTION
454 WHEN NO_DATA_FOUND THEN
455 Record_Error('Get_Attribute_Group_Page: No attribute group was found for page entry ' || l_page_entries(j).attr_group_type || ' ' || l_page_entries(j).attr_group_name || '.', x_error_messages);
456 RAISE e_no_attr_group_found;
457 END;
458 END LOOP;
459
460
461 /*For each attribute group, get attributes*/
462 FOR j in 1 .. x_attr_group_tab.COUNT
463 LOOP
464 BEGIN
465 /*For each attribute group, get Extension IDs*/
466 l_query := 'SELECT EXTENSION_ID' ||
467 ' FROM ' || l_ext_table_name ||
468 ' WHERE ' || l_pk_name || ' = :1'||
469 ' AND ' || l_classification_name || ' = :2'||
470 ' AND ATTR_GROUP_ID = :3';
471 EXECUTE IMMEDIATE l_query
472 BULK COLLECT INTO l_ext_id_array
473 USING p_object_id, l_classification_code, x_attr_group_tab(j).ATTR_GROUP_ID;
474
475 /*Get Attribute Names for the attribute group*/
476 BEGIN
477 SELECT ATTR_NAME, ATTR_DISPLAY_NAME, DISPLAY_CODE, DESCRIPTION, SEQUENCE
478 BULK COLLECT
479 INTO l_attribute_tab
480 FROM EGO_ATTRS_V
481 WHERE APPLICATION_ID = 718
482 AND ATTR_GROUP_TYPE = x_attr_group_tab(j).ATTR_GROUP_TYPE
483 AND ATTR_GROUP_NAME = x_attr_group_tab(j).ATTR_GROUP_NAME
484 AND ENABLED_FLAG = 'Y' --Bug Fix 9709262: The disabled attribute will not show up.
485 ORDER BY SEQUENCE;
486
487 EXCEPTION
488 WHEN NO_DATA_FOUND THEN
489 Record_Error('Get_Attribute_Group_Page: No attribute was found for ' || x_attr_group_tab(j).ATTR_GROUP_TYPE || ' ' || x_attr_group_tab(j).ATTR_GROUP_NAME || '.', x_error_messages);
490 RAISE e_no_attribute_found;
494 Record_Error('Get_Attribute_Group_Page: No attribute was found for ' || x_attr_group_tab(j).ATTR_GROUP_TYPE || ' ' || x_attr_group_tab(j).ATTR_GROUP_NAME || '.', x_error_messages);
491 END;
492
493 IF l_attribute_tab IS NULL OR l_attribute_tab.COUNT = 0 THEN
495 RAISE e_no_attribute_found;
496 END IF;
497
498 /*For each EXT Row, we get the display value for each attribute*/
499 FOR k in 1 .. l_ext_id_array.COUNT
500 LOOP
501 FOR p in 1 .. l_attribute_tab.COUNT
502 LOOP
503 --DBMS_OUTPUT.PUT_LINE('p_attr_group_type: ' || x_attr_group_tab(j).ATTR_GROUP_TYPE);
504 Get_Display_Value(p_object_type,
505 p_object_id,
506 x_attr_group_tab(j).ATTR_GROUP_TYPE,
507 x_attr_group_tab(j).ATTR_GROUP_NAME,
508 l_attribute_tab(p).ATTR_NAME,
509 l_ext_id_array(k),
510 l_x_display_value,
511 l_x_display_type,
512 l_x_dynamic_url,
513 x_error_messages);
514
515 IF x_attribute_tab IS NULL THEN
516 x_attribute_tab := new rrs_attribute_tab();
517 END IF;
518
519 x_attribute_tab.EXTEND();
520 x_attribute_tab(x_attribute_tab.LAST) := new rrs_attribute_rec(
521 x_attr_group_tab(j).ATTR_GROUP_TYPE,
522 x_attr_group_tab(j).ATTR_GROUP_NAME,
523 l_ext_id_array(k),
524 l_attribute_tab(p).ATTR_NAME,
525 l_attribute_tab(p).ATTR_DISPLAY_NAME,
526 l_attribute_tab(p).DESCRIPTION,
527 l_x_display_value,
528 l_x_display_type,
529 l_x_dynamic_url,
530 l_attribute_tab(p).SEQUENCE);
531 END LOOP;
532 END LOOP;
533
534 /*if there is no ext row for this attribute group, we still return the attribute names without any value*/
535 IF l_ext_id_array IS NULL OR l_ext_id_array.COUNT = 0 THEN
536 FOR p in 1 .. l_attribute_tab.COUNT
537 LOOP
538 IF x_attribute_tab IS NULL THEN
539 x_attribute_tab := new rrs_attribute_tab();
540 END IF;
541 x_attribute_tab.EXTEND();
542 l_display_type_code := 'T of C'; -- dummy display type
543 --Bug Fix 9453429: Need consider the Hidden Data Type even for the empty attribute group
544 IF l_attribute_tab(p).DISPLAY_CODE = 'H' THEN
545 l_display_type_code := 'H of C';
546 END IF;
547 x_attribute_tab(x_attribute_tab.LAST) := new rrs_attribute_rec(
548 x_attr_group_tab(j).ATTR_GROUP_TYPE,
549 x_attr_group_tab(j).ATTR_GROUP_NAME,
550 NULL, -- ext_id
551 l_attribute_tab(p).ATTR_NAME,
552 l_attribute_tab(p).ATTR_DISPLAY_NAME,
553 l_attribute_tab(p).DESCRIPTION,
554 NULL, -- display value
555 l_display_type_code,
556 NULL,
557 l_attribute_tab(p).SEQUENCE);
558 END LOOP;
559 END IF;
560 EXCEPTION
561 WHEN e_no_attribute_found THEN
562 DBMS_OUTPUT.PUT_LINE('No availabe attributes for attribute group ' || x_attr_group_tab(j).ATTR_GROUP_NAME);
563 END;
564 END LOOP; --AG LOOP
565 END LOOP; -- PAGE LOOP
566
567
568 EXCEPTION
569 WHEN e_no_uda THEN
570 Record_Error('e_no_uda: ' || p_object_type || ' does not have UDA.', x_error_messages);
571 WHEN e_no_page_found THEN
572 Record_Error('e_no_page_found', x_error_messages);
573 WHEN e_no_page_entry_found THEN
574 Record_Error('e_no_page_entry_found', x_error_messages);
575 WHEN e_no_attr_group_found THEN
576 Record_Error('e_no_attr_group_found', x_error_messages);
577 WHEN e_no_ext_row_found THEN
578 Record_Error('e_no_ext_row_found', x_error_messages);
579 WHEN e_no_attribute_found THEN
580 Record_Error('e_no_attribute_found', x_error_messages);
581 WHEN OTHERS THEN
582 Record_Error('Get_Attribute_Group_Page: ' || dbms_utility.format_error_backtrace, x_error_messages);
583 --DBMS_OUTPUT.PUT_LINE('Other exceptions in Get_Attribute_Page');
584 --dbms_output.put_line(dbms_utility.format_error_backtrace);
585 END;
586
587
588 PROCEDURE Get_Display_Value
589 (
590 p_object_type IN VARCHAR2,
591 p_object_id IN NUMBER,
592 p_attr_group_type IN VARCHAR2,
593 p_attr_group_name IN VARCHAR2,
594 p_attr_name IN VARCHAR2,
595 p_ext_id IN NUMBER,
596 x_display_value OUT NOCOPY VARCHAR2,
597 x_display_type OUT NOCOPY VARCHAR2,
598 x_dynamic_url OUT NOCOPY VARCHAR2,
599 x_error_messages OUT NOCOPY rrs_error_msg_tab
600 ) IS
601
602
603 l_query VARCHAR2(2000);
604
605 l_ext_table_name VARCHAR2(200);
606 l_pk_name VARCHAR2(30);
607 l_attr_group_id EGO_ATTR_GROUPS_V.ATTR_GROUP_ID%TYPE;
608
609 l_attr_info attr_info_rec;
610 l_code_value VARCHAR2(1000);
611 l_date_value DATE;
612
613 l_uom_code RRS_SITES_EXT_VL.UOM_EXT_ATTR1%TYPE;
614 l_uom MTL_UNITS_OF_MEASURE_VL.UNIT_OF_MEASURE_TL%TYPE;
615 l_uom_conversion_rate MTL_UOM_CONVERSIONS.CONVERSION_RATE%TYPE;
616
617 l_index NUMBER := 1;
618 l_dynamic_url EGO_ATTRS_V.INFO_1%TYPE;
619 l_url_value EGO_ATTRS_V.INFO_1%TYPE;
620 l_url_tokens array_of_string := new array_of_string();
621
622 l_x_display_value VARCHAR2(1000);
623 l_x_display_type VARCHAR2(200);
624 l_x_dynamic_url VARCHAR2(1000);
625
626 /*For Table Value Set query*/
627 l_app_table_name FND_FLEX_VALIDATION_TABLES.APPLICATION_TABLE_NAME%TYPE;
628 l_value_column_name FND_FLEX_VALIDATION_TABLES.VALUE_COLUMN_NAME%TYPE;
629 l_value_column_type FND_FLEX_VALIDATION_TABLES.VALUE_COLUMN_TYPE%TYPE;
633
630 l_id_column_name FND_FLEX_VALIDATION_TABLES.ID_COLUMN_NAME%TYPE;
631 l_add_where_clause FND_FLEX_VALIDATION_TABLES.ADDITIONAL_WHERE_CLAUSE%TYPE;
632 l_add_where_clause_holder l_add_where_clause%TYPE;
634 l_date_format_mask VARCHAR2(100);
635
636 --Bug Fix for Bug 9012596 - jijiao 10/15/2009
637 l_dep_attr_name EGO_ATTRS_V.ATTR_NAME%TYPE;
638 l_start_index NUMBER;
639 l_next_space_index NUMBER;
640 l_dep_column EGO_ATTRS_V.DATABASE_COLUMN%TYPE;
641 l_dep_data_type EGO_ATTRS_V.DATA_TYPE_CODE%TYPE;
642 l_dep_code_value VARCHAR2(1000);
643 l_dep_date_value DATE;
644
645 BEGIN
646
647 --DBMS_OUTPUT.PUT_LINE('p_object_type: ' || p_object_type);
648 /* decide which object type we are processing*/
649 IF UPPER(p_object_type) = 'SITE' THEN
650 l_ext_table_name := 'RRS_SITES_EXT_VL';
651 l_pk_name := 'SITE_ID';
652 ELSIF UPPER(p_object_type) = 'LOCATION' THEN
653 l_ext_table_name := 'RRS_LOCATIONS_EXT_VL';
654 l_pk_name := 'LOCATION_ID';
655 ELSIF UPPER(p_object_type) = 'TRADE AREA' THEN
656 l_ext_table_name := 'RRS_TRADE_AREAS_EXT_VL';
657 l_pk_name := 'TRADE_AREA_ID';
658 ELSIF UPPER(p_object_type) = 'HIERARCHY' THEN
659 l_ext_table_name := 'RRS_HIERARCHIES_EXT_VL';
660 l_pk_name := 'SITE_GROUP_VERSION_ID';
661 END IF;
662
663 --DBMS_OUTPUT.PUT_LINE('l_ext_table_name: ' || l_ext_table_name);
664 --DBMS_OUTPUT.PUT_LINE('l_attr_group_name: ' || p_attr_group_type);
665
666 /* query for database column set up for the attribute*/
667 BEGIN
668 --DBMS_OUTPUT.PUT_LINE('p_attr_group_type: ' || p_attr_group_type);
669 --DBMS_OUTPUT.PUT_LINE('p_attr_group_name: ' || p_attr_group_name);
670 --DBMS_OUTPUT.PUT_LINE('p_attr_name: ' || p_attr_name);
671
672 --Bug Fix 8989777 - jijiao 10/12/2009
673 --Need add Attribute Group Type to the where clause to handle the Same Attribute Group Names for the different Attribute Group Types
674 BEGIN
675 SELECT DATABASE_COLUMN, DATA_TYPE_CODE, INFO_1, UOM_CLASS, VALUE_SET_ID, VALIDATION_CODE, DISPLAY_CODE, DISPLAY_MEANING
676 INTO l_attr_info
677 FROM EGO_ATTRS_V
678 WHERE APPLICATION_ID = 718
679 AND ATTR_GROUP_TYPE = p_attr_group_type
680 AND ATTR_GROUP_NAME = p_attr_group_name
681 AND ATTR_NAME = p_attr_name;
682 EXCEPTION
683 WHEN NO_DATA_FOUND THEN
684 Record_Error('Get_Display_Value: No Display Value is found.', x_error_messages);
685 WHEN OTHERS THEN
686 Record_Error('Get_Display_Value: When getting display value, get '|| sqlcode ||': '||sqlerrm, x_error_messages);
687 END;
688 --DBMS_OUTPUT.PUT_LINE('l_database_column: ' || l_attr_info.value_set_id);
689
690 EXCEPTION
691 WHEN NO_DATA_FOUND THEN
692 Record_Error('Get_Display_Value: No attribute was found for ' || p_attr_group_type || ' ' || p_attr_group_name || '.', x_error_messages);
693 RAISE e_no_attribute_found;
694 END;
695
696
697 /* query attribute group id */
698 BEGIN
699 SELECT ATTR_GROUP_ID
700 INTO l_attr_group_id
701 FROM EGO_ATTR_GROUPS_V
702 WHERE APPLICATION_ID = 718
703 AND ATTR_GROUP_TYPE = p_attr_group_type
704 AND ATTR_GROUP_NAME = p_attr_group_name;
705 --DBMS_OUTPUT.PUT_LINE('l_attr_group_id: ' || l_attr_group_id);
706
707 EXCEPTION
708 WHEN NO_DATA_FOUND THEN
709 Record_Error('Get_Display_Value: No attribute group was found for attribute group name ' || p_attr_group_name || '.', x_error_messages);
710 RAISE e_no_attr_group_found;
711 END;
712
713
714 /* query the code value*/
715 BEGIN
716 /*For Date or DateTime Attributes, we need maintain the DATE data type to keep the full date/time infomation*/
717 IF l_attr_info.data_type_code = 'X' OR l_attr_info.data_type_code = 'Y' THEN
718 l_query := 'SELECT ' || l_attr_info.database_column ||
719 ' FROM ' || l_ext_table_name ||
720 ' WHERE ' || l_pk_name || ' = :1' ||
721 ' AND ATTR_GROUP_ID = :2' ||
722 ' AND EXTENSION_ID = :3';
723 EXECUTE IMMEDIATE l_query INTO l_date_value USING p_object_id, l_attr_group_id, p_ext_id;
724 /* for Numeric Attribute, we need consider UOM*/
725 ElSIF l_attr_info.uom_class IS NOT NULL THEN
726 l_query := 'SELECT ' || l_attr_info.database_column || ', ' || REPLACE(l_attr_info.database_column, 'N', 'UOM') ||
727 ' FROM ' || l_ext_table_name ||
728 ' WHERE ' || l_pk_name || ' = :1' ||
729 ' AND ATTR_GROUP_ID = :2' ||
730 ' AND EXTENSION_ID = :3';
731 EXECUTE IMMEDIATE l_query INTO l_code_value, l_uom_code USING p_object_id, l_attr_group_id, p_ext_id;
732 --DBMS_OUTPUT.PUT_LINE(l_attr_info.database_column);
733 --DBMS_OUTPUT.PUT_LINE(l_code_value || ' ' || l_uom_code);
734
735 ELSE
736 l_query := 'SELECT ' || l_attr_info.database_column ||
737 ' FROM ' || l_ext_table_name ||
738 ' WHERE ' || l_pk_name || ' = :1' ||
739 ' AND ATTR_GROUP_ID = :2' ||
740 ' AND EXTENSION_ID = :3';
741 --DBMS_OUTPUT.PUT_LINE(l_query);
745 EXCEPTION
742 EXECUTE IMMEDIATE l_query INTO l_code_value USING p_object_id, l_attr_group_id, p_ext_id;
743 END IF;
744
746 WHEN NO_DATA_FOUND THEN
747 Record_Error('Get_Display_Value: No extension row was found for Extension Id ' || p_ext_id || '.', x_error_messages);
748 RAISE e_no_ext_row_found;
749 END;
750
751 /* Can I say: Because only Numeric Attribute can have UOM, so the attribute has UOM is Numeric.
752 And because the Numeric Value Set's value code will be identifcal with the value meaning,
753 so for the attribute with UOM I do not need consider Value Set issue, but just directly display the value stored in N_EXT_ATTR column of the EXT table?*/
754 IF l_attr_info.data_type_code = 'N' AND l_uom_code IS NOT NULL THEN
755 /* NEED CONVERT The Numberic Value stored in N_EXT_ATTR column from the BASE UOM to the UOM user input*/
756 BEGIN
757 /* query for the translated uom meaning*/
758 SELECT UNIT_OF_MEASURE_TL
759 INTO l_uom
760 FROM MTL_UNITS_OF_MEASURE_VL
761 WHERE UOM_CODE = l_uom_code;
762
763 /* query for the uom conversion rate*/
764 SELECT CONVERSION_RATE
765 INTO l_uom_conversion_rate
766 FROM MTL_UOM_CONVERSIONS
767 WHERE UOM_CODE = l_uom_code;
768
769 --DBMS_OUTPUT.PUT_LINE(l_uom_conversion_rate);
770
771 /* construct the display value at the proper uom*/
772 SELECT ((l_code_value / l_uom_conversion_rate) || ' ' || l_uom)
773 INTO x_display_value
774 FROM DUAL;
775 --Bug 9247705: Use display_code rather than display_meaning.
776 x_display_type := l_attr_info.display_code || ' of ' || l_attr_info.data_type_code || ' with UOM';
777
778 --DBMS_OUTPUT.PUT_LINE(x_display_value || ' is displayed as ' || x_display_type);
779 EXCEPTION
780 WHEN NO_DATA_FOUND THEN
781 Record_Error('Get_Display_Value: No Unit Of Measure was found for the UOM Code ' || l_uom_code || '.', x_error_messages);
782 RAISE e_no_uom_found;
783 END;
784 -- Bug 9247705: Use display_code rather than display_meaning.
785 -- T Text Field
786 -- A Attachment
787 -- D Dynamic URL
788 -- L Text Area
789 -- S Static URL
790 -- C Checkbox
791 -- H Hidden
792 -- R Radio Group
793 ELSIF l_attr_info.display_code = 'D' THEN
794 /* NEED QUERY The Display Value for the TOKEN. For the Numeric Value with UOM, we only need the Numeric Value, but need conert the value from BASE UOM to the UOM user input*/
795 BEGIN
796 l_dynamic_url := l_attr_info.info_1;
797 LOOP
798 -- NO dollar sign found - no token in the url
799 EXIT WHEN (INSTR(l_dynamic_url, '$', 1, l_index) = 0);
800 l_index := l_index + 1;
801 l_url_tokens.EXTEND;
802 l_url_tokens(l_url_tokens.LAST) := SUBSTR(l_dynamic_url, INSTR(l_dynamic_url, '$', 1, l_index-1) + 1,
803 INSTR(l_dynamic_url, '$', 1, l_index) - INSTR(l_dynamic_url, '$', 1, l_index-1) -1
804 );
805 --DBMS_OUTPUT.PUT_LINE(l_url_tokens(l_url_tokens.LAST));
806
807 Get_Display_Value(p_object_type,
808 p_object_id,
809 p_attr_group_type,
810 p_attr_group_name,
811 l_url_tokens(l_url_tokens.LAST),
812 p_ext_id,
813 l_x_display_value,
814 l_x_display_type,
815 l_x_dynamic_url,
816 x_error_messages);
817
818 --DBMS_OUTPUT.PUT_LINE(l_x_display_value);
819 /*if there is UOM with the Numeric value, then we need get rid of the UOM, because in the Dynamic URL, we only use the EXT_ATTR value to take place of the token*/
820 IF INSTR(l_x_display_type, 'N with UOM') > 0 THEN
821 l_dynamic_url := REPLACE(l_dynamic_url, '$'||(l_url_tokens(l_url_tokens.LAST))||'$', SUBSTR(l_x_display_value, 1, INSTR(l_x_display_value, ' ')-1));
822 ELSE
823 l_dynamic_url := REPLACE(l_dynamic_url, '$'||(l_url_tokens(l_url_tokens.LAST))||'$', l_x_display_value);
824 END IF;
825
826 l_index := 1;
827 END LOOP;
828
829 x_display_value := l_code_value;
830 --Bug 9247705: Use display_code rather than display_meaning.
831 x_display_type := l_attr_info.display_code || ' of ' || l_attr_info.data_type_code;
832 x_dynamic_url := l_dynamic_url;
833
834 --DBMS_OUTPUT.PUT_LINE('x_display_value: ' || x_display_value);
835 --DBMS_OUTPUT.PUT_LINE('x_display_type: ' || x_display_type);
836 --DBMS_OUTPUT.PUT_LINE('x_dynamic_url: ' || x_dynamic_url);
837 END;
838
839 /*The attribute with value set*/
840 ELSIF l_attr_info.value_set_id IS NOT NULL THEN
841 BEGIN
842 /*For the regular independent value set, we direclty reutrn FLEX_VALUE stored in EXT_ATTR column*/
843 IF l_attr_info.validation_code = 'I' THEN
844 /*If the attribute is DATE type, we need convert the format*/
845 IF l_attr_info.data_type_code = 'X' OR l_attr_info.data_type_code = 'Y' THEN
846
847 SELECT FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')
848 INTO l_date_format_mask
849 FROM DUAL;
850
851 IF l_attr_info.data_type_code = 'Y' THEN
852 l_date_format_mask := l_date_format_mask || ' HH24:MI:SS';
853 END IF;
854
855 --Bug Fix 9709269
856 l_date_value := To_Client_Timezone(l_date_value);
857 --End Bug Fix 9709269
858 x_display_value := TO_CHAR(l_date_value, l_date_format_mask);
859 ELSE
860 x_display_value := l_code_value;
861 END IF;
862
863 /*For the translatable value set, we return FLEX_VALUE_MEANING*/
864 ELSIF l_attr_info.validation_code = 'X' THEN
865 BEGIN
866 /*If the attribute is DATE type, we need convert the format*/
867 IF (l_attr_info.data_type_code = 'X' OR l_attr_info.data_type_code = 'Y') AND l_date_value IS NOT NULL THEN
868 SELECT FLEX_VALUE_MEANING
869 INTO x_display_value
873
870 FROM FND_FLEX_VALUES_VL
871 WHERE FLEX_VALUE_SET_ID = l_attr_info.value_set_id
872 AND FLEX_VALUE = l_date_value;
874 SELECT FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')
875 INTO l_date_format_mask
876 FROM DUAL;
877
878 IF l_attr_info.data_type_code = 'Y' THEN
879 l_date_format_mask := l_date_format_mask || ' HH24:MI:SS';
880 END IF;
881
882 --Bug Fix 9709269
883 x_display_value := To_Client_Timezone(x_display_value);
884 --End Bug Fix 9709269
885
886 x_display_value := TO_CHAR(x_display_value, l_date_format_mask);
887 --Bug 9247705: Use display_code rather than display_meaning.
888 x_display_type := l_attr_info.display_code || ' of ' || l_attr_info.data_type_code;
889 ELSIF l_code_value IS NOT NULL THEN
890 SELECT FLEX_VALUE_MEANING
891 INTO x_display_value
892 FROM FND_FLEX_VALUES_VL
893 WHERE FLEX_VALUE_SET_ID = l_attr_info.value_set_id
894 AND FLEX_VALUE = l_code_value;
895 END IF;
896 EXCEPTION
897 WHEN NO_DATA_FOUND THEN
898 RAISE e_no_value_set_value_found;
899 WHEN OTHERS THEN
900 Record_Error('Get_Display_Value: ' || dbms_utility.format_error_backtrace, x_error_messages);
901 END;
902 /*For the table value set, we assemble the query to get the value from the target table*/
903 ELSIF l_attr_info.validation_code = 'F' THEN
904 SELECT APPLICATION_TABLE_NAME, VALUE_COLUMN_NAME,
905 VALUE_COLUMN_TYPE, ID_COLUMN_NAME, ADDITIONAL_WHERE_CLAUSE
906 INTO l_app_table_name, l_value_column_name,
907 l_value_column_type, l_id_column_name, l_add_where_clause
908 FROM FND_FLEX_VALIDATION_TABLES
909 WHERE FLEX_VALUE_SET_ID = l_attr_info.value_set_id;
910
911 IF l_add_where_clause IS NOT NULL THEN
912 -- use l_add_where_clause_holder as an index ruler for the l_add_where_clause
913 -- because we cannot predict what user will enter. There could be no WHERE in Where Clause or different combination of capitalization
914 -- We cannot simply UPPER the original l_add_where_clause, because that will modify the original dependent attribute name, which is CASE SENSITIVE.
915 -- But the $ATTRIBUTEGROUP$ has to be all UPPER case, otherwise OA page will also error out.
916 l_add_where_clause_holder := UPPER(l_add_where_clause);
917 IF INSTR(l_add_where_clause_holder, 'WHERE') > 0 THEN
918 l_add_where_clause := SUBSTR(l_add_where_clause, INSTR(l_add_where_clause_holder, 'WHERE') + 6);
919 -- keep consistency between original l_add_where_clause and index ruler l_add_where_clause_holder
920 l_add_where_clause_holder := SUBSTR(l_add_where_clause_holder, INSTR(l_add_where_clause_holder, 'WHERE') + 6);
921 END IF;
922 --DBMS_OUTPUT.PUT_LINE(l_add_where_clause);
923 --DBMS_OUTPUT.PUT_LINE(l_add_where_clause_holder);
924 --Bug Fix for Bug 9029322 - jijiao 10/15/2009
925 --Need handle the DEPENDENT value set, which is depends on another attributes in the same attribute group
926 IF INSTR(l_add_where_clause, ':$ATTRIBUTEGROUP$') > 0 THEN
927 l_start_index := INSTR(l_add_where_clause, ':$ATTRIBUTEGROUP$.') + 18;
928 l_next_space_index := INSTR(l_add_where_clause, ' ', INSTR(l_add_where_clause, ':$ATTRIBUTEGROUP$'), 1);
929
930 IF l_next_space_index > 0 THEN
931 l_dep_attr_name := SUBSTR(l_add_where_clause, l_start_index, (l_next_space_index - l_start_index));
932 ELSE
933 --if the dependent value is written at the end of the Where Clause
934 l_dep_attr_name := SUBSTR(l_add_where_clause, l_start_index, LENGTH(l_add_where_clause) - l_start_index + 1);
935 END IF;
936 --DBMS_OUTPUT.PUT_LINE('l_next_space_index: ' || l_next_space_index);
937 --DBMS_OUTPUT.PUT_LINE('l_dep_attr_name: ' || l_dep_attr_name);
938
939 BEGIN
940 SELECT DATABASE_COLUMN, DATA_TYPE_CODE
941 INTO l_dep_column, l_dep_data_type
942 FROM EGO_ATTRS_V
943 WHERE APPLICATION_ID = 718
944 AND ATTR_GROUP_TYPE = p_attr_group_type
945 AND ATTR_GROUP_NAME = p_attr_group_name
946 AND ATTR_NAME = l_dep_attr_name;
947 EXCEPTION
948 WHEN NO_DATA_FOUND THEN
949 Record_Error('Get_Display_Value: Dependent Attribute is not found', x_error_messages);
950 WHEN OTHERS THEN
951 Record_Error('Other exception'||sqlerrm||' and code '||sqlcode, x_error_messages);
952 END;
953
954 l_query := 'SELECT ' || l_dep_column ||
955 ' FROM ' || l_ext_table_name ||
956 ' WHERE ' || l_pk_name || ' = :1' ||
957 ' AND ATTR_GROUP_ID = :2' ||
958 ' AND EXTENSION_ID = :3';
959 --DBMS_OUTPUT.PUT_LINE(l_query);
960 IF l_dep_data_type = 'X' OR l_dep_data_type = 'Y' THEN
961 EXECUTE IMMEDIATE l_query INTO l_dep_date_value USING p_object_id, l_attr_group_id, p_ext_id;
962 IF l_dep_date_value IS NOT NULL THEN
963 -- For the dependent value set that depends on a Date type value set, in the query, we need explictly convert the CHAR type of date value TO DATE.
964 l_add_where_clause := REPLACE(l_add_where_clause, ':$ATTRIBUTEGROUP$.' || l_dep_attr_name, 'TO_DATE(''' || l_dep_date_value || ''')');
965 ELSE
966 IF INSTR(l_add_where_clause, '= :$ATTRIBUTEGROUP$.') > 0 THEN
967 l_add_where_clause := REPLACE(l_add_where_clause, '= :$ATTRIBUTEGROUP$.' || l_dep_attr_name, ' IS NULL');
968 ELSIF INSTR(l_add_where_clause, '=:$ATTRIBUTEGROUP$.') > 0 THEN
972
969 l_add_where_clause := REPLACE(l_add_where_clause, '=:$ATTRIBUTEGROUP$.' || l_dep_attr_name, ' IS NULL');
970 END IF;
971 END IF;
973 ELSE
974 EXECUTE IMMEDIATE l_query INTO l_dep_code_value USING p_object_id, l_attr_group_id, p_ext_id;
975 IF l_dep_code_value IS NOT NULL THEN
976 -- Bug Fix 9029322 - If the dependent value is CHAR, we have to put single quotes around the value in the query condition.
977 -- Actually here we also embrace Number by the single quotes, but SQL compiler will do the convertion for us. - jijiao 12/5/2009
978 l_add_where_clause := REPLACE(l_add_where_clause, ':$ATTRIBUTEGROUP$.' || l_dep_attr_name, '''' || l_dep_code_value || '''');
979 ELSE
980 IF INSTR(l_add_where_clause, '= :$ATTRIBUTEGROUP$.') > 0 THEN
981 l_add_where_clause := REPLACE(l_add_where_clause, '= :$ATTRIBUTEGROUP$.' || l_dep_attr_name, ' IS NULL');
982 ELSIF INSTR(l_add_where_clause, '=:$ATTRIBUTEGROUP$.') > 0 THEN
983 l_add_where_clause := REPLACE(l_add_where_clause, '=:$ATTRIBUTEGROUP$.' || l_dep_attr_name, ' IS NULL');
984 END IF;
985 END IF;
986
987 END IF;
988 --DBMS_OUTPUT.PUT_LINE('l_add_where_clause: ' || l_add_where_clause);
989
990 END IF;
991
992 -- Bug Fix 9029322, Need handle the case where user does not define ID COLUMN for the value set.
993 IF l_id_column_name IS NOT NULL THEN
994 l_query := 'SELECT ' || l_value_column_name ||
995 ' FROM ' || l_app_table_name ||
996 ' WHERE ' || l_id_column_name || ' = :1' ||
997 ' AND ' || l_add_where_clause;
998 ELSE
999 l_query := 'SELECT ' || l_value_column_name ||
1000 ' FROM ' || l_app_table_name ||
1001 ' WHERE ' || l_add_where_clause;
1002 END IF;
1003 --DBMS_OUTPUT.PUT_LINE(l_add_where_clause);
1004 --DBMS_OUTPUT.PUT_LINE(l_query);
1005 -- Bug Fix for Bug 9012596 - jijiao 10/12/2009
1006 -- Need handle the case there is no Additional Where Clause
1007 -- ALSO NEED HANDLE NO ID COLUMN - THE POPLIST VALUE SET CASE! - jijiao 10/16/2009
1008 ELSIF l_id_column_name IS NOT NULL THEN
1009 l_query := 'SELECT ' || l_value_column_name ||
1010 ' FROM ' || l_app_table_name ||
1011 ' WHERE ' || l_id_column_name || ' = :1';
1012 -- End of Bug Fix
1013 END IF;
1014 /*If the attribute is DATE type, we need convert the format*/
1015 -- Bug Fix for Bug 9012630 - jijiao 10/12/2009
1016 -- Need handle the l_date_value is NULL case, which means there is no value for this Value Set attribute
1017 IF (l_attr_info.data_type_code = 'X' OR l_attr_info.data_type_code = 'Y') AND l_date_value IS NOT NULL THEN
1018 -- Bug Fix for Bug 9012596
1019 -- ALSO NEED HANDLE NO ID COLUMN - THE POPLIST VALUE SET CASE! - jijiao 10/16/2009
1020 IF l_id_column_name IS NOT NULL THEN
1021 EXECUTE IMMEDIATE l_query INTO x_display_value USING l_date_value;
1022 ELSE
1023 -- for the POPLIST value set, the display value is stored directly in the EXT TABLE
1024 x_display_value := l_date_value;
1025 END IF;
1026 SELECT FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')
1027 INTO l_date_format_mask
1028 FROM DUAL;
1029
1030 IF l_attr_info.data_type_code = 'Y' THEN
1031 l_date_format_mask := l_date_format_mask || ' HH24:MI:SS';
1032 END IF;
1033 --Bug Fix 9709269
1034 x_display_value := To_Client_Timezone(x_display_value);
1035 --End Bug Fix 9709269
1036
1037 x_display_value := TO_CHAR(x_display_value, l_date_format_mask);
1038 -- Bug Fix for Bug 9012630 - jijiao 10/12/2009
1039 -- Need handle the l_code_value is NULL case, which means there is no value for this Value Set attribute
1040 ELSIF l_code_value IS NOT NULL THEN
1041 BEGIN
1042 --DBMS_OUTPUT.PUT_LINE(l_query);
1043
1044 -- Bug Fix for Bug 9012596
1045 -- ALSO NEED HANDLE NO ID COLUMN - THE POPLIST VALUE SET CASE! - jijiao 10/16/2009
1046 IF l_id_column_name IS NOT NULL THEN
1047 EXECUTE IMMEDIATE l_query INTO x_display_value USING l_code_value;
1048 ELSE
1049 -- for the POPLIST value set, the display value is stored directly in the EXT TABLE
1050 x_display_value := l_code_value;
1051 END IF;
1052 EXCEPTION
1053 WHEN NO_DATA_FOUND THEN
1054 Record_Error('Get_Display_Value: No Display Value is found.', x_error_messages);
1055 WHEN OTHERS THEN
1056 Record_Error('Get_Display_Value: When getting display value, get '|| sqlcode ||': '||sqlerrm, x_error_messages);
1057 END;
1058
1059 END IF;
1060 -- Bug Fix for 9098853 - jijiao 11/11/2009
1061 -- Need deal with None Validation explicitly. For the None Vadilation Type, because here we only retrieve and display the UDA data
1062 -- we can assume the data has been passed the None Vadlidation, such as the Maximum/minimum length.
1063 -- And once the attribute is associated with None Validation Type value set, it can only be the simple type, which no need to lookup any Independent/Table value set anymore
1064 -- For the only special case which is Number with UOM, it has been handled at the beginning of this procedure, Number with UOM case.
1065 /*Do nothing for the None Validation Type*/
1066 ELSIF l_attr_info.validation_code = 'N' THEN
1067 --DATE OR DATETIME data type
1068 IF (l_attr_info.data_type_code = 'X' OR l_attr_info.data_type_code = 'Y') AND l_date_value IS NOT NULL THEN
1069 SELECT FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')
1070 INTO l_date_format_mask
1071 FROM DUAL;
1072
1073 IF l_attr_info.data_type_code = 'Y' THEN
1074 l_date_format_mask := l_date_format_mask || ' HH24:MI:SS';
1075 END IF;
1079 x_display_value := TO_CHAR(l_date_value, l_date_format_mask);
1076 --Bug Fix 9709269
1077 l_date_value := To_Client_Timezone(l_date_value);
1078 --End Bug Fix 9709269
1080 -- CHAR data type
1081 ELSIF l_code_value IS NOT NULL THEN
1082 x_display_value := l_code_value;
1083 END IF;
1084
1085 END IF;
1086 --Bug 9247705: Use display_code rather than display_meaning.
1087 x_display_type := l_attr_info.display_code || ' of ' || l_attr_info.data_type_code;
1088
1089 EXCEPTION
1090 WHEN e_invalid_where_clause THEN
1091 Record_Error('Invalid WHERE Clause defined in Dependent Value Set.', x_error_messages);
1092 WHEN OTHERS THEN
1093 Record_Error('Get_Display_Value: ' || dbms_utility.format_error_backtrace, x_error_messages);
1094 END;
1095
1096 /*For the DATE or DATETIME data types, the value stored in EXT table is the same as the value in value set meaning column*/
1097 /*What we need to do is to format the date value into the right format for the environment the user is in*/
1098 ELSIF l_attr_info.data_type_code = 'X' OR l_attr_info.data_type_code = 'Y' THEN
1099
1100 SELECT FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')
1101 INTO l_date_format_mask
1102 FROM DUAL;
1103
1104 IF l_attr_info.data_type_code = 'Y' THEN
1105 l_date_format_mask := l_date_format_mask || ' HH24:MI:SS';
1106 END IF;
1107 --Bug Fix 9709269
1108 l_date_value := To_Client_Timezone(l_date_value);
1109 --End Bug Fix 9709269
1110
1111 --DBMS_OUTPUT.PUT_LINE(l_date_format_mask);
1112 --DBMS_OUTPUT.PUT_LINE(l_code_value);
1113 x_display_value := TO_CHAR(l_date_value, l_date_format_mask);
1114 --DBMS_OUTPUT.PUT_LINE(x_display_value);
1115 --Bug 9247705: Use display_code rather than display_meaning.
1116 x_display_type := l_attr_info.display_code || ' of ' || l_attr_info.data_type_code;
1117
1118 /*For the rest cases, we just display as the value is in EXT table*/
1119 ELSE
1120 x_display_value := l_code_value;
1121 --Bug 9247705: Use display_code rather than display_meaning.
1122 x_display_type := l_attr_info.display_code || ' of ' || l_attr_info.data_type_code;
1123
1124 END IF;
1125
1126 --DBMS_OUTPUT.PUT_LINE('x_display_value: ' || x_display_value);
1127 --DBMS_OUTPUT.PUT_LINE('x_display_type: ' || x_display_type);
1128 --DBMS_OUTPUT.PUT_LINE('x_dynamic_url: ' || x_dynamic_url);
1129
1130
1131
1132 EXCEPTION
1133 WHEN e_no_attribute_found THEN
1134 Record_Error('e_no_attribute_found', x_error_messages);
1135 WHEN e_no_attr_group_found THEN
1136 Record_Error('e_no_attr_group_found', x_error_messages);
1137 WHEN e_no_ext_row_found THEN
1138 Record_Error('e_no_ext_row_found', x_error_messages);
1139 WHEN e_no_uom_found THEN
1140 Record_Error('e_no_uom_found', x_error_messages);
1141 WHEN e_no_value_set_value_found THEN
1142 Record_Error('e_no_value_set_value_found', x_error_messages);
1143 WHEN OTHERS THEN
1144 Record_Error('Get_Display_Value: ' || dbms_utility.format_error_backtrace, x_error_messages);
1145 END;
1146
1147
1148 Procedure Get_Display_Value
1149 (
1150 p_object_type IN VARCHAR2,
1151 p_object_id IN NUMBER,
1152 p_attr_group_id IN NUMBER,
1153 p_ext_id IN NUMBER,
1154 p_column_name IN VARCHAR2,
1155 x_display_value OUT NOCOPY VARCHAR2,
1156 x_msg_data OUT NOCOPY VARCHAR2
1157 ) IS
1158
1159 l_object_type_val VARCHAR2(30); -- convert to the object type value that consumed by RRS_ATTR_PANE.Get_Display_Value
1160 l_attr_group_type VARCHAR2(30);
1161 l_attr_group_name VARCHAR2(30);
1162 l_attr_name VARCHAR2(30);
1163 l_x_display_value VARCHAR2(1000);
1164 l_x_display_type VARCHAR2(200);
1165 l_x_dynamic_url VARCHAR2(1000);
1166 l_x_error_messages rrs_error_msg_tab;
1167
1168 BEGIN
1169 -- convert to the object type value that consumed by RRS_ATTR_PANE.Get_Display_Value
1170 IF p_object_type = 'RRS_SITE' THEN
1171 l_object_type_val := 'SITE';
1172 ELSIF p_object_type = 'RRS_LOCATION' THEN
1173 l_object_type_val := 'LOCATION';
1174 ELSIF p_object_type = 'RRS_TRADE_AREA' THEN
1175 l_object_type_val := 'TRADE AREA';
1176 ELSIF p_object_type = 'RRS_HIERARCHY' THEN
1177 l_object_type_val := 'HIERARCHY';
1178 END IF;
1179
1180 -- convert attribute group id to attribute group name
1181 SELECT ATTR_GROUP_TYPE, ATTR_GROUP_NAME
1182 INTO l_attr_group_type, l_attr_group_name
1183 FROM EGO_ATTR_GROUPS_V
1184 WHERE ATTR_GROUP_ID = p_attr_group_id;
1185
1186 -- query the corresponding attribute name of the data column name
1187 SELECT ATTR_NAME
1188 INTO l_attr_name
1189 FROM EGO_ATTRS_V
1190 WHERE APPLICATION_ID = 718
1191 AND ATTR_GROUP_TYPE = l_attr_group_type
1192 AND ATTR_GROUP_NAME = l_attr_group_name
1193 AND DATABASE_COLUMN = p_column_name;
1194
1195 -- invoke the RRS_ATTR_PANE.Get_Display_Value
1196 Get_Display_Value(l_object_type_val,
1197 p_object_id,
1198 l_attr_group_type,
1199 l_attr_group_name,
1200 l_attr_name,
1201 p_ext_id,
1202 l_x_display_value,
1203 l_x_display_type,
1204 l_x_dynamic_url,
1205 l_x_error_messages);
1206
1207 x_display_value := l_x_display_value;
1208
1209 IF l_x_error_messages IS NOT NULL AND l_x_error_messages.COUNT > 0 THEN
1210 x_msg_data := l_x_error_messages(0);
1211 END IF;
1212
1213 EXCEPTION
1214 WHEN NO_DATA_FOUND THEN
1215 x_msg_data := 'Get_Display_Value: No data found with ' ||
1216 p_object_type || ', ' || p_object_id || ', ' || p_attr_group_id || ', ' ||
1220 END Get_Display_Value;
1217 p_ext_id || ', ' || p_column_name || '.';
1218 WHEN OTHERS THEN
1219 x_msg_data := 'Get_Display_Value: Unexcepted Error - ' || dbms_utility.format_error_backtrace;
1221
1222
1223 --Added for Bug Fix 6969229
1224 Procedure Get_Disp_Value_Without_UOM
1225 (
1226 p_object_type IN VARCHAR2,
1227 p_object_id IN NUMBER,
1228 p_attr_group_id IN NUMBER,
1229 p_ext_id IN NUMBER,
1230 p_column_name IN VARCHAR2,
1231 x_display_value OUT NOCOPY VARCHAR2,
1232 x_msg_data OUT NOCOPY VARCHAR2
1233 ) IS
1234
1235 l_object_type_val VARCHAR2(30); -- convert to the object type value that consumed by RRS_ATTR_PANE.Get_Display_Value
1236 l_attr_group_type VARCHAR2(30);
1237 l_attr_group_name VARCHAR2(30);
1238 l_attr_name VARCHAR2(30);
1239 l_x_display_value VARCHAR2(1000);
1240 l_x_display_type VARCHAR2(200);
1241 l_x_dynamic_url VARCHAR2(1000);
1242 l_x_error_messages rrs_error_msg_tab;
1243
1244 BEGIN
1245 -- convert to the object type value that consumed by RRS_ATTR_PANE.Get_Display_Value
1246 IF p_object_type = 'RRS_SITE' THEN
1247 l_object_type_val := 'SITE';
1248 ELSIF p_object_type = 'RRS_LOCATION' THEN
1249 l_object_type_val := 'LOCATION';
1250 ELSIF p_object_type = 'RRS_TRADE_AREA' THEN
1251 l_object_type_val := 'TRADE AREA';
1252 ELSIF p_object_type = 'RRS_HIERARCHY' THEN
1253 l_object_type_val := 'HIERARCHY';
1254 END IF;
1255
1256 -- convert attribute group id to attribute group name
1257 SELECT ATTR_GROUP_TYPE, ATTR_GROUP_NAME
1258 INTO l_attr_group_type, l_attr_group_name
1259 FROM EGO_ATTR_GROUPS_V
1260 WHERE ATTR_GROUP_ID = p_attr_group_id;
1261
1262 -- query the corresponding attribute name of the data column name
1263 SELECT ATTR_NAME
1264 INTO l_attr_name
1265 FROM EGO_ATTRS_V
1266 WHERE APPLICATION_ID = 718
1267 AND ATTR_GROUP_TYPE = l_attr_group_type
1268 AND ATTR_GROUP_NAME = l_attr_group_name
1269 AND DATABASE_COLUMN = p_column_name;
1270
1271 -- invoke the RRS_ATTR_PANE.Get_Display_Value
1272 Get_Display_Value(l_object_type_val,
1273 p_object_id,
1274 l_attr_group_type,
1275 l_attr_group_name,
1276 l_attr_name,
1277 p_ext_id,
1278 l_x_display_value,
1279 l_x_display_type,
1280 l_x_dynamic_url,
1281 l_x_error_messages);
1282
1283 -- strip the UOM from the display value for the Number type
1284 IF INSTR(p_column_name, 'N') = 1 AND INSTR(l_x_display_value, ' ') <> 0 THEN
1285 x_display_value := SUBSTR(l_x_display_value, 1, INSTR(l_x_display_value, ' ') - 1);
1286 ELSE
1287 x_display_value := l_x_display_value;
1288 END IF;
1289
1290 IF l_x_error_messages IS NOT NULL AND l_x_error_messages.COUNT > 0 THEN
1291 x_msg_data := l_x_error_messages(0);
1292 END IF;
1293
1294 EXCEPTION
1295 WHEN NO_DATA_FOUND THEN
1296 x_msg_data := 'Get_Disp_Value_Without_UOM: No data found with ' ||
1297 p_object_type || ', ' || p_object_id || ', ' || p_attr_group_id || ', ' ||
1298 p_ext_id || ', ' || p_column_name || '.';
1299 WHEN OTHERS THEN
1300 x_msg_data := 'Get_Disp_Value_Without_UOM: Unexcepted Error - ' || dbms_utility.format_error_backtrace;
1301 END Get_Disp_Value_Without_UOM;
1302
1303
1304 -- Bug Fix 9709269: Convert Server Timezone to Client Timezone. Oracle only does Timezone conversion for DateTime type
1305 FUNCTION To_Client_Timezone(p_date IN DATE)
1306 RETURN TIMESTAMP
1307 IS
1308 l_timestamp TIMESTAMP;
1309
1310 BEGIN
1311
1312 --IF p_date IS NULL THEN
1313 --DBMS_OUTPUT.PUT_LINE('p_date is null at beginning');
1314 --END IF;
1315
1316 SELECT FROM_TZ(CAST(p_date AS TIMESTAMP), FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) AT TIME ZONE FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE
1317 INTO l_timestamp
1318 FROM DUAL;
1319
1320 --SELECT TO_TIMESTAMP(p_date) AT TIME ZONE FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE
1321 -- INTO l_timestamp
1322 -- FROM DUAL;
1323
1324 --IF p_date IS NULL THEN
1325 --DBMS_OUTPUT.PUT_LINE('p_date is null');
1326 --ELSIF l_timestamp IS NULL THEN
1327 --DBMS_OUTPUT.PUT_LINE('l_timestamp is null');
1328 --END IF;
1329
1330 --DBMS_OUTPUT.PUT_LINE('p_date: ' || TO_CHAR(TO_DATE(p_date, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS'));
1331 --DBMS_OUTPUT.PUT_LINE('l_timestamp: ' || TO_CHAR(l_timestamp, 'YYYY-MM-DD HH24:MI:SS'));
1332
1333 RETURN l_timestamp;
1334 END To_Client_Timezone;
1335
1336 /*Record the Errors*/
1337 PROCEDURE Record_Error
1338 (
1339 p_error_message IN VARCHAR2,
1340 x_error_messages OUT NOCOPY rrs_error_msg_tab
1341 ) IS
1342
1343 BEGIN
1344 IF x_error_messages IS NULL THEN
1345 x_error_messages := new rrs_error_msg_tab();
1346 END IF;
1347 x_error_messages.EXTEND();
1348 x_error_messages(x_error_messages.LAST) := p_error_message;
1349 END Record_Error;
1350
1351
1352 /*TEST METHODS*/
1353 /*
1354 PROCEDURE TEST IS
1355 x_display_value VARCHAR2(1000);
1356 x_display_type VARCHAR2(200);
1357 x_dynamic_url VARCHAR2(1000);
1358
1359 x_ag_page_tab rrs_attr_group_page_tab;
1360 x_attr_group_tab rrs_attribute_group_tab;
1361 x_attribute_tab rrs_attribute_tab;
1362
1363 x_primary_attributes rrs_primary_attribute_rec;
1364 x_error_data VARCHAR2(1000);
1365 x_error_messages rrs_error_msg_tab;
1366
1367 BEGIN
1368
1369 Get_Display_Value('RRS_TRADE_AREA',
1370 17003,
1371 1535,
1372 66754,
1373 'N_EXT_ATTR2',
1374 x_display_value,
1375 x_error_data);
1379
1376 DBMS_OUTPUT.PUT_LINE('x_display_value: ' || x_display_value);
1377 DBMS_OUTPUT.PUT_LINE('x_error_data: ' || x_error_data);
1378
1380 Get_Display_Value('SITE',
1381 62142,
1382 'RRS_SITEMGMT_GROUP',
1383 'TEST_CLIENT_TIMEZONE',
1384 'TEST_DATE_1',
1385 67978,
1386 x_display_value,
1387 x_display_type,
1388 x_dynamic_url,
1389 x_error_messages);
1390
1391
1392 DBMS_OUTPUT.PUT_LINE('x_display_value: ' || x_display_value);
1393 DBMS_OUTPUT.PUT_LINE('x_display_type: ' || x_display_type);
1394 IF x_error_messages IS NOT NULL AND x_error_messages.COUNT > 0 THEN
1395 DBMS_OUTPUT.PUT_LINE(x_error_messages.COUNT);
1396 FOR i in 1 .. x_error_messages.COUNT
1397 LOOP
1398 DBMS_OUTPUT.PUT_LINE(x_error_messages(i));
1399 END LOOP;
1400 END IF;
1401
1402 Get_Attribute_Page('HIERARCHY',
1403 'HIERARCHY',
1404 31000,
1405 'HIERARCHY',
1406 'ROOT',
1407 NULL,
1408 NULL,
1409 x_primary_attributes,
1410 x_ag_page_tab,
1411 x_attr_group_tab,
1412 x_attribute_tab,
1413 x_error_messages);
1414
1415
1416 DBMS_OUTPUT.PUT_LINE('Primary Attributes');
1417 DBMS_OUTPUT.PUT_LINE('============================');
1418 DBMS_OUTPUT.PUT_LINE(x_primary_attributes.OBJECT_ID);
1419 DBMS_OUTPUT.PUT_LINE(x_primary_attributes.OBJECT_IDENTIFICATION_NUMBER);
1420 DBMS_OUTPUT.PUT_LINE(x_primary_attributes.OBJECT_NAME);
1421 DBMS_OUTPUT.PUT_LINE(x_primary_attributes.CLASSIFICATION);
1422 DBMS_OUTPUT.PUT_LINE(x_primary_attributes.DESCRIPTION);
1423 DBMS_OUTPUT.PUT_LINE(x_primary_attributes.SITE_TYPE);
1424 DBMS_OUTPUT.PUT_LINE(x_primary_attributes.STATUS);
1425 DBMS_OUTPUT.PUT_LINE(x_primary_attributes.START_DATE);
1426 DBMS_OUTPUT.PUT_LINE(x_primary_attributes.END_DATE);
1427 DBMS_OUTPUT.PUT_LINE(x_primary_attributes.ADDRESS);
1428 DBMS_OUTPUT.PUT_LINE(x_primary_attributes.COUNTRY);
1429 DBMS_OUTPUT.PUT_LINE(x_primary_attributes.GROUP_TYPE);
1430 DBMS_OUTPUT.PUT_LINE(x_primary_attributes.UNIT_OF_MEASURE);
1431 DBMS_OUTPUT.PUT_LINE('----------------------------');
1432
1433 DBMS_OUTPUT.PUT_LINE('Page Information');
1434 DBMS_OUTPUT.PUT_LINE('====================================');
1435
1436 IF x_ag_page_tab IS NOT NULL AND x_ag_page_tab.COUNT > 0 THEN
1437 FOR i in 1 .. x_ag_page_tab.COUNT
1438 LOOP
1439 DBMS_OUTPUT.PUT_LINE(x_ag_page_tab(i).PAGE_ID);
1440 DBMS_OUTPUT.PUT_LINE(x_ag_page_tab(i).OBJECT_NAME);
1441 DBMS_OUTPUT.PUT_LINE(x_ag_page_tab(i).CLASSIFICATION_CODE);
1442 DBMS_OUTPUT.PUT_LINE(x_ag_page_tab(i).DATA_LEVEL_INT_NAME);
1443 DBMS_OUTPUT.PUT_LINE(x_ag_page_tab(i).INTERNAL_NAME);
1444 DBMS_OUTPUT.PUT_LINE(x_ag_page_tab(i).DISPLAY_NAME);
1445 DBMS_OUTPUT.PUT_LINE(x_ag_page_tab(i).DESCRIPTION);
1446 DBMS_OUTPUT.PUT_LINE(x_ag_page_tab(i).SEQUENCE);
1447 DBMS_OUTPUT.PUT_LINE('--------------------------------');
1448 END LOOP;
1449 END IF;
1450
1451 DBMS_OUTPUT.PUT_LINE('Attribute Group Information');
1452 DBMS_OUTPUT.PUT_LINE('====================================');
1453
1454 IF x_attr_group_tab IS NOT NULL AND x_attr_group_tab.COUNT > 0 THEN
1455 FOR j in 1 .. x_attr_group_tab.COUNT
1456 LOOP
1457 DBMS_OUTPUT.PUT_LINE(x_attR_group_tab(j).PAGE_ID);
1458 DBMS_OUTPUT.PUT_LINE(x_attR_group_tab(j).ATTR_GROUP_ID);
1459 DBMS_OUTPUT.PUT_LINE(x_attR_group_tab(j).ATTR_GROUP_TYPE);
1460 DBMS_OUTPUT.PUT_LINE(x_attR_group_tab(j).ATTR_GROUP_NAME);
1461 DBMS_OUTPUT.PUT_LINE(x_attR_group_tab(j).ATTR_GROUP_DISP_NAME);
1462 DBMS_OUTPUT.PUT_LINE(x_attR_group_tab(j).DESCRIPTION);
1463 DBMS_OUTPUT.PUT_LINE(x_attR_group_tab(j).MULTI_ROW_CODE);
1464 DBMS_OUTPUT.PUT_LINE(x_attR_group_tab(j).SECURITY_CODE);
1465 DBMS_OUTPUT.PUT_LINE(x_attR_group_tab(j).NUM_OF_COLS);
1466 DBMS_OUTPUT.PUT_LINE(x_attR_group_tab(j).NUM_OF_ROWS);
1467 DBMS_OUTPUT.PUT_LINE(x_attR_group_tab(j).SEQUENCE);
1468 DBMS_OUTPUT.PUT_LINE('--------------------------------');
1469 END LOOP;
1470 END IF;
1471
1472 IF x_attribute_tab IS NOT NULL AND x_attribute_tab.COUNT > 0 THEN
1473 DBMS_OUTPUT.PUT_LINE('Attribute Information');
1474 DBMS_OUTPUT.PUT_LINE('====================================');
1475 FOR i in 1 .. x_attribute_tab.COUNT
1476 LOOP
1477 DBMS_OUTPUT.PUT_LINE(x_attribute_tab(i).ATTR_GROUP_TYPE);
1478 DBMS_OUTPUT.PUT_LINE(x_attribute_tab(i).ATTR_GROUP_NAME);
1479 DBMS_OUTPUT.PUT_LINE(x_attribute_tab(i).EXTENSION_ID);
1480 DBMS_OUTPUT.PUT_LINE(x_attribute_tab(i).ATTR_NAME);
1481 DBMS_OUTPUT.PUT_LINE(x_attribute_tab(i).ATTR_DISPLAY_NAME);
1482 DBMS_OUTPUT.PUT_LINE(x_attribute_tab(i).DESCRIPTION);
1483 DBMS_OUTPUT.PUT_LINE(x_attribute_tab(i).DISPLAY_VALUE);
1484 DBMS_OUTPUT.PUT_LINE(x_attribute_tab(i).DISPLAY_TYPE);
1485 DBMS_OUTPUT.PUT_LINE(x_attribute_tab(i).DYNAMIC_URL);
1486 DBMS_OUTPUT.PUT_LINE(x_attribute_tab(i).SEQUENCE);
1487 DBMS_OUTPUT.PUT_LINE('--------------------------------');
1488 END LOOP;
1489 END IF;
1490
1491 IF x_error_messages IS NOT NULL AND x_error_messages.COUNT > 0 THEN
1492 DBMS_OUTPUT.PUT_LINE(x_error_messages.COUNT);
1493 FOR i in 1 .. x_error_messages.COUNT
1494 LOOP
1495 DBMS_OUTPUT.PUT_LINE(x_error_messages(i));
1496 END LOOP;
1497 END IF;
1498
1499 Get_Primary_Attributes('HIERARCHY',
1500 10000,
1501 x_primary_attributes,
1502 x_error_messages);
1503
1504 END TEST;*/
1505
1506
1507 END RRS_ATTR_PANE;
[Home] [Help]