[Home] [Help]
PACKAGE BODY: APPS.GMA_WFSTD_P
Source
1 PACKAGE BODY gma_wfstd_p AS
2 /* $Header: GMAWFSTB.pls 115.7 2002/11/01 21:19:22 appldev ship $ */
3 /* Procedure to get the role. Input parameters are
4 Wokflow type, Process type and Activity_type */
5 PROCEDURE get_role(
6
7 p_wf_item_type IN varchar2,
8 p_process_name IN varchar2,
9 p_activity_name IN varchar2,
10 p_datastring IN VARCHAR2,
11 P_role OUT NOCOPY VARCHAR2
12 ) IS
13
14 l_column_name1 GMA_ACTDATA_WF.COLUMN_NAME1%TYPE DEFAULT NULL;
15 l_column_value1 GMA_ACTDATA_WF.COLUMN_VALUE1%TYPE DEFAULT NULL;
16 l_column_name2 GMA_ACTDATA_WF.COLUMN_NAME2%TYPE DEFAULT NULL;
17 l_column_value2 GMA_ACTDATA_WF.COLUMN_VALUE2%TYPE DEFAULT NULL;
18 l_column_name3 GMA_ACTDATA_WF.COLUMN_NAME3%TYPE DEFAULT NULL;
19 l_column_value3 GMA_ACTDATA_WF.COLUMN_VALUE3%TYPE DEFAULT NULL;
20 l_column_name4 GMA_ACTDATA_WF.COLUMN_NAME4%TYPE DEFAULT NULL;
21 l_column_value4 GMA_ACTDATA_WF.COLUMN_VALUE4%TYPE DEFAULT NULL;
22 l_column_name5 GMA_ACTDATA_WF.COLUMN_NAME5%TYPE DEFAULT NULL;
23 l_column_value5 GMA_ACTDATA_WF.COLUMN_VALUE5%TYPE DEFAULT NULL;
24 l_column_name6 GMA_ACTDATA_WF.COLUMN_NAME6%TYPE DEFAULT NULL;
25 l_column_value6 GMA_ACTDATA_WF.COLUMN_VALUE6%TYPE DEFAULT NULL;
26 l_column_name7 GMA_ACTDATA_WF.COLUMN_NAME7%TYPE DEFAULT NULL;
27 l_column_value7 GMA_ACTDATA_WF.COLUMN_VALUE7%TYPE DEFAULT NULL;
28 l_column_name8 GMA_ACTDATA_WF.COLUMN_NAME8%TYPE DEFAULT NULL;
29 l_column_value8 GMA_ACTDATA_WF.COLUMN_VALUE8%TYPE DEFAULT NULL;
30 l_column_name9 GMA_ACTDATA_WF.COLUMN_NAME9%TYPE DEFAULT NULL;
31 l_column_value9 GMA_ACTDATA_WF.COLUMN_VALUE9%TYPE DEFAULT NULL;
32 l_column_name10 GMA_ACTDATA_WF.COLUMN_NAME10%TYPE DEFAULT NULL;
33 l_column_value10 GMA_ACTDATA_WF.COLUMN_VALUE10%TYPE DEFAULT NULL;
34
35 /* Temperory Place holders in intermediate search */
36 Temp_column_value1 GMA_ACTDATA_WF.COLUMN_VALUE1%TYPE DEFAULT NULL;
37 Temp_column_value2 GMA_ACTDATA_WF.COLUMN_VALUE2%TYPE DEFAULT NULL;
38 Temp_column_value3 GMA_ACTDATA_WF.COLUMN_VALUE3%TYPE DEFAULT NULL;
39 Temp_column_value4 GMA_ACTDATA_WF.COLUMN_VALUE4%TYPE DEFAULT NULL;
40 Temp_column_value5 GMA_ACTDATA_WF.COLUMN_VALUE5%TYPE DEFAULT NULL;
41 Temp_column_value6 GMA_ACTDATA_WF.COLUMN_VALUE6%TYPE DEFAULT NULL;
42 Temp_column_value7 GMA_ACTDATA_WF.COLUMN_VALUE7%TYPE DEFAULT NULL;
43 Temp_column_value8 GMA_ACTDATA_WF.COLUMN_VALUE8%TYPE DEFAULT NULL;
44 Temp_column_value9 GMA_ACTDATA_WF.COLUMN_VALUE9%TYPE DEFAULT NULL;
45 Temp_column_value10 GMA_ACTDATA_WF.COLUMN_VALUE10%TYPE DEFAULT NULL;
46
47
48 /* To count number of Columns */
49 l_column_count NUMBER;
50
51 /* To count the number of parameters passed */
52 l_parameter_count NUMBER;
53
54 /* To Count the number of searches for where clause loop */
55 l_search_count NUMBER;
56 /* To get the default delimiter */
57 l_delimiter varchar2(10);
58
59 /* To get the temperory column name for the cursor */
60
61 l_temp_col_name GMA_ACTDATA_WF.COLUMN_NAME1%TYPE;
62 l_temp_col_value GMA_ACTDATA_WF.COLUMN_VALUE1%TYPE;
63
64
65 l_col_name_value VARCHAR2(100);
66
67 l_activity_id NUMBER;
68
69 l_parsing_sql VARCHAR2(4000);
70
71 l_where_clause VARCHAR2(4000);
72
73 l_loop_counter NUMBER;
74
75 /* To check the fetched rows */
76
77 l_fetched_rows BOOLEAN;
78
79 /* Variables for Dynamic SQL */
80
81 l_dbms_cur integer;
82 l_Rows_processed integer;
83
84 /* Temperory string Holder */
85 l_datastring VARCHAR2(4000);
86
87 /* Cursor to get the column hierarchy for a given activity */
88
89 CURSOR Cur_Actcol_wf(X_activity_id NUMBER) IS
90 SELECT COLUMN_NAME
91 FROM gma_actcol_wf_b
92 WHERE activity_id = X_activity_id
93 ORDER BY Column_hierarchy;
94
95 /* Cursot to fetch a role for the given attributes */
96
97 CURSOR Cur_find_role(X_activity_id NUMBER,
98 X_column_name1 VARCHAR2,
99 X_column_value1 VARCHAR2,
100 X_column_name2 VARCHAR2,
101 X_column_value2 VARCHAR2,
102 X_column_name3 VARCHAR2,
103 X_column_value3 VARCHAR2,
104 X_column_name4 VARCHAR2,
105 X_column_value4 VARCHAR2,
106 X_column_name5 VARCHAR2,
107 X_column_value5 VARCHAR2,
108 X_column_name6 VARCHAR2,
109 X_column_value6 VARCHAR2,
110 X_column_name7 VARCHAR2,
111 X_column_value7 VARCHAR2,
112 X_column_name8 VARCHAR2,
113 X_column_value8 VARCHAR2,
114 X_column_name9 VARCHAR2,
115 X_column_value9 VARCHAR2,
116 X_column_name10 VARCHAR2,
117 X_column_value10 VARCHAR2) IS
118 SELECT role
119 FROM gma_actdata_wf
120 WHERE activity_id = X_activity_id AND
121 nvl(column_name1,0) = nvl(X_column_name1,0) AND
122 nvl(column_value1,0) = nvl(X_column_value1,0) AND
123 nvl(column_name2,0) = nvl(X_column_name2,0) AND
124 nvl(column_value2,0) = nvl(X_column_value2,0) AND
125 nvl(column_name3,0) = nvl(X_column_name3,0) AND
126 nvl(column_value3,0) = nvl(X_column_value3,0) AND
127 nvl(column_name4,0) = nvl(X_column_name4,0) AND
128 nvl(column_value4,0) = nvl(X_column_value4,0) AND
129 nvl(column_name5,0) = nvl(X_column_name5,0) AND
130 nvl(column_value5,0) = nvl(X_column_value5,0) AND
131 nvl(column_name6,0) = nvl(X_column_name6,0) AND
132 nvl(column_value6,0) = nvl(X_column_value6,0) AND
133 nvl(column_name7,0) = nvl(X_column_name7,0) AND
134 nvl(column_value7,0) = nvl(X_column_value7,0) AND
135 nvl(column_name8,0) = nvl(X_column_name8,0) AND
136 nvl(column_value8,0) = nvl(X_column_value8,0) AND
137 nvl(column_name9,0) = nvl(X_column_name9,0) AND
138 nvl(column_value9,0) = nvl(X_column_value9,0) AND
139 nvl(column_name10,0) = nvl(X_column_name10,0) AND
140 nvl(column_value10,0) = nvl(X_column_value10,0);
141
142 BEGIN
143 p_role:='NOROLE';
144 IF (FND_PROFILE.DEFINED ('SY$WF_DELIMITER')) THEN
145 l_delimiter := FND_PROFILE.VALUE ('SY$WF_DELIMITER');
146 ELSE
147 p_role:= 'ERROR';
148 END IF;
149
150 IF l_delimiter is NULL THEN
151 p_role:= 'ERROR';
152 END IF;
153
154 SELECT activity_id into l_activity_id
155 FROM gma_actdef_wf
156 WHERE wf_item_type = p_wf_item_type AND
157 process_name = p_process_name AND
158 activity_name = p_activity_name;
159
160 /* Initializing the column count */
161 l_column_count := 0;
162 /* Populating the column name according to the hierarchy */
163 OPEN Cur_Actcol_wf(l_activity_id);
164 LOOP
165 Fetch Cur_Actcol_wf into l_temp_col_name;
166 IF Cur_Actcol_WF%FOUND THEN
167 l_column_count := l_column_count + 1;
168 IF l_column_count = 1 THEN
169 l_column_name1:=l_temp_col_name;
170 ELSIF l_column_count = 2 THEN
171 l_column_name2:=l_temp_col_name;
172 ELSIF l_column_count = 3 THEN
173 l_column_name3:=l_temp_col_name;
174 ELSIF l_column_count = 4 THEN
175 l_column_name4:=l_temp_col_name;
176 ELSIF l_column_count = 5 THEN
177 l_column_name5:=l_temp_col_name;
178 ELSIF l_column_count = 6 THEN
179 l_column_name6:=l_temp_col_name;
180 ELSIF l_column_count = 7 THEN
181 l_column_name7:=l_temp_col_name;
182 ELSIF l_column_count = 8 THEN
183 l_column_name8:=l_temp_col_name;
184 ELSIF l_column_count = 9 THEN
185 l_column_name9:=l_temp_col_name;
186 ELSIF l_column_count = 10 THEN
187 l_column_name10:=l_temp_col_name;
188 END IF;
189 ELSE
190 EXIT;
191 END IF;
192 END LOOP;
193 CLOSE Cur_Actcol_wf;
194
195 /* Initializing parameter count */
196 l_parameter_count := 0;
197
198 /* Setting Column Count to Where clause search count */
199 l_search_count:=l_column_count;
200
201 l_datastring:=p_datastring;
202 /* Start processing the String */
203
204 LOOP
205 IF l_datastring IS NOT NULL THEN
206 l_parameter_count:=l_parameter_count+1;
207 IF (instr(l_datastring,l_delimiter,1,1) <> 0) THEN
208 l_col_name_value := substr(l_datastring,1,instr(l_datastring,l_delimiter,1,1)-1);
209 l_datastring :=substr(l_datastring,instr(l_datastring,l_delimiter,1,1)+1);
210 ELSE
211 l_col_name_value:=l_datastring;
212 l_datastring:=NULL;
213 END IF;
214
215 /* Checking for the column name and assigning the Value to the column */
216 l_temp_col_name := substr(l_col_name_value,1,instr(l_col_name_value,'=',1,1)-1);
217 l_temp_col_value:= substr(l_col_name_value,instr(l_col_name_value,'=',1,1)+1);
218 /* Assign the column value accordingly */
219
220 IF l_temp_col_name = l_column_name1 THEN
221 l_column_value1:=l_temp_col_value;
222 temp_column_value1:=l_temp_col_value;
223 ELSIF l_temp_col_name = l_column_name2 THEN
224 l_column_value2:=l_temp_col_value;
225 temp_column_value2:=l_temp_col_value;
226 ELSIF l_temp_col_name = l_column_name3 THEN
227 l_column_value3:=l_temp_col_value;
228 temp_column_value3:=l_temp_col_value;
229 ELSIF l_temp_col_name = l_column_name4 THEN
230 l_column_value4:=l_temp_col_value;
231 temp_column_value4:=l_temp_col_value;
232 ELSIF l_temp_col_name = l_column_name5 THEN
233 l_column_value5:=l_temp_col_value;
234 temp_column_value5:=l_temp_col_value;
235 ELSIF l_temp_col_name = l_column_name6 THEN
236 l_column_value6:=l_temp_col_value;
237 temp_column_value6:=l_temp_col_value;
238 ELSIF l_temp_col_name = l_column_name7 THEN
239 l_column_value7:=l_temp_col_value;
240 temp_column_value7:=l_temp_col_value;
241 ELSIF l_temp_col_name = l_column_name8 THEN
242 l_column_value8:=l_temp_col_value;
243 temp_column_value8:=l_temp_col_value;
244 ELSIF l_temp_col_name = l_column_name9 THEN
245 l_column_value9:=l_temp_col_value;
246 temp_column_value9:=l_temp_col_value;
247 ELSIF l_temp_col_name = l_column_name10 THEN
248 l_column_value10:=l_temp_col_value;
249 temp_column_value10:=l_temp_col_value;
250 END IF;
251
252 ELSE
253 EXIT;
254 END IF;
255 END LOOP;
256 /* Check for the passed parameters */
257 IF l_column_count <> l_parameter_count THEN
258 p_role := 'ERROR';
259 END IF;
260
261
262
263 /* Fetch the details using cursor Using Bottom Up Approach */
264 LOOP
265 IF l_search_count > 0 THEN
266 OPEN Cur_find_role(l_activity_id ,
267 l_column_name1,l_column_value1,
268 l_column_name2,l_column_value2,
269 l_column_name3,l_column_value3,
270 l_column_name4,l_column_value4,
271 l_column_name5,l_column_value5,
272 l_column_name6,l_column_value6,
273 l_column_name7,l_column_value7,
274 l_column_name8,l_column_value8,
275 l_column_name9,l_column_value9,
276 l_column_name10,l_column_value10);
277 FETCH cur_find_role into p_role;
278 IF cur_find_role%FOUND THEN
279 CLOSE Cur_find_role;
280 EXIT;
281 ELSE
282 /* Make the last column value in hierarchy NULL */
283 IF l_search_count = 10 THEN
284 l_column_value10:=NULL;
285 ELSIF l_search_count = 9 THEN
286 l_column_value9:=NULL;
287 ELSIF l_search_count = 8 THEN
288 l_column_value8:=NULL;
289 ELSIF l_search_count = 7 THEN
290 l_column_value7:=NULL;
291 ELSIF l_search_count = 6 THEN
292 l_column_value6:=NULL;
293 ELSIF l_search_count = 5 THEN
294 l_column_value5:=NULL;
295 ELSIF l_search_count = 4 THEN
296 l_column_value4:=NULL;
297 ELSIF l_search_count = 3 THEN
298 l_column_value3:=NULL;
299 ELSIF l_search_count = 2 THEN
300 l_column_value2:=NULL;
301 ELSIF l_search_count = 1 THEN
302 l_column_value1:=NULL;
303 END IF;
304 l_search_count:=l_search_count-1;
305 CLOSE Cur_find_role;
306 END IF;
307 ELSE
308 EXIT;
309 END IF;
310 END LOOP;
311
312
313
314 IF p_role = 'NOROLE' THEN
315 /* Re assigning the column values */
316 l_column_value1 :=temp_column_value1;
317 l_column_value2 :=temp_column_value2;
318 l_column_value3 :=temp_column_value3;
319 l_column_value4 :=temp_column_value4;
320 l_column_value5 :=temp_column_value5;
321 l_column_value6 :=temp_column_value6;
322 l_column_value7 :=temp_column_value7;
323 l_column_value8 :=temp_column_value8;
324 l_column_value9 :=temp_column_value9;
325 l_column_value10:=temp_column_value10;
326
327 /* Initilizing the Search count to start from first column in hierarchy */
328 l_search_count:=1;
329
330 /* Fetch the details using cursor Using Top Down Approach */
331
332 LOOP
333 IF l_search_count <= 10 THEN
334
335 IF l_search_count = 10 THEN
336 l_column_value10:=NULL;
337 ELSIF l_search_count = 9 THEN
338 l_column_value9:=NULL;
342 l_column_value7:=NULL;
339 ELSIF l_search_count = 8 THEN
340 l_column_value8:=NULL;
341 ELSIF l_search_count = 7 THEN
343 ELSIF l_search_count = 6 THEN
344 l_column_value6:=NULL;
345 ELSIF l_search_count = 5 THEN
346 l_column_value5:=NULL;
347 ELSIF l_search_count = 4 THEN
348 l_column_value4:=NULL;
349 ELSIF l_search_count = 3 THEN
350 l_column_value3:=NULL;
351 ELSIF l_search_count = 2 THEN
352 l_column_value2:=NULL;
353 ELSIF l_search_count = 1 THEN
354 l_column_value1:=NULL;
355 END IF;
356
357 OPEN Cur_find_role(l_activity_id ,
358 l_column_name1,l_column_value1, l_column_name2,l_column_value2,
359 l_column_name3,l_column_value3, l_column_name4,l_column_value4,
360 l_column_name5,l_column_value5, l_column_name6,l_column_value6,
361 l_column_name7,l_column_value7, l_column_name8,l_column_value8,
362 l_column_name9,l_column_value9, l_column_name10,l_column_value10);
363 FETCH cur_find_role into p_role;
364 IF cur_find_role%FOUND THEN
365 CLOSE Cur_find_role;
366 EXIT;
367 ELSE
368 /* Make the NEXT column value in hierarchy NULL */
369 l_search_count:=l_search_count+1;
370 CLOSE Cur_find_role;
371 END IF;
372 ELSE
373 EXIT;
374 END IF;
375 END LOOP;
376 END IF;
377 EXCEPTION
378 WHEN no_data_found THEN
379 p_role:='ERROR';
380
381 END get_role;
382
383 FUNCTION check_process_approval_req(p_wf_item_type VARCHAR2,
384 p_Process_name VARCHAR2,
385 p_datastring VARCHAR2) RETURN VARCHAR2 IS
386 l_column_name1 GMA_ACTDATA_WF.COLUMN_NAME1%TYPE DEFAULT NULL;
387 l_column_value1 GMA_ACTDATA_WF.COLUMN_VALUE1%TYPE DEFAULT NULL;
388 l_column_name2 GMA_ACTDATA_WF.COLUMN_NAME2%TYPE DEFAULT NULL;
389 l_column_value2 GMA_ACTDATA_WF.COLUMN_VALUE2%TYPE DEFAULT NULL;
390 l_column_name3 GMA_ACTDATA_WF.COLUMN_NAME3%TYPE DEFAULT NULL;
391 l_column_value3 GMA_ACTDATA_WF.COLUMN_VALUE3%TYPE DEFAULT NULL;
392 l_column_name4 GMA_ACTDATA_WF.COLUMN_NAME4%TYPE DEFAULT NULL;
393 l_column_value4 GMA_ACTDATA_WF.COLUMN_VALUE4%TYPE DEFAULT NULL;
394 l_column_name5 GMA_ACTDATA_WF.COLUMN_NAME5%TYPE DEFAULT NULL;
395 l_column_value5 GMA_ACTDATA_WF.COLUMN_VALUE5%TYPE DEFAULT NULL;
396 l_column_name6 GMA_ACTDATA_WF.COLUMN_NAME6%TYPE DEFAULT NULL;
397 l_column_value6 GMA_ACTDATA_WF.COLUMN_VALUE6%TYPE DEFAULT NULL;
398 l_column_name7 GMA_ACTDATA_WF.COLUMN_NAME7%TYPE DEFAULT NULL;
399 l_column_value7 GMA_ACTDATA_WF.COLUMN_VALUE7%TYPE DEFAULT NULL;
400 l_column_name8 GMA_ACTDATA_WF.COLUMN_NAME8%TYPE DEFAULT NULL;
401 l_column_value8 GMA_ACTDATA_WF.COLUMN_VALUE8%TYPE DEFAULT NULL;
402 l_column_name9 GMA_ACTDATA_WF.COLUMN_NAME9%TYPE DEFAULT NULL;
403 l_column_value9 GMA_ACTDATA_WF.COLUMN_VALUE9%TYPE DEFAULT NULL;
404 l_column_name10 GMA_ACTDATA_WF.COLUMN_NAME10%TYPE DEFAULT NULL;
405 l_column_value10 GMA_ACTDATA_WF.COLUMN_VALUE10%TYPE DEFAULT NULL;
406
407 /* Temperory Place holders in intermediate search */
408 Temp_column_value1 GMA_ACTDATA_WF.COLUMN_VALUE1%TYPE DEFAULT NULL;
409 Temp_column_value2 GMA_ACTDATA_WF.COLUMN_VALUE2%TYPE DEFAULT NULL;
410 Temp_column_value3 GMA_ACTDATA_WF.COLUMN_VALUE3%TYPE DEFAULT NULL;
411 Temp_column_value4 GMA_ACTDATA_WF.COLUMN_VALUE4%TYPE DEFAULT NULL;
412 Temp_column_value5 GMA_ACTDATA_WF.COLUMN_VALUE5%TYPE DEFAULT NULL;
413 Temp_column_value6 GMA_ACTDATA_WF.COLUMN_VALUE6%TYPE DEFAULT NULL;
414 Temp_column_value7 GMA_ACTDATA_WF.COLUMN_VALUE7%TYPE DEFAULT NULL;
415 Temp_column_value8 GMA_ACTDATA_WF.COLUMN_VALUE8%TYPE DEFAULT NULL;
416 Temp_column_value9 GMA_ACTDATA_WF.COLUMN_VALUE9%TYPE DEFAULT NULL;
417 Temp_column_value10 GMA_ACTDATA_WF.COLUMN_VALUE10%TYPE DEFAULT NULL;
418
419
420 /* To count number of Columns */
421 l_column_count NUMBER;
422
423 /* To count the number of parameters passed */
424 l_parameter_count NUMBER;
425
426 /* To Count the number of searches for where clause loop */
427 l_search_count NUMBER;
428 /* To get the default delimiter */
429 l_delimiter varchar2(10);
430
431 /* To get the temperory column name for the cursor */
432
433 l_temp_col_name GMA_ACTDATA_WF.COLUMN_NAME1%TYPE;
434 l_temp_col_value GMA_ACTDATA_WF.COLUMN_VALUE1%TYPE;
435
436
437 l_col_name_value VARCHAR2(100);
438
439 l_activity_id NUMBER;
440
441 l_parsing_sql VARCHAR2(4000);
442
443 l_where_clause VARCHAR2(4000);
444
445 l_loop_counter NUMBER;
446
447 /* To check the fetched rows */
451 /* Variables for Dynamic SQL */
448
449 l_fetched_rows BOOLEAN;
450
452
453 l_dbms_cur integer;
454 l_Rows_processed integer;
455
456 /* Temperory string Holder */
457 l_datastring VARCHAR2(4000);
458
459 /* Cursor to get the column hierarchy for a given activity */
460
461 CURSOR Cur_Actcol_wf(X_wf_item_type VARCHAR2,
462 X_process_name VARCHAR2) IS
463 SELECT COLUMN_NAME
464 FROM gma_proccol_wf_b
465 WHERE wf_item_type = X_wf_item_type
466 AND process_name = X_process_name
467 ORDER BY Column_hierarchy;
468
469 /* Cursot to fetch a role for the given attributes */
470
471 CURSOR Cur_appr_req(X_wf_item_type VARCHAR2,
472 X_process_name VARCHAR2,
473 X_column_name1 VARCHAR2,
474 X_column_value1 VARCHAR2,
475 X_column_name2 VARCHAR2,
476 X_column_value2 VARCHAR2,
477 X_column_name3 VARCHAR2,
478 X_column_value3 VARCHAR2,
479 X_column_name4 VARCHAR2,
480 X_column_value4 VARCHAR2,
481 X_column_name5 VARCHAR2,
482 X_column_value5 VARCHAR2,
483 X_column_name6 VARCHAR2,
484 X_column_value6 VARCHAR2,
485 X_column_name7 VARCHAR2,
486 X_column_value7 VARCHAR2,
487 X_column_name8 VARCHAR2,
488 X_column_value8 VARCHAR2,
489 X_column_name9 VARCHAR2,
490 X_column_value9 VARCHAR2,
491 X_column_name10 VARCHAR2,
492 X_column_value10 VARCHAR2) IS
493 SELECT ENABLE_FLAG
494 FROM gma_procdata_wf
495 WHERE wf_item_type = X_wf_item_type AND
496 process_name = X_process_name AND
497 nvl(column_name1,0) = nvl(X_column_name1,0) AND
498 nvl(column_value1,0) = nvl(X_column_value1,0) AND
499 nvl(column_name2,0) = nvl(X_column_name2,0) AND
500 nvl(column_value2,0) = nvl(X_column_value2,0) AND
501 nvl(column_name3,0) = nvl(X_column_name3,0) AND
502 nvl(column_value3,0) = nvl(X_column_value3,0) AND
503 nvl(column_name4,0) = nvl(X_column_name4,0) AND
504 nvl(column_value4,0) = nvl(X_column_value4,0) AND
505 nvl(column_name5,0) = nvl(X_column_name5,0) AND
506 nvl(column_value5,0) = nvl(X_column_value5,0) AND
507 nvl(column_name6,0) = nvl(X_column_name6,0) AND
508 nvl(column_value6,0) = nvl(X_column_value6,0) AND
509 nvl(column_name7,0) = nvl(X_column_name7,0) AND
510 nvl(column_value7,0) = nvl(X_column_value7,0) AND
511 nvl(column_name8,0) = nvl(X_column_name8,0) AND
512 nvl(column_value8,0) = nvl(X_column_value8,0) AND
513 nvl(column_name9,0) = nvl(X_column_name9,0) AND
514 nvl(column_value9,0) = nvl(X_column_value9,0) AND
515 nvl(column_name10,0) = nvl(X_column_name10,0) AND
516 nvl(column_value10,0) = nvl(X_column_value10,0);
517 l_enable_flag gma_procdata_wf.enable_flag%type;
518 BEGIN
519 l_enable_flag :='Z';
520 IF (FND_PROFILE.DEFINED ('SY$WF_DELIMITER')) THEN
521 l_delimiter := FND_PROFILE.VALUE ('SY$WF_DELIMITER');
522 ELSE
523 l_enable_flag := 'E';
524 RETURN l_enable_flag ;
525 END IF;
526
527 IF l_delimiter is NULL THEN
528 l_enable_flag := 'E';
529 RETURN l_enable_flag ;
530 END IF;
531
532 /* Initializing the column count */
533 l_column_count := 0;
534 /* Populating the column name according to the hierarchy */
535 OPEN Cur_Actcol_wf(p_wf_item_type,p_process_name);
536 LOOP
537 Fetch Cur_Actcol_wf into l_temp_col_name;
538 IF Cur_Actcol_WF%FOUND THEN
539 l_column_count := l_column_count + 1;
540 IF l_column_count = 1 THEN
541 l_column_name1:=l_temp_col_name;
542 ELSIF l_column_count = 2 THEN
543 l_column_name2:=l_temp_col_name;
544 ELSIF l_column_count = 3 THEN
545 l_column_name3:=l_temp_col_name;
546 ELSIF l_column_count = 4 THEN
550 ELSIF l_column_count = 6 THEN
547 l_column_name4:=l_temp_col_name;
548 ELSIF l_column_count = 5 THEN
549 l_column_name5:=l_temp_col_name;
551 l_column_name6:=l_temp_col_name;
552 ELSIF l_column_count = 7 THEN
553 l_column_name7:=l_temp_col_name;
554 ELSIF l_column_count = 8 THEN
555 l_column_name8:=l_temp_col_name;
556 ELSIF l_column_count = 9 THEN
557 l_column_name9:=l_temp_col_name;
558 ELSIF l_column_count = 10 THEN
559 l_column_name10:=l_temp_col_name;
560 END IF;
561 ELSE
562 EXIT;
563 END IF;
564 END LOOP;
565 CLOSE Cur_Actcol_wf;
566
567 /* Initializing parameter count */
568 l_parameter_count := 0;
569
570 /* Setting Column Count to Where clause search count */
571 l_search_count:=l_column_count;
572
573 l_datastring:=p_datastring;
574 /* Start processing the String */
575
576 LOOP
577 IF l_datastring IS NOT NULL THEN
578 l_parameter_count:=l_parameter_count+1;
579 IF (instr(l_datastring,l_delimiter,1,1) <> 0) THEN
580 l_col_name_value := substr(l_datastring,1,instr(l_datastring,l_delimiter,1,1)-1);
581 l_datastring :=substr(l_datastring,instr(l_datastring,l_delimiter,1,1)+1);
582 ELSE
583 l_col_name_value:=l_datastring;
584 l_datastring:=NULL;
585 END IF;
586
587 /* Checking for the column name and assigning the Value to the column */
588 l_temp_col_name := substr(l_col_name_value,1,instr(l_col_name_value,'=',1,1)-1);
589 l_temp_col_value:= substr(l_col_name_value,instr(l_col_name_value,'=',1,1)+1);
590 /* Assign the column value accordingly */
591
592 IF l_temp_col_name = l_column_name1 THEN
593 l_column_value1:=l_temp_col_value;
594 temp_column_value1:=l_temp_col_value;
595 ELSIF l_temp_col_name = l_column_name2 THEN
596 l_column_value2:=l_temp_col_value;
597 temp_column_value2:=l_temp_col_value;
598 ELSIF l_temp_col_name = l_column_name3 THEN
599 l_column_value3:=l_temp_col_value;
600 temp_column_value3:=l_temp_col_value;
601 ELSIF l_temp_col_name = l_column_name4 THEN
602 l_column_value4:=l_temp_col_value;
603 temp_column_value4:=l_temp_col_value;
604 ELSIF l_temp_col_name = l_column_name5 THEN
605 l_column_value5:=l_temp_col_value;
606 temp_column_value5:=l_temp_col_value;
607 ELSIF l_temp_col_name = l_column_name6 THEN
608 l_column_value6:=l_temp_col_value;
609 temp_column_value6:=l_temp_col_value;
610 ELSIF l_temp_col_name = l_column_name7 THEN
611 l_column_value7:=l_temp_col_value;
612 temp_column_value7:=l_temp_col_value;
613 ELSIF l_temp_col_name = l_column_name8 THEN
614 l_column_value8:=l_temp_col_value;
615 temp_column_value8:=l_temp_col_value;
616 ELSIF l_temp_col_name = l_column_name9 THEN
617 l_column_value9:=l_temp_col_value;
618 temp_column_value9:=l_temp_col_value;
619 ELSIF l_temp_col_name = l_column_name10 THEN
620 l_column_value10:=l_temp_col_value;
621 temp_column_value10:=l_temp_col_value;
622 END IF;
623
624 ELSE
625 EXIT;
626 END IF;
627 END LOOP;
628 /* Check for the passed parameters */
629 IF l_column_count <> l_parameter_count THEN
630 l_enable_flag := 'E';
631 RETURN l_enable_flag ;
632 END IF;
633
634
635
636 /* Fetch the details using cursor Using Bottom Up Approach */
637 LOOP
641 l_column_name2,l_column_value2,
638 IF l_search_count > 0 THEN
639 OPEN Cur_appr_req( p_wf_item_type,p_process_name,
640 l_column_name1,l_column_value1,
642 l_column_name3,l_column_value3,
643 l_column_name4,l_column_value4,
644 l_column_name5,l_column_value5,
645 l_column_name6,l_column_value6,
646 l_column_name7,l_column_value7,
647 l_column_name8,l_column_value8,
648 l_column_name9,l_column_value9,
649 l_column_name10,l_column_value10);
650 FETCH cur_appr_req into l_enable_flag;
651 IF cur_appr_req%FOUND THEN
652 CLOSE Cur_appr_req;
653 EXIT;
654 ELSE
655 /* Make the last column value in hierarchy NULL */
656 IF l_search_count = 10 THEN
657 l_column_value10:=NULL;
658 ELSIF l_search_count = 9 THEN
659 l_column_value9:=NULL;
660 ELSIF l_search_count = 8 THEN
661 l_column_value8:=NULL;
662 ELSIF l_search_count = 7 THEN
663 l_column_value7:=NULL;
664 ELSIF l_search_count = 6 THEN
665 l_column_value6:=NULL;
666 ELSIF l_search_count = 5 THEN
667 l_column_value5:=NULL;
668 ELSIF l_search_count = 4 THEN
669 l_column_value4:=NULL;
670 ELSIF l_search_count = 3 THEN
671 l_column_value3:=NULL;
672 ELSIF l_search_count = 2 THEN
673 l_column_value2:=NULL;
674 ELSIF l_search_count = 1 THEN
675 l_column_value1:=NULL;
676 END IF;
677 l_search_count:=l_search_count-1;
678 CLOSE Cur_appr_req;
679 END IF;
680 ELSE
681 EXIT;
682 END IF;
683 END LOOP;
684
685
686
687 IF l_enable_flag not in ('Y','N') THEN
688 /* Re assigning the column values */
689 l_column_value1 :=temp_column_value1;
690 l_column_value2 :=temp_column_value2;
691 l_column_value3 :=temp_column_value3;
692 l_column_value4 :=temp_column_value4;
693 l_column_value5 :=temp_column_value5;
694 l_column_value6 :=temp_column_value6;
695 l_column_value7 :=temp_column_value7;
696 l_column_value8 :=temp_column_value8;
697 l_column_value9 :=temp_column_value9;
698 l_column_value10:=temp_column_value10;
699
700 /* Initilizing the Search count to start from first column in hierarchy */
701 l_search_count:=1;
702
703 /* Fetch the details using cursor Using Top Down Approach */
704
705 LOOP
706 IF l_search_count <= 10 THEN
707
708 IF l_search_count = 10 THEN
709 l_column_value10:=NULL;
710 ELSIF l_search_count = 9 THEN
711 l_column_value9:=NULL;
712 ELSIF l_search_count = 8 THEN
713 l_column_value8:=NULL;
714 ELSIF l_search_count = 7 THEN
715 l_column_value7:=NULL;
716 ELSIF l_search_count = 6 THEN
717 l_column_value6:=NULL;
718 ELSIF l_search_count = 5 THEN
719 l_column_value5:=NULL;
720 ELSIF l_search_count = 4 THEN
721 l_column_value4:=NULL;
722 ELSIF l_search_count = 3 THEN
723 l_column_value3:=NULL;
724 ELSIF l_search_count = 2 THEN
725 l_column_value2:=NULL;
726 ELSIF l_search_count = 1 THEN
727 l_column_value1:=NULL;
728 END IF;
729
730 OPEN Cur_appr_req(p_wf_item_type,p_process_name,
731 l_column_name1,l_column_value1, l_column_name2,l_column_value2,
732 l_column_name3,l_column_value3, l_column_name4,l_column_value4,
733 l_column_name5,l_column_value5, l_column_name6,l_column_value6,
734 l_column_name7,l_column_value7, l_column_name8,l_column_value8,
735 l_column_name9,l_column_value9, l_column_name10,l_column_value10);
736 FETCH cur_appr_req into l_enable_flag;
740 ELSE
737 IF cur_appr_req%FOUND THEN
738 CLOSE Cur_appr_req;
739 EXIT;
741 /* Make the NEXT column value in hierarchy NULL */
742 l_search_count:=l_search_count+1;
743 CLOSE Cur_appr_req;
744 END IF;
745 ELSE
746 EXIT;
747 END IF;
748 END LOOP;
749 END IF;
750 RETURN l_enable_flag ;
751 EXCEPTION
752 WHEN no_data_found THEN
753 RETURN l_enable_flag ;
754 end check_process_approval_req;
755
756 FUNCTION check_activity_approval_req(p_wf_item_type VARCHAR2,
757 p_Process_name VARCHAR2,
758 p_activity_name IN varchar2,
759 p_datastring VARCHAR2) RETURN VARCHAR2 IS
760 l_column_name1 GMA_ACTDATA_WF.COLUMN_NAME1%TYPE DEFAULT NULL;
761 l_column_value1 GMA_ACTDATA_WF.COLUMN_VALUE1%TYPE DEFAULT NULL;
762 l_column_name2 GMA_ACTDATA_WF.COLUMN_NAME2%TYPE DEFAULT NULL;
763 l_column_value2 GMA_ACTDATA_WF.COLUMN_VALUE2%TYPE DEFAULT NULL;
764 l_column_name3 GMA_ACTDATA_WF.COLUMN_NAME3%TYPE DEFAULT NULL;
765 l_column_value3 GMA_ACTDATA_WF.COLUMN_VALUE3%TYPE DEFAULT NULL;
766 l_column_name4 GMA_ACTDATA_WF.COLUMN_NAME4%TYPE DEFAULT NULL;
767 l_column_value4 GMA_ACTDATA_WF.COLUMN_VALUE4%TYPE DEFAULT NULL;
768 l_column_name5 GMA_ACTDATA_WF.COLUMN_NAME5%TYPE DEFAULT NULL;
769 l_column_value5 GMA_ACTDATA_WF.COLUMN_VALUE5%TYPE DEFAULT NULL;
770 l_column_name6 GMA_ACTDATA_WF.COLUMN_NAME6%TYPE DEFAULT NULL;
771 l_column_value6 GMA_ACTDATA_WF.COLUMN_VALUE6%TYPE DEFAULT NULL;
772 l_column_name7 GMA_ACTDATA_WF.COLUMN_NAME7%TYPE DEFAULT NULL;
773 l_column_value7 GMA_ACTDATA_WF.COLUMN_VALUE7%TYPE DEFAULT NULL;
774 l_column_name8 GMA_ACTDATA_WF.COLUMN_NAME8%TYPE DEFAULT NULL;
775 l_column_value8 GMA_ACTDATA_WF.COLUMN_VALUE8%TYPE DEFAULT NULL;
776 l_column_name9 GMA_ACTDATA_WF.COLUMN_NAME9%TYPE DEFAULT NULL;
777 l_column_value9 GMA_ACTDATA_WF.COLUMN_VALUE9%TYPE DEFAULT NULL;
778 l_column_name10 GMA_ACTDATA_WF.COLUMN_NAME10%TYPE DEFAULT NULL;
779 l_column_value10 GMA_ACTDATA_WF.COLUMN_VALUE10%TYPE DEFAULT NULL;
780
781 /* Temperory Place holders in intermediate search */
782 Temp_column_value1 GMA_ACTDATA_WF.COLUMN_VALUE1%TYPE DEFAULT NULL;
783 Temp_column_value2 GMA_ACTDATA_WF.COLUMN_VALUE2%TYPE DEFAULT NULL;
784 Temp_column_value3 GMA_ACTDATA_WF.COLUMN_VALUE3%TYPE DEFAULT NULL;
785 Temp_column_value4 GMA_ACTDATA_WF.COLUMN_VALUE4%TYPE DEFAULT NULL;
786 Temp_column_value5 GMA_ACTDATA_WF.COLUMN_VALUE5%TYPE DEFAULT NULL;
787 Temp_column_value6 GMA_ACTDATA_WF.COLUMN_VALUE6%TYPE DEFAULT NULL;
788 Temp_column_value7 GMA_ACTDATA_WF.COLUMN_VALUE7%TYPE DEFAULT NULL;
789 Temp_column_value8 GMA_ACTDATA_WF.COLUMN_VALUE8%TYPE DEFAULT NULL;
790 Temp_column_value9 GMA_ACTDATA_WF.COLUMN_VALUE9%TYPE DEFAULT NULL;
791 Temp_column_value10 GMA_ACTDATA_WF.COLUMN_VALUE10%TYPE DEFAULT NULL;
792
793
794 /* To count number of Columns */
795 l_column_count NUMBER;
796
797 /* To count the number of parameters passed */
798 l_parameter_count NUMBER;
799
800 /* To Count the number of searches for where clause loop */
801 l_search_count NUMBER;
802 /* To get the default delimiter */
803 l_delimiter varchar2(10);
804
805 /* To get the temperory column name for the cursor */
806
807 l_temp_col_name GMA_ACTDATA_WF.COLUMN_NAME1%TYPE;
808 l_temp_col_value GMA_ACTDATA_WF.COLUMN_VALUE1%TYPE;
809
810
811 l_col_name_value VARCHAR2(100);
812
813 l_activity_id NUMBER;
814
815 l_parsing_sql VARCHAR2(4000);
816
817 l_where_clause VARCHAR2(4000);
818
819 l_loop_counter NUMBER;
820
821 /* To check the fetched rows */
822
823 l_fetched_rows BOOLEAN;
824
825 /* Variables for Dynamic SQL */
826
827 l_dbms_cur integer;
828 l_Rows_processed integer;
829
830 /* Temperory string Holder */
831 l_datastring VARCHAR2(4000);
832
833 /* Cursor to get the column hierarchy for a given activity */
834
835 CURSOR Cur_Actcol_wf(X_activity_id NUMBER) IS
836 SELECT COLUMN_NAME
837 FROM gma_actcol_wf_b
838 WHERE activity_id = X_activity_id
839 ORDER BY Column_hierarchy;
840
841 /* Cursot to fetch a role for the given attributes */
842
843 CURSOR Cur_find_role(X_activity_id NUMBER,
844 X_column_name1 VARCHAR2,
845 X_column_value1 VARCHAR2,
846 X_column_name2 VARCHAR2,
847 X_column_value2 VARCHAR2,
848 X_column_name3 VARCHAR2,
849 X_column_value3 VARCHAR2,
850 X_column_name4 VARCHAR2,
851 X_column_value4 VARCHAR2,
852 X_column_name5 VARCHAR2,
853 X_column_value5 VARCHAR2,
854 X_column_name6 VARCHAR2,
855 X_column_value6 VARCHAR2,
856 X_column_name7 VARCHAR2,
857 X_column_value7 VARCHAR2,
858 X_column_name8 VARCHAR2,
862 X_column_name10 VARCHAR2,
859 X_column_value8 VARCHAR2,
860 X_column_name9 VARCHAR2,
861 X_column_value9 VARCHAR2,
863 X_column_value10 VARCHAR2) IS
864 SELECT ENABLE_FLAG
865 FROM gma_actdata_wf
866 WHERE activity_id = X_activity_id AND
867 nvl(column_name1,0) = nvl(X_column_name1,0) AND
868 nvl(column_value1,0) = nvl(X_column_value1,0) AND
869 nvl(column_name2,0) = nvl(X_column_name2,0) AND
870 nvl(column_value2,0) = nvl(X_column_value2,0) AND
871 nvl(column_name3,0) = nvl(X_column_name3,0) AND
872 nvl(column_value3,0) = nvl(X_column_value3,0) AND
873 nvl(column_name4,0) = nvl(X_column_name4,0) AND
874 nvl(column_value4,0) = nvl(X_column_value4,0) AND
875 nvl(column_name5,0) = nvl(X_column_name5,0) AND
876 nvl(column_value5,0) = nvl(X_column_value5,0) AND
877 nvl(column_name6,0) = nvl(X_column_name6,0) AND
878 nvl(column_value6,0) = nvl(X_column_value6,0) AND
879 nvl(column_name7,0) = nvl(X_column_name7,0) AND
880 nvl(column_value7,0) = nvl(X_column_value7,0) AND
881 nvl(column_name8,0) = nvl(X_column_name8,0) AND
882 nvl(column_value8,0) = nvl(X_column_value8,0) AND
883 nvl(column_name9,0) = nvl(X_column_name9,0) AND
884 nvl(column_value9,0) = nvl(X_column_value9,0) AND
885 nvl(column_name10,0) = nvl(X_column_name10,0) AND
886 nvl(column_value10,0) = nvl(X_column_value10,0);
887 l_enable_flag gma_procdata_wf.enable_flag%type;
888 BEGIN
889 l_enable_flag :='Y';
890 IF (FND_PROFILE.DEFINED ('SY$WF_DELIMITER')) THEN
891 l_delimiter := FND_PROFILE.VALUE ('SY$WF_DELIMITER');
892 ELSE
893 l_enable_flag := 'E';
894 END IF;
895
896 IF l_delimiter is NULL THEN
897 l_enable_flag := 'E';
898 END IF;
899
900 SELECT activity_id into l_activity_id
901 FROM gma_actdef_wf
902 WHERE wf_item_type = p_wf_item_type AND
903 process_name = p_process_name AND
904 activity_name = p_activity_name;
905
906 /* Initializing the column count */
907 l_column_count := 0;
908 /* Populating the column name according to the hierarchy */
909 OPEN Cur_Actcol_wf(l_activity_id);
910 LOOP
911 Fetch Cur_Actcol_wf into l_temp_col_name;
912 IF Cur_Actcol_WF%FOUND THEN
913 l_column_count := l_column_count + 1;
914 IF l_column_count = 1 THEN
915 l_column_name1:=l_temp_col_name;
916 ELSIF l_column_count = 2 THEN
917 l_column_name2:=l_temp_col_name;
918 ELSIF l_column_count = 3 THEN
919 l_column_name3:=l_temp_col_name;
920 ELSIF l_column_count = 4 THEN
921 l_column_name4:=l_temp_col_name;
922 ELSIF l_column_count = 5 THEN
923 l_column_name5:=l_temp_col_name;
924 ELSIF l_column_count = 6 THEN
925 l_column_name6:=l_temp_col_name;
926 ELSIF l_column_count = 7 THEN
927 l_column_name7:=l_temp_col_name;
928 ELSIF l_column_count = 8 THEN
929 l_column_name8:=l_temp_col_name;
930 ELSIF l_column_count = 9 THEN
931 l_column_name9:=l_temp_col_name;
932 ELSIF l_column_count = 10 THEN
933 l_column_name10:=l_temp_col_name;
934 END IF;
935 ELSE
936 EXIT;
937 END IF;
938 END LOOP;
939 CLOSE Cur_Actcol_wf;
940
941 /* Initializing parameter count */
942 l_parameter_count := 0;
943
944 /* Setting Column Count to Where clause search count */
945 l_search_count:=l_column_count;
946
947 l_datastring:=p_datastring;
948 /* Start processing the String */
949
950 LOOP
951 IF l_datastring IS NOT NULL THEN
952 l_parameter_count:=l_parameter_count+1;
953 IF (instr(l_datastring,l_delimiter,1,1) <> 0) THEN
954 l_col_name_value := substr(l_datastring,1,instr(l_datastring,l_delimiter,1,1)-1);
955 l_datastring :=substr(l_datastring,instr(l_datastring,l_delimiter,1,1)+1);
956 ELSE
957 l_col_name_value:=l_datastring;
958 l_datastring:=NULL;
959 END IF;
960
961 /* Checking for the column name and assigning the Value to the column */
962 l_temp_col_name := substr(l_col_name_value,1,instr(l_col_name_value,'=',1,1)-1);
963 l_temp_col_value:= substr(l_col_name_value,instr(l_col_name_value,'=',1,1)+1);
964 /* Assign the column value accordingly */
965
969 ELSIF l_temp_col_name = l_column_name2 THEN
966 IF l_temp_col_name = l_column_name1 THEN
967 l_column_value1:=l_temp_col_value;
968 temp_column_value1:=l_temp_col_value;
970 l_column_value2:=l_temp_col_value;
971 temp_column_value2:=l_temp_col_value;
972 ELSIF l_temp_col_name = l_column_name3 THEN
973 l_column_value3:=l_temp_col_value;
974 temp_column_value3:=l_temp_col_value;
975 ELSIF l_temp_col_name = l_column_name4 THEN
976 l_column_value4:=l_temp_col_value;
977 temp_column_value4:=l_temp_col_value;
978 ELSIF l_temp_col_name = l_column_name5 THEN
979 l_column_value5:=l_temp_col_value;
980 temp_column_value5:=l_temp_col_value;
981 ELSIF l_temp_col_name = l_column_name6 THEN
982 l_column_value6:=l_temp_col_value;
983 temp_column_value6:=l_temp_col_value;
984 ELSIF l_temp_col_name = l_column_name7 THEN
985 l_column_value7:=l_temp_col_value;
986 temp_column_value7:=l_temp_col_value;
987 ELSIF l_temp_col_name = l_column_name8 THEN
988 l_column_value8:=l_temp_col_value;
989 temp_column_value8:=l_temp_col_value;
990 ELSIF l_temp_col_name = l_column_name9 THEN
991 l_column_value9:=l_temp_col_value;
992 temp_column_value9:=l_temp_col_value;
993 ELSIF l_temp_col_name = l_column_name10 THEN
994 l_column_value10:=l_temp_col_value;
995 temp_column_value10:=l_temp_col_value;
996 END IF;
997
998 ELSE
999 EXIT;
1000 END IF;
1001 END LOOP;
1002 /* Check for the passed parameters */
1003 IF l_column_count <> l_parameter_count THEN
1004 l_enable_flag := 'E';
1005 END IF;
1006
1007
1008
1009 /* Fetch the details using cursor Using Bottom Up Approach */
1010 LOOP
1011 IF l_search_count > 0 THEN
1012 OPEN Cur_find_role(l_activity_id ,
1013 l_column_name1,l_column_value1,
1014 l_column_name2,l_column_value2,
1015 l_column_name3,l_column_value3,
1016 l_column_name4,l_column_value4,
1017 l_column_name5,l_column_value5,
1018 l_column_name6,l_column_value6,
1019 l_column_name7,l_column_value7,
1020 l_column_name8,l_column_value8,
1021 l_column_name9,l_column_value9,
1022 l_column_name10,l_column_value10);
1023 FETCH cur_find_role into l_enable_flag ;
1024 IF cur_find_role%FOUND THEN
1025 CLOSE Cur_find_role;
1026 EXIT;
1027 ELSE
1028 /* Make the last column value in hierarchy NULL */
1029 IF l_search_count = 10 THEN
1030 l_column_value10:=NULL;
1031 ELSIF l_search_count = 9 THEN
1032 l_column_value9:=NULL;
1033 ELSIF l_search_count = 8 THEN
1034 l_column_value8:=NULL;
1035 ELSIF l_search_count = 7 THEN
1036 l_column_value7:=NULL;
1037 ELSIF l_search_count = 6 THEN
1038 l_column_value6:=NULL;
1039 ELSIF l_search_count = 5 THEN
1040 l_column_value5:=NULL;
1041 ELSIF l_search_count = 4 THEN
1042 l_column_value4:=NULL;
1043 ELSIF l_search_count = 3 THEN
1044 l_column_value3:=NULL;
1045 ELSIF l_search_count = 2 THEN
1046 l_column_value2:=NULL;
1047 ELSIF l_search_count = 1 THEN
1048 l_column_value1:=NULL;
1049 END IF;
1050 l_search_count:=l_search_count-1;
1051 CLOSE Cur_find_role;
1052 END IF;
1053 ELSE
1054 EXIT;
1055 END IF;
1056 END LOOP;
1057
1058
1059
1060 IF l_enable_flag = 'E' THEN
1061 /* Re assigning the column values */
1062 l_column_value1 :=temp_column_value1;
1063 l_column_value2 :=temp_column_value2;
1064 l_column_value3 :=temp_column_value3;
1065 l_column_value4 :=temp_column_value4;
1066 l_column_value5 :=temp_column_value5;
1067 l_column_value6 :=temp_column_value6;
1068 l_column_value7 :=temp_column_value7;
1072
1069 l_column_value8 :=temp_column_value8;
1070 l_column_value9 :=temp_column_value9;
1071 l_column_value10:=temp_column_value10;
1073 /* Initilizing the Search count to start from first column in hierarchy */
1074 l_search_count:=1;
1075
1076 /* Fetch the details using cursor Using Top Down Approach */
1077
1078 LOOP
1079 IF l_search_count <= 10 THEN
1080
1081 IF l_search_count = 10 THEN
1082 l_column_value10:=NULL;
1083 ELSIF l_search_count = 9 THEN
1084 l_column_value9:=NULL;
1085 ELSIF l_search_count = 8 THEN
1086 l_column_value8:=NULL;
1087 ELSIF l_search_count = 7 THEN
1088 l_column_value7:=NULL;
1089 ELSIF l_search_count = 6 THEN
1090 l_column_value6:=NULL;
1091 ELSIF l_search_count = 5 THEN
1092 l_column_value5:=NULL;
1093 ELSIF l_search_count = 4 THEN
1094 l_column_value4:=NULL;
1095 ELSIF l_search_count = 3 THEN
1096 l_column_value3:=NULL;
1097 ELSIF l_search_count = 2 THEN
1098 l_column_value2:=NULL;
1099 ELSIF l_search_count = 1 THEN
1100 l_column_value1:=NULL;
1101 END IF;
1102
1103 OPEN Cur_find_role(l_activity_id ,
1104 l_column_name1,l_column_value1, l_column_name2,l_column_value2,
1105 l_column_name3,l_column_value3, l_column_name4,l_column_value4,
1106 l_column_name5,l_column_value5, l_column_name6,l_column_value6,
1107 l_column_name7,l_column_value7, l_column_name8,l_column_value8,
1108 l_column_name9,l_column_value9, l_column_name10,l_column_value10);
1109 FETCH cur_find_role into l_enable_flag ;
1110 IF cur_find_role%FOUND THEN
1111 CLOSE Cur_find_role;
1112 EXIT;
1113 ELSE
1114 /* Make the NEXT column value in hierarchy NULL */
1115 l_search_count:=l_search_count+1;
1116 CLOSE Cur_find_role;
1117 END IF;
1118 ELSE
1119 EXIT;
1120 END IF;
1121 END LOOP;
1122 END IF;
1123 RETURN l_enable_flag;
1124 EXCEPTION
1125 WHEN no_data_found THEN
1126 l_enable_flag :='E';
1127 RETURN l_enable_flag;
1128 end check_activity_approval_req;
1129 /******************************************************************************************/
1130 FUNCTION check_process_enabled(p_wf_item_type VARCHAR2,
1131 p_Process_name VARCHAR2) RETURN BOOLEAN IS
1132 l_enable_flag GMA_PROCDEF_WF.enable_flag%TYPE;
1133 BEGIN
1134 SELECT enable_flag INTO l_enable_flag
1135 FROM GMA_PROCDEF_WF
1136 WHERE wf_item_type = p_wf_item_type AND
1137 process_name = p_process_name;
1138 IF l_enable_flag = 'Y' THEN
1139 RETURN TRUE;
1140 ELSE
1141 RETURN FALSE;
1142 END IF;
1143 END check_process_enabled;
1144 /***************************************************************************************/
1145 /*********************************************************************************/
1146
1147 PROCEDURE WF_GET_CONTORL_PARAMS(P_WF_ITEM_TYPE IN VARCHAR2,
1148 P_PROCESS_NAME IN VARCHAR2,
1149 P_ACTIVITY_NAME IN VARCHAR2,
1150 P_TABLE_NAME IN VARCHAR2,
1151 P_WHERE_CLAUSE IN VARCHAR2,
1152 P_DATASTRING OUT NOCOPY VARCHAR2,
1153 P_WFSTRING OUT NOCOPY VARCHAR2) IS
1154 l_sql_stmt VARCHAR2(4000);
1155 l_where_position Integer;
1156 l_key_Table Integer;
1157 l_column_name1 GMA_ACTDATA_WF.COLUMN_NAME1%TYPE DEFAULT NULL;
1158 l_column_value1 GMA_ACTDATA_WF.COLUMN_VALUE1%TYPE DEFAULT NULL;
1159 l_column_name2 GMA_ACTDATA_WF.COLUMN_NAME2%TYPE DEFAULT NULL;
1160 l_column_value2 GMA_ACTDATA_WF.COLUMN_VALUE2%TYPE DEFAULT NULL;
1161 l_column_name3 GMA_ACTDATA_WF.COLUMN_NAME3%TYPE DEFAULT NULL;
1162 l_column_value3 GMA_ACTDATA_WF.COLUMN_VALUE3%TYPE DEFAULT NULL;
1163 l_column_name4 GMA_ACTDATA_WF.COLUMN_NAME4%TYPE DEFAULT NULL;
1164 l_column_value4 GMA_ACTDATA_WF.COLUMN_VALUE4%TYPE DEFAULT NULL;
1165 l_column_name5 GMA_ACTDATA_WF.COLUMN_NAME5%TYPE DEFAULT NULL;
1166 l_column_value5 GMA_ACTDATA_WF.COLUMN_VALUE5%TYPE DEFAULT NULL;
1167 l_column_name6 GMA_ACTDATA_WF.COLUMN_NAME6%TYPE DEFAULT NULL;
1168 l_column_value6 GMA_ACTDATA_WF.COLUMN_VALUE6%TYPE DEFAULT NULL;
1169 l_column_name7 GMA_ACTDATA_WF.COLUMN_NAME7%TYPE DEFAULT NULL;
1170 l_column_value7 GMA_ACTDATA_WF.COLUMN_VALUE7%TYPE DEFAULT NULL;
1171 l_column_name8 GMA_ACTDATA_WF.COLUMN_NAME8%TYPE DEFAULT NULL;
1175 l_column_name10 GMA_ACTDATA_WF.COLUMN_NAME10%TYPE DEFAULT NULL;
1172 l_column_value8 GMA_ACTDATA_WF.COLUMN_VALUE8%TYPE DEFAULT NULL;
1173 l_column_name9 GMA_ACTDATA_WF.COLUMN_NAME9%TYPE DEFAULT NULL;
1174 l_column_value9 GMA_ACTDATA_WF.COLUMN_VALUE9%TYPE DEFAULT NULL;
1176 l_column_value10 GMA_ACTDATA_WF.COLUMN_VALUE10%TYPE DEFAULT NULL;
1177 l_column_prompt1 GMA_ACTCOL_WF_TL.COLUMN_PROMPT%TYPE DEFAULT NULL;
1178 l_column_prompt2 GMA_ACTCOL_WF_TL.COLUMN_PROMPT%TYPE DEFAULT NULL;
1179 l_column_prompt3 GMA_ACTCOL_WF_TL.COLUMN_PROMPT%TYPE DEFAULT NULL;
1180 l_column_prompt4 GMA_ACTCOL_WF_TL.COLUMN_PROMPT%TYPE DEFAULT NULL;
1181 l_column_prompt5 GMA_ACTCOL_WF_TL.COLUMN_PROMPT%TYPE DEFAULT NULL;
1182 l_column_prompt6 GMA_ACTCOL_WF_TL.COLUMN_PROMPT%TYPE DEFAULT NULL;
1183 l_column_prompt7 GMA_ACTCOL_WF_TL.COLUMN_PROMPT%TYPE DEFAULT NULL;
1184 l_column_prompt8 GMA_ACTCOL_WF_TL.COLUMN_PROMPT%TYPE DEFAULT NULL;
1185 l_column_prompt9 GMA_ACTCOL_WF_TL.COLUMN_PROMPT%TYPE DEFAULT NULL;
1186 l_column_prompt10 GMA_ACTCOL_WF_TL.COLUMN_PROMPT%TYPE DEFAULT NULL;
1187
1188 l_activity_id GMA_ACTDATA_WF.ACTIVITY_ID%TYPE;
1189 /* Cursor to get the column list for a given activity */
1190 V_cursor_id INTEGER;
1191 v_dummy INTEGER;
1192 Data_string Varchar2(2500);
1193 wf_Data_string Varchar2(2500);
1194 l_temp_col_name GMA_ACTDATA_WF.COLUMN_NAME1%TYPE;
1195 l_temp_prompt GMA_ACTCOL_WF_TL.COLUMN_PROMPT%TYPE;
1196 CURSOR Cur_Actcol_wf IS
1197 SELECT COLUMN_NAME,COLUMN_PROMPT
1198 FROM gma_actcol_wf_vl
1199 WHERE activity_id = l_activity_id
1200 ORDER BY Column_hierarchy;
1201 CURSOR Cur_Proccol_wf IS
1202 SELECT COLUMN_NAME,COLUMN_PROMPT
1203 FROM gma_proccol_wf_vl
1204 WHERE WF_ITEM_TYPE = P_WF_ITEM_TYPE AND
1205 PROCESS_NAME = p_PROCESS_NAME
1206 ORDER BY Column_hierarchy;
1207 l_column_count Integer :=0;
1208 l_no_rows Integer :=0;
1209 l_col_vals Integer :=0;
1210 error_message VARCHAR2(2000);
1211 /* To get the default delimiter */
1212 l_delimiter varchar2(10);
1213 WF_ERROR Exception;
1214 BEGIN
1215 IF p_where_clause is null
1216 Then
1217 data_string:= 'WHERE_ERROR';
1218 Raise WF_ERROR;
1219 END IF;
1220 IF P_table_name IS NULL
1221 THEN
1222 data_string:= 'TABLE_ERROR';
1223 Raise WF_ERROR;
1224 END IF;
1225 IF (FND_PROFILE.DEFINED ('SY$WF_DELIMITER')) THEN
1226 l_delimiter := FND_PROFILE.VALUE ('SY$WF_DELIMITER');
1227 ELSE
1228 data_string:= 'PROFILE_ERROR';
1229 Raise WF_ERROR;
1230 END IF;
1231
1232 IF l_delimiter is NULL THEN
1233 data_string:= 'PROFILE_ERROR';
1234 Raise WF_ERROR;
1235 END IF;
1236 IF P_ACTIVITY_NAME IS NULL THEN
1237 SELECT column_data_sql into l_sql_stmt
1238 FROM GMA_PROCDEF_WF
1239 WHERE WF_ITEM_TYPE = P_WF_ITEM_TYPE AND
1240 PROCESS_NAME = p_PROCESS_NAME;
1241 ELSE
1242 SELECT column_data_sql,activity_id into l_sql_stmt,l_activity_id
1243 FROM GMA_ACTDEF_WF
1244 WHERE WF_ITEM_TYPE = P_WF_ITEM_TYPE AND
1245 PROCESS_NAME = p_PROCESS_NAME AND
1246 ACTIVITY_NAME= P_ACTIVITY_NAME;
1247 END IF;
1248 l_key_table := instr(UPPER(l_sql_stmt),UPPER(p_table_name));
1249 IF nvl(l_key_table,0) = 0
1250 /* Key table is missing in SQL Statement or
1251 no SQL statement We can't proced with the workflow */
1252 THEN
1253 data_string:= 'SQL_ERROR';
1254 Raise WF_ERROR;
1255 ELSE
1256 l_where_position := instr(UPPER(l_sql_stmt),'WHERE');
1257 IF l_where_position = 0 /* No where Clause */
1258 THEN
1259 l_sql_stmt := l_sql_stmt || ' WHERE '||P_where_clause;
1260 ELSE
1261 l_sql_stmt := l_sql_stmt || ' AND '||p_where_clause;
1262 END IF;
1263 END IF;
1264 BEGIN
1265 -- Open the cursor for processing
1266 V_cursor_id := DBMS_SQL.OPEN_CURSOR;
1267
1268 -- Parse the Query
1269
1270 DBMS_SQL.PARSE(v_cursor_id,l_sql_stmt,DBMS_SQL.V7);
1271
1272 IF P_ACTIVITY_NAME IS NOT NULL THEN
1273 OPEN Cur_Actcol_wf;
1274 LOOP
1275 Fetch Cur_Actcol_wf into l_temp_col_name,l_temp_prompt;
1276 EXIT WHEN Cur_Actcol_WF%NOTFOUND;
1277 l_column_count := l_column_count + 1;
1278 IF l_column_count = 1 THEN
1279 l_column_name1 := l_temp_col_name;
1280 l_column_prompt1 := l_temp_prompt;
1281 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value1,240);
1282 ELSIF l_column_count = 2 THEN
1283 l_column_name2:=l_temp_col_name;
1284 l_column_prompt2 := l_temp_prompt;
1285 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value2,240);
1286 ELSIF l_column_count = 3 THEN
1287 l_column_name3:=l_temp_col_name;
1288 l_column_prompt3 := l_temp_prompt;
1289 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value3,240);
1290 ELSIF l_column_count = 4 THEN
1291 l_column_name4:=l_temp_col_name;
1292 l_column_prompt4 := l_temp_prompt;
1293 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value4,240);
1297 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value5,240);
1294 ELSIF l_column_count = 5 THEN
1295 l_column_name5:=l_temp_col_name;
1296 l_column_prompt5 := l_temp_prompt;
1298 ELSIF l_column_count = 6 THEN
1299 l_column_name6:=l_temp_col_name;
1300 l_column_prompt6 := l_temp_prompt;
1301 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value6,240);
1302 ELSIF l_column_count = 7 THEN
1303 l_column_name7:=l_temp_col_name;
1304 l_column_prompt7 := l_temp_prompt;
1305 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value7,240);
1306 ELSIF l_column_count = 8 THEN
1307 l_column_name8:=l_temp_col_name;
1308 l_column_prompt8 := l_temp_prompt;
1309 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value8,240);
1310 ELSIF l_column_count = 9 THEN
1311 l_column_name9:=l_temp_col_name;
1312 l_column_prompt9 := l_temp_prompt;
1313 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value9,240);
1314 ELSIF l_column_count = 10 THEN
1315 l_column_name10:=l_temp_col_name;
1316 l_column_prompt10 := l_temp_prompt;
1317 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value10,240);
1318 END IF;
1319 END LOOP;
1320 CLOSE Cur_Actcol_wf;
1321 ELSE
1322 OPEN Cur_proccol_wf;
1323 LOOP
1324 Fetch Cur_proccol_wf into l_temp_col_name,l_temp_prompt;
1325 EXIT WHEN Cur_proccol_WF%NOTFOUND;
1326 l_column_count := l_column_count + 1;
1327 IF l_column_count = 1 THEN
1328 l_column_name1:=l_temp_col_name;
1329 l_column_prompt1 := l_temp_prompt;
1330 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value1,240);
1331 ELSIF l_column_count = 2 THEN
1332 l_column_name2:=l_temp_col_name;
1333 l_column_prompt2 := l_temp_prompt;
1334 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value2,240);
1335 ELSIF l_column_count = 3 THEN
1336 l_column_name3:=l_temp_col_name;
1337 l_column_prompt3 := l_temp_prompt;
1338 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value3,240);
1339 ELSIF l_column_count = 4 THEN
1340 l_column_name4:=l_temp_col_name;
1341 l_column_prompt4 := l_temp_prompt;
1342 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value4,240);
1343 ELSIF l_column_count = 5 THEN
1344 l_column_name5:=l_temp_col_name;
1345 l_column_prompt5 := l_temp_prompt;
1346 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value5,240);
1347 ELSIF l_column_count = 6 THEN
1348 l_column_name6:=l_temp_col_name;
1349 l_column_prompt6 := l_temp_prompt;
1350 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value6,240);
1351 ELSIF l_column_count = 7 THEN
1352 l_column_name7:=l_temp_col_name;
1353 l_column_prompt8 := l_temp_prompt;
1354 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value7,240);
1355 ELSIF l_column_count = 8 THEN
1356 l_column_name8:=l_temp_col_name;
1357 l_column_prompt8 := l_temp_prompt;
1358 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value8,240);
1359 ELSIF l_column_count = 9 THEN
1360 l_column_name9:=l_temp_col_name;
1361 l_column_prompt9 := l_temp_prompt;
1362 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value9,240);
1363 ELSIF l_column_count = 10 THEN
1364 l_column_name10:=l_temp_col_name;
1365 l_column_prompt10 := l_temp_prompt;
1366 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,l_column_count,l_column_value10,240);
1367 END IF;
1368 END LOOP;
1369 CLOSE Cur_proccol_wf;
1370 END IF;
1371 -- Execute the statement
1372 v_dummy := DBMS_SQL.EXECUTE(v_cursor_id);
1373 LOOP
1374 IF DBMS_SQL.FETCH_ROWS(v_cursor_id) = 0 THEN
1375 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1376 IF l_no_rows > 1 THEN
1377 -- Close the cursor
1378 data_string := 'MULTI_ROWS_ERROR';
1379 RAISE WF_ERROR;
1380 END IF;
1381 EXIT;
1382 END IF;
1383 FOR l_col_vals in 1..l_column_count
1384 LOOP
1385
1386
1387 --
1388 -- Modified following code to fix bug 2478400
1389 -- Modification is chnage if condions to use l_col_vals instead of l_column_count.
1390 --
1391
1392 IF l_col_vals = 1 THEN
1393 DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor_id,l_col_vals,l_column_value1);
1394 data_string := data_string ||l_column_name1||'='||trim(l_column_value1);
1395 wf_data_string := wf_data_string ||l_column_prompt1||'='||trim(l_column_value1);
1396 ELSIF l_col_vals = 2 THEN
1397 DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor_id,l_col_vals,l_column_value2);
1398 data_string := data_string ||l_delimiter||l_column_name2||'='||trim(l_column_value2);
1399 wf_data_string := wf_data_string ||l_column_prompt2||'='||trim(l_column_value2);
1400 ELSIF l_col_vals = 3 THEN
1401 DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor_id,l_col_vals,l_column_value3);
1402 data_string := data_string ||l_delimiter||l_column_name3||'='||trim(l_column_value3);
1403 wf_data_string := wf_data_string ||l_column_prompt3||'='||trim(l_column_value3);
1404 ELSIF l_col_vals = 4 THEN
1405 DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor_id,l_col_vals,l_column_value4);
1409 DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor_id,l_col_vals,l_column_value5);
1406 data_string := data_string ||l_delimiter||l_column_name4||'='||trim(l_column_value4);
1407 wf_data_string := wf_data_string ||l_column_prompt4||'='||trim(l_column_value4);
1408 ELSIF l_col_vals = 5 THEN
1410 data_string := data_string ||l_delimiter||l_column_name5||'='||trim(l_column_value5);
1411 wf_data_string := wf_data_string ||l_column_prompt5||'='||trim(l_column_value5);
1412 ELSIF l_col_vals = 6 THEN
1413 DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor_id,l_col_vals,l_column_value6);
1414 data_string := data_string ||l_delimiter||l_column_name6||'='||trim(l_column_value6);
1415 wf_data_string := wf_data_string ||l_column_prompt6||'='||trim(l_column_value6);
1416 ELSIF l_col_vals = 7 THEN
1417 DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor_id,l_col_vals,l_column_value7);
1418 data_string := data_string ||l_delimiter||l_column_name7||'='||trim(l_column_value7);
1419 wf_data_string := wf_data_string ||l_column_prompt7||'='||trim(l_column_value7);
1420 ELSIF l_col_vals = 8 THEN
1421 DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor_id,l_col_vals,l_column_value8);
1422 wf_data_string := wf_data_string ||l_column_prompt8||'='||trim(l_column_value8);
1423 data_string := data_string ||l_delimiter||l_column_name8||'='||trim(l_column_value8);
1424 ELSIF l_col_vals = 9 THEN
1425 wf_data_string := wf_data_string ||l_column_prompt9||'='||trim(l_column_value9);
1426 DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor_id,l_col_vals,l_column_value9);
1427 data_string := data_string ||l_delimiter||l_column_name9||'='||trim(l_column_value9);
1428 ELSIF l_col_vals = 10 THEN
1429 DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor_id,l_col_vals,l_column_value10);
1430 wf_data_string := wf_data_string ||l_column_prompt10||'='||trim(l_column_value10);
1431 data_string := data_string ||l_delimiter||l_column_name10||'='||trim(l_column_value10);
1432 END IF;
1433 END LOOP;
1434 l_no_rows :=l_no_rows + 1;
1435 END LOOP;
1436 P_DATASTRING := data_string;
1437 P_WFSTRING := wf_data_string;
1438 EXCEPTION WHEN OTHERS THEN
1439 data_string := 'SQL_ERROR';
1440 Raise WF_ERROR;
1441 END;
1442 EXCEPTION WHEN WF_ERROR THEN
1443 BEGIN
1444 IF data_string = 'SQL_ERROR' THEN
1445 error_message := FND_MESSAGE.GET_STRING('GMA','GMA_WF_CTRL_SQL_ERR');
1446 FND_MESSAGE.SET_NAME('GMA','GMA_WF_CTRL_SQL_ERR');
1447 ELSIF data_string = 'MULTI_ROWS_ERROR' THEN
1448 error_message := FND_MESSAGE.GET_STRING('GMA','GMA_WF_CTRL_MULTI_ROW_ERR');
1449 FND_MESSAGE.SET_NAME('GMA','GMA_WF_CTRL_MULTI_ROW_ERR');
1450 ELSIF data_string = 'PROFILE_ERROR' THEN
1451 error_message := FND_MESSAGE.GET_STRING('GMA','GMA_WF_CTRL_PROF_ERR');
1452 FND_MESSAGE.SET_NAME('GMA','GMA_WF_CTRL_PROF_ERR');
1453 END IF;
1454 END;
1455 WF_CORE.CONTEXT ('gma_wfstd_p','WF_GET_CONTORL_PARAMS',P_WF_ITEM_TYPE,P_PROCESS_NAME,P_ACTIVITY_NAME,error_message);
1456 app_exception.raise_exception;
1457 WHEN OTHERS THEN
1458 WF_CORE.CONTEXT ('gma_wfstd_p','WF_GET_CONTORL_PARAMS',P_WF_ITEM_TYPE,P_PROCESS_NAME,P_ACTIVITY_NAME,SQLERRM(SQLCODE));
1459 app_exception.raise_exception;
1460 END WF_GET_CONTORL_PARAMS;
1461 END gma_wfstd_p;