[Home] [Help]
PACKAGE BODY: APPS.ZPB_TRANSFORM_IO_TABLES
Source
1 PACKAGE BODY ZPB_TRANSFORM_IO_TABLES AS
2 /* $Header: zpblevels.plb 115.22 2004/07/01 23:46:54 memayer ship $ */
3 PROCEDURE AddRow(p_dimensions curr_dims_table,
4 p_dimtable IN DimTabTyp,
5 p_temp_table IN VARCHAR2,
6 p_member IN zpb_solve_input_levels.member%TYPE,
7 p_leveltype IN VARCHAR2,
8 p_userid IN NUMBER,
9 p_memberorder IN zpb_solve_input_levels.MEMBER_ORDER%TYPE)
10
11 IS
12 dimcount INTEGER;
13 counter INTEGER;
14 tableinsert VARCHAR2(5000);
15 tablevalues VARCHAR2(5000);
16 CALCULATED_SOURCE INTEGER := 1200;
17
18 BEGIN
19 counter := 1;
20 dimcount := p_dimensions.count;
21 tableinsert := 'INSERT INTO ' || p_temp_table || '(MEMBER, ';
22 tableinsert := tableinsert || ' LEVEL_TYPE, MEMBER_ORDER, USER_ID, ';
23 tablevalues := ' VALUES(' || '''' || p_member || '''' || ', ';
24 tablevalues := tablevalues || '''' || p_leveltype || '''' || ', ';
25 tablevalues := tablevalues || p_memberorder || ', ';
26 tablevalues := tablevalues || p_userid || ', ';
27 WHILE counter <= dimcount
28 LOOP
29 --tableinsert := tableinsert || p_dimtable(counter).dimension;
30 tableinsert := tableinsert || p_dimensions(counter);
31 IF p_dimtable(1).loaded = CALCULATED_SOURCE THEN
32 tablevalues := tablevalues || '''' || ' ' || '''';
33 ELSIF p_dimtable.count < counter THEN
34 tablevalues := tablevalues || '''' || ' ' || '''';
35 ELSE
36 tablevalues := tablevalues || '''' || p_dimtable(counter).io_level || '''';
37 END IF;
38 IF counter = dimcount THEN
39 tableinsert := tableinsert || ')';
40 tablevalues := tablevalues || ')';
41 ELSE
42 tableinsert := tableinsert || ', ';
43 tablevalues := tablevalues || ', ';
44 END IF;
45 counter := counter + 1;
46 END LOOP;
47
48 EXECUTE IMMEDIATE tableinsert || tablevalues;
49
50 END AddRow;
51
52 PROCEDURE ZPB_TRANSFORM_INPUT_TABLE (p_ac_id IN NUMBER,
53 p_line_dim IN VARCHAR2,
54 p_temp_table IN VARCHAR2,
55 p_userid IN NUMBER,
56 p_view_dim_name IN VARCHAR2,
57 p_view_member_column IN VARCHAR2,
58 p_view_long_lbl_column IN VARCHAR2,
59 labelCursor OUT NOCOPY ZPB_TRANSFORM_IO_TABLES.ref_cursor,
60 dataCursor OUT NOCOPY ZPB_TRANSFORM_IO_TABLES.ref_cursor)
61
62 IS
63
64 Dimtable DimTabTyp;
65 dimcount INTEGER;
66 counter INTEGER;
67 member zpb_solve_input_levels.member%TYPE;
68 memberorder zpb_solve_input_levels.MEMBER_ORDER%TYPE;
69 rowcount BINARY_INTEGER;
70 bStart BOOLEAN;
71 tableSelect VARCHAR2(5000);
72 c4 ZPB_TRANSFORM_IO_TABLES.ref_cursor;
73 dimensions curr_dims_table;
74 TYPE level_rec IS RECORD (
75 member zpb_solve_input_levels.member%TYPE,
76 memberorder zpb_solve_input_levels.MEMBER_ORDER%TYPE,
77 dimension zpb_solve_input_levels.dimension%TYPE,
78 input_level VARCHAR2(2000),
79 SOURCE_TYPE zpb_solve_member_defs.SOURCE_TYPE%TYPE);
80
81 input_rec level_rec;
82
83
84 BEGIN
85
86 --make sure we have data
87 SELECT DISTINCT(Dimension) bulk collect into dimensions
88 FROM zpb_solve_output_levels
89 WHERE ANALYSIS_CYCLE_ID = p_ac_id
90 ORDER BY Dimension;
91
92 dimcount := dimensions.count;
93 IF dimcount = 0 THEN
94 raise NO_DATA_FOUND;
95 END IF;
96
97 --delete rows from previous run
98 tableSelect := 'DELETE FROM ' || p_temp_table || ' WHERE USER_ID = ' ;
99 tableSelect := tableSelect || p_userid || ' AND LEVEL_TYPE = ''' || INPUT_TYPE || '''';
100 EXECUTE IMMEDIATE tableSelect;
101 rowcount := 1;
102 bStart := true;
103
104 tableSelect := 'SELECT DISTINCT def.member, def.member_order, ';
105 tableSelect := tableSelect || ' nvl(input.dimension, ';
106 tableSelect := tableSelect || '''' || '-'|| '''';
107 tableSelect := tableSelect || ') AS dimension,';
108 tableSelect := tableSelect || ' levellookup.OBJECT_LONG_LABEL
109 AS input_level, def.SOURCE_TYPE ';
110 tableSelect := tableSelect || ' FROM ((zpb_solve_member_defs def ';
111 tableSelect := tableSelect || ' LEFT OUTER JOIN zpb_solve_input_levels input
112 ON def.member = input.member
113 AND def.ANALYSIS_CYCLE_ID = input.ANALYSIS_CYCLE_ID)
114 LEFT OUTER JOIN ZPB_SESSION_METADATA_LABELS levellookup ';
115 tableSelect := tableSelect ||
116 'ON levellookup.dimension = input.dimension
117 AND levellookup.OBJECT_AW_NAME = input.input_level ';
118 tableSelect := tableSelect || ' AND levellookup.USER_ID = ' || p_userid ;
119 tableSelect := tableSelect || ' AND levellookup.OBJECT_TYPE = ';
120 tableSelect := tableSelect || '''' || 'LEVEL' || '''' || ')';
121 tableSelect := tableSelect || ' WHERE def.ANALYSIS_CYCLE_ID = ';
122 tableSelect := tableSelect || p_ac_id;
123
124 tableSelect := tableSelect || ' ORDER BY 1, 3';
125
126 OPEN c4 FOR tableSelect;
127 LOOP
128 FETCH c4 into input_rec;
129 EXIT WHEN c4%NOTFOUND;
130 IF bStart THEN
131 member := input_rec.member;
132 memberorder := input_rec.memberorder;
133 Dimtable(rowcount).dimension := input_rec.dimension;
134 Dimtable(rowcount).io_level := input_rec.input_level;
135 Dimtable(rowcount).loaded := input_rec.SOURCE_TYPE;
136 bStart := false;
137 ELSIF member <> input_rec.member THEN
138 AddRow(dimensions, Dimtable, p_temp_table, member, INPUT_TYPE, p_userid, memberorder);
139 rowcount := 1;
140 member := input_rec.member;
141 memberorder := input_rec.memberorder;
142 Dimtable(rowcount).dimension := input_rec.dimension;
143 Dimtable(rowcount).io_level := input_rec.input_level;
144 Dimtable(rowcount).loaded := input_rec.SOURCE_TYPE;
145 ELSIF Dimtable(rowcount).dimension <> input_rec.dimension THEN
146 rowcount := rowcount + 1;
147 Dimtable(rowcount).dimension := input_rec.dimension;
148 Dimtable(rowcount).io_level := input_rec.input_level;
149 Dimtable(rowcount).loaded := input_rec.SOURCE_TYPE;
150 ELSE
151 Dimtable(rowcount).io_level := Dimtable(rowcount).io_level || ', ' ||
152 input_rec.input_level;
153 END IF;
154 END LOOP;
155
156 AddRow(dimensions, Dimtable, p_temp_table, member, INPUT_TYPE, p_userid, memberorder);
157
158 tableSelect := 'SELECT OBJECT_LONG_LABEL' ||
159 ' FROM ZPB_SESSION_METADATA_LABELS' ||
160 ' WHERE USER_ID = ' || p_userid ||
161 ' AND OBJECT_TYPE = ''' || 'DIMENSION' || '''' ||
162 ' AND OBJECT_AW_NAME IN' ||
163 ' (SELECT Dimension FROM ZPB_SOLVE_OUTPUT_LEVELS' ||
164 ' WHERE ANALYSIS_CYCLE_ID = ' || p_ac_id || ')' ||
165 ' ORDER BY Dimension';
166
167 OPEN labelCursor FOR tableSelect;
168
169 tableSelect := 'SELECT solvetemp.MEMBER as member, memberlookup.';
170 tableSelect := tableSelect || p_view_long_lbl_column || ' as MemberName, ';
171 counter := 1;
172 for indx IN dimensions.FIRST .. dimensions.LAST
173 LOOP
174 tableselect := tableselect || 'solvetemp.' || dimensions(indx);
175 IF counter < dimcount THEN
176 tableselect := tableselect || ', ';
177 END IF;
178 counter := counter + 1;
179 END LOOP;
180 tableselect := tableselect || ' FROM ' || p_temp_table || ' solvetemp, ';
181 tableselect := tableselect || p_view_dim_name || ' memberlookup ';
182 tableselect := tableselect || ' WHERE solvetemp.LEVEL_TYPE = ''' || INPUT_TYPE || '''';
183 tableselect := tableselect || ' AND solvetemp.MEMBER = memberlookup.';
184 tableselect := tableselect || p_view_member_column;
185 tableselect := tableselect || ' ORDER BY solvetemp.MEMBER_ORDER';
186
187 OPEN dataCursor FOR tableSelect;
188
189
190 END ZPB_TRANSFORM_INPUT_TABLE;
191
192 PROCEDURE ZPB_TRANSFORM_OUTPUT_TABLE (p_ac_id IN NUMBER,
193 p_line_dim IN VARCHAR2,
194 p_temp_table IN VARCHAR2,
195 p_userid IN NUMBER,
196 p_view_dim_name IN VARCHAR2,
197 p_view_member_column IN VARCHAR2,
198 p_view_long_lbl_column IN VARCHAR2,
199 labelCursor OUT NOCOPY ZPB_TRANSFORM_IO_TABLES.ref_cursor,
200 dataCursor OUT NOCOPY ZPB_TRANSFORM_IO_TABLES.ref_cursor)
201
202 IS
203
204 Dimtable DimTabTyp;
205 dimcount INTEGER;
206 counter INTEGER;
207 member zpb_solve_output_levels.member%TYPE;
208 memberorder zpb_solve_output_levels.MEMBER_ORDER%TYPE;
209 rowcount BINARY_INTEGER;
210 bStart BOOLEAN;
211 tableSelect VARCHAR2(5000);
212 c4 ZPB_TRANSFORM_IO_TABLES.ref_cursor;
213 dimensions curr_dims_table;
214 TYPE level_rec IS RECORD (
215 member zpb_solve_output_levels.member%TYPE,
216 memberorder zpb_solve_output_levels.MEMBER_ORDER%TYPE,
217 dimension zpb_solve_output_levels.dimension%TYPE,
218 hierarchy zpb_solve_output_levels.hierarchy%TYPE,
219 output_level VARCHAR2(2000));
220
221
222
223 output_rec level_rec;
224
225 BEGIN
226
227 SELECT DISTINCT(Dimension) bulk collect into dimensions
228 FROM zpb_solve_output_levels
229 WHERE ANALYSIS_CYCLE_ID = p_ac_id
230 ORDER BY Dimension;
231
232 dimcount := dimensions.count;
233 IF dimcount = 0 THEN
234 raise NO_DATA_FOUND;
235 END IF;
236
237 --delete rows from previous run
238 tableSelect := 'DELETE FROM ' || p_temp_table || ' WHERE USER_ID = ' ;
239 tableSelect := tableSelect || p_userid || ' AND LEVEL_TYPE = ''' || OUTPUT_TYPE || '''';
240 EXECUTE IMMEDIATE tableSelect;
241 rowcount := 1;
242 bStart := true;
243
244 tableSelect := 'SELECT distinct output.member, output.member_order, ';
245 tableSelect := tableSelect || ' output.dimension AS dimension,
246 output.hierarchy AS hierarchy,
247 levellookup.OBJECT_LONG_LABEL AS output_level
248 FROM zpb_solve_output_levels output ';
249 tableSelect := tableSelect || ' INNER JOIN ZPB_SESSION_METADATA_LABELS levellookup
250 ON levellookup.dimension = output.dimension
251 AND levellookup.OBJECT_AW_NAME = output.output_level
252 WHERE output.ANALYSIS_CYCLE_ID = ';
253 tableSelect := tableSelect || p_ac_id;
254 tableSelect := tableSelect || ' AND levellookup.USER_ID = ' || p_userid ;
255 tableSelect := tableSelect || ' AND levellookup.OBJECT_TYPE = ';
256 tableSelect := tableSelect || '''' || 'LEVEL' || '''';
257 tableSelect := tableSelect || ' ORDER BY output.member, output.dimension';
258
259 OPEN c4 FOR tableSelect;
260
261
262 LOOP
263 FETCH c4 into output_rec;
264 EXIT WHEN c4%NOTFOUND;
265 IF bStart THEN
266 member := output_rec.member;
267 memberorder := output_rec.memberorder;
268 Dimtable(rowcount).dimension := output_rec.dimension;
269 Dimtable(rowcount).io_level := output_rec.output_level;
270 bStart := false;
271 ELSIF member <> output_rec.member THEN
272 AddRow(dimensions, Dimtable, p_temp_table, member, OUTPUT_TYPE, p_userid, memberorder);
273 rowcount := 1;
274 member := output_rec.member;
275 memberorder := output_rec.memberorder;
276 Dimtable(rowcount).dimension := output_rec.dimension;
277 Dimtable(rowcount).io_level := output_rec.output_level;
278 ELSIF Dimtable(rowcount).dimension <> output_rec.dimension THEN
279 rowcount := rowcount + 1;
280 Dimtable(rowcount).dimension := output_rec.dimension;
281 Dimtable(rowcount).io_level := output_rec.output_level;
282 ELSE
283 Dimtable(rowcount).io_level := Dimtable(rowcount).io_level || ', ' ||
284 output_rec.output_level;
285 END IF;
286 END LOOP;
287
288 AddRow(dimensions, Dimtable, p_temp_table, member, OUTPUT_TYPE, p_userid, memberorder);
289
290 tableSelect := 'SELECT OBJECT_LONG_LABEL' ||
291 ' FROM ZPB_SESSION_METADATA_LABELS' ||
292 ' WHERE USER_ID = ' || p_userid ||
293 ' AND OBJECT_TYPE = ''' || 'DIMENSION' || '''' ||
294 ' AND OBJECT_AW_NAME IN' ||
295 ' (SELECT Dimension FROM ZPB_SOLVE_OUTPUT_LEVELS' ||
296 ' WHERE ANALYSIS_CYCLE_ID = ' || p_ac_id || ')' ||
297 ' ORDER BY Dimension';
298
299 OPEN labelCursor FOR tableSelect;
300
301 tableSelect := 'SELECT solvetemp.MEMBER as member, memberlookup.';
302 tableSelect := tableSelect || p_view_long_lbl_column || ' as MemberName, ';
303 counter := 1;
304 for indx IN dimensions.FIRST .. dimensions.LAST
305 LOOP
306 tableselect := tableselect || 'solvetemp.' || dimensions(indx);
307 IF counter < dimcount THEN
308 tableselect := tableselect || ', ';
309 END IF;
310 counter := counter + 1;
311 END LOOP;
312 tableselect := tableselect || ' FROM ' || p_temp_table || ' solvetemp, ';
313 tableselect := tableselect || p_view_dim_name || ' memberlookup ';
314 tableselect := tableselect || ' WHERE solvetemp.LEVEL_TYPE = ''' || OUTPUT_TYPE || '''';
315 tableselect := tableselect || ' AND solvetemp.MEMBER = memberlookup.';
316 tableselect := tableselect || p_view_member_column;
317 tableselect := tableselect || ' ORDER BY solvetemp.MEMBER_ORDER';
318
319 OPEN dataCursor FOR tableSelect;
320
321 END ZPB_TRANSFORM_OUTPUT_TABLE;
322
323 END ZPB_TRANSFORM_IO_TABLES;