1 package body AK_RUN_FLOW_PKG as
2 /* $Header: akdrunfb.pls 115.2 99/07/17 15:19:49 porting s $ */
3 procedure GET_ATTRIBUTE_LIST_VALUES (
4 X_TRACE_NUM in NUMBER,
5 X_REGION_APPLICATION_ID in NUMBER,
6 X_REGION_CODE in VARCHAR2
7 ) is
8 cursor check_object_csr is
9 select 1
10 from all_objects ao, ak_regions ar
11 where ao.object_name = ar.database_object_name
12 and ar.region_application_id = x_region_application_id
13 and ar.region_code = x_region_code
14 and ao.object_type in ('TABLE', 'VIEW');
15 TYPE label_table_type is TABLE of varchar2(50)
16 index by binary_integer;
17 l_regions_table AK_QUERY_PKG.regions_table_type;
18 l_items_table AK_QUERY_PKG.items_table_type;
19 l_results_table AK_QUERY_PKG.results_table_type;
20 l_label1_table label_table_type;
21 l_label2_table label_table_type;
22 l_label3_table label_table_type;
23
24 l_display_value varchar2(510);
25 l_dummy number;
26 l_index number;
27 l_region_rec_id number;
28 begin
29 --
30 -- Check to see if the database object for the given region exists.
31 -- Otherwise, the call to exec_query will fail with errors.
32 --
33 open check_object_csr;
34 fetch check_object_csr into l_dummy;
35 if (check_object_csr%notfound) then
36 close check_object_csr;
37 return;
38 end if;
39 close check_object_csr;
40 --
41 -- Call exec_query to obtain the list values
42 --
43 AK_QUERY_PKG.exec_query(
44 p_flow_appl_id => null,
45 p_flow_code => null,
46 p_parent_page_appl_id => null,
47 p_parent_page_code => null,
48 p_parent_region_appl_id => x_region_application_id,
49 p_parent_region_code => x_region_code,
50 p_parent_primary_key_name => null,
51 p_child_page_appl_id => null,
52 p_child_page_code => null,
53 p_parent_key_value1 => null,
54 p_parent_key_value2 => null,
55 p_parent_key_value3 => null,
56 p_parent_key_value4 => null,
57 p_parent_key_value5 => null,
58 p_parent_key_value6 => null,
59 p_parent_key_value7 => null,
60 p_parent_key_value8 => null,
61 p_parent_key_value9 => null,
62 p_parent_key_value10 => null,
63 p_where_clause => null,
64 p_return_parents => 'T',
65 p_return_children => 'F',
66 p_set_trace => 'F',
67 p_return_node_display_only => 'F');
68 --
69 -- Load attribute labels into PL/SQL table - they will be used
70 -- in building the string that will be the display_value column
71 -- in the temp table.
72 -- *Note 1: only load the labels for the first 3 attributes in each
73 -- region - since only 3 attributes will be included in
74 -- display_value.
75 -- *Note 2: The PL/SQL table is sparse, and the region_rec_id returned
76 -- from execute_query will be used as index
77 --
78 l_regions_table := ak_query_pkg.g_regions_table;
79 l_items_table := ak_query_pkg.g_items_table;
80 l_results_table := ak_query_pkg.g_results_table;
81
82 if l_items_table.count > 0 then
83 l_index := l_items_table.first;
84 while l_index is not null loop
85 l_region_rec_id := l_items_table(l_index).region_rec_id;
86 if (l_items_table(l_index).value_id = 1) then
87 l_label1_table(l_region_rec_id) :=
88 l_items_table(l_index).attribute_label_long;
89 elsif (l_items_table(l_index).value_id = 2) then
90 l_label2_table(l_region_rec_id) :=
91 l_items_table(l_index).attribute_label_long;
92 elsif (l_items_table(l_index).value_id = 3) then
93 l_label3_table(l_region_rec_id) :=
94 l_items_table(l_index).attribute_label_long;
95 end if;
96 l_index := l_items_table.next(l_index);
97 end loop; /* while l_index is not null */
98 end if; /* if l_items_table.count > 0 */
99 --
100 -- Insert result values into the temp table for use by the RUN FLOW FORM
101 --
102 if l_results_table.count > 0 then
103 for l_index in l_results_table.first .. l_results_table.last loop
104 --
105 -- build display_value string
106 --
107 l_region_rec_id := l_results_table(l_index).region_rec_id;
108 l_display_value := null;
109 if l_label1_table.exists(l_region_rec_id) and
110 l_label1_table(l_region_rec_id) is not null then
111 l_display_value := substr(l_label1_table(l_region_rec_id),1,30)
112 || ':';
113 end if;
114 if (l_results_table(l_index).value1 is not null) then
115 l_display_value := l_display_value ||
116 substr(l_results_table(l_index).value1,
117 1,30);
118 end if;
119 if l_label2_table.exists(l_region_rec_id) and
120 l_label2_table(l_region_rec_id) is not null then
121 l_display_value := l_display_value || ', ' ||
122 substr(l_label2_table(l_region_rec_id),1,30)
123 || ': ';
124 end if;
125 if (l_results_table(l_index).value2 is not null) then
126 l_display_value := l_display_value ||
127 substr(l_results_table(l_index).value2,
128 1,30);
129 end if;
130 if l_label3_table.exists(l_region_rec_id) and
131 l_label3_table(l_region_rec_id) is not null then
132 l_display_value := l_display_value || ', ' ||
133 substr(l_label3_table(l_region_rec_id),1,30)
134 || ': ';
135 end if;
136 if (l_results_table(l_index).value3 is not null) then
137 l_display_value := l_display_value ||
138 substr(l_results_table(l_index).value3,
139 1,30);
140 end if;
141 l_display_value := l_display_value || ' ( ';
142 if l_results_table(l_index).key1 is not null then
143 l_display_value := l_display_value ||
144 substr(l_results_table(l_index).key1, 1, 30);
145 end if;
146 if l_results_table(l_index).key2 is not null then
147 l_display_value := l_display_value || ', ' ||
148 substr(l_results_table(l_index).key2, 1, 30);
149 end if;
150 if l_results_table(l_index).key3 is not null then
151 l_display_value := l_display_value || ', ' ||
152 substr(l_results_table(l_index).key3, 1, 30);
153 end if;
154 if l_results_table(l_index).key4 is not null then
155 l_display_value := l_display_value || ', ' ||
156 substr(l_results_table(l_index).key4, 1, 30);
157 end if;
158 if l_results_table(l_index).key5 is not null then
159 l_display_value := l_display_value || ', ' ||
160 substr(l_results_table(l_index).key5, 1, 30);
161 end if;
162 if l_results_table(l_index).key6 is not null then
163 l_display_value := l_display_value || ', ' ||
164 substr(l_results_table(l_index).key6, 1, 30);
165 end if;
166 if l_results_table(l_index).key7 is not null then
167 l_display_value := l_display_value || ', ' ||
168 substr(l_results_table(l_index).key7, 1, 30);
169 end if;
170 if l_results_table(l_index).key8 is not null then
171 l_display_value := l_display_value || ', ' ||
172 substr(l_results_table(l_index).key8, 1, 30);
173 end if;
174 if l_results_table(l_index).key9 is not null then
175 l_display_value := l_display_value || ', ' ||
176 substr(l_results_table(l_index).key9, 1, 30);
177 end if;
178 if l_results_table(l_index).key10 is not null then
179 l_display_value := l_display_value || ', ' ||
180 substr(l_results_table(l_index).key10, 1, 30);
181 end if;
182 l_display_value := l_display_value || ' )';
183 --
184 -- Insert results into temp database table
185 --
186 insert into ak_object_values_temp (
187 TRACE_NUM,
188 REGION_APPLICATION_ID,
189 REGION_CODE,
190 DISPLAY_VALUE,
191 KEY_VALUE1,
192 KEY_VALUE2,
193 KEY_VALUE3,
194 KEY_VALUE4,
195 KEY_VALUE5,
196 KEY_VALUE6,
197 KEY_VALUE7,
198 KEY_VALUE8,
199 KEY_VALUE9,
200 KEY_VALUE10
201 ) values (
202 X_TRACE_NUM,
203 X_REGION_APPLICATION_ID,
204 X_REGION_CODE,
205 l_display_value,
206 l_results_table(l_index).key1,
207 l_results_table(l_index).key2,
208 l_results_table(l_index).key3,
209 l_results_table(l_index).key4,
210 l_results_table(l_index).key5,
211 l_results_table(l_index).key6,
212 l_results_table(l_index).key7,
213 l_results_table(l_index).key8,
214 l_results_table(l_index).key9,
215 l_results_table(l_index).key10
216 );
217 end loop; /* for l_index in ... */
218 end if; /* if l_results_table.count > 0 */
219 end GET_ATTRIBUTE_LIST_VALUES;
220
221 end AK_RUN_FLOW_PKG;
[Home] [Help]