[Home] [Help]
PACKAGE BODY: APPS.QA_CHART_CONTROL_PKG
Source
1 PACKAGE BODY qa_chart_control_pkg AS
2 /* $Header: qacctrlb.pls 120.3 2006/02/08 16:26:15 bso noship $ */
3
4 --
5 -- Tracking Bug 4939897
6 -- R12 Forms Tech Stack Upgrade - Obsolete Oracle Graphics
7 --
8
9
10 --
11 -- Define a character to be used as separator to pass two y-axis
12 -- labels in one shot. It can be anything that is not possible as
13 -- as collection element name nor one of the seeded characters.
14 -- We will use a vertical bar. The Control Chart Controller
15 -- Object will use the same character to parse out the y-axises.
16 --
17 g_separator CONSTANT VARCHAR2(1) := '|';
18
19
20 FUNCTION get_x_axis_label(p_subgroup_size NUMBER) RETURN VARCHAR2 IS
21 BEGIN
22 --
23 -- x-axis label is constructed by QA_CHART_CONTROL_X_LABEL
24 --
25 fnd_message.set_name('QA', 'QA_CHART_CONTROL_X_LABEL');
26 fnd_message.set_token('SIZE', p_subgroup_size);
27 RETURN fnd_message.get;
28 END get_x_axis_label;
29
30
31 FUNCTION get_y_axis_label(
32 p_chart_type NUMBER,
33 p_element_id NUMBER) RETURN VARCHAR2 IS
34
35 l_label VARCHAR2(2000);
36 l_element qa_chars.name%TYPE;
37
38 BEGIN
39 --
40 -- The y-axis label is constructed by concatenating the
41 -- top chart label and the bottom chart label into one
42 -- string by g_separator. They will be parsed out by
43 -- the control chart's Controller Object then set to
44 -- the appropriate chart bean.
45 --
46 -- Top label is QA_CHART_CONTROL_TOP_Y_LABELxx
47 -- Bottom label is QA_CHART_CONTROL_BOT_Y_LABELxx
48 --
49 -- where xx is the subchart type.
50 --
51 l_element := qa_chars_api.get_element_name(p_element_id);
52 fnd_message.set_name('QA', 'QA_CHART_CONTROL_TOP_Y_LABEL' ||
53 p_chart_type);
54 fnd_message.set_token('ELEMENT_NAME', l_element);
55 l_label := fnd_message.get;
56
57 fnd_message.set_name('QA', 'QA_CHART_CONTROL_BOT_Y_LABEL' ||
58 p_chart_type);
59 fnd_message.set_token('ELEMENT_NAME', l_element);
60 l_label := l_label || g_separator || fnd_message.get;
61
62 RETURN l_label;
63 END get_y_axis_label;
64
65
66 PROCEDURE populate_data(
67 p_chart_id NUMBER,
68 p_top_ucl NUMBER,
69 p_top_mean NUMBER,
70 p_top_lcl NUMBER,
71 p_bottom_ucl NUMBER,
72 p_bottom_mean NUMBER,
73 p_bottom_lcl NUMBER,
74 x_row_count OUT NOCOPY NUMBER) IS
75
76 --
77 -- This is a very specific private procedure that assumes
78 -- qltcontb has been called to populate qa_chart_data
79 -- (the old data point table), it then migrates the data over
80 -- to the qa_chart_control table. It does a direct INSERT INTO
81 -- qa_chart_control...SELECT FROM qa_chart_data. This is not
82 -- the most efficient way to achieve a histogram, but in order
83 -- to completely re-use the legacy code for implementation and
84 -- maintenance efficient and, more importantly, to not destabilize
85 -- existing code, this is a reasonable approach.
86 --
87 -- bso Fri Jan 13 18:52:25 PST 2006
88 --
89
90 BEGIN
91 --
92 -- There is no WHERE clause because this table always
93 -- hold data for one single session. This will result
94 -- in a Full Table Scan in Performance Repository.
95 -- This is expected and there is no performance issue
96 -- as the session data will be rolled back momentarily,
97 -- so it always contains a small no. of records only.
98 --
99 -- bso Fri Jan 13 18:57:22 PST 2006
100 --
101 INSERT INTO qa_chart_control(
102 created_by,
103 creation_date,
104 last_updated_by,
105 last_update_date,
106 last_update_login,
107 chart_id,
108 line,
109 x_value,
110 top_y_value,
111 top_ucl_value,
112 top_mean_value,
113 top_lcl_value,
114 bottom_y_value,
115 bottom_ucl_value,
116 bottom_mean_value,
117 bottom_lcl_value)
118 SELECT
119 /* Small amount of data from one session only. */
120 /* FTS expected and reviewed by bso 1/13/2006. */
121 fnd_global.user_id,
122 sysdate,
123 fnd_global.user_id,
124 sysdate,
125 fnd_global.login_id,
126 p_chart_id,
127 rownum,
128 subgroup_number,
129 average,
130 p_top_ucl,
131 p_top_mean,
132 p_top_lcl,
133 nvl(range, 0), -- range can be NULL for XmR
134 p_bottom_ucl,
135 p_bottom_mean,
136 p_bottom_lcl
137 FROM
138 qa_chart_data;
139
140 x_row_count := sql%ROWCOUNT;
141
142 END populate_data;
143
144
145 PROCEDURE create_chart(
146 p_criteria_id NUMBER,
147 p_chart_type NUMBER,
148 p_element_id NUMBER,
149 p_title VARCHAR2,
150 p_description VARCHAR2,
151 p_sql VARCHAR2,
152 p_subgroup_size NUMBER,
153 p_subgroup_num NUMBER,
154 p_dec_prec NUMBER,
155 p_top_ucl NUMBER,
156 p_top_mean NUMBER,
157 p_top_lcl NUMBER,
158 p_bottom_ucl NUMBER,
159 p_bottom_mean NUMBER,
160 p_bottom_lcl NUMBER,
161 x_chart_id OUT NOCOPY NUMBER,
162 x_row_count OUT NOCOPY NUMBER) IS
163
164 l_dec_prec NUMBER;
165 l_subgroup_num NUMBER;
166
167 --
168 -- The following are dummies required by qltcontb
169 --
170 l_top_mean NUMBER;
171 l_bottom_mean NUMBER;
172 l_top_ucl NUMBER;
173 l_top_lcl NUMBER;
174 l_bottom_ucl NUMBER;
175 l_bottom_lcl NUMBER;
176 l_dummy NUMBER;
177
178 BEGIN
179
180 l_dec_prec := nvl(p_dec_prec, 12);
181 l_subgroup_num := p_subgroup_num;
182
183 qa_chart_headers_pkg.create_chart(
184 p_criteria_id => p_criteria_id,
185 p_title => p_title,
186 p_description => p_description,
187 p_x_label => get_x_axis_label(p_subgroup_size),
188 p_y_label => get_y_axis_label(p_chart_type, p_element_id),
189 x_chart_id => x_chart_id);
190
191 CASE p_chart_type
192 WHEN qa_ss_const.control_chart_XBarR THEN
193 qltcontb.x_bar_r(
194 sql_string => p_sql,
195 subgrp_size => p_subgroup_size,
196 num_subgroups => l_subgroup_num,
197 dec_prec => l_dec_prec,
198 grand_mean => l_top_mean,
199 range_average => l_bottom_mean,
200 ucl => l_top_ucl,
201 lcl => l_top_lcl,
202 r_ucl => l_bottom_ucl,
203 r_lcl => l_bottom_lcl,
204 not_enough_data => l_dummy,
205 compute_new_limits => false);
206
207 WHEN qa_ss_const.control_chart_XmR THEN
208 qltcontb.xmr(
209 sql_string => p_sql,
210 subgrp_size => p_subgroup_size,
211 num_points => l_subgroup_num,
212 dec_prec => l_dec_prec,
213 grand_mean => l_top_mean,
214 range_average => l_bottom_mean,
215 ucl => l_top_ucl,
216 lcl => l_top_lcl,
217 r_ucl => l_bottom_ucl,
218 r_lcl => l_bottom_lcl,
219 not_enough_data => l_dummy,
220 compute_new_limits => false);
221
222 WHEN qa_ss_const.control_chart_XBarS THEN
223 qltcontb.x_bar_s(
224 sql_string => p_sql,
225 subgrp_size => p_subgroup_size,
226 num_subgroups => l_subgroup_num,
227 dec_prec => l_dec_prec,
228 grand_mean => l_top_mean,
229 std_dev_average => l_bottom_mean,
230 ucl => l_top_ucl,
231 lcl => l_top_lcl,
232 r_ucl => l_bottom_ucl,
233 r_lcl => l_bottom_lcl,
234 not_enough_data => l_dummy,
235 compute_new_limits => false);
236 END CASE;
237
238 populate_data(
239 p_chart_id => x_chart_id,
240 p_top_ucl => p_top_ucl,
241 p_top_mean => p_top_mean,
242 p_top_lcl => p_top_lcl,
243 p_bottom_ucl => p_bottom_ucl,
244 p_bottom_mean => p_bottom_mean,
245 p_bottom_lcl => p_bottom_lcl,
246 x_row_count => x_row_count);
247
248 DELETE
249 /* Small amount of data from one session only. */
250 /* FTS expected and reviewed by bso 1/13/2006. */
251 FROM qa_chart_data;
252
253 END create_chart;
254
255
256 PROCEDURE create_chart_autonomous(
257 p_criteria_id NUMBER,
258 p_chart_type NUMBER,
259 p_element_id NUMBER,
260 p_title VARCHAR2,
261 p_description VARCHAR2,
262 p_sql VARCHAR2,
263 p_subgroup_size NUMBER,
264 p_subgroup_num NUMBER,
265 p_dec_prec NUMBER,
266 p_top_ucl NUMBER,
267 p_top_mean NUMBER,
268 p_top_lcl NUMBER,
269 p_bottom_ucl NUMBER,
270 p_bottom_mean NUMBER,
271 p_bottom_lcl NUMBER,
272 x_chart_id OUT NOCOPY NUMBER,
273 x_row_count OUT NOCOPY NUMBER) IS
274
275 --
276 -- This is a wrapper to create_chart and performs
277 -- autonomous commit.
278 --
279 PRAGMA autonomous_transaction;
280
281 BEGIN
282 create_chart(
283 p_criteria_id,
284 p_chart_type,
285 p_element_id,
286 p_title,
287 p_description,
288 p_sql,
289 p_subgroup_size,
290 p_subgroup_num,
291 p_dec_prec,
292 p_top_ucl,
293 p_top_mean,
294 p_top_lcl,
295 p_bottom_ucl,
296 p_bottom_mean,
297 p_bottom_lcl,
298 x_chart_id,
299 x_row_count);
300 COMMIT;
301 END create_chart_autonomous;
302
303
304 PROCEDURE delete_data(p_chart_id NUMBER) IS
305 BEGIN
306 DELETE FROM qa_chart_control
307 WHERE chart_id = p_chart_id;
308 END delete_data;
309
310
311 PROCEDURE delete_data_autonomous(p_chart_id NUMBER) IS
312 --
313 -- This is a wrapper to delete_data and performs
314 -- autonomous commit.
315 --
316 PRAGMA autonomous_transaction;
317 BEGIN
318 delete_data(p_chart_id);
319 COMMIT;
320 END delete_data_autonomous;
321
322
323 END qa_chart_control_pkg;