1 PACKAGE BODY qa_chart_generic_pkg AS
2 /* $Header: qacgenb.pls 120.2 2006/03/20 12:08:39 bso noship $ */
3
4 --
5 -- Tracking Bug 4939897
6 -- R12 Forms Tech Stack Upgrade - Obsolete Oracle Graphics
7 --
8
9 PROCEDURE populate_data(
10 --
11 -- A simple procedure to execute a SQL and populate the
12 -- qa_chart_generic table with the resulting values. The SQL
13 -- should always select two columns: x-axis ticker label and
14 -- numeric y-value and should almost ways have an ORDER BY clause
15 -- so that the leftmost x-axis ticker as appear on the resultant
16 -- graph comes first and so forth.
17 --
18 -- bso Tue Jan 10 16:10:13 PST 2006
19 --
20 p_chart_id NUMBER,
21 p_sql VARCHAR2,
22 p_row_limit NUMBER DEFAULT NULL,
23 x_row_count OUT NOCOPY NUMBER) IS
24
25 l_x_values dbms_sql.varchar2s;
26 l_y_values dbms_sql.number_table;
27 l_line_values dbms_sql.number_table;
28 l_row_count NUMBER;
29 l_sql VARCHAR2(30000);
30
31 BEGIN
32 --
33 -- Dev Notes: This does not work if l_sql contains GROUP BY
34 -- becuase rownum cannot be used as SELECT when there is GB.
35 --
36 -- Constructed a simple SQL by using the input p_sql string
37 -- so to query all all data selected by it and populate
38 -- the qa_chart_generic table with the results.
39 --
40 -- l_sql :=
41 -- 'INSERT INTO qa_chart_generic(
42 -- created_by,
43 -- creation_date,
44 -- last_updated_by,
45 -- last_update_date,
46 -- last_update_login,
47 -- chart_id,
48 -- line,
49 -- x_value,
50 -- y_value)
51 -- SELECT
52 -- fnd_global.user_id,
53 -- sysdate,
54 -- fnd_global.user_id,
55 -- sysdate,
56 -- fnd_global.login_id,
57 -- :1,
58 -- rownum,' ||
59 -- l_sql;
60 --
61
62 --
63 -- Dev Notes: A different method has been investigated to
64 -- see if there is really a need to chop off the SELECT:
65 --
66 -- SELECT * FROM
67 -- (SELECT fnd_global.user_id, sysdate ... etc FROM dual),
68 -- (<original SQL>);
69 --
70 -- It seems to work, except there doesn't seem to be a way
71 -- to generate incremental line numbers for the line column.
72 -- Adding rownum to the dual clause simply generates rownum
73 -- once (i.e., all lines will have 1).
74 --
75 -- bso Tue Jan 10 16:48:14 PST 2006
76 --
77
78 --
79 -- Rewritten to use BULK COLLECT
80 -- bso Wed Feb 8 14:43:03 PST 2006
81 --
82
83 --
84 -- Bug 5044017. If p_row_limit is given, rewrite the SQL so
85 -- only first p_row_limit rows are fetched by applying a rownum
86 -- filter. Needed for Pareto top_n_groups feature but is also
87 -- a generic feature for future purpose.
88 -- bso Mon Mar 20 11:43:04 PST 2006
89 --
90 IF p_row_limit IS NULL THEN
91 EXECUTE IMMEDIATE p_sql
92 BULK COLLECT INTO l_x_values, l_y_values;
93 ELSE
94 l_sql := 'SELECT * FROM (' || p_sql ||
95 ') WHERE rownum <= :1';
96
97 EXECUTE IMMEDIATE l_sql
98 BULK COLLECT INTO l_x_values, l_y_values
99 USING p_row_limit;
100 END IF;
101
102 l_row_count := sql%ROWCOUNT;
103
104 FOR i IN l_x_values.FIRST .. l_x_values.LAST LOOP
105 l_line_values(i) := i;
106 END LOOP;
107
108 --
109 -- Notice, currently the legend column of qa_chart_generic
110 -- is unused. In future expansion, we may introduce a new
111 -- procedure populate_data_with_legend where the input SQL
112 -- string selects a third (legend) column to populate it.
113 --
114 IF l_row_count > 0 THEN
115 FORALL i IN 1 .. l_row_count
116 INSERT INTO qa_chart_generic(
117 created_by,
118 creation_date,
119 last_updated_by,
120 last_update_date,
121 last_update_login,
122 chart_id,
123 line,
124 x_value,
125 y_value)
126 VALUES (
127 fnd_global.user_id, -- created_by
128 sysdate, -- creation_date
129 fnd_global.user_id, -- last_updated_by
130 sysdate, -- last_update_date
131 fnd_global.login_id, -- last_update_login
132 p_chart_id, -- chart_id
133 l_line_values(i), -- line
134 l_x_values(i), -- x_value
135 l_y_values(i)); -- y_value
136 END IF;
137
138 x_row_count := l_row_count;
139
140 END populate_data;
141
142
143 PROCEDURE populate_data_autonomous(
144 p_chart_id NUMBER,
145 p_sql VARCHAR2,
146 p_row_limit NUMBER DEFAULT NULL,
147 x_row_count OUT NOCOPY NUMBER) IS
148
149 PRAGMA autonomous_transaction;
150
151 BEGIN
152 populate_data(
153 p_chart_id,
154 p_sql,
155 p_row_limit,
156 x_row_count);
157 COMMIT;
158 END populate_data_autonomous;
159
160
161 PROCEDURE delete_data(p_chart_id NUMBER) IS
162 BEGIN
163 DELETE FROM qa_chart_generic
164 WHERE chart_id = p_chart_id;
165 END delete_data;
166
167
168 PROCEDURE delete_data_autonomous(p_chart_id NUMBER) IS
169 PRAGMA autonomous_transaction;
170 BEGIN
171 delete_data(p_chart_id);
172 COMMIT;
173 END delete_data_autonomous;
174
175
176
177 END qa_chart_generic_pkg;
[Home] [Help]