1 package body icx_ak_java_query as
2 /* $Header: ICXAKJQB.pls 115.4 99/07/17 03:15:10 porting ship $ */
3
4
5
6 procedure execute_query(p_region_app_id in varchar2,
7 p_region_code in varchar2,
8 p_where_clause in varchar2,
9 p_order_clause in varchar2,
10 p_time in varchar2) is
11
12 l_result_row icx_util.char240_table;
13
14 begin
15 if icx_sec.validateSession then
16
17 ak_query_pkg.exec_query(P_PARENT_REGION_APPL_ID => p_region_app_id,
18 P_PARENT_REGION_CODE => p_region_code,
19 P_WHERE_CLAUSE => p_where_clause,
20 P_ORDER_BY_CLAUSE => p_order_clause,
21 P_RETURN_PARENTS => 'T',
22 P_RETURN_CHILDREN => 'F');
23
24
25 htp.p(ak_query_pkg.g_items_table.count);
26 htp.p(ak_query_pkg.g_results_table.count);
27
28 for item in 1..ak_query_pkg.g_items_table.count loop
29 if ak_query_pkg.g_items_table(item-1).object_attribute_flag = 'Y' then
30 htp.p(ak_query_pkg.g_items_table(item-1).attribute_code);
31 htp.p(ak_query_pkg.g_items_table(item-1).value_id);
32 htp.p(ak_query_pkg.g_items_table(item-1).attribute_label_long);
33 htp.p(ak_query_pkg.g_items_table(item-1).display_sequence);
34 htp.p(ak_query_pkg.g_items_table(item-1).object_attribute_flag);
35 htp.p(ak_query_pkg.g_items_table(item-1).node_display_flag);
36 htp.p(ak_query_pkg.g_items_table(item-1).display_value_length);
37 end if;
38 end loop;
39
40
41 for result in 1 .. ak_query_pkg.g_results_table.count loop
42 icx_util.transfer_Row_To_Column(ak_query_pkg.g_results_table(result-1),l_result_row);
43
44 for l_item_num in 1..ak_query_pkg.g_items_table.count loop
45 if ak_query_pkg.g_items_table(l_item_num-1).object_attribute_flag = 'Y' then
46 htp.p(l_result_row(l_item_num));
47 end if;
48 end loop;
49 end loop;
50
51 end if;
52 end;
53
54
55
56 procedure get_item(p_item_id in number,
57 p_attribute_code out varchar2,
58 p_value_id out number,
59 p_attribute_label_long out varchar2,
60 p_display_sequence out number,
61 p_object_attribute_flag out varchar2,
62 p_node_display_flag out varchar2,
63 p_display_length out number) is
64
65 begin
66
67 p_attribute_code := ak_query_pkg.g_items_table(p_item_id).attribute_code;
68 p_value_id := ak_query_pkg.g_items_table(p_item_id).value_id;
69 p_attribute_label_long := ak_query_pkg.g_items_table(p_item_id).attribute_label_long;
70 p_display_sequence := ak_query_pkg.g_items_table(p_item_id).display_sequence;
71 p_object_attribute_flag := ak_query_pkg.g_items_table(p_item_id).object_attribute_flag;
72 p_node_display_flag := ak_query_pkg.g_items_table(p_item_id).node_display_flag;
73 p_display_length := ak_query_pkg.g_items_table(p_item_id).display_value_length;
74
75 end;
76
77
78
79 procedure get_result_row(p_row_num in number,
80 p_result_row out icx_util.char240_table) is
81
82 l_item_num number;
83 l_result_row icx_util.char240_table;
84
85 begin
86
87 -- transfer one row of the l_results_table into a seperate pl/sql table
88 -- that will enable the specific transfer of only those elements of the
89 -- row that are populated (eliminating the need for a 100 member array
90 -- to be allocated in java)
91
92 icx_util.transfer_Row_To_Column(ak_query_pkg.g_results_table(p_row_num),l_result_row);
93
94 for l_item_num in 1..ak_query_pkg.g_items_table.count loop
95 if ak_query_pkg.g_items_table(l_item_num - 1).object_attribute_flag = 'Y' then
96 p_result_row(l_item_num) := l_result_row(ak_query_pkg.g_items_table(l_item_num - 1).value_id);
97 else
98 p_result_row(l_item_num) := '';
99 end if;
100 end loop;
101
102 end;
103
104
105
106 procedure update_oc_kids(p_group_id in number) is
107
108 cursor option_classes is
109 select row_id,
110 sort_order
111 from icx_config_components_web_v
112 where group_id = p_group_id
113 and bom_item_type in (1,2)
114 order by sort_order;
115
116 l_option_count number;
117
118 begin
119
120 for oc in option_classes loop
121
122 select count(*) into l_option_count
123 from icx_config_components_web_v
124 where group_id = p_group_id
125 and bom_item_type = 4
126 and substr(sort_order,1,length(sort_order)-4) = oc.sort_order
127 and selected_flag = 'Y';
128
129 update icx_config_components_web_v
130 set number_kids = l_option_count
131 where row_id = oc.row_id;
132
133 end loop;
134
135 end;
136
137
138
139
140 end icx_ak_java_query;
[Home] [Help]