1 PACKAGE BODY okc_wf as
2 /*$Header: OKCRWFSB.pls 120.3 2011/03/10 18:08:27 harchand noship $*/
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 --
7 -- Package Variables
8 --
9 -- the string to be sent to wf
10 -- the string should be built in format readable for the package
11 wf_string varchar2(4000):='';
12
13 -- public proc - see in spec
14 procedure init_wf_string
15 is
16 begin
17 wf_string:='';
18 end;
19
20 -- public proc - see in spec
21 procedure init_wf_string(p_wf in varchar2)
22 is
23 begin
24 wf_string:=p_wf;
25 end;
26
27 -- public proc - see in spec
28 function get_wf_string return varchar2
29 is
30 begin
31 return wf_string;
32 end;
33
34 -- public proc - see in spec
35 procedure init_wf_header(p_head in varchar2)
36 is
37 begin
38 wf_string:='\h='||p_head;
39 end;
40
41 -- public proc - see in spec
42 procedure append_wf_string( p_dnum in number,
43 p_dname in varchar2,
44 p_dtype in varchar2,
45 p_dvalue in varchar2
46 )
47 is
48 begin
49 if upper(p_dtype) not in ('CHAR','NUMBER','DATE') then return; end if;
50 wf_string:=wf_string|| '\#='||p_dnum||
51 '\t='||upper(substr(p_dtype,1,1))||
52 '\n='||upper(p_dname)||
53 '\l='||length(p_dvalue)||
54 '\v='||p_dvalue;
55 end; -- returns parameter name if the parameter has wrong type
56
57 -- private func
58 function get_start_pos( p_num in number,
59 p_wf in varchar2
60 ) return number
61 is
62 i number:=1;
63 l_pos1 number:=1;
64 l_pos2 number:=1;
65 len number:=0;
66 begin
67 for i in 2..p_num
68 loop
69 l_pos1:=instr(p_wf,'\#='||(i-1)||'\t',l_pos1,1); -- next start point
70 l_pos1:=instr(p_wf,'\l=',l_pos1,1)+3; -- next line position
71 l_pos2:=instr(p_wf,'\v=',l_pos1,1); -- next value position -3
72 len:=to_number(substr(p_wf,l_pos1,l_pos2-l_pos1)); -- length of value string
73 l_pos1:=l_pos2+len+3; -- after value position
74 end loop;
75 l_pos1:=instr(p_wf,'\#='||p_num||'\t',l_pos1,1); -- next start point
76 return l_pos1;
77 end; -- returns 0 if cannot find
78
79 -- private func
80 function get_start_pos( p_num in number
81 ) return number
82 is
83 begin
84 return get_start_pos(p_num,wf_string);
85 end; -- returns 0 if cannot find
86
87 -- private func
88 function get_type_pos( p_num in number,
89 p_wf in varchar2
90 ) return number
91 is
92 l_pos number;
93 begin
94 l_pos:=get_start_pos(p_num,p_wf);
95 l_pos:=instr(p_wf,'\#='||to_char(p_num)||'\t',l_pos,1);
96 if l_pos = 0 then return 0; -- not found - go out
97 end if;
98 return (instr(p_wf,'\t=',l_pos,1)+3);
99 end; -- returns 0 if cannot find
100
101 -- private func
102 function get_type_pos( p_num in number
103 ) return number
104 is
105 begin
106 return get_type_pos(p_num,wf_string);
107 end; -- returns 0 if cannot find
108
109 -- private func
110 function get_name_pos( p_num in number,
111 p_wf in varchar2
112 ) return number
113 is
114 l_pos number;
115 begin
116 l_pos:=get_type_pos(p_num,p_wf);
117 if l_pos = 0 then return 0; -- not found - go out
118 end if;
119 return (instr(p_wf,'\n=',l_pos,1)+3);
120 end; -- returns 0 if cannot find
121
122 -- private func
123 function get_name_pos( p_num in number
124 ) return number
125 is
126 begin
127 return get_name_pos(p_num,wf_string);
128 end; -- returns 0 if cannot find
129
130 -- private func
131 function get_length_pos( p_num in number,
132 p_wf in varchar2
133 ) return number
134 is
135 l_pos number;
136 begin
137 l_pos:=get_name_pos(p_num,p_wf);
138 if l_pos = 0 then return 0; -- not found - go out
139 end if;
140 return (instr(p_wf,'\l=',l_pos,1)+3);
141 end; -- returns 0 if cannot find
142
143 -- private func
144 function get_length_pos( p_num in number
145 ) return number
146 is
147 begin
148 return get_length_pos(p_num,wf_string);
149 end; -- returns 0 if cannot find
150
151 -- private func
152 function get_value_pos( p_num in number,
153 p_wf in varchar2
154 ) return number
155 is
156 l_pos number;
157 begin
158 l_pos:=get_type_pos(p_num,p_wf);
159 if l_pos = 0 then return 0; -- not found - go out
160 end if;
161 return (instr(p_wf,'\v=',l_pos,1)+3);
162 end; -- returns 0 if cannot find
163
164 -- private func
165 function get_value_pos( p_num in number
166 ) return number
167 is
168 begin
169 return get_value_pos(p_num,wf_string);
170 end; -- returns 0 if cannot find
171
172 -- private func
173 function get_header( p_wf in varchar2
174 ) return varchar2
175 is
176 l_num number;
177 begin
178 if instr(p_wf,'\h=') = 0 then return null;
179 end if;
180 l_num:=instr(p_wf,'\#=1\t',1,1)-4;
181 return (substr(p_wf,4,l_num));
182 end; -- returns null if cannot find
183
184 -- private func
185 function get_header return varchar2
186 is
187 begin
188 return get_header(wf_string);
189 end; -- returns null if cannot find
190
191 -- private func
192 function get_type( p_num in number,
193 p_wf in varchar2
194 ) return varchar2
195 is
196 l_pos number;
197 begin
198 l_pos:=get_type_pos(p_num, p_wf);
199 if l_pos = 0 then return null;
200 end if;
201 return (substr(p_wf,l_pos,1));
202 end; -- returns null if cannot find
203
204 -- private func
205 function get_type( p_num in number
206 ) return varchar2
207 is
208 begin
209 return get_type(p_num,wf_string);
210 end; -- returns null if cannot find
211
212 -- private func
213 function get_name( p_num in number,
214 p_wf in varchar2
215 ) return varchar2
216 is
217 l_pos1 number;
218 l_pos2 number;
219 begin
220 l_pos1:=get_name_pos(p_num, p_wf);
221 l_pos2:=get_length_pos(p_num, p_wf)-3;
222 if l_pos1 = 0 or l_pos2 = 0 then return null;
223 end if;
224 return (substr(p_wf,l_pos1,l_pos2-l_pos1));
225 end; -- returns null if cannot find
226
227 -- private func
228 function get_name( p_num in number
229 ) return varchar2
230 is
231 begin
232 return get_name(p_num,wf_string);
233 end; -- returns null if cannot find
234 -- private func
235 function get_length( p_num in number,
236 p_wf in varchar2
237 ) return number
238 is
239 l_pos1 number;
240 l_pos2 number;
241 begin
242 l_pos1:=get_length_pos(p_num, p_wf);
243 l_pos2:=get_value_pos(p_num, p_wf)-3;
244 if l_pos1 = 0 or l_pos2 = 0 then return 0;
245 end if;
246 return (to_number(substr(p_wf,l_pos1,l_pos2-l_pos1)));
247 end; -- returns 0 if cannot find
248
249 -- private func
250 function get_length( p_num in number
251 ) return number
252 is
253 begin
254 return get_length(p_num,wf_string);
255 end; -- returns 0 if cannot find
256
257 -- private func
258 function get_value( p_num in number,
259 p_wf in varchar2
260 ) return varchar2
261 is
262 l_pos number;
263 l_length number;
264 begin
265 l_pos:=get_value_pos(p_num, p_wf);
266 l_length:=get_length(p_num, p_wf);
267 if l_pos = 0 or l_length = 0 then return null;
268 end if;
269 return substr(p_wf,l_pos,l_length);
270 end; -- returns null if cannot find
271
272 -- private func
273 function get_value( p_num in number
274 ) return varchar2
275 is
276 begin
277 return get_value(p_num,wf_string);
278 end; -- returns null if cannot find
279
280 -- public func - see in spec
281 function Nvalue(p_num in number) return number
282 is
283 l_value varchar2(255);
284 begin
285 l_value:=get_value(p_num,wf_string);
286 if l_value is not null then
287 if l_value = 'OKC_API.G_MISS_NUM' then
288 return OKC_API.G_MISS_NUM;
289 end if;
290 if l_value = 'NULL' then
291 return null;
292 end if;
293 end if;
294 return to_number(l_value);
295 end; -- returns null if no value
296
297 -- public func - see in spec
298 function Dvalue(p_num in number) return date
299 is
300 l_value varchar2(255);
301 begin
302 l_value:=get_value(p_num,wf_string);
303 if l_value is not null then
304 if l_value = 'OKC_API.G_MISS_DATE' then
305 return OKC_API.G_MISS_DATE;
306 end if;
307 if l_value = 'NULL' then
308 return null;
309 end if;
310 end if;
311 return to_date(l_value,'YYYY/MM/DD');
312 end; -- returns null if no value
313
314 -- public func - see in spec
315 function Cvalue(p_num in number) return varchar2
316 is
317 l_value varchar2(255);
318 begin
319 l_value:=get_value(p_num,wf_string);
320 if l_value is not null then
321 if l_value = 'OKC_API.G_MISS_CHAR' then
322 return OKC_API.G_MISS_CHAR;
323 end if;
324 if l_value = 'NULL' then
325 return null;
326 end if;
327 end if;
328 return l_value;
329 end; -- returns null if no value
330
331 -- public func - see in spec
332 function build_wf_string( p_outcome_name in varchar2,
333 p_outcome_tbl in p_outcometbl_type
334 ) return varchar2
335 is
336 i number:=0;
337 begin
338 init_wf_string;
339 init_wf_header(p_outcome_name);
340 for i in 1..p_outcome_tbl.COUNT
341 loop
342 append_wf_string( i,
343 p_outcome_tbl(i).name,
344 p_outcome_tbl(i).data_type,
345 p_outcome_tbl(i).value);
346 end loop;
347 return wf_string;
348 end;
349
350 -- private func
351 function prebuild_wf_plsql( p_wf in varchar2
352 ) return varchar2
353 is
354 i number:=1; -- start point
355 l_plsql varchar2(4000);
356 l_name varchar2(255);
357 begin
358 l_plsql := get_header(p_wf);
359 if l_plsql is null then return null; -- no header go out
360 end if;
361 l_plsql := l_plsql || '(';
362 while get_type_pos(i,p_wf) <> 0
363 loop
364 l_name := get_name(i,p_wf);
365 if l_name is null then
366 l_plsql :=
367 l_plsql||'okc_wf.'||get_type(i,p_wf)||'value('||i||'), ';
368 else
369 l_plsql :=
370 l_plsql||l_name||' => okc_wf.'||get_type(i,p_wf)||'value('||i||'), ';
371 end if;
372 i:=i+1;
373 end loop;
374 return l_plsql;
375 end;
376
377 -- public func - see in spec
378 function prebuild_wf_plsql return varchar2
379 is
380 begin
381 return prebuild_wf_plsql(wf_string);
382 end;
383
384 -- public func - see in spec
385 function build_wf_plsql(p_prebuilt_wf_plsql in varchar2) return varchar2
386 is
387 l_plsql varchar2(4000);
388 begin
389 if p_prebuilt_wf_plsql is null then return null; -- no prebuilt - go out
390 end if;
391 l_plsql := p_prebuilt_wf_plsql;
392 -- add standard trail
393 l_plsql := l_plsql|| 'P_INIT_MSG_LIST => OKC_API.G_FALSE, '||
394 'X_RETURN_STATUS => :V_RETURN_STATUS, '||
395 'X_MSG_COUNT => V_MSG_COUNT, '||
396 'X_MSG_DATA => V_MSG_DATA);';
397 l_plsql := 'Begin ' ||l_plsql||' End;'; -- add block frame
398 return l_plsql;
399 end; -- returns null if wrong
400
401 -- public proc
402 function exec_wf_plsql(p_proc in varchar2) return varchar2
403 is
404 x_return_status varchar2(1);
405 begin
406 savepoint exec_plsql_call;
407 begin EXECUTE IMMEDIATE P_PROC USING IN OUT x_return_status;
408 if (x_return_status in ('E','U')) then
409 rollback to exec_plsql_call;
410 end if;
411 return x_return_status;
412 exception when others then
413 rollback to exec_plsql_call;
414 return x_return_status;
415 end;
416 end;
417
418 end okc_wf;
[Home] [Help]