[Home] [Help]
PACKAGE BODY: APPS.FND_CONC_SUMMARIZER
Source
1 package body FND_CONC_SUMMARIZER as
2 /* $Header: AFCPSUMB.pls 120.2 2006/02/02 16:22:16 vvengala noship $ */
3
4 -- Declare a record type to contain name value pair
5 TYPE sum_record_type is RECORD
6 (name varchar2(2000),
7 value number);
8
9 -- Declare a PL SQL table type having rows of the record type
10 TYPE sum_table_type is TABLE of sum_record_type
11 index by binary_integer;
12
13 -- P_SUMMARIZER is a PL SQL table
14 P_SUMMARIZERS sum_table_type;
15 P_COUNT number := 0;
16
17 --
18 -- Function
19 -- prepare_str
20 -- Purpose
21 -- Return varchar of name || '=' || value || ';'
22 -- Arguments
23 -- Name varchar2 name of the string
24 -- value varchar2 value of the name
25 -- Returns
26 -- varchar2
27 --
28
29 function prepare_string(name in varchar2,
30 value in varchar2) return varchar2
31 is
32 begin
33 return name || '=' || value || ';';
34 end;
35
36 --
37 -- Function
38 -- execute_summarizer
39 -- Purpose
40 -- Executes the summarizer procedure provided as an argument
41 -- and return varchar of ';' separated name=value pairs
42 -- Arguments
43 -- sum_proc varchar2 Name of Summarizer Procedure
44 -- Notes
45 -- Return varchar2.
46 --
47
48 function execute_summarizer(sum_proc varchar2)
49 return varchar2
50 is
51 empty_sum_array sum_table_type;
52 return_str varchar2(10000) := '';
53 str varchar2(100);
54 name varchar2(2000);
55 value number;
56 begin
57 -- Initialize table count
58 P_COUNT := 0;
59
60 -- Initialize pl/sql table so that it will clear the previous table contents
61 P_SUMMARIZERS := empty_sum_array;
62
63 -- Create an anonymous pl/sql block to call the procedure sum_proc
64 str := 'begin '|| UPPER(sum_proc) || '(); end;';
65
66 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
67 fnd_message.set_name ('FND', 'CALLING_SUMMARIZER_PROC');
68 fnd_message.set_token ('PROCEDURE',sum_proc, FALSE);
69 FND_LOG.MESSAGE(FND_LOG.LEVEL_STATEMENT, ' FND_CONC_SUMMARIZER.execute_summarizer',TRUE);
70 end if;
71
72 -- Use Dynamic sql to execute the procedure passed as an argument
73 EXECUTE IMMEDIATE str;
74
75 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
76 fnd_message.set_name ('FND','RETURNING_SUMMARIZER_PROC');
77 fnd_message.set_token ('PROCEDURE',sum_proc, FALSE);
78 FND_LOG.MESSAGE(FND_LOG.LEVEL_STATEMENT, ' FND_CONC_SUMMARIZER.execute_summarizer',TRUE);
79 end if;
80
81 -- Parse the pl/sql table to return name=value separated by ;
82 for i in 1..P_COUNT loop
83 name := P_SUMMARIZERS(i).name;
84 value := P_SUMMARIZERS(i).value;
85
86 -- Call prepare_string to make name=value; string
87 return_str := return_str || prepare_string(name,to_char(value));
88 end loop;
89
90 -- remove last ';' character
91 return_str := substr(return_str,1,length(return_str)-1);
92
93 -- return parsed string
94 return return_str;
95 exception
96
97 when others then
98 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
99 fnd_message.set_name ('FND', 'SQL-Generic error');
100 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
101 fnd_message.set_token ('REASON', sqlerrm, FALSE);
102 fnd_message.set_token ( 'ROUTINE',' FND_CONC_SUMMARIZER.execute_summarizer', FALSE);
103 FND_LOG.MESSAGE(FND_LOG.LEVEL_STATEMENT,' FND_CONC_SUMMARIZER.execute_summarizer',FALSE);
104 end if;
105 return return_str;
106 end;
107
108 --
109 -- Procedure
110 -- insert_row
111 -- Purpose
112 -- Insert a row in PL/SQL table P_SUMMARIZER
113 -- Arguments
114 -- Name varchar2 name of the string
115 -- value varchar2 value of the name
116 --
117
118 procedure insert_row(name in varchar2,
119 value in varchar2) as
120 begin
121 P_COUNT := P_COUNT+1;
122 P_SUMMARIZERS(P_COUNT).name := name;
123 P_SUMMARIZERS(P_COUNT).value := value;
124 end;
125
126 --
127 -- Function
128 -- purge_program
129 -- Purpose
130 -- Sample Summarizer Procedure to insert a row in PL/SQL table P_SUMMARIZER
131 -- Arguments
132 -- None
133 --
134
135 PROCEDURE purge_program is
136 cnt number := 0;
137 --temp_name varchar2(100);
138 begin
139
140 --temp_name := fnd_message.get_string('FND', 'FND_CONC_REQUESTS');
141
142 -- FND_CONCURRENT_REQUEST table count
143 select count(*)
144 into cnt
145 from fnd_concurrent_requests
146 where phase_code = 'C';
147
148 insert_row('Concurrent Requests', to_char(cnt));
149
150 --temp_name := fnd_message.get_string('FND', 'FND_CONC_PROCESSES');
151
152 -- FND_CONCURRENT_PROCESSES
153 select count(*)
154 into cnt
155 from fnd_concurrent_processes
156 where process_status_code not in ('A', 'C', 'T', 'M');
157
158 insert_row('Concurrent Processes', to_char(cnt));
159
160 --temp_name := fnd_message.get_string('FND', 'FND_CRM_HISTORY');
161
162 -- FND_CRM_HISTORY
163 select count(*)
164 into cnt
165 from fnd_crm_history
166 where work_start < sysdate -1 ;
167
168 insert_row('Conflict Resolution History', to_char(cnt));
169
170 --temp_name := fnd_message.get_string('FND', 'FND_TM_EVENTS');
171
172 -- FND_TM_EVENTS
173 select count(*)
174 into cnt
175 from fnd_tm_events
176 where event_type in (1,2,3,4) and timestamp < sysdate -1 ;
177
178 insert_row('Transaction Management Events', to_char(cnt));
179
180 --temp_name := fnd_message.get_string('FND', 'FND_TEMP_FILES');
181
182 -- FND_TEMP_FILES
183 select count(*)
184 into cnt
185 from fnd_temp_files
186 WHERE TYPE <> 'R'
187 AND session_id NOT in (SELECT SID FROM v$session);
188
189 insert_row('Temporary Files', to_char(cnt));
190
191 end;
192
193 end FND_CONC_SUMMARIZER;