1 PACKAGE BODY qa_chart_histogram_pkg AS
2 /* $Header: qachistb.pls 120.1 2006/08/09 01:05:16 bso noship $ */
3
4 --
5 -- Tracking Bug 4939897
6 -- R12 Forms Tech Stack Upgrade - Obsolete Oracle Graphics
7 --
8
9
10 PROCEDURE populate_data(
11 p_chart_id NUMBER,
12 x_row_count OUT NOCOPY NUMBER) IS
13
14 --
15 -- This is a very specific private procedure that assumes
16 -- qltcontb.histogram has been called to populate qa_chart_data
17 -- (the old data point table), it then migrates the data over
18 -- to the qa_chart_generic table. It does a direct INSERT INTO
19 -- qa_chart_generic...SELECT FROM qa_chart_data. This is not
20 -- the most efficient way to achieve a histogram, but in order
21 -- to completely re-use the legacy code for implementation and
22 -- maintenance efficient and, more importantly, to not destabilize
23 -- existing code, this is a reasonable approach.
24 --
25 -- bso Wed Jan 11 13:53:16 PST 2006
26 --
27
28 BEGIN
29 --
30 -- There is no WHERE clause because this table always
31 -- hold data for one single session. This will result
32 -- in a Full Table Scan in Performance Repository.
33 -- This is expected and there is no performance issue
34 -- as the session data will be rolled back momentarily,
35 -- so it always contains a small no. of records only.
36 --
37 -- bso Wed Jan 11 13:54:47 PST 2006
38 --
39 INSERT INTO qa_chart_generic(
40 created_by,
41 creation_date,
42 last_updated_by,
43 last_update_date,
44 last_update_login,
45 chart_id,
46 line,
47 x_value,
48 y_value)
49 SELECT
50 /* Small amount of data from one session only. */
51 /* FTS expected and reviewed by bso 1/11/2006. */
52 fnd_global.user_id,
53 sysdate,
54 fnd_global.user_id,
55 sysdate,
56 fnd_global.login_id,
57 p_chart_id,
58 bar_number,
59 hist_range,
60 num_occurrences
61 FROM
62 qa_chart_data;
63
64 x_row_count := sql%ROWCOUNT;
65
66 END populate_data;
67
68
69 FUNCTION get_x_axis_label(
70 p_element_id NUMBER,
71 p_spec_id NUMBER) RETURN VARCHAR2 IS
72 --
73 -- Construct the x-axis label.
74 -- If p_spec_id is null, it is simply the <element name>.
75 -- Else, it features both the <element name> and the <spec name>
76 -- in a message QA_CHART_HISTOGRAM_X_LABEL.
77 --
78 BEGIN
79
80 IF p_spec_id IS NULL THEN
81 RETURN qa_chars_api.get_element_name(p_element_id);
82 ELSE
83 fnd_message.set_name('QA', 'QA_CHART_HISTOGRAM_X_LABEL');
84 fnd_message.set_token('ELEMENT_NAME',
85 qa_chars_api.get_element_name(p_element_id));
86 fnd_message.set_token('SPEC_NAME',
87 qa_specs_pkg.get_spec_name(p_spec_id));
88
89 RETURN fnd_message.get;
90 END IF;
91
92 END get_x_axis_label;
93
94
95 FUNCTION get_y_axis_label RETURN VARCHAR2 IS
96 --
97 -- y-axis label is always the function name "Count", translated.
98 --
99 BEGIN
100 RETURN qa_eres_util.get_mfg_lookups_meaning(
101 p_lookup_type => 'QA_FUNCTION',
102 p_lookup_code => qa_ss_const.chart_function_count);
103 END get_y_axis_label;
104
105
106 PROCEDURE create_chart(
107 --
108 -- This is a wrapper to the original qltcontb.histogram
109 -- procedure. It calls qltcontb.histogram and then calls
110 -- populate_data. It will be called by QLTENGIN.pld.
111 --
112 -- Bug 5130880 Added target value.
113 --
114 p_criteria_id NUMBER,
115 p_element_id NUMBER,
116 p_spec_id NUMBER,
117 p_title VARCHAR2,
118 p_description VARCHAR2,
119 p_sql VARCHAR2,
120 p_dec_prec NUMBER,
121 p_usl NUMBER,
122 p_lsl NUMBER,
123 p_target_value NUMBER,
124 x_cp IN OUT NOCOPY NUMBER,
125 x_cpk IN OUT NOCOPY NUMBER,
126 x_num_points IN OUT NOCOPY NUMBER,
127 x_num_bars IN OUT NOCOPY NUMBER,
128 x_chart_id OUT NOCOPY NUMBER,
129 x_row_count OUT NOCOPY NUMBER) IS
130
131 l_dummy NUMBER;
132
133 BEGIN
134
135 --
136 -- Main worker to populate the data points in qa_chart_data
137 -- table. This is a pre-R12 existing API.
138 --
139 qltcontb.histogram(
140 sql_string => p_sql,
141 num_points => x_num_points,
142 dec_prec => p_dec_prec,
143 num_bars => x_num_bars,
144 usl => p_usl,
145 lsl => p_lsl,
146 cp => x_cp,
147 cpk => x_cpk,
148 not_enough_data => l_dummy);
149
150 qa_chart_headers_pkg.create_chart(
151 p_criteria_id => p_criteria_id,
152 p_title => p_title,
153 p_description => p_description,
154 p_x_label => get_x_axis_label(p_element_id, p_spec_id),
155 p_y_label => get_y_axis_label,
156 x_chart_id => x_chart_id);
157
158 --
159 -- Bug 5130880. Need to have spec limits, cp, cpk and uom code
160 -- in the chart header for display purpose.
161 -- bso Tue Aug 8 17:25:53 PDT 2006
162 --
163 UPDATE qa_chart_headers
164 SET last_update_date = sysdate,
165 upper_spec_limit = p_usl,
166 lower_spec_limit = p_lsl,
167 target_value = p_target_value,
168 cp = x_cp,
169 cpk = x_cpk,
170 uom_code = (
171 SELECT uom_code
172 FROM qa_spec_chars
173 WHERE spec_id = p_spec_id AND
174 char_id = p_element_id)
175 WHERE chart_id = x_chart_id;
176
177 populate_data(x_chart_id, x_row_count);
178
179 DELETE
180 /* Small amount of data from one session only. */
181 /* FTS expected and reviewed by bso 1/11/2006. */
182 FROM qa_chart_data;
183
184 END create_chart;
185
186
187 PROCEDURE create_chart_autonomous(
188 --
189 -- This is a wrapper to create_chart and performs
190 -- autonomous commit.
191 --
192 -- Bug 5130880 Added target value.
193 --
194 p_criteria_id NUMBER,
195 p_element_id NUMBER,
196 p_spec_id NUMBER,
197 p_title VARCHAR2,
198 p_description VARCHAR2,
199 p_sql VARCHAR2,
200 p_dec_prec NUMBER,
201 p_usl NUMBER,
202 p_lsl NUMBER,
203 p_target_value NUMBER,
204 x_cp IN OUT NOCOPY NUMBER,
205 x_cpk IN OUT NOCOPY NUMBER,
206 x_num_points IN OUT NOCOPY NUMBER,
207 x_num_bars IN OUT NOCOPY NUMBER,
208 x_chart_id OUT NOCOPY NUMBER,
209 x_row_count OUT NOCOPY NUMBER) IS
210
211 PRAGMA autonomous_transaction;
212
213 BEGIN
214 create_chart(
215 p_criteria_id,
216 p_element_id,
217 p_spec_id,
218 p_title,
219 p_description,
220 p_sql,
221 p_dec_prec,
222 p_usl,
223 p_lsl,
224 p_target_value,
225 x_cp,
226 x_cpk,
227 x_num_points,
228 x_num_bars,
229 x_chart_id,
230 x_row_count);
231 COMMIT;
232 END create_chart_autonomous;
233
234
235 PROCEDURE delete_data(p_chart_id NUMBER) IS
236 --
237 -- This is a simple wrapper to qa_chart_generic_pkg
238 -- since Histogram uses generic charting architecture.
239 --
240 BEGIN
241 qa_chart_generic_pkg.delete_data(p_chart_id);
242 END delete_data;
243
244
245 PROCEDURE delete_data_autonomous(p_chart_id NUMBER) IS
246 --
247 -- This is a wrapper to delete_data and performs
248 -- autonomous commit.
249 --
250 PRAGMA autonomous_transaction;
251 BEGIN
252 delete_data(p_chart_id);
253 COMMIT;
254 END delete_data_autonomous;
255
256
257 END qa_chart_histogram_pkg;
[Home] [Help]