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.GMA_WF_GENRIC_PKG

Source


1 PACKAGE BODY GMA_WF_GENRIC_PKG AS
2 /* $Header: GMAWSRTB.pls 115.2 2002/12/03 22:41:38 appldev noship $ */
3 PROCEDURE SORT_ACT_DATA (P_ACTIVITY_ID NUMBER,
4                       SORTED_DATA IN OUT NOCOPY ACT_SORTED_DATA_TBL_TYPE) AS
5   TYPE RETRIEVE_DATA IS REF CURSOR;
6   RET_DATA   RETRIEVE_DATA;  -- declare cursor variable
7   sql_stmt   VARCHAR2(300);
8   DATA_REC   GMA_ACTDATA_WF%ROWTYPE;
9   COUNTER1   NUMBER(5) := 10;
10   l_where1   VARCHAR2(40) := ' AND COLUMN_VALUE1 IS NOT NULL  ';
11   l_where2   VARCHAR2(40) := ' AND COLUMN_VALUE2 IS NOT NULL  ';
12   l_where3   VARCHAR2(40) := ' AND COLUMN_VALUE3 IS NOT NULL  ';
13   l_where4   VARCHAR2(40) := ' AND COLUMN_VALUE4 IS NOT NULL  ';
14   l_where5   VARCHAR2(40) := ' AND COLUMN_VALUE5 IS NOT NULL  ';
15   l_where6   VARCHAR2(40) := ' AND COLUMN_VALUE6 IS NOT NULL  ';
16   l_where7   VARCHAR2(40) := ' AND COLUMN_VALUE7 IS NOT NULL  ';
17   l_where8   VARCHAR2(40) := ' AND COLUMN_VALUE8 IS NOT NULL  ';
18   l_where9   VARCHAR2(40) := ' AND COLUMN_VALUE9 IS NOT NULL  ';
19   l_where10  VARCHAR2(40) := ' AND COLUMN_VALUE10 IS NOT NULL  ';
20   sql_stmt1  VARCHAR2(4000);
21   no_cols    NUMBER(5);
22   set_where  NUMBER(5);
23   i    INTEGER := 1;
24 BEGIN
25   sql_stmt := 'SELECT * FROM GMA_ACTDATA_WF WHERE ACTIVITY_ID = ' ||P_ACTIVITY_ID;
26   LOOP
27     IF COUNTER1 > 0 THEN
28                   /* Make the last column value in hierarchy NULL */
29       sql_stmt1 := sql_stmt || l_where1 || l_where2 || l_where3
30                   || l_where4 || l_where5 || l_where6
31                   || l_where7 || l_where8 || l_where9
32                   || l_where10;
33       OPEN RET_DATA FOR sql_stmt1;
34       LOOP
35         FETCH RET_DATA INTO DATA_REC;
36         EXIT WHEN RET_DATA%NOTFOUND;
37 	SORTED_DATA(i).ACTIVITY_ID         :=DATA_REC.ACTIVITY_ID;
38 	SORTED_DATA(i).COLUMN_NAME1        :=DATA_REC.COLUMN_NAME1;
39 	SORTED_DATA(i).COLUMN_VALUE1       :=DATA_REC.COLUMN_VALUE1;
40 	SORTED_DATA(i).COLUMN_NAME2        :=DATA_REC.COLUMN_NAME2;
41 	SORTED_DATA(i).COLUMN_VALUE2       :=DATA_REC.COLUMN_VALUE2;
42 	SORTED_DATA(i).COLUMN_NAME3        :=DATA_REC.COLUMN_NAME3;
43 	SORTED_DATA(i).COLUMN_VALUE3       :=DATA_REC.COLUMN_VALUE3;
44 	SORTED_DATA(i).COLUMN_NAME4        :=DATA_REC.COLUMN_NAME4;
45 	SORTED_DATA(i).COLUMN_VALUE4       :=DATA_REC.COLUMN_VALUE4;
46 	SORTED_DATA(i).COLUMN_NAME5        :=DATA_REC.COLUMN_NAME5;
47 	SORTED_DATA(i).COLUMN_VALUE5       :=DATA_REC.COLUMN_VALUE5;
48 	SORTED_DATA(i).COLUMN_NAME6        :=DATA_REC.COLUMN_NAME6;
49 	SORTED_DATA(i).COLUMN_VALUE6       :=DATA_REC.COLUMN_VALUE6;
50 	SORTED_DATA(i).COLUMN_NAME7        :=DATA_REC.COLUMN_NAME7;
51 	SORTED_DATA(i).COLUMN_VALUE7       :=DATA_REC.COLUMN_VALUE7;
52 	SORTED_DATA(i).COLUMN_NAME8        :=DATA_REC.COLUMN_NAME8;
53 	SORTED_DATA(i).COLUMN_VALUE8       :=DATA_REC.COLUMN_VALUE8;
54 	SORTED_DATA(i).COLUMN_NAME9        :=DATA_REC.COLUMN_NAME9;
55 	SORTED_DATA(i).COLUMN_VALUE9       :=DATA_REC.COLUMN_VALUE9;
56 	SORTED_DATA(i).COLUMN_NAME10       :=DATA_REC.COLUMN_NAME10;
57 	SORTED_DATA(i).COLUMN_VALUE10      :=DATA_REC.COLUMN_VALUE10;
58 	SORTED_DATA(i).ROLE                :=DATA_REC.ROLE;
59 	SORTED_DATA(i).LAST_UPDATE_LOGIN   :=DATA_REC.LAST_UPDATE_LOGIN;
60 	SORTED_DATA(i).LAST_UPDATED_BY     :=DATA_REC.LAST_UPDATED_BY;
61 	SORTED_DATA(i).CREATED_BY          :=DATA_REC.CREATED_BY;
62 	SORTED_DATA(i).CREATION_DATE       :=DATA_REC.CREATION_DATE;
63 	SORTED_DATA(i).LAST_UPDATE_DATE    :=DATA_REC.LAST_UPDATE_DATE;
64 	SORTED_DATA(i).ENABLE_FLAG         :=DATA_REC.ENABLE_FLAG;
65       UPDATE GMA_ACTDATA_WF
66       SET SORT_ORDER = i
67       WHERE ACTIVITY_ID          = DATA_REC.ACTIVITY_ID
68 	  AND NVL(COLUMN_VALUE1,0) = NVL(DATA_REC.COLUMN_VALUE1,0)
69 	  AND NVL(COLUMN_VALUE2,0) = NVL(DATA_REC.COLUMN_VALUE2,0)
70 	  AND NVL(COLUMN_VALUE3,0) =NVL(DATA_REC.COLUMN_VALUE3,0)
71 	  AND NVL(COLUMN_VALUE4,0) =NVL(DATA_REC.COLUMN_VALUE4,0)
72 	  AND NVL(COLUMN_VALUE5,0) =NVL(DATA_REC.COLUMN_VALUE5,0)
73 	  AND NVL(COLUMN_VALUE6,0) =NVL(DATA_REC.COLUMN_VALUE6,0)
74 	  AND NVL(COLUMN_VALUE7,0) =NVL(DATA_REC.COLUMN_VALUE7,0)
75 	  AND NVL(COLUMN_VALUE8,0) =NVL(DATA_REC.COLUMN_VALUE8,0)
76 	  AND NVL(COLUMN_VALUE9,0) =NVL(DATA_REC.COLUMN_VALUE9,0)
77 	  AND NVL(COLUMN_VALUE10,0)=NVL(DATA_REC.COLUMN_VALUE10,0);
78              i:=i+1;
79       END LOOP;
80       CLOSE RET_DATA;
81       IF  COUNTER1 = 10 THEN
82           l_where10 :=  ' AND COLUMN_VALUE10 IS NULL  ';
83       ELSIF COUNTER1 = 9 THEN
84           l_where9 :=  ' AND COLUMN_VALUE9 IS NULL  ';
85       ELSIF COUNTER1 = 8 THEN
86           l_where8 :=  ' AND COLUMN_VALUE8 IS NULL  ';
87       ELSIF COUNTER1 = 7 THEN
88           l_where7 :=  ' AND COLUMN_VALUE7 IS NULL  ';
89       ELSIF COUNTER1 = 6 THEN
90           l_where6 :=  ' AND COLUMN_VALUE6 IS NULL  ';
91       ELSIF COUNTER1 = 5 THEN
92           l_where5 :=  ' AND COLUMN_VALUE5 IS NULL  ';
93       ELSIF COUNTER1 = 4 THEN
94           l_where4 :=  ' AND COLUMN_VALUE4 IS NULL  ';
95       ELSIF COUNTER1 = 3 THEN
96           l_where3 :=  ' AND COLUMN_VALUE3 IS NULL  ';
97       ELSIF COUNTER1 = 2 THEN
98           l_where2 :=  ' AND COLUMN_VALUE2 IS NULL  ';
99       ELSIF COUNTER1 = 1 THEN
100           l_where1 :=  ' AND COLUMN_VALUE1 IS NULL  ';
101       END IF;
102       COUNTER1 := COUNTER1 - 1;
103     ELSE
104       EXIT;
105     END IF;
106   END LOOP;
107   SELECT count(*) INTO no_cols
108   from  GMA_ACTCOL_WF_B
109   WHERE ACTIVITY_ID = P_ACTIVITY_ID;
110   l_where1   := ' AND COLUMN_VALUE1 IS NULL  ';
111   l_where2   := ' AND COLUMN_VALUE2 IS NOT NULL  ';
112   l_where3   := ' AND COLUMN_VALUE3 IS NOT NULL  ';
113   l_where4   := ' AND COLUMN_VALUE4 IS NOT NULL  ';
114   l_where5   := ' AND COLUMN_VALUE5 IS NOT NULL  ';
115   l_where6   := ' AND COLUMN_VALUE6 IS NOT NULL  ';
116   l_where7   := ' AND COLUMN_VALUE7 IS NOT NULL  ';
117   l_where8   := ' AND COLUMN_VALUE8 IS NOT NULL  ';
118   l_where9   := ' AND COLUMN_VALUE9 IS NOT NULL  ';
119   l_where10  := ' AND COLUMN_VALUE10 IS NOT NULL  ';
120   COUNTER1 := 10;
121   set_where := no_cols;
122   no_cols := 10 - no_cols;
123   LOOP
124     IF no_cols >0 THEN
125        IF  COUNTER1 = 10 THEN
126           l_where10 :=  '  ';
127       ELSIF COUNTER1 = 9 THEN
128           l_where9 :=  '   ';
129       ELSIF COUNTER1 = 8 THEN
130           l_where8 :=  '   ';
131       ELSIF COUNTER1 = 7 THEN
132           l_where7 :=  '   ';
133       ELSIF COUNTER1 = 6 THEN
134           l_where6 :=  '   ';
135       ELSIF COUNTER1 = 5 THEN
136           l_where5 :=  '   ';
137       ELSIF COUNTER1 = 4 THEN
138           l_where4 :=  '  ';
139       ELSIF COUNTER1 = 3 THEN
140           l_where3 :=  '  ';
141       ELSIF COUNTER1 = 2 THEN
142           l_where2 :=  '  ';
143       ELSIF COUNTER1 = 1 THEN
144           l_where1 :=  '  ';
145       END IF;
146       no_cols := no_cols - 1;
147       counter1 := counter1 -1;
148     ELSE
149       EXIT;
150     END IF;
151   END LOOP;
152   COUNTER1 := 1;
153   LOOP
154     IF COUNTER1 <= Set_where THEN
155                   /* Make the last column value in hierarchy NULL */
156       sql_stmt1 := sql_stmt || l_where1 || l_where2 || l_where3
157                   || l_where4 || l_where5 || l_where6
158                   || l_where7 || l_where8 || l_where9
159                   || l_where10;
160       OPEN RET_DATA FOR sql_stmt1;
161       LOOP
162         FETCH RET_DATA INTO DATA_REC;
163         EXIT WHEN RET_DATA%NOTFOUND;
164 	SORTED_DATA(i).ACTIVITY_ID         :=DATA_REC.ACTIVITY_ID;
165 	SORTED_DATA(i).COLUMN_NAME1        :=DATA_REC.COLUMN_NAME1;
166 	SORTED_DATA(i).COLUMN_VALUE1       :=DATA_REC.COLUMN_VALUE1;
167 	SORTED_DATA(i).COLUMN_NAME2        :=DATA_REC.COLUMN_NAME2;
168 	SORTED_DATA(i).COLUMN_VALUE2       :=DATA_REC.COLUMN_VALUE2;
169 	SORTED_DATA(i).COLUMN_NAME3        :=DATA_REC.COLUMN_NAME3;
170 	SORTED_DATA(i).COLUMN_VALUE3       :=DATA_REC.COLUMN_VALUE3;
171 	SORTED_DATA(i).COLUMN_NAME4        :=DATA_REC.COLUMN_NAME4;
172 	SORTED_DATA(i).COLUMN_VALUE4       :=DATA_REC.COLUMN_VALUE4;
173 	SORTED_DATA(i).COLUMN_NAME5        :=DATA_REC.COLUMN_NAME5;
174 	SORTED_DATA(i).COLUMN_VALUE5       :=DATA_REC.COLUMN_VALUE5;
175 	SORTED_DATA(i).COLUMN_NAME6        :=DATA_REC.COLUMN_NAME6;
176 	SORTED_DATA(i).COLUMN_VALUE6       :=DATA_REC.COLUMN_VALUE6;
177 	SORTED_DATA(i).COLUMN_NAME7        :=DATA_REC.COLUMN_NAME7;
178 	SORTED_DATA(i).COLUMN_VALUE7       :=DATA_REC.COLUMN_VALUE7;
179 	SORTED_DATA(i).COLUMN_NAME8        :=DATA_REC.COLUMN_NAME8;
180 	SORTED_DATA(i).COLUMN_VALUE8       :=DATA_REC.COLUMN_VALUE8;
181 	SORTED_DATA(i).COLUMN_NAME9        :=DATA_REC.COLUMN_NAME9;
182 	SORTED_DATA(i).COLUMN_VALUE9       :=DATA_REC.COLUMN_VALUE9;
183 	SORTED_DATA(i).COLUMN_NAME10       :=DATA_REC.COLUMN_NAME10;
184 	SORTED_DATA(i).COLUMN_VALUE10      :=DATA_REC.COLUMN_VALUE10;
185 	SORTED_DATA(i).ROLE                :=DATA_REC.ROLE;
186 	SORTED_DATA(i).LAST_UPDATE_LOGIN   :=DATA_REC.LAST_UPDATE_LOGIN;
187 	SORTED_DATA(i).LAST_UPDATED_BY     :=DATA_REC.LAST_UPDATED_BY;
188 	SORTED_DATA(i).CREATED_BY          :=DATA_REC.CREATED_BY;
189 	SORTED_DATA(i).CREATION_DATE       :=DATA_REC.CREATION_DATE;
190 	SORTED_DATA(i).LAST_UPDATE_DATE    :=DATA_REC.LAST_UPDATE_DATE;
191 	SORTED_DATA(i).ENABLE_FLAG         :=DATA_REC.ENABLE_FLAG;
192       UPDATE GMA_ACTDATA_WF
193       SET SORT_ORDER = i
194       WHERE ACTIVITY_ID          = DATA_REC.ACTIVITY_ID
195 	  AND NVL(COLUMN_VALUE1,0) = NVL(DATA_REC.COLUMN_VALUE1,0)
196 	  AND NVL(COLUMN_VALUE2,0) = NVL(DATA_REC.COLUMN_VALUE2,0)
197 	  AND NVL(COLUMN_VALUE3,0) =NVL(DATA_REC.COLUMN_VALUE3,0)
198 	  AND NVL(COLUMN_VALUE4,0) =NVL(DATA_REC.COLUMN_VALUE4,0)
199 	  AND NVL(COLUMN_VALUE5,0) =NVL(DATA_REC.COLUMN_VALUE5,0)
200 	  AND NVL(COLUMN_VALUE6,0) =NVL(DATA_REC.COLUMN_VALUE6,0)
201 	  AND NVL(COLUMN_VALUE7,0) =NVL(DATA_REC.COLUMN_VALUE7,0)
202 	  AND NVL(COLUMN_VALUE8,0) =NVL(DATA_REC.COLUMN_VALUE8,0)
203 	  AND NVL(COLUMN_VALUE9,0) =NVL(DATA_REC.COLUMN_VALUE9,0)
204 	  AND NVL(COLUMN_VALUE10,0)=NVL(DATA_REC.COLUMN_VALUE10,0);
205         i:=i+1;
206       END LOOP;
207       CLOSE RET_DATA;
208       COUNTER1 := COUNTER1 + 1;
209       IF  COUNTER1 = 10 THEN
210           l_where10 :=  ' AND COLUMN_VALUE10 IS NULL  ';
211       ELSIF COUNTER1 = 9 THEN
212           l_where9 :=  ' AND COLUMN_VALUE9 IS NULL  ';
213       ELSIF COUNTER1 = 8 THEN
214           l_where8 :=  ' AND COLUMN_VALUE8 IS NULL  ';
215       ELSIF COUNTER1 = 7 THEN
216           l_where7 :=  ' AND COLUMN_VALUE7 IS NULL  ';
217       ELSIF COUNTER1 = 6 THEN
218           l_where6 :=  ' AND COLUMN_VALUE6 IS NULL  ';
219       ELSIF COUNTER1 = 5 THEN
220           l_where5 :=  ' AND COLUMN_VALUE5 IS NULL  ';
221       ELSIF COUNTER1 = 4 THEN
222           l_where4 :=  ' AND COLUMN_VALUE4 IS NULL  ';
223       ELSIF COUNTER1 = 3 THEN
224           l_where3 :=  ' AND COLUMN_VALUE3 IS NULL  ';
225       ELSIF COUNTER1 = 2 THEN
226           l_where2 :=  ' AND COLUMN_VALUE2 IS NULL  ';
227       ELSIF COUNTER1 = 1 THEN
228           l_where1 :=  ' AND COLUMN_VALUE1 IS NULL  ';
229       END IF;
230     ELSE
231       EXIT;
232     END IF;
233   END LOOP;
234 END;
235 PROCEDURE SORT_PROC_DATA (P_WF_ITEM_TYPE VARCHAR2,
236                           P_PROCESS_NAME VARCHAR2,
237                       SORTED_DATA IN OUT NOCOPY PROC_SORTED_DATA_TBL_TYPE)  IS
238   TYPE RETRIEVE_DATA IS REF CURSOR;
239   RET_DATA   RETRIEVE_DATA;  -- declare cursor variable
240   sql_stmt   VARCHAR2(300);
241   DATA_REC   GMA_PROCDATA_WF%ROWTYPE;
242   COUNTER1   NUMBER(5) := 10;
243   l_where1   VARCHAR2(40) := ' AND COLUMN_VALUE1 IS NOT NULL  ';
244   l_where2   VARCHAR2(40) := ' AND COLUMN_VALUE2 IS NOT NULL  ';
245   l_where3   VARCHAR2(40) := ' AND COLUMN_VALUE3 IS NOT NULL  ';
249   l_where7   VARCHAR2(40) := ' AND COLUMN_VALUE7 IS NOT NULL  ';
246   l_where4   VARCHAR2(40) := ' AND COLUMN_VALUE4 IS NOT NULL  ';
247   l_where5   VARCHAR2(40) := ' AND COLUMN_VALUE5 IS NOT NULL  ';
248   l_where6   VARCHAR2(40) := ' AND COLUMN_VALUE6 IS NOT NULL  ';
250   l_where8   VARCHAR2(40) := ' AND COLUMN_VALUE8 IS NOT NULL  ';
251   l_where9   VARCHAR2(40) := ' AND COLUMN_VALUE9 IS NOT NULL  ';
252   l_where10  VARCHAR2(40) := ' AND COLUMN_VALUE10 IS NOT NULL  ';
253   sql_stmt1  VARCHAR2(4000);
254   no_cols    NUMBER(5);
255   set_where  NUMBER(5);
256   i    INTEGER := 1;
257 BEGIN
258   sql_stmt := 'SELECT * FROM GMA_PROCDATA_WF WHERE WF_ITEM_TYPE = ' ||''''||P_WF_ITEM_TYPE ||'''' ||
259               '   AND  PROCESS_NAME  = ' || ''''||P_PROCESS_NAME ||'''' ||'   ';
260   LOOP
261     IF COUNTER1 > 0 THEN
262                   /* Make the last column value in hierarchy NULL */
263       sql_stmt1 := sql_stmt || l_where1 || l_where2 || l_where3
264                   || l_where4 || l_where5 || l_where6
265                   || l_where7 || l_where8 || l_where9
266                   || l_where10;
267       OPEN RET_DATA FOR sql_stmt1;
268       LOOP
269         FETCH RET_DATA INTO DATA_REC;
270         EXIT WHEN RET_DATA%NOTFOUND;
271 	SORTED_DATA(i).WF_ITEM_TYPE        :=DATA_REC.WF_ITEM_TYPE;
272 	SORTED_DATA(i).PROCESS_NAME        :=DATA_REC.PROCESS_NAME;
273 	SORTED_DATA(i).COLUMN_NAME1        :=DATA_REC.COLUMN_NAME1;
274 	SORTED_DATA(i).COLUMN_VALUE1       :=DATA_REC.COLUMN_VALUE1;
275 	SORTED_DATA(i).COLUMN_NAME2        :=DATA_REC.COLUMN_NAME2;
276 	SORTED_DATA(i).COLUMN_VALUE2       :=DATA_REC.COLUMN_VALUE2;
277 	SORTED_DATA(i).COLUMN_NAME3        :=DATA_REC.COLUMN_NAME3;
278 	SORTED_DATA(i).COLUMN_VALUE3       :=DATA_REC.COLUMN_VALUE3;
279 	SORTED_DATA(i).COLUMN_NAME4        :=DATA_REC.COLUMN_NAME4;
280 	SORTED_DATA(i).COLUMN_VALUE4       :=DATA_REC.COLUMN_VALUE4;
281 	SORTED_DATA(i).COLUMN_NAME5        :=DATA_REC.COLUMN_NAME5;
282 	SORTED_DATA(i).COLUMN_VALUE5       :=DATA_REC.COLUMN_VALUE5;
283 	SORTED_DATA(i).COLUMN_NAME6        :=DATA_REC.COLUMN_NAME6;
284 	SORTED_DATA(i).COLUMN_VALUE6       :=DATA_REC.COLUMN_VALUE6;
285 	SORTED_DATA(i).COLUMN_NAME7        :=DATA_REC.COLUMN_NAME7;
286 	SORTED_DATA(i).COLUMN_VALUE7       :=DATA_REC.COLUMN_VALUE7;
287 	SORTED_DATA(i).COLUMN_NAME8        :=DATA_REC.COLUMN_NAME8;
288 	SORTED_DATA(i).COLUMN_VALUE8       :=DATA_REC.COLUMN_VALUE8;
289 	SORTED_DATA(i).COLUMN_NAME9        :=DATA_REC.COLUMN_NAME9;
290 	SORTED_DATA(i).COLUMN_VALUE9       :=DATA_REC.COLUMN_VALUE9;
291 	SORTED_DATA(i).COLUMN_NAME10       :=DATA_REC.COLUMN_NAME10;
292 	SORTED_DATA(i).COLUMN_VALUE10      :=DATA_REC.COLUMN_VALUE10;
293 	SORTED_DATA(i).ROLE                :=DATA_REC.ROLE;
294 	SORTED_DATA(i).LAST_UPDATE_LOGIN   :=DATA_REC.LAST_UPDATE_LOGIN;
295 	SORTED_DATA(i).LAST_UPDATED_BY     :=DATA_REC.LAST_UPDATED_BY;
296 	SORTED_DATA(i).CREATED_BY          :=DATA_REC.CREATED_BY;
297 	SORTED_DATA(i).CREATION_DATE       :=DATA_REC.CREATION_DATE;
298 	SORTED_DATA(i).LAST_UPDATE_DATE    :=DATA_REC.LAST_UPDATE_DATE;
299 	SORTED_DATA(i).ENABLE_FLAG         :=DATA_REC.ENABLE_FLAG;
300       UPDATE GMA_PROCDATA_WF
301       SET SORT_ORDER = i
302       WHERE WF_ITEM_TYPE          = DATA_REC.WF_ITEM_TYPE
303         AND PROCESS_NAME          = DATA_REC.PROCESS_NAME
304 	  AND NVL(COLUMN_VALUE1,0) = NVL(DATA_REC.COLUMN_VALUE1,0)
305 	  AND NVL(COLUMN_VALUE2,0) = NVL(DATA_REC.COLUMN_VALUE2,0)
306 	  AND NVL(COLUMN_VALUE3,0) =NVL(DATA_REC.COLUMN_VALUE3,0)
307 	  AND NVL(COLUMN_VALUE4,0) =NVL(DATA_REC.COLUMN_VALUE4,0)
308 	  AND NVL(COLUMN_VALUE5,0) =NVL(DATA_REC.COLUMN_VALUE5,0)
309 	  AND NVL(COLUMN_VALUE6,0) =NVL(DATA_REC.COLUMN_VALUE6,0)
310 	  AND NVL(COLUMN_VALUE7,0) =NVL(DATA_REC.COLUMN_VALUE7,0)
311 	  AND NVL(COLUMN_VALUE8,0) =NVL(DATA_REC.COLUMN_VALUE8,0)
312 	  AND NVL(COLUMN_VALUE9,0) =NVL(DATA_REC.COLUMN_VALUE9,0)
313 	  AND NVL(COLUMN_VALUE10,0)=NVL(DATA_REC.COLUMN_VALUE10,0);
314         i:=i+1;
315       END LOOP;
316       CLOSE RET_DATA;
317       IF  COUNTER1 = 10 THEN
318           l_where10 :=  ' AND COLUMN_VALUE10 IS NULL  ';
319       ELSIF COUNTER1 = 9 THEN
320           l_where9 :=  ' AND COLUMN_VALUE9 IS NULL  ';
321       ELSIF COUNTER1 = 8 THEN
322           l_where8 :=  ' AND COLUMN_VALUE8 IS NULL  ';
323       ELSIF COUNTER1 = 7 THEN
324           l_where7 :=  ' AND COLUMN_VALUE7 IS NULL  ';
325       ELSIF COUNTER1 = 6 THEN
326           l_where6 :=  ' AND COLUMN_VALUE6 IS NULL  ';
327       ELSIF COUNTER1 = 5 THEN
328           l_where5 :=  ' AND COLUMN_VALUE5 IS NULL  ';
329       ELSIF COUNTER1 = 4 THEN
330           l_where4 :=  ' AND COLUMN_VALUE4 IS NULL  ';
331       ELSIF COUNTER1 = 3 THEN
332           l_where3 :=  ' AND COLUMN_VALUE3 IS NULL  ';
333       ELSIF COUNTER1 = 2 THEN
334           l_where2 :=  ' AND COLUMN_VALUE2 IS NULL  ';
335       ELSIF COUNTER1 = 1 THEN
336           l_where1 :=  ' AND COLUMN_VALUE1 IS NULL  ';
337       END IF;
338       COUNTER1 := COUNTER1 - 1;
339     ELSE
340       EXIT;
341     END IF;
342   END LOOP;
343   SELECT count(*) INTO no_cols
344   from  GMA_PROCCOL_WF_B
348   l_where2   := ' AND COLUMN_VALUE2 IS NOT NULL  ';
345   WHERE WF_ITEM_TYPE = P_WF_ITEM_TYPE
346     AND PROCESS_NAME = P_PROCESS_NAME;
347   l_where1   := ' AND COLUMN_VALUE1 IS NULL  ';
349   l_where3   := ' AND COLUMN_VALUE3 IS NOT NULL  ';
350   l_where4   := ' AND COLUMN_VALUE4 IS NOT NULL  ';
351   l_where5   := ' AND COLUMN_VALUE5 IS NOT NULL  ';
352   l_where6   := ' AND COLUMN_VALUE6 IS NOT NULL  ';
353   l_where7   := ' AND COLUMN_VALUE7 IS NOT NULL  ';
354   l_where8   := ' AND COLUMN_VALUE8 IS NOT NULL  ';
355   l_where9   := ' AND COLUMN_VALUE9 IS NOT NULL  ';
356   l_where10  := ' AND COLUMN_VALUE10 IS NOT NULL  ';
357   COUNTER1 := 10;
358   set_where := no_cols;
359   no_cols := 10 - no_cols;
360   LOOP
361     IF no_cols >0 THEN
362        IF  COUNTER1 = 10 THEN
363           l_where10 :=  '  ';
364       ELSIF COUNTER1 = 9 THEN
365           l_where9 :=  '   ';
366       ELSIF COUNTER1 = 8 THEN
367           l_where8 :=  '   ';
368       ELSIF COUNTER1 = 7 THEN
369           l_where7 :=  '   ';
370       ELSIF COUNTER1 = 6 THEN
371           l_where6 :=  '   ';
372       ELSIF COUNTER1 = 5 THEN
373           l_where5 :=  '   ';
374       ELSIF COUNTER1 = 4 THEN
375           l_where4 :=  '  ';
376       ELSIF COUNTER1 = 3 THEN
377           l_where3 :=  '  ';
378       ELSIF COUNTER1 = 2 THEN
379           l_where2 :=  '  ';
380       ELSIF COUNTER1 = 1 THEN
381           l_where1 :=  '  ';
382       END IF;
383       no_cols := no_cols - 1;
384       counter1 := counter1 -1;
385     ELSE
386       EXIT;
387     END IF;
388   END LOOP;
389   COUNTER1 := 1;
390   LOOP
391     IF COUNTER1 <= Set_where THEN
392                   /* Make the last column value in hierarchy NULL */
393       sql_stmt1 := sql_stmt || l_where1 || l_where2 || l_where3
394                   || l_where4 || l_where5 || l_where6
395                   || l_where7 || l_where8 || l_where9
396                   || l_where10;
397       OPEN RET_DATA FOR sql_stmt1;
398       LOOP
399         FETCH RET_DATA INTO DATA_REC;
400         EXIT WHEN RET_DATA%NOTFOUND;
401 	SORTED_DATA(i).WF_ITEM_TYPE        :=DATA_REC.WF_ITEM_TYPE;
402 	SORTED_DATA(i).PROCESS_NAME        :=DATA_REC.PROCESS_NAME;
403 	SORTED_DATA(i).COLUMN_NAME1        :=DATA_REC.COLUMN_NAME1;
404 	SORTED_DATA(i).COLUMN_VALUE1       :=DATA_REC.COLUMN_VALUE1;
405 	SORTED_DATA(i).COLUMN_NAME2        :=DATA_REC.COLUMN_NAME2;
406 	SORTED_DATA(i).COLUMN_VALUE2       :=DATA_REC.COLUMN_VALUE2;
407 	SORTED_DATA(i).COLUMN_NAME3        :=DATA_REC.COLUMN_NAME3;
408 	SORTED_DATA(i).COLUMN_VALUE3       :=DATA_REC.COLUMN_VALUE3;
409 	SORTED_DATA(i).COLUMN_NAME4        :=DATA_REC.COLUMN_NAME4;
410 	SORTED_DATA(i).COLUMN_VALUE4       :=DATA_REC.COLUMN_VALUE4;
411 	SORTED_DATA(i).COLUMN_NAME5        :=DATA_REC.COLUMN_NAME5;
412 	SORTED_DATA(i).COLUMN_VALUE5       :=DATA_REC.COLUMN_VALUE5;
413 	SORTED_DATA(i).COLUMN_NAME6        :=DATA_REC.COLUMN_NAME6;
414 	SORTED_DATA(i).COLUMN_VALUE6       :=DATA_REC.COLUMN_VALUE6;
415 	SORTED_DATA(i).COLUMN_NAME7        :=DATA_REC.COLUMN_NAME7;
416 	SORTED_DATA(i).COLUMN_VALUE7       :=DATA_REC.COLUMN_VALUE7;
417 	SORTED_DATA(i).COLUMN_NAME8        :=DATA_REC.COLUMN_NAME8;
418 	SORTED_DATA(i).COLUMN_VALUE8       :=DATA_REC.COLUMN_VALUE8;
419 	SORTED_DATA(i).COLUMN_NAME9        :=DATA_REC.COLUMN_NAME9;
420 	SORTED_DATA(i).COLUMN_VALUE9       :=DATA_REC.COLUMN_VALUE9;
421 	SORTED_DATA(i).COLUMN_NAME10       :=DATA_REC.COLUMN_NAME10;
422 	SORTED_DATA(i).COLUMN_VALUE10      :=DATA_REC.COLUMN_VALUE10;
423 	SORTED_DATA(i).ROLE                :=DATA_REC.ROLE;
424 	SORTED_DATA(i).LAST_UPDATE_LOGIN   :=DATA_REC.LAST_UPDATE_LOGIN;
425 	SORTED_DATA(i).LAST_UPDATED_BY     :=DATA_REC.LAST_UPDATED_BY;
426 	SORTED_DATA(i).CREATED_BY          :=DATA_REC.CREATED_BY;
427 	SORTED_DATA(i).CREATION_DATE       :=DATA_REC.CREATION_DATE;
428 	SORTED_DATA(i).LAST_UPDATE_DATE    :=DATA_REC.LAST_UPDATE_DATE;
429 	SORTED_DATA(i).ENABLE_FLAG         :=DATA_REC.ENABLE_FLAG;
430       UPDATE GMA_PROCDATA_WF
431       SET SORT_ORDER = i
432       WHERE WF_ITEM_TYPE          = DATA_REC.WF_ITEM_TYPE
433         AND PROCESS_NAME          = DATA_REC.PROCESS_NAME
434 	  AND NVL(COLUMN_VALUE1,0) = NVL(DATA_REC.COLUMN_VALUE1,0)
435 	  AND NVL(COLUMN_VALUE2,0) = NVL(DATA_REC.COLUMN_VALUE2,0)
436 	  AND NVL(COLUMN_VALUE3,0) =NVL(DATA_REC.COLUMN_VALUE3,0)
437 	  AND NVL(COLUMN_VALUE4,0) =NVL(DATA_REC.COLUMN_VALUE4,0)
438 	  AND NVL(COLUMN_VALUE5,0) =NVL(DATA_REC.COLUMN_VALUE5,0)
439 	  AND NVL(COLUMN_VALUE6,0) =NVL(DATA_REC.COLUMN_VALUE6,0)
440 	  AND NVL(COLUMN_VALUE7,0) =NVL(DATA_REC.COLUMN_VALUE7,0)
441 	  AND NVL(COLUMN_VALUE8,0) =NVL(DATA_REC.COLUMN_VALUE8,0)
442 	  AND NVL(COLUMN_VALUE9,0) =NVL(DATA_REC.COLUMN_VALUE9,0)
443 	  AND NVL(COLUMN_VALUE10,0)=NVL(DATA_REC.COLUMN_VALUE10,0);
444         i:=i+1;
445       END LOOP;
446       CLOSE RET_DATA;
447       COUNTER1 := COUNTER1 + 1;
448       IF  COUNTER1 = 10 THEN
449           l_where10 :=  ' AND COLUMN_VALUE10 IS NULL  ';
450       ELSIF COUNTER1 = 9 THEN
451           l_where9 :=  ' AND COLUMN_VALUE9 IS NULL  ';
452       ELSIF COUNTER1 = 8 THEN
453           l_where8 :=  ' AND COLUMN_VALUE8 IS NULL  ';
454       ELSIF COUNTER1 = 7 THEN
455           l_where7 :=  ' AND COLUMN_VALUE7 IS NULL  ';
456       ELSIF COUNTER1 = 6 THEN
457           l_where6 :=  ' AND COLUMN_VALUE6 IS NULL  ';
458       ELSIF COUNTER1 = 5 THEN
459           l_where5 :=  ' AND COLUMN_VALUE5 IS NULL  ';
460       ELSIF COUNTER1 = 4 THEN
461           l_where4 :=  ' AND COLUMN_VALUE4 IS NULL  ';
462       ELSIF COUNTER1 = 3 THEN
463           l_where3 :=  ' AND COLUMN_VALUE3 IS NULL  ';
464       ELSIF COUNTER1 = 2 THEN
465           l_where2 :=  ' AND COLUMN_VALUE2 IS NULL  ';
466       ELSIF COUNTER1 = 1 THEN
467           l_where1 :=  ' AND COLUMN_VALUE1 IS NULL  ';
468       END IF;
469     ELSE
470       EXIT;
471     END IF;
472   END LOOP;
473 END;
474 END;
475 
476