eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.QLTSTORB

Source


1 PACKAGE BODY QLTSTORB as
2 /* $Header: qltstorb.plb 115.2 2002/11/27 19:34:44 jezheng ship $ */
3 
4 -- 3/8/95 - CREATED
5 -- Kevin Wiggen
6 
7 --  This is a storage unit used for creating the FK Lookups in the
8 --  Selection Criteria Engine, and in the Dynamic View Creation
9 --
10   -- Well actually this is the server and we don't have record groups,
11   -- so I'm am going to fake it with PLSQL tables
12   -- In this way the procedures can be the same for the client and server
13 
14   TYPE numtable IS TABLE OF NUMBER
15         INDEX BY BINARY_INTEGER;
16   TYPE char30table IS TABLE OF VARCHAR2(30)
17         INDEX BY BINARY_INTEGER;
18   TYPE char15table IS TABLE OF VARCHAR2(15)
19         INDEX BY BINARY_INTEGER;
20   TYPE char80table IS TABLE OF VARCHAR2(80)
21         INDEX BY BINARY_INTEGER;
22   TYPE char5table IS TABLE OF VARCHAR2(5)
23         INDEX BY BINARY_INTEGER;
24   TYPE char2000table IS TABLE OF VARCHAR2(2000)
25         INDEX BY BINARY_INTEGER;
26 
27  -- The following are used to reset the tables
28 
29   empty_numtable numtable;
30   empty_char30table char30table;
31   empty_char15table char15table;
32   empty_char80table char80table;
33   empty_char5table char5table;
34   empty_char2000table char2000table;
35 
36  -- The following mimic the kevin record group on the client
37 
38   char_num numtable;
39   hardcoded_column char30table;
40   result_column_name char30table;
41   datatype numtable;
42   op char15table;
43   low char80table;
44   high char80table;
45   sel numtable;
46   disp_len numtable;
47   promptt char80table;
48   orderr numtable;
49   totall numtable;
50   functionn numtable;
51   fxn_promptt char30table;
52   precisionn numtable;
53   fk_lookup_typee numtable;
54   fk_table_namee char30table;
55   fk_table_short_namee char5table;
56   pk_idd char30table;
57   pk_idd2 char30table;
58   pk_idd3 char30table;
59   fk_idd char30table;
60   fk_idd2 char30table;
61   fk_idd3 char30table;
62   fk_meaningg char30table;
63   fk_descc char30table;
64   fk_add_wheree char2000table;
65   table_namee char80table;
66   parent_block_namee char30table;
67   list_idd numtable;
68   TOTAL_ROWS BINARY_INTEGER := 0;
69 
70  -- The following mimic the daedsiluap record group on the client
71 
72   from_partial char2000table;
73   ROWS_FROM BINARY_INTEGER := 0;
74 
75  -- The following mimic the maggard record group on the client
76 
77   where_partial char2000table;
78   ROWS_WHERE BINARY_INTEGER := 0;
79 
80 
81    PROCEDURE MAKE_REC_GROUP IS
82 
83 
84   BEGIN
85     char_num := empty_numtable;
86     hardcoded_column := empty_char30table;
87     result_column_name := empty_char30table;
88     datatype := empty_numtable;
89     op := empty_char15table;
90     low := empty_char80table;
91     high := empty_char80table;
92     sel := empty_numtable;
93     disp_len := empty_numtable;
94     promptt := empty_char80table;
95     orderr := empty_numtable;
96     totall := empty_numtable;
97     functionn := empty_numtable;
98     fxn_promptt := empty_char30table;
99     precisionn := empty_numtable;
100     fk_table_namee := empty_char30table;
101     fk_table_short_namee := empty_char5table;
102     pk_idd := empty_char30table;
103     pk_idd2 := empty_char30table;
104     pk_idd3 := empty_char30table;
105     fk_idd := empty_char30table;
106     fk_idd2 := empty_char30table;
107     fk_idd3 := empty_char30table;
108     fk_meaningg := empty_char30table;
109     fk_descc := empty_char30table;
110     fk_add_wheree := empty_char2000table;
111     table_namee := empty_char80table;
112     parent_block_namee := empty_char30table;
113     list_idd := empty_numtable;
114     TOTAL_ROWS := 0;
115   END MAKE_REC_GROUP;
116 
117 
118    PROCEDURE ADD_ROW_TO_REC_GROUP (NUM NUMBER,
119 				   HARD_COLUMN VARCHAR2,
120 				   RES_COL_NAME VARCHAR2,
121  			  	   DATA NUMBER,
122 				   OPER VARCHAR2,
123 				   LOW_VAL VARCHAR2,
124 				   HIGH_VAL VARCHAR2,
125                                    SELE NUMBER,
126 				   DISP_LENGTH NUMBER,
127 				   PROMPT VARCHAR2,
128 				   ORDER_SEQ NUMBER,
129 				   TOTAL NUMBER,
130 				   FUNCTION NUMBER,
131 				   FXN_PROMPT VARCHAR2,
132 				   PRECISION NUMBER,
133 				   FK_LOOK_TYPE NUMBER,
134 				   FK_TABL_NAME VARCHAR2,
135 				   FK_TABL_SH_NAME VARCHAR2,
136 				   PK_ID VARCHAR2,
137 			           PK_ID2 VARCHAR2,
138   				   PK_ID3 VARCHAR2,
139 				   FK_ID VARCHAR2,
140 				   FK_ID2 VARCHAR2,
141 				   FK_ID3 VARCHAR2,
142 				   FK_MEANING VARCHAR2,
143 				   FK_DESC VARCHAR2,
144 				   FK_ADD_WHERE VARCHAR2,
145 				   TABLE_NAME VARCHAR2,
146 				   PARENT_BLOCK_NAME VARCHAR2,
147 				   LIST_ID NUMBER) IS
148 
149   BEGIN
150 
151     TOTAL_ROWS := TOTAL_ROWS + 1;
152     char_num(TOTAL_ROWS) := NUM;
153     hardcoded_column(TOTAL_ROWS) := HARD_COLUMN;
154     result_column_name(TOTAL_ROWS) := RES_COL_NAME;
155     datatype(TOTAL_ROWS) := DATA;
156     op(TOTAL_ROWS) := OPER;
157     low(TOTAL_ROWS) := LOW_VAL;
158     high(TOTAL_ROWS) := HIGH_VAL;
159     sel(TOTAL_ROWS) := SELE;
160     disp_len(TOTAL_ROWS) := DISP_LENGTH;
161     promptt(TOTAL_ROWS) := PROMPT;
162     orderr(TOTAL_ROWS) := ORDER_SEQ;
163     totall(TOTAL_ROWS) := TOTAL;
164     functionn(TOTAL_ROWS) := FUNCTION;
165     fxn_promptt(TOTAL_ROWS) := FXN_PROMPT;
166     precisionn(TOTAL_ROWS) := PRECISION;
167     fk_lookup_typee(TOTAL_ROWS) := FK_LOOK_TYPE;
168     fk_table_namee(TOTAL_ROWS) := FK_TABL_NAME;
169     fk_table_short_namee(TOTAL_ROWS) := FK_TABL_SH_NAME;
170     pk_idd(TOTAL_ROWS) := PK_ID;
171     pk_idd2(TOTAL_ROWS) := PK_ID2;
172     pk_idd3(TOTAL_ROWS) := PK_ID3;
173     fk_idd(TOTAL_ROWS) := FK_ID;
174     fk_idd2(TOTAL_ROWS) := FK_ID2;
175     fk_idd3(TOTAL_ROWS) := FK_ID3;
176     fk_meaningg(TOTAL_ROWS) := FK_MEANING;
177     fk_descc(TOTAL_ROWS) := FK_DESC;
178     fk_add_wheree(TOTAL_ROWS) := FK_ADD_WHERE;
179     table_namee(TOTAL_ROWS) := TABLE_NAME;
180     parent_block_namee(TOTAL_ROWS) := PARENT_BLOCK_NAME;
181     list_idd(TOTAL_ROWS) := LIST_ID;
182  END ADD_ROW_TO_REC_GROUP;
183 
184 
185   FUNCTION ROWS_IN_REC_GROUP
186 	RETURN NUMBER IS
187 
188   BEGIN
189     RETURN(TOTAL_ROWS);
190   END ROWS_IN_REC_GROUP;
191 
192   FUNCTION GET_NUMBER(X_COLUMN VARCHAR2, X_ROW NUMBER)
193 	RETURN NUMBER  IS
194 
195   i BINARY_INTEGER;  -- Just to be safe
196 
197   BEGIN
198     i := X_ROW;
199 
200     -- Yes this method is not the best but its the best we can do
201 
202     if X_COLUMN = 'char_num' then
203 	RETURN(char_num(i));
204     elsif X_COLUMN = 'datatype' then
205 	RETURN(datatype(i));
206     elsif X_COLUMN = 'sel' then
207       	RETURN(sel(i));
208     elsif X_COLUMN = 'disp_length' then
209 	RETURN(disp_len(i));
210     end if;
211 
212     if X_COLUMN = 'order' then
213 	RETURN(orderr(i));
214     elsif X_COLUMN = 'total' then
215  	RETURN(totall(i));
216     elsif X_COLUMN = 'function' then
217   	RETURN(functionn(i));
218     elsif X_COLUMN = 'fk_lookup_type' then
219  	RETURN(fk_lookup_typee(i));
220     elsif X_COLUMN = 'precision' then
221 	RETURN(precisionn(i));
222     elsif X_COLUMN = 'list_id' then
223         RETURN(list_idd(i));
224     end if;
225 
226   END GET_NUMBER;
227 
228 
229   FUNCTION GET_CHAR(X_COLUMN VARCHAR2, X_ROW NUMBER)
230 	RETURN VARCHAR2 IS
231 
232    i BINARY_INTEGER;
233 
234   BEGIN
235    i := X_ROW;
236 
237    if X_COLUMN = 'hardcoded_column' then
238 	RETURN(hardcoded_column(i));
239    elsif X_COLUMN = 'result_column_name' then
240 	RETURN(result_column_name(i));
241    elsif X_COLUMN = 'op' then
242 	RETURN(op(i));
243    elsif X_COLUMN = 'low' then
244 	RETURN(low(i));
245    elsif X_COLUMN = 'high' then
246  	RETURN(high(i));
247    elsif X_COLUMN = 'prompt' then
248  	RETURN(promptt(i));
249    end if;
250 
251    if X_COLUMN = 'fxn_prompt' then
252 	RETURN(fxn_promptt(i));
253    elsif X_COLUMN = 'fk_table_name' then
254  	RETURN(fk_table_namee(i));
255    elsif X_COLUMN = 'fk_table_short_name' then
256 	RETURN(fk_table_short_namee(i));
257    elsif X_COLUMN = 'pk_id' then
258 	RETURN(pk_idd(i));
259    elsif X_COLUMN = 'pk_id2' then
260 	RETURN(pk_idd2(i));
261    elsif X_COLUMN = 'pk_id3' then
262 	RETURN(pk_idd3(i));
263    end if;
264 
265    if X_COLUMN = 'fk_id' then
266 	RETURN(fk_idd(i));
267    elsif X_COLUMN = 'fk_id2' then
268 	RETURN(fk_idd2(i));
269    elsif X_COLUMN = 'fk_id3' then
270 	RETURN(fk_idd3(i));
271    elsif X_COLUMN = 'fk_meaning' then
272 	RETURN(fk_meaningg(i));
273    elsif X_COLUMN = 'fk_desc' then
274  	RETURN(fk_descc(i));
275    elsif X_COLUMN = 'fk_add_where' then
276  	RETURN(fk_add_wheree(i));
277    end if;
278 
279    if X_COLUMN = 'table_name' then
280         RETURN(table_namee(i));
281    elsif X_COLUMN = 'parent_block_name' then
282         RETURN(parent_block_namee(i));
283    end if;
284 
285   end GET_CHAR;
286 
287   PROCEDURE KILL_REC_GROUP IS
288 
289   BEGIN
290     char_num := empty_numtable;
291     hardcoded_column := empty_char30table;
292     result_column_name := empty_char30table;
293     datatype := empty_numtable;
294     op := empty_char15table;
295     low := empty_char80table;
296     high := empty_char80table;
297     sel := empty_numtable;
298     disp_len := empty_numtable;
299     promptt := empty_char80table;
300     orderr := empty_numtable;
301     totall := empty_numtable;
302     functionn := empty_numtable;
303     fxn_promptt := empty_char30table;
304     precisionn := empty_numtable;
305     fk_table_namee := empty_char30table;
306     fk_table_short_namee := empty_char5table;
307     pk_idd := empty_char30table;
308     pk_idd2 := empty_char30table;
309     pk_idd3 := empty_char30table;
310     fk_idd := empty_char30table;
311     fk_idd2 := empty_char30table;
312     fk_idd3 := empty_char30table;
313     fk_meaningg := empty_char30table;
314     fk_descc := empty_char30table;
315     fk_add_wheree := empty_char2000table;
316     table_namee := empty_char80table;
317     parent_block_namee := empty_char30table;
318     list_idd := empty_numtable;
319     TOTAL_ROWS := 0;
320   END KILL_REC_GROUP;
321 
322 
323   PROCEDURE Make_FROM_Rec_grp IS
324 
325   BEGIN
326     from_partial := empty_char2000table;
327     ROWS_FROM := 0;
328   END Make_FROM_Rec_grp;
329 
330   PROCEDURE Kill_FROM_Rec_grp IS
331 
332   BEGIN
333     from_partial := empty_char2000table;
334     ROWS_FROM := 0;
335   END Kill_FROM_Rec_grp;
336 
337 
338   Procedure ADD_ROW_TO_FROM_REC_GROUP(table_name VARCHAR2) IS
339     i BINARY_INTEGER := 1;
340     PRESENT  BOOLEAN := FALSE;
341 
342   BEGIN
343     WHILE (i <= ROWS_FROM) and (not PRESENT) LOOP
344           if table_name = from_partial(i) then
345 	     PRESENT := TRUE;
346           else
347  	     i := i + 1;
348 	  end if;
349     end LOOP;
350     if (not PRESENT) then
351         ROWS_FROM := ROWS_FROM + 1;
352         from_partial(ROWS_FROM) := table_name;
353     end if;
354   END ADD_ROW_TO_FROM_REC_GROUP;
355 
356 
357   Function Create_From_Clause
358  	Return VARCHAR2 IS
359 
360     i BINARY_INTEGER := 1;
361     V_FROM VARCHAR2(32767) := null;
362 
363   BEGIN
364     while i <= ROWS_FROM LOOP
365 	V_FROM := ', ' || from_partial(i) || V_FROM;
366         i := i + 1;
367     end LOOP;
368     V_FROM := 'FROM ' || SUBSTR(V_FROM,3);
369     from_partial := empty_char2000table;
370     ROWS_FROM := 0;
371     RETURN(V_FROM);
372   END Create_From_Clause;
373 
374   PROCEDURE Make_WHERE_Rec_grp IS
375 
376   BEGIN
377     where_partial := empty_char2000table;
378     ROWS_WHERE := 0;
379   END Make_WHERE_Rec_grp;
380 
381   PROCEDURE Kill_WHERE_Rec_grp IS
382 
383   BEGIN
384     where_partial := empty_char2000table;
385     ROWS_WHERE := 0;
386   END Kill_WHERE_Rec_grp;
387 
388   Procedure ADD_ROW_TO_WHERE_REC_GROUP (X_WHERE_PORTION VARCHAR2) IS
389     i BINARY_INTEGER := 1;
390     PRESENT  BOOLEAN := FALSE;
391 
392   BEGIN
393     WHILE (i <= ROWS_WHERE) and (not PRESENT) LOOP
394           if X_WHERE_PORTION = where_partial(i) then
395 	     PRESENT := TRUE;
396           else
397  	     i := i + 1;
398 	  end if;
399     end LOOP;
400     if (not PRESENT) then
401         ROWS_WHERE := ROWS_WHERE + 1;
402         where_partial(ROWS_WHERE) := X_WHERE_PORTION;
403     end if;
404   END ADD_ROW_TO_WHERE_REC_GROUP;
405 
406 
407   Function Create_WHERE_Clause
408  	Return VARCHAR2 IS
409 
410    i BINARY_INTEGER := 1;
411    V_WHERE VARCHAR2(32767) := null;
412 
413   BEGIN
414    while i <= ROWS_WHERE LOOP
415          V_WHERE := V_WHERE || ' AND ' || where_partial(i);
416  	 i := i + 1;
417    end LOOP;
418    if V_WHERE IS NOT NULL THEN
419       V_WHERE := 'WHERE ' || SUBSTR(V_WHERE,5);
420    end if;
421    where_partial := empty_char2000table;
422    ROWS_WHERE := 0;
423    RETURN(V_WHERE);
424   END Create_WHERE_Clause;
425 
426 END QLTSTORB;
427