1 PACKAGE BODY PO_WF_FUN AS
2 /* $Header: powffunb.pls 120.0 2005/06/01 13:23:00 appldev noship $ */
3
4 PROCEDURE EXTRACT_STR(in_str varchar2,
5 out_str out NOCOPY varchar2)
6 IS
7 x_left number;
8 x_right number;
9 BEGIN
10 select instr(in_str, ''''), instr(in_str,'''',1,2)
11 into x_left, x_right
12 from dual;
13
14 select substr(in_str,x_left+1, x_right-x_left-1)
15 into out_str
16 from dual;
17
18 END EXTRACT_STR;
19
20
21 PROCEDURE PRINT_FUNCTION(x_item_type varchar2)
22 IS
23 x_wf_function wf_functions_cursor%rowtype;
24 x_wf_function_code wf_function_codes_cursor%rowtype;
25 x_line_s number;
26 x_line_e number;
27 x_count number :=0 ;
28 x_text varchar2(300);
29 x_temp varchar2(300) :=x_item_type;
30 x_item_type_name varchar2(200);
31 x_function_name varchar2(200);
32 l_apps_schema_name fnd_oracle_userid.oracle_username%type; --bug4025028
33
34 BEGIN
35
36 if (x_item_type like 'ALL') then
37 x_temp := '%';
38 end if;
39 --bug4025028 Start
40 -- Deriving the APPS Universal Schema name
41 -- This value would be used in queries below instead of using hardcoded 'APPS'
42 BEGIN
43 SELECT oracle_username
44 INTO l_apps_schema_name
45 FROM fnd_oracle_userid
46 WHERE read_only_flag = 'U';
47 EXCEPTION
48 WHEN OTHERS THEN
49 RAISE;
50 END;
51 --bug4025028 End
52 open wf_functions_cursor(x_temp);
53
54 loop
55
56 fetch wf_functions_cursor into x_wf_function;
57 exit when wf_functions_cursor%notfound;
58
59 select display_name
60 into x_item_type_name
61 from wf_item_types_tl
62 where name = x_wf_function.item_type
63 and language = 'US';
64
65 select distinct display_name
66 into x_function_name
67 from wf_activities_tl
68 where item_type = x_wf_function.item_type
69 and name = x_wf_function.name
70 and language = 'US' ;
71
72 select min(line)
73 into x_line_s
74 from all_source
75 where name= x_wf_function.package_name
76 and owner= l_apps_schema_name --bug4025028
77 and type='PACKAGE BODY'
78 and upper(text) like '%PROCEDURE'||'% '||x_wf_function.procedure_name||'%' ;
79
80 select max(line)
81 into x_line_e
82 from all_source
83 where name= x_wf_function.package_name
84 and owner= l_apps_schema_name --bug4025028
85 and type='PACKAGE BODY'
86 and (upper(text) like '%END'||'% '||x_wf_function.procedure_name||'%');
87
88 open wf_function_codes_cursor(x_wf_function.package_name, x_line_s, x_line_e,l_apps_schema_name);
89
90 loop
91 fetch wf_function_codes_cursor into x_wf_function_code;
92 exit when wf_function_codes_cursor%NOTFOUND;
93
94 if(x_count =0) then
95 dbms_output.put_line(' /***************************************************/');
96 dbms_output.put_line(' Item type '||x_item_type_name);
97 dbms_output.put_line(' Item type(Internal name) '||x_wf_function.item_type);
98 dbms_output.put_line(' Function Activity '||x_function_name);
99 dbms_output.put_line(' Package '||x_wf_function.package_name);
100 dbms_output.put_line(' Procedure '||x_wf_function.procedure_name);
101 dbms_output.put_line(' /***************************************************/');
102 x_count :=1;
103 end if;
104
105 if (upper(x_wf_function_code.text) like '%SETITEMATTR%' ) then
106 select text
107 into x_text
108 from all_source
109 where name = x_wf_function.package_name
110 and owner= l_apps_schema_name --bug4025028
111 and type='PACKAGE BODY'
112 and line >=x_wf_function_code.line and line <= (x_wf_function_code.line+5)
113 and upper(text) like '%ANAME%';
114
115 extract_str(x_text,x_temp);
116 dbms_output.put_line('SETITEMATTR '||x_temp);
117
118 elsif (upper(x_wf_function_code.text) like '%GETITEMATTR%') then
119 select text
120 into x_text
121 from all_source
122 where name = x_wf_function.package_name
123 and owner= l_apps_schema_name --bug4025028
124 and type='PACKAGE BODY'
125 and line >=x_wf_function_code.line and line <= (x_wf_function_code.line+3)
126 and upper(text) like '%ANAME%';
127
128 extract_str(x_text,x_temp);
129 dbms_output.put_line('GETITEMATTR '||x_temp);
130 else
131 dbms_output.put_line(x_wf_function_code.text);
132 end if;
133
134 end loop;
135
136 close wf_function_codes_cursor;
137
138 dbms_output.put_line(' ');
139 x_count := 0;
140 end loop;
141
142 close wf_functions_cursor;
143
144 EXCEPTION
145
146 WHEN OTHERS THEN
147 dbms_output.put_line ('In exception');
148 raise_application_error(-20001,sqlerrm||'---');
149 END PRINT_FUNCTION;
150
151
152 END PO_WF_FUN;